• Repost - originally posted 21-11-2012

Database mirroring listener port change

Recently we had a client that had to change to all of their IP addresses. This included the DR site which is on another network and not in the same AD. Database mirroring between the two sites broke. Even though mirroring had been setup with FQDN's, internally these were kept as IP addresses in sys.database_mirrroing. Unfortunately you can't update this table - adhoc updates aren't possible (unless you start in single user mode and use DAC). So we had to go through the following process. 1.Stop application activity 2.Remove mirroring (SET PARTNER OFF) 3.Stop Mirroring endpoints (on principal and mirror) 4.Alter Mirroring endpoints to use new IP addresses e.g. ALTER ENDPOINT SET LISTENER_IP = 5.Start endpoints on principal and mirror 6.Enable mirroring (ALTER DATABASE SET PARTNER = TCP://x.x.x.x:yyyy) We then had to make sure the mirroring ports were open on both firewalls for the new IP addresses. Not an easy or quick or pretty process for a relatively minor change.

4 views0 comments

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t