There goes my mirror – SQL error 1474, severity 16, state 1
Microsoft SQL mirror setup – error 1474
This blog will show you issues that may occur when working with a Microsoft SQL mirror. This blog will also show you how to solve it and why you need to make sure everything is setup exactly the same way. A Microsoft SQL mirror setup is not that hard, if you follow the basic steps you might even find it a very easy. This might change if you run into a freaky error that it may give, the error most of the time is useless and leaves you in the dark. Read on for the solution for error 1474, severity 16, state 1.
For those who read my blog about mirroring know that I build a mirror a while ago. As good practice and because the customer had to rearrange the whole datacenter and hosts I broke the mirror and was going to set it up again.
Was there a real reason to do that? perhaps not, but I tend to do that to see if the steps are correct and if the initial setup was not a accident that I can perform only once.
Let’s do a little recap and tell you about the environment.
I got three Microsoft SQL servers, two standard ones and one Express version (the Witness).
The servers are running Microsoft Windows 2012R2.
The database is used for a Citrix XenApp 7.5 environment so it has to be highly available for Citrix dumped the LHC.
The error after creating the mirror
Creating the mirror seems to go fine for a while and at the end (Read the blogpost) you can start the mirror. When I start the mirror I see that the status of the database is changed to Synchronized but the whole process seems to take longer than expected.
SQL servers mirror setup pops up a message saying;
“The ALTER DATABASE command could not be sent to the remote server instance TCP://server FQDN:5022“.. hmm what;s that all about.
The mirror seems to be running fine only thing is that the witness server field is empty.
So I break the mirror again and start over, must have missed something somewhere… more coffee and let’s do it again. practice make perfect is what they say.
Again wizard, enter service accounts, all green and let’s start the mirror.
Waiting, status changes of the database, more waiting and boom error.
“Database mirroring connection error 4 The connection was closed by the remote end, or and error occurred while receiving data: ’64 ( the specified network name is no longer available.) for TCP;//server FQDN:5022“
Whaat? it worked flawlessly before, the only thing changed since last time was that the host had moved to another data center.
So first step now is to ask my good and dear friend Google to have a look in it’s vast database, surely many have encountered this and a solution is around the corner… after a 15 minute search I realize I’m alone, read many workaround and many command that I should fire off on the servers but none give me enough faith and so I do what I mostly do,, dig in and try to fix it myself.
I sat back and thought about mirroring, what are the basic steps that you need to take to build a mirror?
– You need to run the SQL service with the same service account on each server (of course you can change that but that is recommended I read somewhere).
So I looked at all the three servers and checked the SQL service account used there. Remember it worked before and nothing had changed but a check is the first step.
I noted one difference, on the principal and mirror server the service account was Domain.lanservice_account and on the Witness server it was firstname.lastname@example.org.
Of course this should not be the issue for it’s the same user account managing that service but hey let’s change it so that it represents the other server.
All three servers now have the SQL service run with the account written Domain.lanserver_account.
After this I ran the mirror wizard again and boom it worked, no error no issue just like that.
So please if you create a mirror make sure that the notation of the service accounts is exactly the same on each server, I don’t want you to have these issues.
Hope this blog helps some of you…