STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Reut Almog Talmi

How To establish mirroring session following domain name change

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:


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

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

  3. 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)

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



#mirroring #disconnected

JOIN OUR MAILING LIST

CONTACT US

4 Itzhak Ben Zvi, Hod-Ha'sharon,

Israel 4537302

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle