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:

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

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.

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 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