Problem
Client
wants to move datacenters, so production DB needs to be migrated. So far, so
simple. However, there are some challenges:
1) DB is in AlwaysON
Availability Group
2) DB is TDE encrypted
3) New datacenter is set up domainless
Solution
Point 3 is
where it gets tricky. To accommodate this, a colleague built the cluster for
me. We set up the same user accounts in Windows on both nodes with the same
passwords. Then I installed SQL, transferred the logins that would still be
needed (Transfer
logins and passwords between instances - SQL Server | Microsoft Docs) and
then proceeded setting up the availability group. The steps are actually not
drastically different from a setup on domain-joined servers. First create a
master key, then restore the certificates required for TDE on both nodes. After
that I restored the production backups to node 1. Now we come to the extra step
for the domainless setup: we need to create
certificates on each server for the counterpart to connect to the mirroring
endpoint, export the public key to this counterpart and create users and logins
for the counterparts:
-
Server1:
Create Cert1 for Endpoint1 -> Backup1
-
Server2:
Create Cert2 for Endpoint2 -> Backup2
-
Server1:
Create Login2, User2 for Login2, Import Cert2 for User2 from Backup2
-
Server2:
Create Login1, User1 for Login1, Import Cert1 for User1 from Backup1
-
Server1:
Grant connect on Endpoint1 to Login2
-
Server2:
Grant connect on Endpoint2 to Login1
Afterwards
we can run through the normal process of setting up an availability group:
Create AG
on Server1, create listener, add Server2 to AG, backup DBs and their logs on Server1,
add them to AG on Server1, restore to Server2 and add them to the AG there.
Done!
Code
And here is
the code, to run this process in PowerShell:
$Server1 = "SQL-DB-1\SQL01"
$Server2 = "SQL-DB-2\SQL01"
$AGname = "SQLAG"
$Computer1 = $Server1.Split("\")[0]
$Computer2 = $Server2.Split("\")[0]
$EndPoint1 = "TCP://$($Computer1).datacenter.local:5022"
$EndPoint2 = "TCP://$($Computer2).datacenter.local:5022"
$ListenerIP = "1.2.3.4"
$ListenerMask = "255.255.255.0"
$BackUpShare = 'X:\Backup'
$CertFile = "$($BackUpShare)\Cert"
$KeyFile = "$($BackUpShare)\CertKey.pvk"
$CertPWD = "0xF9B76E2C6D4AD30B29B006EF4BC337277702A131"
$MasterKey = "R@nd0mK€y"
$strongPWD = '$Tr0ngP@$$w0rD'
$cred = Get-Credential
#Enable AlwaysOn Feature on both nodes
Enable-SqlAlwaysOn -Path "SQLSERVER:\sql\$Server1" -Credential $cred
Enable-SqlAlwaysOn -Path "SQLSERVER:\sql\$Server2" -Credential $cred
#restart the service on both nodes
Get-Service -Name MSSQL* -ComputerName $Computer1 | Restart-Service
Get-Service -Name MSSQL* -ComputerName $Computer2 | Restart-Service
#create master
key and restore TDE certificate on both nodes
$q = @"
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$MasterKey';
GO
USE MASTER
GO
CREATE CERTIFICATE TDEcert
FROM FILE = '$CertFile'
WITH PRIVATE KEY (FILE = '$KeyFile',
DECRYPTION BY PASSWORD = '$CertPWD');
"@
Invoke-Sqlcmd -Query $q -ServerInstance $Server1
Invoke-Sqlcmd -Query $q -ServerInstance $Server2
#restore each database from BackupShare
$date = [datetime]::Today
$Backups = Get-ChildItem $BackUpShare -Filter *.bak -Recurse | Where-Object {$_.CreationTime.DateTime -gt $date} | sort-object $_.CreationTime
foreach($backup in $Backups)
{
$DB = $backup.DirectoryName.Split("\")[-2]
$path = $backup.DirectoryName.Replace("FULL","LOG")
$files = Get-ChildItem $path -Filter *.trn -Recurse | Where-Object {$_.CreationTime.DateTime -gt $backup.CreationTime.DateTime} | sort-object $_.CreationTime
#if log backup present, restore with norecovery, else restore with recovery
if($files.count -eq 0){Restore-SqlDatabase -Database $DB -BackupFile $($backup.Fullname) -ServerInstance $Server1}
else{Restore-SqlDatabase -Database $DB -BackupFile $($backup.Fullname) -ServerInstance $Server1 -NoRecovery}
$i=0
foreach($file in $files)
{
$i++
#if last file in set, restore with recovery, else restore with norecovery
if($i -eq $files.Count){Restore-SqlDatabase -Database $DB -BackupFile $($file.Fullname) -ServerInstance $Server1 -RestoreAction Log}
else{Restore-SqlDatabase -Database $DB -BackupFile $($file.Fullname) -ServerInstance $Server1 -RestoreAction Log -NoRecovery}
}
}
#create certificate and endpoint required to connect to Node1
$q = @"
CREATE CERTIFICATE $($AGname)01
WITH SUBJECT = '$($AGname)01',
START_DATE = '$((get-date).tostring("yyyyMMdd"))'
GO
BACKUP CERTIFICATE $($AGname)01
TO FILE = '$BackUpShare\$($AGname)01.cert'
GO
CREATE ENDPOINT $($AGname)01_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE $($AGname)01,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance $Server1
#create certificate and endpoint required to connect to Node2
$q = @"
CREATE CERTIFICATE $($AGname)02
WITH SUBJECT = '$($AGname)02',
START_DATE = '$((get-date).tostring("yyyyMMdd"))'
GO
BACKUP CERTIFICATE $($AGname)02
TO FILE = '$BackUpShare\$($AGname)02.cert'
GO
CREATE ENDPOINT $($AGname)02_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(
AUTHENTICATION = CERTIFICATE $($AGname)02,
ROLE = ALL,
ENCRYPTION = REQUIRED ALGORITHM AES
)
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance $Server2
#assign required permissions for health detection
$q = @"
USE [master]
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance $Server1
Invoke-Sqlcmd -Query $q -ServerInstance $Server2
#On Node1 create a login for Node2
$q = @"
USE [master]
GO
CREATE LOGIN NODE2Login WITH PASSWORD = '$strongPWD'
GO
CREATE USER NODE2User FOR LOGIN NODE2Login
GO
CREATE CERTIFICATE $($AGname)02_Public
AUTHORIZATION NODE2User
FROM FILE = '$BackUpShare\$($AGname)02.cert'
GO
GRANT CONNECT ON ENDPOINT::$($AGname)01_Endpoint TO NODE2Login
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance $Server1
#On Node2 create a login for Node1
$q = @"
USE [master]
GO
CREATE LOGIN NODE1Login WITH PASSWORD = '$strongPWD'
GO
CREATE USER NODE1User FOR LOGIN NODE1Login
GO
CREATE CERTIFICATE $($AGname)01_Public
AUTHORIZATION NODE1User
FROM FILE = '$BackUpShare\$($AGname)01.cert'
GO
GRANT CONNECT ON ENDPOINT::$($AGname)02_Endpoint TO NODE1Login
GO
"@
Invoke-Sqlcmd -Query $q -ServerInstance $Server2
# Create an object for the primary replica.
$ServerObject = Get-Item "SQLSERVER:\SQL\$Server1"
$primaryReplica = New-SqlAvailabilityReplica -Name $Server1 -EndpointUrl $EndPoint1 -FailoverMode Automatic -AvailabilityMode SynchronousCommit -AsTemplate -Version $ServerObject.Version
# Create an object for the secondary replica.
$ServerObject = Get-Item "SQLSERVER:\SQL\$Server2"
$secondaryReplica = New-SqlAvailabilityReplica -Name $Server2 -EndpointUrl $EndPoint2 -FailoverMode Automatic -AvailabilityMode SynchronousCommit -AsTemplate -Version $ServerObject.Version
# Create the availability group
New-SqlAvailabilityGroup -Name $AGname -Path "SQLSERVER:\SQL\$Server1" -AvailabilityReplica @($primaryReplica,$secondaryReplica)
# Create the listener for the availability group
New-SqlAvailabilityGroupListener -Name "$($AGname)01" -StaticIp "$ListenerIP/$ListenerMask" -Path "SQLSERVER:\SQL\$Server1\AvailabilityGroups\$AGname"
# Join the secondary replica to the availability group.
Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\$Server2" -Name $AGname
foreach($Database in Get-ChildItem "SQLSERVER:\SQL\$Server1\Databases")
{
# Backup each database and their log on the primary
Backup-SqlDatabase -Database $Database.Name -BackupFile $(Join-Path -ChildPath "$($Database.Name).bak" -Path $BackUpShare) -ServerInstance $Server1
Backup-SqlDatabase -Database $Database.Name -BackupFile $(Join-Path -ChildPath "$($Database.Name).trn" -Path $BackUpShare) -ServerInstance $Server1 -BackupAction Log
# Join the database on primary to the availability group.
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$Server1\AvailabilityGroups\$AGname" -Database $Database.Name
# Restore the databases and logs on the secondary (using NO RECOVERY)
Restore-SqlDatabase -Database $Database.Name -BackupFile $(Join-Path -ChildPath "$($Database.Name).bak" -Path $BackUpShare) -ServerInstance $Server2 -NoRecovery
Restore-SqlDatabase -Database $Database.Name -BackupFile $(Join-Path -ChildPath "$($Database.Name).trn" -Path $BackUpShare) -ServerInstance $Server2 -RestoreAction Log -NoRecovery
# Join the database on secondary to the availability group.
Add-SqlAvailabilityDatabase -Path "SQLSERVER:\SQL\$Server2\AvailabilityGroups\$AGname" -Database $Database.Name
}
