Synchronizing SQL Server environments

When deploying changes through DTAP, you might need to sync your environments. For example, I sync my Acceptance environment before deploying a new increment, so I can easily see the effects of my new software on production data. To be honest, I'm not very good in these database management tasks1 so I have to automate these sync tasks. Here's how:

Getting the data across

Synchronizing two databases is not hard - especially when these databases are in fact data warehouses with scheduled batch ETL. My basic way of working here is:

  1. Back up databases on source environment
  2. Transfer backups across the network towards the target environment
  3. Restore databases on target environment, overwriting existing databases

movedatabases

When backing up databases, remind these need to be kept out of the regular backup line - you don't want to interfere with regular incremental backups, but instead do a full backup that's only meant to copy the database somewhere. So, remember to set "CopyOnly" to TRUE!

Below is my entire script for automated backup, move and restore across the network. All variables are set in the first 13 lines.

Recording Right User Rights

When databases are transferred to another environment, the user rights are transferred along. This is great for "real" backups, but not so much for our "copy only" usage here. In order to store which rights ought to be in place on which environment, I've put a small mechanism in place to record the original rights of the databases involved in this process.

The key artifact here is an environment-specific metadata database. Inside this database live five tables storing the users & rights as they should be in this specific environment. Also included is a stored procedure that updates the metadata tables:

rightsstorage

That's all, really.

Restoring Right Rights

In order to restore the rights as they should be, currently I'm still using a manual SQL script. It can be automated in two ways, but I haven't found time for that yet.

  1. Call automatically after each sync (i.e. within the PowerShell-script)
  2. Enhance the script so that the same databases are used as the "update" stp uses2

Basically the "restore rights" SQL script looks like this:

Notice the MetadataDatabase and MyJustRestoredDatabaseName here. Obviously you need to replace that with resp. your metadata database-name and the database just synced by the sync-script.

Conclusion

No rocket science today, but some scripts I find rather useful and can be used pretty easily without much configuration in other environments. Besides, the ability to transfer production data to another (preferably thoroughly shielded & sandboxed) environment is an important building block for setting up automated regression testing...

All sources are on GitHub: https://github.com/vstrien/database-sync

Founder of this blog. Business Intelligence consultant, developer, coach, trainer and speaker at events. Currently working at Dura Vermeer. Loves to explain things, providing insight in complex issues. Watches the ongoing development of the Microsoft Business Intelligence stack closely. Keeping an eye on Big Data, Data Science and IoT.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Next ArticleYour data is going places