Database Mirroring in SQL Server - Configuration

Configuring Database Mirroring becomes easier when one understands completely about what is mirroring. Please read my previous posts about introduction of Database Mirroring here,

Database Mirroring in SQL Server - Introduction (Part - 1)

Database Mirroring in SQL Server - Introduction (Part - 2)

Mirroring can be configured by using SQL Server Management Studio or Transact - SQL statements. Before configuring mirroring session, ensure the following conditions are met,

• The recovery model of Principal database is FULL.
• Create a mirror database on Mirror server by restoring the latest full backup of Principal Database WITH NO RECOVERY which allows inserting log records into it.
• Take a log backup of Principal database and restore it on Mirror database WITH NO RECOVERY option.
• To setup Database Mirroring, the used login should be of sysadmin fixed role.

The communication between instances that are participated in Mirroring is performed over TCP endpoints. Each instance should have its own Endpoint that listens over a unique TCP/IP port.

Configuring Database Mirroring using SQL Server Management Studio

In the Object Explorer, right click on the Principal Database and select Properties.

Go to Mirroring Page and click the Configure Security button which launches Configure Database Mirroring Wizard. Click Next.

Then comes Include Witness Server page which asks whether you need a Witness instance to be configured. If you want you Mirroring Session, select NO otherwise YES. Right now, I'm selecting YES.

Click Next which takes you to Choose Servers to Configure. Ensure that Witness Server Instance is checked to configure Witness.

Click Next to go to Principal Server Instance configuration. There the Principal Server Instance is already selected. As said earlier in this post, communication is done over TCP Endpoints. Each endpoint has its own TCP/IP port. By default there is a port 5022. If you're setting up Mirroring in your production environment, use another port for security reasons. The name of endpoint is Mirroring by default. You can rename it. There is also one more option for encrypting data that is sent through that endpoint, checked by default. If you want your data not to be encrypted, uncheck that check box.

Click Next to configure Mirror Server Instance. Select an instance that has to be used as Mirror and connect to it. The above specified things imply here also. Make sure that the listener port of Mirror should not be same if both Principal and Mirror are two separate instances on a single machine.

Click Next to configure Witness Server Instance. Even here, the above specified conditions imply.

Click Next to go to Service Accounts page. If the three instances belong to same domain account then no need to specify service accounts, leave the text boxes empty. If not, specify service accounts. This creates logins for each account and grants CONNECT permission on the endpoints. If the three instances are in a Workgroup then also no need to specify any Service Account.

Click Next which takes you to Complete the Wizard page. Verify all the options selected till now and click Finish if everything is OK. Now the endpoints are created for each instance.

Click Close button which takes you to Database Properties dialog box. In this dialog box, the properties of Principal, Mirror and Witness are displayed. SQL Server asks whether to start mirroring or not. If you want it to start mirroring right now, click on Start Mirroring. To start the session later, select Do Not Start Mirroring.

If you click Start Mirroring, it takes a few seconds to start the session and gives you the following screen.

At the bottom of above screen, you can see the current status of your Mirroring Session. You have options to Pause or Remove Mirroring and Failover (this is for Manual Failover).

You can also see your databases in Object Explorer by connecting to your Principal and Mirror Instances.

Now Principal and Mirror are synchronized with each other. The Mirror database will be in Restoring state as it was created WITH NO RECOVERY option. It cannot be connected now. Only Principal can be connected.

To test synchronizing, do some transactions on Principal and refresh the Object Explorer. You can see Principal and Mirror getting synchronized.

To test Automatic Failover, go to SQL Server Configuration Manager and shutdown Principal instance. Refresh the object explorer and you can find Mirror has become Principal. This is done by Witness.