Setting Up SQL Server Database Mirroring with T-SQL

Database mirroring lets you maintain a hot standby copy of a SQL Server database on a separate server instance, providing automatic failover capability and data redundancy. While Microsoft deprecated database mirroring in SQL Server 2012 in favour of Always On Availability Groups, it remains widely deployed across SQL Server 2008, 2008 R2, and 2012 environments, and many organisations continue to run it in production today.

This guide walks through the complete T-SQL setup process for both synchronous and asynchronous database mirroring modes, including why you'd choose one over the other.

Why Use T-SQL Instead of the GUI?

The SQL Server Management Studio mirroring wizard has a well-known limitation: it refuses to configure mirroring between different SQL Server editions, for example between a Standard and Enterprise instance. This is a GUI restriction only. T-SQL has no such constraint, which makes scripted setup the more reliable and flexible approach regardless of your environment.

Scripting the configuration also gives you a repeatable, documentable process. If you're setting up mirroring across multiple databases or need to rebuild after a failure, a tested T-SQL script is far more useful than clicking through a wizard.

Synchronous vs Asynchronous Mode: Which Should You Use?

Before touching a single line of T-SQL, you need to decide which operating mode suits your requirements.

Synchronous mode (High Safety) requires the principal server to wait for the mirror to confirm it has written each transaction to its log before committing on the principal. This guarantees zero data loss on failover, but introduces latency. If your mirror server is on a slow or high-latency network link, synchronous mode will directly impact your application's transaction throughput.

Asynchronous mode (High Performance) removes that two-phase commit requirement. The principal commits transactions without waiting for the mirror to confirm. This eliminates the latency penalty and is the right choice when network conditions between sites make synchronous mode impractical. The trade-off is that a small amount of data may not have reached the mirror at the moment of a failure.

In practice, synchronous mode suits environments where the principal and mirror are on the same local network with low latency, typically under 1 millisecond round-trip. Asynchronous mode is the standard choice for cross-site mirroring where latency is 5 milliseconds or higher.

Step 1: Prepare the Mirror Database

The mirror database must be restored from a full backup of the principal, followed by a transaction log backup. Both restores must use the WITH NORECOVERY option. This leaves the mirror database in a restoring state, which is required for mirroring to initialise.

Run these steps on the principal server first:

-- Full backup on the principal
BACKUP DATABASE DatabaseName
TO DISK = 'C:\Backups\DatabaseName_Full.bak'
WITH FORMAT, INIT, NAME = 'DatabaseName Full Backup';

-- Transaction log backup on the principal
BACKUP LOG DatabaseName
TO DISK = 'C:\Backups\DatabaseName_Log.trn'
WITH FORMAT, INIT, NAME = 'DatabaseName Log Backup';

Restore both to the mirror server using WITH NORECOVERY:

-- Restore full backup on the mirror (do not recover)
RESTORE DATABASE DatabaseName
FROM DISK = 'C:\Backups\DatabaseName_Full.bak'
WITH NORECOVERY, MOVE 'DatabaseName' TO 'D:\Data\DatabaseName.mdf',
MOVE 'DatabaseName_log' TO 'D:\Logs\DatabaseName_log.ldf';

-- Restore log backup on the mirror (do not recover)
RESTORE LOG DatabaseName
FROM DISK = 'C:\Backups\DatabaseName_Log.trn'
WITH NORECOVERY;

If additional log backups occurred on the principal between your full backup and the log backup, restore those in sequence as well, all with WITH NORECOVERY. The mirror database must be caught up to the point of the last log backup before you can establish the partnership.

Step 2: Create Endpoints on Both Servers

Database mirroring uses dedicated TCP endpoints to communicate between instances. You need to create one on each server, the principal and the mirror. If you're using a witness server for automatic failover, it needs an endpoint too.

Run this on both the principal and mirror servers, adjusting the port number to match your environment. Port 5022 is the Microsoft default for mirroring endpoints, though any available port works.

CREATE ENDPOINT EndPointName
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (
    ROLE = PARTNER,
    AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM RC4
);

Make sure the service accounts running SQL Server on each instance have CONNECT permission on the endpoint. If the instances run under different domain accounts, you'll need to grant access explicitly:

GRANT CONNECT ON ENDPOINT::EndPointName TO [DOMAIN\ServiceAccount];

Firewall rules must permit TCP traffic on your chosen port between the two servers. This is a common oversight that causes mirroring to fail silently during setup.

Step 3: Configure the Mirror Server Partnership

With the endpoints created and the database restored on the mirror, connect to the mirror server and set the principal as its partner:

-- Run on the mirror server
ALTER DATABASE DatabaseName
SET PARTNER = N'TCP://PrincipalServerName:5022';

Replace PrincipalServerName with the fully qualified hostname or IP address of your principal server. The port must match the listener port defined in the endpoint.

Step 4: Configure the Principal Server and Set Operating Mode

Connect to the principal server and complete the configuration. This is where you set the operating mode:

-- Run on the principal server
ALTER DATABASE DatabaseName
SET PARTNER = N'TCP://MirrorServerName:5022';

-- Choose ONE of the following:

-- Asynchronous mode (High Performance - no data loss guarantee)
ALTER DATABASE DatabaseName SET SAFETY OFF;

-- Synchronous mode (High Safety - zero data loss)
ALTER DATABASE DatabaseName SET SAFETY FULL;

Once both SET PARTNER commands have been executed, mirroring initialises and the mirror database begins synchronising. You'll see the database status change to "Synchronising" on the principal and "Restoring" on the mirror in SSMS.

Step 5: Enable Mirroring Monitor

SQL Server includes a built-in monitoring job that tracks mirroring status and records performance data. Set it up on both servers:

EXEC sys.sp_dbmmonitoraddmonitoring; -- Default polling interval is 1 minute

This populates the msdb.dbo.dbm_monitor_data table and enables the Database Mirroring Monitor in SSMS. It's a lightweight job and worth enabling in every mirrored environment.

What to Watch Out For

A few issues come up repeatedly in production mirroring deployments:

  • Database recovery model: The principal database must be in Full recovery model. Mirroring cannot be configured on databases using Simple recovery.
  • Endpoint conflicts: If a mirroring endpoint already exists on an instance from a previous configuration, the CREATE ENDPOINT statement will fail. Check with SELECT * FROM sys.endpoints before running the script.
  • Orphaned logins: SQL Server logins on the principal need to exist on the mirror with matching SIDs. After failover, applications connecting with SQL authentication will fail if logins aren't synchronised. Script logins from the principal and recreate them on the mirror.
  • Witness server: Without a witness, automatic failover is not available. You'll need to initiate manual failover in the event of a principal failure.

Key Takeaways

  • Database mirroring is configured entirely via T-SQL to avoid the edition compatibility restrictions imposed by the SSMS wizard.
  • Synchronous mode guarantees zero data loss but adds commit latency. Asynchronous mode prioritises performance and suits cross-site deployments with higher network latency.
  • The mirror database must be restored with WITH NORECOVERY before the partnership can be established.
  • Both servers require dedicated TCP endpoints, and firewall rules must permit traffic on the mirroring port.
  • Automatic failover requires a witness server. Without one, failover is manual only.

Database mirroring is a proven technology, but it requires careful initial setup and ongoing monitoring to stay reliable. If you're running a mirrored environment and aren't confident the configuration is solid, a DBA Services health check will identify gaps in your setup, including endpoint configuration, login synchronisation, and failover readiness. Our team has been managing SQL Server high availability environments for Australian organisations for over 20 years. Get in touch to find out how we can help.