How we use SQL Server Data Tools

This post describes our process for developing databases with SQL Server Data Tools (SSDT) in Visual Studio.

For it to work, the conventions below must be respected.

• Use the live database as the gold standard for schema objects (and data).
• Deploy only database projects that have been built successfully.
• Deploy to a database that matches the schema of the live database.

At the beginning of a development iteration

1. Restore a copy of the live database onto the development computer.
2. Synchronise database project schema objects with the schema objects in the restored database.
3. Remove pre-deployment and post-deployment scripts from the database project.
4. Update the database project version number.
5. Build the database project.
6. If the build fails, fix the errors and rebuild.
7. If the build completes, check in the changes.

During a development iteration

1. Make changes to script files in the database project.
2. If the changes might result in data loss, write pre-deployment and post-deployment scripts to migrate the data.
3. Build the database project.
4. If the build fails, fix the errors and rebuild.
5. If the build succeeds, publish the changes onto the database on the development computer and test.

Interim releases to the test environment

1. Restore a copy of the live database from backup.
2. Build the database project.
3. Publish the database project onto the test server.

Deployment to the live environment

1. Back up the live database.
2. Build the database project.
3. Publish the database onto the live server.