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.
 

T-SQL reference for Database Mirroring

• How to manually failover to partner server?

There are 2 ways to manually failover to partner server in SQL server database mirroring session.
First is from primary server server and second is from secondary server. See below script for partner failover.

--1. Manual Failover from Primary server

ALTER DATABASE <Database_Name> SET PARTNER FAILOVER;

--2. Manual Failover from Secondary server

ALTER DATABASE <Database_Name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

Failover from primary server does not cuase any data loss but if you do it from secondary server then you are forcing secondary server to loss data. Data loss will only happend if primary server is not accesible while failover.

• How to remove database mirroring session?

Use below script to remove database mirroring session. This script can be used on either primary server or on secondary server.

--Remove mirroring for database

ALTER DATABASE <Database_Name> SET PARTNER OFF;

• How to stop and start databaes mirroring session?

Use below script to pause and resume the database mirroring session. You can SUSPEND mirroring session from either primary server or from secodary server. But It can only be RESUMEd from primary server.

 

--Pause/Suspend database mirroring session

ALTER DATABASE <Database_Name> SET PARTNER SUSPEND;

--Resume database mirroring session

ALTER DATABASE <Database_Name> SET PARTNER RESUME;

• How to add/remove witness server to/from already running database mirroring session?

Use below script to add or remove witness server to/from already running database mirroring session

--Add witness server to database mirroring session

ALTER DATABASE <Database_Name> SET WITNESS <witness_server>;

--Remove witness server from database mirroring session

ALTER DATABASE <Database_Name> SET WITNESS OFF;

SET WITNESS is only allowed on primary server.