Automatically comparing and synchronizing SQL Server database schema changes

If only schema changes are made in the source control repository, a 3rd party tool – ApexSQL Diff can be used to perform the schema synchronization. ApexSQL Diff is a tool that can be used to compare and synchronize schema differences between live SQL database, source control repositories, database backups, script folders and ApexSQL snapshots. It can handle any dependencies, triggers, etc. and create an error-free synchronization script, while ensuring a database integrity.

January 13, 2017

How to automatically monitor a SQL Server database for schema changes and create an audit log in source control

The challenge

As a DBA considering version controlling a database, there are a lot of challenges to setting up the team with software to check in changes, to manage the development environment (shared or dedicated models) and set the rules of the game – locking vs not locking etc. But before committing to this fully, a potentially preliminary stage would be to set up a centralized system, where the database was automatically committed to source control each night. This could be done by a single person and wouldn’t require developer participation or even knowledge.

January 10, 2017

How to automatically keep two SQL Server database schemas in sync

Challenge

When working on a SQL database development, there is sometimes a requirement to keep two databases in sync. For example, you have a development environment and want to automatically synchronize changes with a QA database frequently, so that tests can always be run on the most recent version. The implemented mechanism should handle this by detecting a specific schema change in the DEV database and automatically synchronizing with the QA database, fully unattended and on a schedule. The whole process should be run unattended and to be fully automated and the databases will be updated in near real time as we’ll schedule the process to run every 15 minutes

January 9, 2017

SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script

In this article, the last step of SQL Server database continuous integration (CI) workflow, or the first step of a continuous delivery (CD) workflow) the Sync step.

In the previous article, the Test step described how to create and run unit tests against a database. If all tests are passed, the tested database can be compared to a final QA environment or even Production databases, and a synchronization script will be created to publish the changes.

July 4, 2016

How to deploy changes directly to a SQL database from a source control repository

Challenge

One of the challenges these days is how to pull the latest changes (of SQL objects) from the source control repository and deploy them into a SQL database. This process is particularly helpful in the CI workflow Build step, when developers want to build a SQL database from the committed changes in the source control repository, so they can test if their changes compromised SQL objects from the source control repository or they will be built successfully.

April 27, 2016

How to work with SQL database source control labels

A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.

October 27, 2015