How to setup database mirroring session in MS SQL server.

Following is step by step guide for setting up database mirroring session. There are 2 major steps involved in setting up database mirroring setup.

1. Prepare mirror database.
2. Configure database mirroring session.

 
Now let's see both of these steps in detail.
 
Prepare mirror database.

1. Take full backup of the primary database. You can use existing full backup file if you have available one. Before taking full backup make sure that database recovery model is set to full. To check recovery model Right click on database - Click on property - Select Options Tab.
2. Restore full back from Step - 1. This restore must be done using WITH NORECOVERY option. And resored database name must be same as primary database.
3. Restore differential backup (if any) taken after full backup used to restore in step 2. Also restore log backup (if any) taken after full backup or differential backup. All these restore must use WITH NORECOVERY option.

 
Now your secodary database is ready to start. Now lets see how to configure actual mirroring session.
         
 
Configure databae mirroring session.
 

• Open database mirroring setup wizard in SSMS. Right click on database - Select Tasks - Click on Mirror... OR Right click on databaes - Click on Property - Select Mirroring Tab
 
 
• Open Databaes Mirroring Security Wizard by clicking on Configure Secutity... button.
 
• Click on Next. To make things simple we will skip witness setup option. So select No on Include Witness Server page and click next. Deselect witness server instance and click next.

 

 
• This will open Primary server Instance wizard. Enter end point name and Listener port (leave default for this example) and click on Next.

 

 
• Click on connect to Secodary Serever. Provide authentication details and connect to secodary server. Leave other details as default and click on next.

 

• Now your session is almost ready. Click on finish. Which popup below window. You can start mirroring immidiately or start later form main mirroing tab. See below screen.

 

 

 

You can also generate script before clicking on Ok on main tab. Scripit is use full for documentaion and future reference. So it is always advisable to generate script and save somewhere safe, probably in source control.