So… this is it how it happened… the previous database administrator had setup SQL Server 2008 mirroring within the same domain using encrypted traffic and certificated endpoints. Why? I hear you cry. Well, I maybe a complete database zealot – but I understand the KISS principle (I’m not a cop out – oh so cynical reader). So when the certificates expired, and the mirrored database [high safety - synchronous] quietly died like a flan in a cupboard (thanks Eddie Izzard)… I had to put the thing back together. Being a little rusty in setting up the mirror, I headed to Google for all my answers and stumbled upon the usual gold mine – Microsoft Books Online… where a beautiful how-to professed to fix all aliments (lie #1) and allow any Tom, Dick or Database Zealot to setup super cool mirroring (lie #2). Oh dear reader, how much it lied with it’s accurately described processes and instructions – which turned out to be missing rather large pieces of instruction (or maybe I couldn’t read properly… but let’s not let the facts stand in the way of a jolly good post now).
So… What did I discover. I discovered that a) The Database Mirroring Wizard does all the work that the document tells you to setup manually (and incorrectly) and b) NEVER set your administration account’s default database to anything other than master.
Yep. I did that. What a FAIL.
Well come on it was Tuesday and I was already suffering after my household of four kids, a stomach bug and the traffic snarl in the morning had taken most of my brain power by 8am. That, and I even think the wind was blowing from the South.
Besides the impending odds, I eventually got the process happening by following these simple steps (on a SQL Server 2008 Enterprise install):
Pre-reqs: You don’t need encryption and you sure as hell don’t want certificates to be used. Also, you are creating a brand new mirror on an already installed instance of SQL Server 2008 Enterprise. Now stop reading this bit and move on to the 1,2,3 steps. Go on. Move it. Now. Please?
- Make sure the account being used to initiate the mirroring wizard has appropriate access to the database.
- Backup and restore your principle to the mirror server (make sure to use the NORECOVERY option!) – use the same name and for goodness sake, make sure your mirror has the same drive sizes and directories as the principle.
- DON’T pre-create the endpoints, you don’t need to as the wonderful wizard does it….
- Just before you are going to run the mirror wizard, make sure you apply a transaction log backup from your principle server to the mirror (remember… NORECOVERY – otherwise you’ll have to start again).
- Use the mirror wizard to create the mirror, follow your nose. When prompted for the SERVICE accounts – DON’T put anything in (assuming you want your current logged in account to be the handler for this particular process).
- The mirror wizard will create the endpoints, initialize the mirror and whoop de doo, you’ll have a fully working mirrored database. If you don’t then I suggest you run for the hills with a couple of sandbags, a water canteen and pea shooter… Or you can ask someone to help you out (like me). The cost is 3 unused tea bags and some hot water.
To conclude:
So there you have it… don’t believe everything you read, even from the Gods themselves (I’m not one, just a mere mortal).
Any one else out there suffered at the hands of vendor instructions, only to find there are completely useless and following ones nose and experience actually got you through?
Comment away fellow Zealots.
