Today I had to set up mirroring on a Named Instance of our Test Box for doing some testing and I came across a scenario which prompted me to write this blog.
Let me give a background of the Testing Environment, I was trying to establish High Safety Mirroring without any Witness on SQL Server 2008- Service Pack 2 and Windows Server 2003 R2. Both the servers were in same domain and one of SQL Server was a Named Instance in different Server.
I had already restored the necessary backups in NORECOVERY mode in the Mirror Partner Server (Which was a Named Instance) and had created the necessary Mirroring Endpoints with proper Authorization. Next I gave the below command where database was in Restoring Mode:ALTER DATABASE Mirror_Test
SET PARTNER = ‘TCP://us-bet-PrimaryServer.abc.com:5022’
I worked fine. Now I issued the below command on Primary Server:ALTER DATABASE Mirror_Test SET PARTNER = ‘TCP://us-bet-PartnerServer.abc.com\NamedInstance:5022’
And Boom!! I got the below error:
Msg 1449, Level 16, State 5, Line 1
ALTER DATABASE command failed due to an invalid server connection string.
I checked everything that was possible, googled a lot but it was of no help and then I tried something and Phew !! It was working.
On Primary Server I again issued the below command:ALTER DATABASE Mirror_Test
SET PARTNER = ‘TCP://us-bet-PartnerServer.abc.com:5022’
Note: I did not specify the Named instance part and it started working.
Let me know if you face any issues.