The estimated reading time for this post is 7 minutes
Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.
Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations.
Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances. The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role.
Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible.
There are two mirroring operating modes. high-safety mode supports synchronous operation and high-performance mode, runs asynchronously.
After a mirroring session starts or resumes, the process by which log records of the principal database that have accumulated on the principal server are sent to the mirror server, which writes these log records to disk as quickly as possible to catch up with the principal server.
The illustration below shows database mirroring with high-safety operation mode.
The illustration below shows database mirroring with high-performance operation mode
Prerequisites
For a mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
The two partners, that is the principal server and mirror server, must be running the same edition of SQL Server. The witness, if any, can run on any edition of SQL Server that supports database mirroring.
The database must use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, bulk operations are always fully logged for a mirrored database.
Verify that the mirror server has sufficient disk space for the mirror database.
When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY.
Only user databases can be mirrored. You cannot mirror the master, msdb, tempdb, or model databases.
A mirrored database cannot be renamed during a database mirroring session.
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
Database mirroring is not supported with either cross-database transactions or distributed transactions.
Implementation Steps
In this illustration, I’m going to show you how is implementation of database mirroring between two SQL Server instances in same server without witness by SQL authentication login.
SAEED-PC (SQL Server 13.0.1742.0 – Saeed) as Principal Server
SAEED-PC\SaeedSQL (SQL Server 13.0.1742.0 – Saeed) as Mirror Server
1 – Get full backup and log backup a database which will be principal
2 – Restore full backup and log backups of principal database in mirror server with same name including WITH NORECOVERY backup option for all backup files (You must do it manually in mirror server).
3 – Right click on principal database, click Properties and choose Mirroring. In Mirroring pane click on Configure Security tab and follow direction as below:
Remember this step is necessary to be done for first database mirroring, for next database mirroring you can skip this step.
This step also will create endpoint ports internally but for checking you can use these system views to find out endpoint which is created and its port number.
SELECT * FROM sys.endpoints
SELECT * FROM sys.tcp_endpoints
If there is no any endpoint for database mirroring, you can use this T-SQL statements to create endpoint.
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING
(ENCRYPTION = DISABLED , ROLE = ALL)
4 – Create inbound and outbound rules for new ports and enable them through Windows Defender Firewall with Advanced Security.
5 – Specify each database’s partner with its address. At first do it in mirror database then in principal database by using T-SQL.
ALTER DATABASE TestMirroring SET PARTNER = 'TCP://SAEED-PC:5022'
ALTER DATABASE TestMirroring SET PARTNER = 'TCP://SAEED-PC:5023'
6– Refresh SQL Server instances.
Now to verify our database mirroring functionality, right click on principal database and open Properties then click Failover button. Refresh SQL Server instances and select Test table from ex mirror database which is principal database now, after failover.
I hope this blog post would be helpful for you to understand and implement basic concept of database mirroring.
Leave a Comment