By: Sergey Gigoyan | Updated: 2021-09-13 | Comments (1) | Related: > Database Mirroring
Problem
Database mirroring is one of the traditional Microsoft SQL Server high availability solutions (clustering, log shipping, database backup and restore, replication, etc.). Microsoft said it will be depreciated in future versions of SQL Server and recommended Always On Availability Groups instead. However, there are some cases where database mirroring still makes sense such as when high availability solutions are needed across domains. It is impossible to implement Always On Availability Groups across domains (with Windows Server 2016 it is possible to overcome this obstacle by configuring Domain-Independent Availability Groups), so database mirroring is still the best choice in this case and much easier to implement.
Solution
Introduction to SQL Server Database Mirroring
Database mirroring is a SQL Server high availability solution, that allows having a copy of the production database on another server (mirror server). It is possible to configure database mirroring in such a way that the mirror database is fully synchronized with the production database (principal database) ensuring no data loss in case of a failover. It is also possible to implement the ability of automatic failover using a third instance (witness server).
It is worth noting that database mirroring is possible only between pairs of principal and mirror databases (it is not possible to have more than one mirror database) and the database must be in the full recovery model. Database mirroring can be asynchronous or synchronous. In asynchronous mode, which is also called high-performance mode, transactions are transferred from the principal to the mirror asynchronously, so in case of failover, data loss is possible. In synchronous mode, which is also known as high-safety mode, transactions are first committed on the mirror database and then on the principal. Therefore, the data between these two databases are fully synchronized. Nevertheless, the synchronous mode works slower than the asynchronous. By adding a witness server to high-safety mode, we will have a high-safety mode with automatic failover and this mode provides the highest availability. It is also possible to add a witness server in high-performance mode, but it is not recommended because there is no benefit, and it can cause problems.
Database mirroring increases the database availability by switching to the mirror database in case of the principal server failure. In high safety mode, when automatic failover occurs, the downtime will be minimal. Database mirroring can be used also for disaster recovery purposes, but in this case, the mirror server should be placed in a different location, to minimize risks in case of disaster. Another benefit of database mirroring is minimizing downtime in case of upgrades. We can switch servers and upgrade them sequentially, so the database will be available for clients during the whole process.
Configuring Synchronous SQL Server Database Mirroring
While it is possible to configure database mirroring using the GUI of the SQL Server Management Studio (SSMS), the same task can be accomplished by using only T-SQL code. This article is aimed at illustrating database mirroring configuration via T-SQL.
In our example, we will step-by-step configure synchronous database mirroring between two database servers that are in the same domain. In our domain – testdomain.com, we have two test servers – DBSERVER1 (which will act as a principal) and DBSERVER2 (mirror). Let's connect to these servers via SSMS and execute our T-SQL commands step-by-step.
First, we need to create a database mirroring endpoint that is used to make connections between database mirroring session instances. Endpoints use TCP protocol and listen to a unique port number. We should create one endpoint on both principal and mirror servers. Then, if SQL Server account logins are different on the principal and mirror servers, we should create the corresponding account of the other server in each server. In our environment, TESTDOMAIN\DBServer1_sqluser and TESTDOMAIN\DBServer2_sqluser are the SQL Server user accounts of DBSERVER1 and DBSERVER2 correspondingly. After that, we should grant the CONNECT permission on the endpoint to these users.
To perform the above-mentioned steps on the principal server, we run the code below on DBSERVER1:
---------Connect to DBSERVER1(primary server) via SSMS
USE master
GO
-- Step-1. (PRIMARY)
-- Create a database mirroring endpoint
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
-- Step-2. (PRIMARY)
-- Create Windows login in primary server for mirror server's SQL Server account (as they run under different accounts)
CREATE LOGIN [TESTDOMAIN\DBServer2_sqluser] FROM WINDOWS
GO
-- Step-3. (PRIMARY)
-- Grant connect permissions on endpoint to the login account of the mirror server's SQL Server account
GRANT CONNECT ON ENDPOINT::Mirroring TO [TESTDOMAIN\DBServer2_sqluser]
Then, we need to perform similar steps on the mirror server. So, on DBSERVER2, we run the following code:
---------Connect to DBSERVER2(mirror server) via SSMS
USE master
GO
-- Step-4. (MIRROR)
-- Create a database mirroring endpoint
CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO
-- Step-5. (MIRROR)
-- Create Windows login in the mirror server for primary server's SQL Server account (as they run under different accounts)
CREATE LOGIN [TESTDOMAIN\DBServer1_sqluser] FROM WINDOWS
GO
-- Step-6. (MIRROR)
-- Grant connect permissions on endpoint to the login account of the primary server's SQL Server account
GRANT CONNECT ON ENDPOINT::Mirroring TO [TESTDOMAIN\DBServer1_sqluser]
Now, let's switch back to the principal server's query window and run the next piece of code to take full and transaction log backups from the principal database (TestDB). As mentioned above, the database must be in a full recovery model to participate in a mirroring session, so we set the database recovery model to FULL.
---------Connect to DBSERVER1(primary server) via SSMS USE master GO -- Step-7. (PRIMARY) -- Change recovery model of the database to full (as it is in the simple mode) ALTER DATABASE TestDB SET RECOVERY FULL -- Step-8. (PRIMARY) -- Take full and transaction log backups of the database BACKUP DATABASE TestDB TO DISK='\\Backups\TestDB\TestDB.bak' GO BACKUP LOG TestDB TO DISK='\\Backups\TestDB\TestDB.trn' GO
After we have taken the backups, we switch to the mirror instance to restore these backups.
---------Connect to DBSERVER2(mirror server) via SSMS USE master GO -- Step-9. (MIRROR) -- Restore backups on the mirror server with NORECOVERY RESTORE DATABASE [TestDB] FROM DISK = N'\\Backups\TestDB\TestDB.bak' WITH FILE = 1, MOVE N'TestDB' TO N'D:\TestDB\TestDB.mdf', MOVE N'TestDB2' TO N'D:\TestDB\TestDB2.ndf', MOVE N'TestDB3' TO N'D:\TestDB\TestDB3.ndf', MOVE N'TestDB_log' TO N'D:\TestDB\TestDB.ldf', NORECOVERY, NOUNLOAD, REPLACE, STATS = 5 GO RESTORE LOG [TestDB] FROM DISK = '\\Backups\TestDB\TestDB.trn' WITH NORECOVERY GO -- Step-10. (MIRROR) -- Set the partner instance on the mirror server ALTER DATABASE TestDB SET PARTNER ='TCP://DBSERVER1.testdomain.com:5022' GO
We can see, that we restored backups using NORECOVERY, which will allow applying transaction logs on the mirror database.
The last step in the code above defines the mirroring partner for the mirror server (DBSERVER1). Let's move to the principal server one more time and define the mirroring partner (DBSERVER2):
---------Connect to DBSERVER1(primary server) via SSMS USE master GO -- Step-11. (PRIMARY) -- Set the partner instance on the primary server ALTER DATABASE TestDB SET PARTNER = 'TCP://DBSERVER2.testdomain.com:5022' GO
Configuring Asynchronous Database Mirroring
If we want to set up asynchronous mode, we run the following statement after the code above on the primary server:
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
By default, SAFETY is ON, which means the mode is synchronous. Now, we should have the mirroring session successfully configured.To facilitate the process of switching between the principal and mirror servers, I connected to both instances with SSMS and placed the query windows vertically so I can run the steps in order.
Monitor Database Mirroring
To monitor the database synchronization status, we can use the following code:
USE master GO SELECT * FROM sys.database_mirroring
In the result set, if the "mirroring_state_desc" is "SYNCHRONIZED" we can say that we have successfully completed our task. Please note, that with this query, we can identify the server role by "mirroring_role_desc". If we run the query on the mirror, the value of the "mirroring_role_desc" will be "MIRROR" and if we run it on the principal, it will be "PRINCIPAL".
Conclusion
All in all, in this article, we learned about setting up database mirroring and configuring the database mirroring session between the servers within the same domain using only T-SQL code. Understanding the T-SQL code of the database mirroring configuration can be very helpful in minimizing manual work in database mirroring deployments for various environments. This is because it can be parameterized and applied to different servers with minimal changes.
Next Steps
Please find additional information by using the links below:
- MSSQLTips articles
- Microsoft articles
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-09-13
