40 Database Mirroring

Database must be  in FULL Recovery mode and can not have the file stream file group.

Database mirroring is the SQL Server Enterprise Edition feature. The Principal and Mirror database must use Enterprise Edition. The Witness server can be Express.

The default port for the SQL Server mirroring endpoint is 5022 but it can be changed to any unused port.

Below example is setting up the database mirror on the high safety with  automatic failover or called Synchronous Database Mirroring (High-Safety Mode)

• SG1 is the principal SQL Server host.
• SG21 is the Mirror SQL Server host.
• SG13 is the Witness SQL Server host.

Restore Database

Restore Database from principal to Mirror database with non recovery and restore Log from Principal to Mirror database with non recovery

1. RESTORE DATABASE [AdventureWorks] from DISK = '\\SG11\C$\DUMP\AdventureWorks_20111105_3.db' with replace,norecovery
2. RESTORE LOG [AdventureWorks] from DISK = '\\SG11\C$\DUMP\AdventureWorks_20111105_3.log' with norecovery

Create the Endpoint on Principal, Mirror and witness server

You will need to change the port number if they are on the same server. However, it is recommend you put in the different server ( for High availability of course )  

Create end point on both Principal and Mirror server

1. CREATE ENDPOINT mirror
2.     STATE = STARTED
3.     AS TCP ( LISTENER_PORT = 7051 )
4.     FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=REQUIRED ALGORITHM RC4);
5. GO

Create the end point on the witness server

1. CREATE ENDPOINT mirror
2.     STATE = STARTED
3.     AS TCP ( LISTENER_PORT = 7051 )
4.     FOR DATABASE_MIRRORING (ROLE=WITNESS,ENCRYPTION=REQUIRED ALGORITHM RC4);
5. GO

 

Setup the Mirroring database

Run below on the Mirroring database server

1. ALTER DATABASE AdventureWorks
2.    SET PARTNER = 'TCP://SG11:7051'

Setup the principal and Witness server

Run below on the principal server.

1. ALTER DATABASE AdventureWorks
2.    SET PARTNER = 'TCP://SG21:7051'
3. ALTER DATABASE AdventureWorks
4.    SET WITNESS = 'TCP://SG13:7051'

The SQL Agent job will be created on the mirroring database server, we can use  msdb.dbo.sp_help_job to review the job detail.

Use below tables to view the Mirror database and endpoint configuration. This can be run on either Principal, mirror and witness server.

1. use master;
2. SELECT name, port FROM sys.tcp_endpoints;
3. select * from sys.database_mirroring_endpoints;
4. SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints;
5. select * from sys.database_mirroring_witnesses;
6. select * from sys.database_mirroring;

Access data on the mirroring database

Since the mirroring database is under the recovery and mirroring state. We can not directly access the data. As alternative, we can create the snapshot and access the snapshot data.

1. CREATE DATABASE [S_AdventureWorks_20111105]
2. ON (NAME='AdventureWorks_data' ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSG21\MSSQL\DATA\S_AdventureWorks_20111105.ss')
3. AS SNAPSHOT of  [AdventureWorks]

Manual failover

In the Synchronous Database Mirroring (High-Safety Mode), the failover will automatically happen in the event the principal database is not available. If you set the mirror mode as high performance mode, this will require the manually failover and use  FORCE_SERVICE_ALLOW_DATA_LOSS

To manually failover in the High-Safety mode, we can run below on the principal server.

1. Alter database [AdventureWorks]set partner failover

Once it is done, when we select from sys.database_mirroring, we can see the original principal database will become “Mirror” and the old mirror will become ‘Principal’.

Remove the witness

Anytime during the mirroring, we can disable the witness by running below command.

1. ALTER DATABASE [AdventureWorks] SET WITNESS OFF

Remove the database mirroring

• Remove the mirroring database session

Run on any of the partner server

1. ALTER DATABASE [AdventureWorks]  SET PARTNER OFF

• Recover the mirroring database (Optional)

If any snapshots were created, the needs to be drop before we can recover the database.

1. RESTORE DATABASE  [AdventureWorks] WITH RECOVERY;

• Remove the mirroring monitor agent job (Optional)

1. msdb.dbo.sp_delete_job @job_name='Database Mirroring Monitor Job'

• Remove the end point (optional)

1. DROP ENDPOINT mirror

Database Mirroring Monitor

We can use this to monitor the database mirroring status.

Notes

A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.

Reference

Database Mirroring Overview http://msdn.microsoft.com/en-us/library/ms189852.aspx