Synchronizing SQL Server environments
28 november 2016 2016-11-28 14:33Synchronizing 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 tasks[ref]I've worked once in an environment where these syncs were done manually. Of course I interchanged the two environments, ending up with production data being replaced with test data..[/ref] 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:
- Back up databases on source environment
- Transfer backups across the network towards the target environment
- Restore databases on target environment, overwriting existing databases
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.
Import-Module Sqlps -DisableNameChecking; $databasesToSync = @("NameOfStagingDatabase", "NameOfDWHDatabase", "NameOfDMDatabase", "FeelFreeToAddAsMuchAsYouNeedHere") $backupInst = "ProdServer\InstanceName" $restoreInst = "AccServer\InstanceName" $filesystem_prefix = "Microsoft.PowerShell.Core\FileSystem::" $bdrive_local = "E:" $bdrive_remote = "\\" + $backupInst + "\e$" $bdir = "Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup" $rdrive_local = "E:" $rdir = $bdir $rdrive_remote = "\\" + $restoreInst + "\e$" $backupSvr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $backupInst foreach($databaseToSync in $databasesToSync) { $db = $backupSvr.Databases[$databaseToSync] $dbname = $db.Name $dt = get-date -format yyyyMMddHHmmss $backupItem_remote = $bdrive_remote + "\" + $bdir + "\" + $dbname + "_db_" + $dt + ".bak" $backupItem_local = $rdrive_local + "\" + $bdir + "\" + $dbname + "_db_" + $dt + ".bak" $restoreItem_remote = $rdrive_remote + "\" + $rdir + "\" + $dbname + "_db_" + $dt + ".bak" $restoreItem_local = $rdrive_local + "\" + $rdir + "\" + $dbname + "_db_" + $dt + ".bak" $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup') $dbbk.Action = 'Database' $dbbk.BackupSetDescription = "Full backup of " + $dbname $dbbk.BackupSetName = $dbname + " Backup" $dbbk.Database = $dbname $dbbk.MediaDescription = "Disk" $dbbk.CompressionOption = 1 $dbbk.CopyOnly = $TRUE $dbbk.Devices.AddDevice($backupItem_local, 'File') Write-Host "Backup of" $databaseToSync "started @ " $backupInst $dbbk.SqlBackup($backupSvr) Write-Host "Backup finished " Write-Host "Moving backup" $databaseToSync "across network" Move-Item ($filesystem_prefix + $backupItem_remote) ($filesystem_prefix + $restoreItem_remote) -force Write-Host "Move finished" $restoreSvr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $restoreInst $dbrs = new-object Microsoft.SqlServer.Management.Smo.Restore $dbrs.Devices.AddDevice($restoreItem_local , [Microsoft.SqlServer.Management.Smo.DeviceType]::File) $dbrs.Database = $dbname $dbrs.ReplaceDatabase = $TRUE $dbrs.NoRecovery = $FALSE Write-Host "Setting" $databaseToSync "in single-user mode" $restore_db = $restoreSvr.Databases[$databaseToSync] if($restore_db.UserAccess -ne [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Single) { $restore_db.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Single; $restore_db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately); $restore_db.Refresh(); } Write-Host "Restoring " $databaseToSync "..." $dbrs.SqlRestore($restoreSvr) Write-Host "Restore finished." Write-Host "Setting" $databaseToSync "in multi-user mode..." $restore_db = $restoreSvr.Databases[$databaseToSync] $restore_db.UserAccess = [Microsoft.SqlServer.Management.Smo.DatabaseUserAccess]::Multiple; $restore_db.Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately); $restore_db.Refresh(); } Write-Host "All done." Write-Host "Press any key to exit." $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")
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:
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.
- Call automatically after each sync (i.e. within the PowerShell-script)
- Enhance the script so that the same databases are used as the "update" stp uses[ref]I think this functionality is a matter of "scratch your own itch" - as I'm currently only handling my own databases, it doesn't itch that much. Pull requests will be accepted though.[/ref]
Basically the "restore rights" SQL script looks like this:
USE MyJustRestoredDatabaseName -- Currently you need to execute this block once for every database you transfer. GO -- Drop user rights transferred along from other environment DECLARE @DropStatement NVARCHAR(MAX) = ''; SELECT @DropStatement += 'DROP USER [' + [name] + ']; ' FROM MyJustRestoredDatabaseName.sys.sysusers WHERE issqlrole = 0 AND sid IS NOT NULL AND name NOT IN ('dbo', 'guest'); exec sp_executesql @DropStatement; GO -- Restore users according to metadata database DECLARE @CreateUsersStatement NVARCHAR(MAX) = ''; SELECT @CreateUsersStatement += 'CREATE USER [' + LoginIdentity + '] FOR LOGIN [' + LoginIdentity + ']; ' FROM MetadataDatabase.[ops].[LoginDatabaseRoles] WHERE DatabaseName = 'MyJustRestoredDatabaseName' GROUP BY LoginIdentity exec sp_executesql @CreateUsersStatement; -- Restore roles according to metadata database DECLARE @AlterRoleStatement NVARCHAR(MAX) = ''; SELECT @AlterRoleStatement += 'ALTER ROLE [' + DatabaseRole + '] ADD MEMBER [' + LoginIdentity + ']; ' FROM MetadataDatabase.[ops].[LoginDatabaseRoles] WHERE DatabaseName = 'MyJustRestoredDatabaseName' GROUP BY LoginIdentity, DatabaseRole exec sp_executesql @AlterRoleStatement;
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