Database mirroring on SQL 2005 SP2

Howdy folks,

This week I went together with a colleague to a customer where there were some troubles with the database mirroring on SQL 2005. It a high available mirror and the problem was it didn't work 100% of the time. Some times something like a split brain occurs. The database is recovering as well on the mirror as on the principal.

The thing we noticed was in the mirroring monitor that the server who had the principal role didn't have a successful connection to the witness server.

First thing we checked was the network. The situation goes as follows. The servers are in workgroup mode and there is a dedicated Gigabit connection between those server to sent the transactions to both servers. All servers were also defined in the host file so even when the DNS goes down it should work.

After crusing down the Internet I found a post in a forum of somebody with the same problem. The problem was solved for this guy by rebooting the system. So we restarted the SQL service of the witness server and it worked.

So that needed some further investigation. We created a new database, made it mirror and the same scenario ... no witness on the principal, only for that database. We restarted the SQL service on the witness and it worked :-)

Then we did the ultimate test, we stopped the endpoint on the server for which the server was mirror for the databases in production and principal for our test database. (So there was no impact for production). First time we tested it, everything went fine and the test database failed over (and we wrote a record in it). The second time we tried it, it failed and a split brain like situation occured. Okay, there was no problem to bring the test database online and since it is in sync mode no transaction could have been written to one side and not to the other.

The odd thing is that the production server had to be turned off that evening and the automatic fail over worked without any problems.

So some further investigations will be required. Currently we are thinking in the direction of cummulative updates. When we find it I'll make a post about it.

A night at an ISP

Recently I've spent the night at one of Belgium's bigger Internet service providers. The ISP had had some trouble with their databases last December and I had to implement database mirroring.

In the beginning of July I had created a test database for their IT people so they could play with it. And now, the time had come to implement it for all their databases as a test to adapt their programming and make it fail-over aware.

There were some specifics as the mirror had to be synchronous and encrypted and it had to be the same port on each server.

So here are my findings:

• Use 2005 SP2, it figures but I prefer to mention it ;)
• You need the database to be in full recovery mode
• Watch out for the auto close option, it runs the fun
• You need a full backup and a transaction log backup
  

It actually went pretty smooth since everything (mirror endpoints, encryption and witness) was already there from the previous month.

The mirror wizard doesn't use the full qualified network name for the principal server so at the end it proposes to start mirroring but it fails because you have to manually adapt the principal server.

The only thing that was a real problem was 1 database. For some reason it failed time after time and the error message was that it was unable to connect to the witness or mirror server.

The cause was one app that writes constantly in the database and since it took about 15 minutes to move the backup and restore it with no recovery on the witness it was not possible to create the mirror.

To work around this I made the full backup, restored it with no recovery and then I made the transactional backup. I had the permission to take the database offline once I made the transaction log backup had finished and restored it on the mirror . Once I had put the database back online the mirroring was no problem at all.

We ran some tests and everything went fine. The only thing my customer still has to do is create maintenance plans on the mirror (for some weird reason you can't mirror those) and alter his apps.

At the break of rush hour we all went home for some sleep :).

An update: 10 days later and something went wrong, for some reason one database went suspect on the principal.