SQL Compare 10

Version control for databases

Red Gate now offers SQL Source Control - a tool to source control your database, within SSMS

The situation:

You have a number of developers working on updates to your database schema. You need to prevent two developers making simultaneous changes to the SQL objects during the development process, so you use a source control program. With developers making concurrent changes, you have a substantial change management overhead, and creating migration scripts is a time-consuming process.

There may also be a need to restrict access to only certain schema objects in source control and as a further security measure, to compile a change history for the sake of auditing that details who did what and when.

If a developer needs to revert to an earlier version of a SQL object, such as a view or stored procedure, the entire database must be considered, accounting for dependencies. Manually source-controlling change scripts or object creation scripts does not account for referential integrity. There would be no cross-referencing to dependencies.

Reverting to an earlier version of the object by relying on only the line-by-line differences in the SQL script could render the entire database ineffective.

Key challenges:

• Keeping records of different versions of databases throughout the development process without storing a copy of the entire database each time.
• Enabling developers to easily share schema changes so development does not get out of step.
• Maintaining the integrity of the database when a developer needs to revert to a previous version.

How we can solve it:

• Use SQL Source Control to keep the database schema under source control.
  SQL Source Control is an add-in for SQL Server Management Studio that makes it simple to commit schema changes and get the latest version as you work. Because SQL Source Control is powered by our SQL Compare technology, you can use SQL Compare Pro® to automate deployment and migration of your source-controlled database.
• If you need to revert to a previous version of an object, use SQL Compare Pro® to select the object(s) in question and synchronize directly from the original SQL script under source control to the test database.

Benefits of our solution:

• SQL Source Control lets you work directly in SQL Server Management Studio, not with offline scripts.
• SQL Source Control works with Team Foundation Server & Subversion, so you can take advantage of your existing source control systems.
• Teams can easily stay in sync, and change management is simplified as everyone can quickly get the latest version of the database from source control, and see what's changed.
• A complete history of database development and changes is recorded and can provide a valuable audit trail.
• Working with SQL Source Control and their own dedicated local copies of the development database gives developers a safe sandbox to make changes without impacting the rest of the team.

Support & documentation:

For more information on working with database source control, see:

• The SQL Source Control evaluation center
• SQL Compare - compare and synchronize database schemas
• Setting up SQL Source Control with Subversion or with Team Foundation Server