SQL Server Upgrade Database Mirroring Issues
By Brad Foutz
Article Date: 2008-07-21
Before upgrading our production servers I simply removed the mirroring with the wizard. After the upgrade process the last step was to recreate the mirroring. If you have done this you know that you need to do a full backup with transaction log backup of the db and restore it.
This can take a while if the db is large. After the restore job finished I started the database mirroring wizard, I added the principal, mirror and witness (btw...the mirror is SQL server 2005 express ed.) without any issue. When tried to start the mirroring I got an error stating that;
"The ALTER DATABASE command could not be sent to the remote server instance 'TCP://servername.domainname.com:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again. (Microsoft SQL Server, Error: 1456)The mirroring was started in "High Safety without auto-failover" but I was really thrown off by these messages. I searched and searched for something but I couldn't find any resolutions for this problem. Since the event log said that there was something wrong with the encryption algorithm I figured I should just set it to the correct algorithm and it would start working.
In the Event viewer I would get a message that says, "Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22'. [CLIENT: ipaddress].
I found some information about how to check the database tables in the witness server. That was when I noticed that all of the tables where there and had data in them. After a bit of looking at the data in the tables I figured that the problem may have something to do with when I originally removed the mirroring, the mirroring wizard only disconnecting the servers and not removing the data from the tables.
I was lucky since the witness db server was only used for mirroring I could easily reinstall SQL server express and give the wizard another try. This was what was needed, as soon as I tried the wizard again the mirroring started immediately.
I wrote about this upgrade in another post that is here.
About the Author:
Brad Foutz started working in IT after traveling around a bit. When he came back he decided to finish up school so he went to University of Maryland, Baltimore County to study Information system. Brad's years of experience have allowed him to work in helpdesk, system analysis, system/network administrator, network consultant and system architecture. Check out his blog, IT Network Guru.