Approaches

State-first approachMigrations-first approach
Summary

A CREATE script representing the definition of each object in your database is stored in the version control system of your choice. Deployment scripts are automatically generated by comparing the current state of a target database with the desired database state.

Pre- and post- deployment scripts enable flexibility for deployments where custom coding is required to effect the desired change.

Changes are captured in editable "migration" scripts, which are stored in the version control system of your choice. Typically, this is a series of numerically ordered ALTER scripts. In a deployment, migration scripts are applied in sequence to effect larger, iterative updates to a target database.

State-based programmable objects and an offline schema model reduce the number of individual migration scripts needed, enable users to compare source code with databases, and support identifying merge conflicts.

Key points

Focused on defining the desired end state of your database schema, not how the transition occurs.

A simple approach to get started with standardizing development practices, especially for teams who are experienced with SQL Compare and other state-based development tools.

Focused on how to get to the desired end state through precisely controlled individual migration scripts, which act as the building blocks for the eventual deployment script.

Ideal for complex database changes and environments with high uptime requirements, as there is no ambiguity in the code which will be executed in a deployment.

Tools needed

Use the SQL Source Control extension for SQL Server Management Studio to add state to a SQL Source Control project and commit database changes to your version control system.

Use SQL Change Automation's PowerShell cmdlets and add-ons for automation to implement continuous integration and automated deployments for your database changes.

Use the SQL Change Automation Visual Studio extension or the SQL Change Automation SQL Server Management Studio extension for database development to add changes to a SQL Change Automation project. Team members may collaborate on the same project using either or both extensions.

Use SQL Change Automation's PowerShell cmdlets and add-ons for automation to implement continuous integration and automated deployments for your database changes.

What to buy

SQL Toolbelt gives you access to all the tools needed and exclusively contains SQL Change Automation

Find out more about the SQL Toolbelt