Whenever there is something to be done in a production environment (like a quick UPDATE), or you wonder how you should organize environments – tips below may be useful.
Separation of environments
I assume that in most cases, there is a production environment (client using an app), testing / staging environment (client acceptance / final testing), and development environment or environments to mess around.
There are two ways to prevent making changes in a wrong environment: automation and separation.
The more automated builds, deploys, patches and updates are, there is less chance of a mistake.
Similarly, with separation. Major shortcut like “Let’s put all databases on one SQL Server instance” may backfire accordingly. Especially, if this refers to the development and production databases.
Data loss or a bug introduction is a concern when there is no separation.
For example, SQL Server Management Studio adds a database name to the SELECT TOP query by default.
Imagine adding to the developed application a query pointing to the production database, and then testing the app, thinking we still work on a test database. (Running an automated test for database names in SQL queries may help, but still.)
Same environment makes every action potentially dangerous.
Performance is another problem. Running development version and doing performance tests in the same environment where the production app is running, well…
Security and preserving processes will be hard if anyone who is developing an application can change anything in production, omitting QA, release changes etc.
Making the separation
-different virtual machine
-different database instance
-different database names between environments
-different passwords between environments (server, database)
More separation can be added on the development level, by adding support for different configurations in different environments. You don’t want your client to press the “Send empty e-mail campaign” to all customers during an application preview.
Another tip to quickly visualize what type of app we are currently running is to e.g. add the text “Test version 1.14.7” to the footer. No label = production.
Have different colors for different servers/databases in SQL Server Management Studio. This can be done by Views -> Registered Servers (for servers), or for more advanced combinations, third party tools will help, e.g. SSMS Tools Pack.
Protect the data
Obviously, having the most up do date backup possible.
If anything is shared and outside of source control (this can be a development database or a release text file), make a backup just in case.
Have a previous version secured, so you can roll back at any moment.
Backups are a long topic, starting from what, when and how, ending at a daily full copy of the production environment to test or replace in a critical situation. (The best way to validate a backup is to restore from it!)
A nice thing to be backed up before starting some critical task on production is doing a sanity check. Is everything prepared, is anyone else making a release right now, am I going to be interrupted soon, etc.
Few other tips for SQL Server Management Studio users:
-use different colors for servers as described above
-disconnecting from Object Explorer does not disconnect from the server; query window is the active connection
-use transactions for a data modification
BEGIN TRAN -- YOUR UPDATE QUERY HERE ROLLBACK TRAN
Even better transaction code would be
Begin tran deleteprod_yourname delete from table_name where 1=0 --and --0=1 --commit tran deleteprod_yourname
-First line adds responsibility
-Conditions for where are in new lines, so they can be easily switched by just commenting one of them (more difficult if where was in the one line)
-Default condition is always false just in case
-Commit is commented out as the transaction would serve no purpose if the script can be run in one go, without seeing how many rows have been affected
When working with many environments and making updates/deploys onto production, important are:
-previous version to roll back