MS SQL Server 2012 - Database Mirroring Setup - using SSMS

http://thedbavault.blogspot.in/2012/10/ms-sql-server-2012-database-mirroring.html

Database mirroring is a technology solution for increasing the availability of a SQL Server database. it is implemented on a per-database basis and works with databases that use full recovery model. With the launch of SQL Server 2012 and introduction of AlwaysOn HADR feature, Microsoft plans to remove Database Mirroring capabilities in future versions of Microsoft SQL Server. It is better for DBAs not to deploy Database Mirroring  feature in new development work and plan to modify applications that currently use this feature and convert them to AlwaysOn Availability Groups as the enterprise upgrades to SQL Server 2012. 

Setting up database mirroring in MS SQL Server 2012 is similar to previous versions. Information in this guide is for reference only, for all new developments MS SQL Server 2012 AlwaysOn HADR options are preferred.

Implementing Database Mirroring:

Step 1: Confirm User database is set to use 'FULL' recovery model.

Database Mirroring requires the recovery model of the principal database to be in FULL recovery model.

Click Start | All Programs | Microsoft SQL Server 2012 | SQL Server Management Studio . 

Connect to the Principal Server <<PrincipalServerName\InstanceName>>

In the Object Explorer pane, expand the Databases node. 

Right click on <<Database>> and select Properties . 

When the Database Properties displays click Options . 

Confirm the Database Recovery Model is set to Full . Click OK .

Step 2: Backup the Principal Database

In the Object Explorer pane, click New Query . 

Run the following T-SQL command to backup database and log file:

BACKUP DATABASE <<database name>> TO DISK=’<<path>>’ WITH INIT;

BACKUP LOG  <<database name>> TO  DISK=’<<path>>’ WITH INIT;

This can also be done graphically through the SSMS as shows in the screenshots below:

Step 3: Prepare the Mirror Database

Using Windows Explorer create a folder on the mirror server & copy the .BAK and .TRN file from the Principal Server to the Mirror Server. 

Using SSMS, connect to the server that will host the mirror database and restore the database in recovery mode, to allow the mirror to be created.

In the Object Explorer pane, click New Query . Run the following T-SQL command:

RESTORE DATABASE <<database name>> FROM DISK=’<<path>>’ WITH NORECOVERY, REPLACE ;

RESTORE LOG <<database name>> FROM DISK=’<<path>>’ WITH NORECOVERY;

In the Object Explorer pane, expand the Databases node, and then note that the database is in Restoring mode.

Important: NORECOVERY option is critical for this step because the database needs to be able to apply additional transaction log backups as part of the mirroring process.

Here is how you can do the same graphically through SSMS as shown in the screenshots below:

Step 4: Setting up Database Mirroring

1. In SQL Server Management Studio, right- click the <<database name>>, click Tasks, and then click Mirror . 

2. In the Database Properties dialog box, click Configure Security .

3. On the Configure Database Mirroring Security Wizard page, click Next . 

4. On the Include Witness Server page, select Yes, then click Next .

5. On the Mirror Server Instance page, select <<MirrorServerName\InstanceName>>, and then click Connect . 

6. On the Mirror Server Instance page, click Next . 

7. On the Witness Server Instance page, <<WitnessServerName\InstanceName>>, and then click Connect . 

8. On the Witness Server Instance page, click Next .

9. On the Service Accounts page, in the Principal, Witness, and Mirror boxes, type <<Domain>>\<<SQLServiceAccount>> .

10. On the Service Accounts page, click Next . 

11. On the Complete the Wizard page, click Finish.

12. On the Configuring Endpoints page, click Close when the configuration completes.

13. In the Database Properties dialog box, click Start Mirroring .

14. When the database mirroring starts, note the synchronization status. Click OK . 

Congratulations! Database Mirroring setup is complete.  Mirroring configuration for the primary database is high -safety mode. The <<database name>> is now collecting all transactions and applying them to the Secondary instance before they are committed in the Primary instance. 

Database Mirror Monitor:

The Database Mirroring Monitor is part of the SQL Server Monitor. It is launched from SQL Server Management Studio.

To launch the Database Mirroring Monitor, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.

Expand Databases, and select the database to be monitored.

Right-click the database, select Tasks, and then click Launch Database Mirroring Monitor.

In the Database Mirroring Monitor dialog box, click Register Mirrored Database to register one or more mirrored database.

Database Mirroring Monitor is used to monitor any subset of the mirrored databases on a server instance. It helps in verifying whether database mirror configuration is in good condition and how well data is flowing in the database mirroring session. It is also useful for troubleshooting the cause of reduced data flow.