Have you ever needed to change the database mirroring partner names?
One of our clients had to transfer all their servers from one domain to a new one, including SQL server machines. So, they simply disconnected all servers from the old domain and connected them to the new one. Breath fluently, this was test environment.
It was a preview for a similar change planned to take place few weeks later in production environment. So, in order to be prepared for prod, I decided treating this case as if it was prod and doing all I can to re-establish mirroring connection without setting up mirroring from scratch.
The existing configuration of the database mirroring partners used FQDN names of this format: 'TCP://SERVERNAME.OldDomainName.com:port' as Microsoft suggests here and here.
(To check your mirroring configurations you can query table sys.database_mirroring)
So, now, following domain name change, the servers obviously cannot connect with each other and the databases involved in mirroring, are in status disconnected.
What needs to be done is changing the definition of mirroring partner FQDN names to consist of the new domain name instead of the old one. But it turned out it is not so straight forward.
Trying the following command:
ALTER DATABASE [DBName] SET PARTNER = 'TCP://SERVERNAME.NewDomainName.com:port'
Resulted in this error message:
Neither the partner nor the witness server instance for database "DBName" is available. Reissue the command when at least one of the instances becomes available.
Trying to change the configuration using IPs instead of server names like this:
ALTER DATABASE [DBName] SET PARTNER = TCP://xxx.xxx.xxx.xxx:port'
Didn't work as well. Same error.
I've verified connectivity between the servers using FQDN and port using 'telnet' utility in command line – works fine.
Tried to pause mirroring first, and only then execute the above ALTER commands – no change. Keep getting the same error.
Eventually, what I did was:
Executing command ALTER DATABASE [DBName] SET PARTNER OFF first on secondary server, then on principal server. what resulted in breaking the mirroring session and leaving the database on the secondary server in restoring state.
Then, I had to locate the LOG backup that contains the required LSN for the mirrored database, which is the LSN at which the mirroring session was last connected, somewhere yesterday evening when the server was disconnected from the old domain. since we have a mirroring monitor process, that collects data based on sp_dbmmonitorresults system stored procedure into a static table, I could locate the time at which the status has changed.
now, restoring the gap is easy first, I've stopped & disabled the log backups job on principal server, then, generated a serial restore log commands from the principal server, based on backupset and backupmediafamily tables in msdb (script below)
and executing the result on secondary server
USE msdb
GO
DECLARE
@DatabaseName SYSNAME = 'MyDatabaseName',
@MirroringDisconDateTime DATETIME = '2020-05-07 19:30'
SELECT
CONCAT('RESTORE LOG ',QUOTENAME(@DatabaseName),' FROM DISK=''',bmf.physical_device_name,''' WITH STATS = 1,REPLACE, NORECOVERY;')
FROM
backupset bs
INNER JOIN
backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE
database_name = @DatabaseName
AND
bs.type = 'L'
AND
bs.backup_start_date >= @MirroringDisconDateTime
ORDER BY
bs.backup_set_id ASC
now they are finally aligned!!!
now I could finally execute the commands to set the correct partner names, first on secondary server, then on principal server:
ALTER DATABASE [DBName] SET PARTNER = 'TCP://SERVERNAME.NewDomainName.com:port'
commands completed successfully.
YESSSSSS!!
preparing coffee and waiting for those bastards to synchronize...
To sum up,
I've tried to avoid breaking the mirroring. I thought it would require restoring full and sequential log backups on secondary server and setting up mirroring from scratch (what would have probably taken less time than I spent on this case eventually)
But, guess what?
It turned out that although SETTING PARTNERs OFF, breaks the mirroring session, drops the mirroring information from all system objects and leaves the mirrored databases on secondary server in restoring state.
But, you can still establish the mirroring session again by restoring the log backups from the time the databases were last synchronized.
As long as you still have the required LSN records in the transaction log of the principal database, or in log backups, then you can continue the log chain and bring the databases to be synchronized again.
Comments