SQL Server: Database deployment best practices

Here are some of the SQL Server database deployment tips that have been very useful to me.

1. Store all your database object scripts as individual files under source control. This provides an excellent way to keep the database code organized and will also be very useful when auditing code changes. Another advantage would be that these scripts can be used to creating an instance of an application database with minimal amount of data.

2. Maintain a senior resource as a single point of contact to handle all SQL changes in non-DEV environments. This resource could also be responsible for code reviews and helping with code optimization. This provides an excellent way for enforce organizational best practices and it will also make it simple to keep track of changes in a given database environment.

3. Compare the database which is getting deployed with the last database that got updated in the software factory line. Comparing the schema and/or data will give you a contemporary view of the database state, thus helping you with a diligent way of preparing your deployment scripts. You can use tools like Redgate SQL-Compare, dbcomparer, etc for this purpose.

4. Make sure that the SQL scripts always successfully pass through the development, QA and stage environments before reaching the production/live environment. On a side note data in the production should be replicated (after scrubbing is necessary) to all the non-production environments as frequently as possible to achieve best level of code compatibility.

5. Always take a backup of the database, turn off replication agents and all SQL jobs, before deploying to the production/staging environment. This might add some overhead, but making this a habit will help with a error free, faster and a easy to restore deployment.

6. This may seem trivial by it is good practice to end every SQL Script with “GO”. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad-hoc session or script if this is the first GO [1].

7. Maintain the deployment scripts with a naming convention. This will help in organizing the scripts for executing them in predetermined sequence and will give you a good idea about what a script is intended to do. I usually name my files as follows: naming the scripts with number in the following format give established the order of execution and also give you an opportunity to add new script(s) in between any two scripts.

Convention: Script_[Number]_[Number]_[ObjectName]_[Operation].sql

• Script_100_001_table_employee_add.sql
• Script_200_001_spGetAllEmployees_update.sql
• Script_200_002_viewEmployeeDetails_update.sql

8. It is good practice to sandwich your SQL script in between a Transaction and Rollback/Commit block. One exception to this rule (if you will) would be when you have a script that performs a huge number of data manipulation operations. This could some times cause the transaction log to grow really big and cause the server to run out of space.

9. Create data validation scripts for the deployment changes. This way you do not have to wait for the application testing to respond to any errors.

10. I have used script consolidation for some of the deployments I was involved with. File consolidation helps in saving time when you have to execute a ton of script files. Here is the code for the SQL Merge tool.

11. Prepare a deployment document for establishing and organizing the deployment process with the following information; this will be useful with maintaining compliance with the Sarbanes-Oxley Act [2].

• Deployment Schedule
• Activity ownership
• Pre-deployment activities
• Deployment activities
• Post-deployment activities
• Rollback plan
• Contact list

References:
1. MSDN Online
2. Sarbanes-Oxley Act