Disaster Recovery Using Database Mirroring

Setup the Primary CommServe
Setup the Standby CommServe
Prepare the Database for Mirroring
Setup the Database Mirroring

Switching the CommServe from Primary to Standby in Case of failures

Setting up an Email Alert to Monitor SQL Database Mirroring

Overview

The document describes the process of setting up disaster recovery using the SQL Server Database Mirroring. This process takes the advantage of SQL Server automatic failover feature and is easy to setup and manage.

This setup requires the following:

CommServe software to be installed on Primary CommServe and Standby CommServe. This installation will automatically install SQL Server software.
Install SQL Server Management Tools - Complete to enable Mirroring option during database mirroring setup.
To install SQL Server Management Tools - Complete, navigate to <Software Installation Disc>\<CPU_Type>\<SQL_Version>\SQL_Enterprise_Edition\ and run Setup.exe.
SQL Server database mirroring setup between Primary CommServe and Standby CommServe.

The following section outlines the steps for setting up the SQL Server Database Mirroring and managing this setup.

Install and Setup
Setup the Primary CommServe
1.	Install the CommServe software on the primary CommServe computer. The primary CommServe must be setup using the Dual IP license. (See Licensing and Disaster Recovery for more information.) Make sure that all updates are installed on the primary CommServe.	See Deployment - CommServe for more information on installing the CommServe software.
Setup the Standby CommServe
2.	Install the CommServe software on the standby CommServe computer. Note down the CommServe Client Name and the CommServe Host Name provided in the CommServe Name dialog box during the installation. Make sure that all the updates installed in the primary CommServe are also installed on the standby CommServe.	See Deployment - CommServe for more information on installing the CommServe software
3.	Stop all Services on the standby CommServe. You cannot have services running on both the primary and standby computers.	See Stop Services on Windows for step-by-step instructions.
Prepare the Database for Mirroring
4.	Set the SQL Server services to run using an account that has administrative permissions on the primary and standby CommServe. Perform the following steps in both the primary and standby CommServe to change the user account: Click Start \| Settings \| Control Panel \| Administrative Tools. From Administrative Tools double-click Service. Right-click SQL Server (BullCalypso), select Properties, and then select the Log On tab. Select This Account option and provide [DomainName\AdministratorName]. Type correct password in the Password and Confirm password and then click OK. Restart the services on Primary CommServe, by right-clicking SQL Server (BullCalypso), and click Start.
5.	Verify that the database uses the Full Recovery model. Perform the following steps to modify the recovery model of a database: Open Microsoft SQL Server Management Studio. Navigate to SQL Instance \| Database \| CommServ. Right-click the CommServ database, and then click Properties. In the Select a Page pane, click Options. In the Recovery Model, select Full. Click OK.
6.	Perform the following steps to back up the primary CommServe database to a full database backup: Open Microsoft SQL Server Management Studio. Navigate to Server Instance \| Database \| CommServ. Right-click the CommServ database, select Tasks and then click Backup. The Back Up Database dialog box appears. In the Database, verify the database name. In the Backup type, select Full. In the Backup Component, select Database option. Accept the default backup set name in the Name, or you can enter a different name for the backup set. Specify when the Backup set will expire. By default After is selected with value 0. In the Destination area select Disk option. To select the path, click Add. The selected path will be displayed in the Backup to list. To remove backup destination, select the destination path and click Remove. Click OK.
7.	Perform the following steps to do a Transaction Log backup of the SQL database in the primary CommServe: Open Microsoft SQL Server Management Studio. Navigate to Server Instance \| Database \| CommServ. Right-click the CommServ database, select Tasks and then click Backup. The Back Up Database dialog box appears. In the Database, verify the database name. In the Backup type, select Transaction Log. In the Backup Component, select Database option. Accept the default backup set name in the Name, or you can enter a different name for the backup set. Specify when the Backup set will expire. By default After is selected with value 0. In the Destination area select Disk option. To select the path, click Add. The selected path will be displayed in the Backup to list. To remove backup destination, select the destination path and click Remove. Click OK.
8.	Copy the backup dump file(s) created in Step 6 and Step 7 to Standby CommServe using any of the following method: Using a physical media. Using a network drive that is accessible from both the Servers.
9.	Perform the following steps to restore the full database to the Standby CommServe: Open Microsoft SQL Server Management Studio. Navigate to Server Instance \| Database. Right-click Database, select Restore Database. The Restore Database dialog box appears. On the General page, do the following: In the To Database, select or type the name of a database. To specify the source and location of the backup sets to restore, select From device option. Click the Browse button, Backup dialog box appears. In the Backup media, select one of the device type. Click Add to provide the path of dump file copied in the previous step. Click OK. On the Options page, do the following: In the Restore options, choose any of the options, appropriate for your situation. The Recovery state determines the state of the database after the restore operation. Choose Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY) option. Click OK.
10.	Perform the following steps to restore the Transaction log to the Standby CommServe: Open Microsoft SQL Server Management Studio. Navigate to Server Instance \| Database \| CommServ. Right-click CommServ, select Tasks and then click Restore. Click Transaction Log, then Restore Transaction Log dialog box appears. On the General page, do the following: In the To Database, select the name of a database. To specify the source and location of the backup sets to restore, select From device option. Click the Browse button, Backup dialog box appears. In the Backup media, select one of the device type. Click Add to provide the path of dump file copied in the previous step. Click OK. On the Options page, do the following: In the Restore options, choose any of the options, appropriate for your situation. The Recovery state determines the state of the database after the restore operation. Choose Leave the database non-operational, and do not roll back the uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY) option. Click OK.
Setup the Database Mirroring
11.	Perform the following steps on Primary CommServe database to setup the database mirroring: Open Microsoft SQL Server Management Studio. Navigate to Server Instance \| Database \| CommServ. Right-click CommServ database, select Tasks and then click Mirror. Database Properties dialog box displayed. Click Configure Security. Configure Database Mirroring Security Wizard will be displayed.
12.	Click Next to configure database mirroring.
13.	Select No to witness server instance. Click Next to continue.
14.	Ensure that the Principal server instance is primary CommServe name. Click Next to continue.
15.	Click Connect on the Mirror Server Instance. Connect to Server window appears, provide the destination server information and click Connect. Click Next to continue.
16.	Ensure that the Service Accounts remain empty. Click Next to continue.
17.	Click Finish.
18.	For Specified database mirroring configuration setting, click Do Not Start Mirroring.
19.	Select preferred option for Operating mode: Select High performance (asynchronous) option to commit the changes at the primary CommServe and then transfer the changes to standby CommServe. Select High safety without automatic failover (synchronous) options to transfers the changes that are made to the data on Primary CommServe database to Standby CommServe database in real time.
20.	Select Start Mirroring to begin mirroring. Once you select Start Mirroring, the primary CommServe gets connected with Standby CommServe. Depending upon the Operating mode the transaction logs are copied and replayed in real time on Standby CommServe.
Switching the CommServe from Primary to Standby in Case of failures
1.	If the primary CommServe is inactive, and the operating mode of the database is in asynchronous state or in a synchronous state without automatic failover, make the database available by forcing service to fail over to the standby CommServe. Execute the following command on standby CommServe in SQL Query Analyzer: ALTER DATABASE CommServ SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS	To execute the command open SQL Query Analyzer as follows: On the standby CommServe, open Microsoft SQL Server Management Studio. From the standard menu bar, click New Query. SQL Query Analyzer will be displayed. Type the given command and click Execute. The Standby CommServe immediately transitions to Primary CommServe.
	If the primary CommServe is active and the operating mode of the database is in synchronous state, you can initiate manual failover to the standby CommServe by executing the command on Primary CommServe. Execute the following command on primary CommServe in SQL Query Analyzer: ALTER DATABASE CommServ SET PARTNER FAILOVER	To execute the command open SQL Query Analyzer as follows: On the primary CommServe, open Microsoft SQL Server Management Studio. From the standard menu bar, click New Query. SQL Query Analyzer will be displayed. Type the given command and click Execute. The Standby CommServe immediately transitions to Primary CommServe.
2.	Make sure that all the post-release updates or Service Packs that may have been released after the release of the software installed in the primary CommServe are also installed on the standby CommServe.	See Automatic Updates for more information on installing the latest service pack.
3.	In the standby CommServe, use the Name Management option from the Control Panel to switch the CommServe name from the primary to the standby CommServe. Alternatively, you can update the DNS entry to change IP address of the primary computer to point to the standby computer, and then use the ipconfig /flushdns command to update the clients of the CommServe name change.	See Informing Clients of CommServe Name Change for set-by-step instructions to inform the clients of the CommServe change.
4.	Install the other software components, e.g., iDataAgents, MediaAgents, etc., on the standby CommServe.	See Installation for more information on installing the components.

Setting up an Email Alert to Monitor SQL Database Mirroring

In a disaster recovery setup where the SQL Server Database Mirroring process is used, it is recommended to configure an alert which will notify the user when any of the participating mirrored servers, either the Primary or Standby server, undergo a state change. For example, from the synchronized state to a suspended or disconnected state.

This alert will allow you to monitor the Database Mirroring process for any mirrored database that is found in an abnormal state. To setup the alert, follow the steps described in the sections below:

Create a Database Mail profile
This section provides the steps to create a mail profile and associate it with a SMTP account. Skip this section if you already have a Database Mail profile configured.
Create and Schedule the Alert
This section provides the steps to configure the alert job for monitoring the mirrored servers.

Create a Database Mail profile

1.	On the Primary server, open the Microsoft SQL Server Management Studio. Navigate to <Server Instance> \| Management and double-click Database Mail.
2.	Click Next.
3.	The setup option is selected by default. Click Next.
	If the Database Mail feature is not enabled, click Yes to enable it.
4.	Specify the profile name for the database mail in the Profile name box. Click Add to specify the SMTP account to be associated with the profile.
5.	Enter the credentials for the SMTP Server as appropriate to your environment. Click OK.
6.	Click Next.
7.	Select the profile name that you specified and then click Next.
8.	Click Next.
9.	Click Finish.
10.	When the Database Mail profile is successfully configured, you will see the image below. Click Close.

Create and Schedule the Alert

On the Primary server, open the Microsoft SQL Server Management Studio and navigate to <Server Instance> | SQL Server Agent.

Right-click Jobs and click New Job....

Specify the name of the Database Mail profile created for the DB mirroring alert in the Name box.

Click Steps on the left pane, and then click the New button.

Specify the name of the step in the Step name box e.g., 'DB Alert Script'.

Select the database name from the Database list e.g., CommServ.

Copy and paste the SQL script provided below in the Command box and then click OK.

Make sure to replace the dummy email 'myemail@company.com' with a valid email in the SQL script.

The Database Mail profile name created and the DB mirroring alert profile name in the script should be same otherwise the Alert will error with a profile name error.

DECLARE @state VARCHAR(30) DECLARE @DbMirrored INT DECLARE @DbId INT DECLARE @String VARCHAR(100) DECLARE @databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30)) -- get status for mirrored databases INSERT @databases SELECT database_id, mirroring_state_desc FROM sys.database_mirroring WHERE mirroring_role_desc IN ('PRINCIPAL','MIRROR') AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING') -- iterate through mirrored databases and send email alert WHILE EXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL) BEGIN SELECT TOP 1 @DbId = DBid, @State = mirroring_state_desc FROM @databases SET @string = 'Host: '+@@servername+'.'+CAST(DB_NAME(@DbId) AS VARCHAR)+ ' - DB Mirroring is '+@state +' - notify DBA' EXEC msdb.dbo.sp_send_dbmail 'DBMAIL', 'mymail@company.com', @body = @string, @subject = @string DELETE FROM @databases WHERE DBid = @DbId END --also notify if there is no mirroring in case there should be mirroring SELECT @DbMirrored = COUNT(*) FROM sys.database_mirroring WHERE mirroring_state IS NOT NULL IF @DbMirrored = 0 BEGIN SET @string = 'Host: '+@@servername+' - No databases are mirrored on this server - notify DBA' EXEC msdb.dbo.sp_send_dbmail 'DBMAIL', 'mymail@company.com', @body = @string, @subject = @string END

Click Schedules on the left pane, and then click the New button.

Specify a name for the schedule in the Name box and then configure the schedule frequency as per your needs. For example, you can select to run the schedule once every Sunday, Monday and Tuesday, as shown in the image below.

Click OK.

The alert for monitoring Database Mirroring is now created.