Force SQL Server Mirroring on specific IP Network!

Have you ever tried to change the network on wich SQL Server Mirroring runs on, from all available to one specific network? I have, and it is not always straight forward. So I created a little log of my efforts, and thought I should share it with you all:

Starting setup: Principal, Mirror, Witness listens on ALL Run: ALTER ENDPOINT EndPoint_Mirroring AS TCP (LISTENER_IP = (xxx.xxx.xxx.xxx)) –on all 3

Result: Split brain Not good! So it wasn’t that easy after all. Ok , what next? First, fix what is broken. Run: ALTER ENDPOINT EndPoint_Mirroring AS TCP (LISTENER_IP = ALL)

Result: Mirroring Works again Ok, lets try this then! Pause mirroring, and Run: ALTER ENDPOINT EndPoint_Mirroring AS TCP (LISTENER_IP = (xxx.xxx.xxx.xxx)) –on Principal Then resume mirroring Result: Trying to view properties on db gives: Database xxxxxx is enabled for Database Mirroring, but the database lacks quorum: the database cannot be opened.  Check the partner and witness connections if configured. (Microsoft SQL Server,Error: 955)

Mirroring status: Disconnected Oops! Not too good a status either… Back out and try again! Run: ALTER ENDPOINT EndPoint_Mirroring AS TCP (LISTENER_IP = ALL)

Result: Properties Works again Mirroring status: Suspended Oops again! What happened here? I am worse off then ever, and out of ideas of how to do this in a simple manner. What to do? Start from the beginning…. Run: Break mirroring. Set up Mirroring using IP adresses instead of DNS names! Run on Mirror: USE MASTER GO ALTER DATABASE [xxxx] SET PARTNER = ”TCP://xxx.xxx.xxx.xxx:xxxx’ GO Run on  Primary: USE MASTER GO ALTER DATABASE [xxxx] SET PARTNER = ”TCP://xxx.xxx.xxx.xxx:xxxx’ GO USE MASTER GO ALTER DATABASE [xxxx] SET WITNESS = ‘TCP://xxx.xxx.xxx.xxx:xxxx’ GO

Result: Mirroring works on correct network! Verify using “netstat.exe -n” So, time to limit us to only the correct network. Run on all: ALTER ENDPOINT EndPoint_Mirroring AS TCP (LISTENER_IP = (xxx.xxx.xxx.xxx))

Result: Mirroring works as expected, on the correct IP network! What can we learn from this? It is easier to set it up correctly from the start (duh!), but also, Mirroring is quite easy to set up but can be cumbersome to change. So starting from scratch with the new settings you want is probably a faster option in most cases, except when you have really large databases.

If you would like us to help you with setting up Mirroring, don’t hesitate to contact us!