Thursday, August 7, 2008

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.

No comments: