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 from principal to Mirror database with non recovery and restore Log from Principal to Mirror database with non recovery
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
Create the end point on the witness server
Setup the Mirroring database
Run below on the Mirroring database server
Setup the principal and Witness server
Run below on the principal server.
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.
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.
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.
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.
Remove the database mirroring
- Remove the mirroring database session
Run on any of the partner server
- Recover the mirroring database (Optional)
If any snapshots were created, the needs to be drop before we can recover the database.
- Remove the mirroring monitor agent job (Optional)
- Remove the end point (optional)
Database Mirroring Monitor
We can use this to monitor the database mirroring status.
A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database Mirroring Overview http://msdn.microsoft.com/en-us/library/ms189852.aspx