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.