Steps 1 –
The first step is to make sure that the logins under Security match on both the principal and mirror servers.
- On my primary ( principal) DB I did a right-click on the login “script Login as/CREATE To/clipboard
- On the mirror DB I pasted the information in a query window and once I had each of the login scripts pasted in, I ran the query and automatically created the logins. This saved me a ton of time from trying to manually create each login.
The next step is to back up the database that you plan to mirror.
- This DB should be in the full recovery model before the backup is completed. If the recovery model is set to simple the Mirror process will not work.
- Back up the DB to a local location on the principal server and then copy the DB over to the mirror server.
NOTE: If you did not do it in the first place you may need to add the file extension .bak to the filename of the backed up DB in order to restore it
- Restore the DB on the mirror server.
- Make sure that the DB name that you restore to on the mirror server is EXACTLY the same as the name on the principal server.
- Make sure that you restore the DB in the no recovery state. !!!!!! THIS IS CRITICAL !!!!!!
Restore Transaction Logs
- I found that for myself I did not need to do this. I believe that this was due to the fact that I had just switched the databases to full recovery mode and no transaction logs had yet been taken. If you find this step to be necessary there are further details here: http://msdn.microsoft.com/en-us/library/ms189047(v=SQL.100).aspx
- Connect to the principal DB
- Right-click on the database to be mirrored and select tasks\Mirror…
Click the Configure Security button
- ◦For my application I configured security to include a witness server as well
- ◦Select the Principal server Instance (I just left the other settings as default)
- ◦Select the Mirror server instance (selected my mirror server and accepted the defaults)
- ◦Select the Witness server instance (selected the witness server and the defaults)
- Since my servers are all in the same domain I entered the service accounts for each of my servers. Originally I thought that I needed to create new Active directory Users for the mirroring process but found out the hard way that what it is looking for was the service user that was originally used to set up SQL on each machine.
- Click Finish and the wizard should perform the designated functions and hopefully show success for setting up the endpoint on each one.
- Click the Start Mirroring button.
- If everything works correctly you should see the words Principal, Synchronizing in parenthesis after the name of your database.
- You may need to refresh the database list in order to see this.
Steps 1 –