How to automatically compare and synchronize multiple databases on different SQL Server instances

Challenge

It’s often quite a challenge to keep all SQL databases located on different SQL Servers in sync. As time goes by, a lot of schema and data changes are made on QA databases on daily basis that require to be in sync with Production databases.

To keep everything in sync, there should a system that would be either triggered or scheduled to run the comparison of all SQL databases and synchronize the ones were changes are detected. This system should be also aware of any dependencies during the synchronization in order to keep the SQL database integrity.

April 5, 2017

Automatically comparing and synchronizing data changes in a SQL Server database

For the second use case, when only data changes are made to the source control repository, a combination of 3rd party applications – ApexSQL Build and ApexSQL Data Diff, can be used to perform this Sync step. ApexSQL Build can be used to build a database from the source control repository along with its static data, while ApexSQL Data Diff can be used to synchronize data from the built database to a QA database.

January 13, 2017

How to commit and/or update SQL Server database static data to a source control repository

Challenge

In order to version static data, just like database objects e.g. Tables, a mechanism needs to be created to identify only those tables designated as “static” (see below) and to come up with a reproducible process, that will Commit the data in these tables to source control. This article covers this process

December 14, 2016

How to automatically synchronize the data in two SQL Server databases on a schedule

Challenge

It’s often needed to have a way of synchronizing data between SQL databases in real time, e.g. when a certain data is changed/added in one place (database) to be synchronized with another database. Changes need to be detected, automatically, on a schedule, and automatically synchronized with another database. This process should be fully automated and run totally unattended.

December 9, 2016

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 narrow a data comparison to affected rows only

When confronted with a disaster recovery scenario with a very large database, but a small group of effected records, an opportunity exists to both speed the process and reduce risk of further damaging data, by updating more rows than necessary, simply by narrowing the subset of compared records.

ApexSQL Data Diff offers the ability to narrow the rows that should be synchronized, so only affected rows are updated. But if the filtering isn’t done properly, you risk “false positives”, rows that you have flagged as different/changed and should be synchronized, even if they weren’t in the subset of affected rows. Synchronizing these rows will update “good” data and roll back any production changes that may have been made, further damaging your data.

November 6, 2015

How to compare SQL Server database tables with different names

When comparing live database or database backups using ApexSQL Data Diff, tables with the same name are compared automatically. But what happens with tables and columns with different names when compare SQL tables for differences?

By default, they are excluded from the comparison process and need to be mapped manually using the Object mapping feature. This feature also allows changing tables paired by default, i.e. unmapping them and creating customized comparison pairs. This can be helpful in scenarios where the same tables are differently named in the development and the production database, and data needs to be pushed from the development database to the production one.

April 2, 2015

Multiple SQL Server data recovery options

In the past two weeks we had some interesting Solution center articles describing Microsoft SQL Server topics – each described as the problem (or the challenge) users may encounter with SQL Server data recovery

Any of recovery options using Microsoft’s SQL Server Management Studio requires the availability of a full database backup. And, the backup must be restored in order to be used as a recovery data source

July 5, 2013

Recover deleted SQL data from a backup or from online database files

Recovering DELETED records is something we would all like to avoid, but no matter how careful we are, it can come to that. Having regular full database and transaction log backups is a necessary practice and a great advantage in this situation, but even then, bringing back the deleted records might not be easy

June 4, 2013