You've been tasked with ensuring fault tolerance for legacy databases on outdated SQL Server editions where Always On AG is unavailable due to licensing. We frequently encounter such requests from clients who still have systems running on legacy versions in production. Database Mirroring is a working solution, even though Microsoft has marked it as deprecated. Below are practical details to help you avoid common pitfalls.
Why Database Mirroring Is Still in Demand
Despite the age of the technology, mirroring remains the only option for older SQL Server editions (Standard, Web) without an Enterprise license. It requires no additional components like Windows clustering, and setup takes 2–3 days. Synchronous mode provides RPO=0, which is critical for financial systems and ERP. Additionally, mirroring is OS-version independent and can be deployed on isolated servers without shared storage.
How to Choose: Synchronous or Asynchronous Mode
The choice is determined by RPO and performance requirements. Synchronous mode (High Safety) guarantees zero data loss but increases response time by 10–30% due to waiting for mirror acknowledgment. Asynchronous mode (High Performance) does not wait, maximizing throughput, but data loss of a few seconds is possible during a failure. Automatic failover works only in synchronous mode with a witness server.
| Mode | RPO | RTO | Performance | Automatic Failover |
|---|---|---|---|---|
| High Safety (synchronous) | 0 | Seconds | Decreases by 10–30% | Yes (with witness) |
| High Performance (asynchronous) | A few seconds (possible loss) | Minutes | Maximum | No (manual only) |
How to Set Up Database Mirroring via T-SQL
Setup consists of three steps: creating endpoints, initializing the mirror database, and starting the session. The procedure typically takes about 30–40 minutes with clean servers.
Creating Endpoints
On both servers and the witness (optional), create endpoints on TCP port 5022:
-- On PRINCIPAL and MIRROR
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED ALGORITHM AES);
-- On WITNESS (can be SQL Express)
CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);Initializing the Mirror Database
Perform a full backup plus log backup on the principal, restore on the mirror in NORECOVERY mode:
-- On principal: backup
BACKUP DATABASE AppDB TO DISK = 'D:\Backup\AppDB_mirror_init.bak' WITH COMPRESSION;
BACKUP LOG AppDB TO DISK = 'D:\Backup\AppDB_mirror_log.bak';
-- On mirror: restore
RESTORE DATABASE AppDB FROM DISK = 'D:\Backup\AppDB_mirror_init.bak' WITH NORECOVERY, REPLACE, MOVE 'AppDB' TO 'E:\SQLData\AppDB.mdf', MOVE 'AppDB_log' TO 'E:\SQLLog\AppDB_log.ldf';
RESTORE LOG AppDB FROM DISK = 'D:\Backup\AppDB_mirror_log.bak' WITH NORECOVERY;Starting the Session and Verification
First on the mirror, then on the principal:
-- On MIRROR: ALTER DATABASE AppDB SET PARTNER = N'TCP://SQL01.corp.local:5022';
-- On PRINCIPAL: ALTER DATABASE AppDB SET PARTNER = N'TCP://SQL02.corp.local:5022';
-- Adding witness (if automatic failover needed): ALTER DATABASE AppDB SET WITNESS = N'TCP://SQL03.corp.local:5022';
-- Check status: SELECT mirroring_state_desc, mirroring_role_desc, mirroring_safety_level_desc FROM sys.database_mirroring WHERE database_id = DB_ID('AppDB');Normal state is SYNCHRONIZED (synchronous) or SYNCHRONIZING (asynchronous). SUSPENDED indicates a problem.
How to Avoid Typical Errors
A classic situation: after setting up endpoints, connectivity is verified (telnet to port 5022 works), but ALTER DATABASE SET PARTNER hangs with error 1418. The cause is that SQL Server service accounts do not have CONNECT permission on each other's endpoints. The solution is an explicit GRANT:
-- On SQL01 allow SQL02
GRANT CONNECT ON ENDPOINT::Mirroring TO [CORP\SQL02SvcAccount];
-- On SQL02 allow SQL01
GRANT CONNECT ON ENDPOINT::Mirroring TO [CORP\SQL01SvcAccount];If SQL runs under Local System or Network Service, use certificates instead of Windows authentication. Another common mistake is forgetting to configure DNS or add hosts file entries, causing connection failures.
| Error | Cause | Solution |
|---|---|---|
| Error 1418 on SET PARTNER | No CONNECT on endpoint | GRANT CONNECT |
| SUSPENDED after network issue | Loss of connectivity, log overflow | Check logs, ALTER ... RESUME |
| Automatic failover not triggered | No witness or asynchronous mode | Add witness, switch to synchronous |
What Our Mirroring Setup Includes
Our engineers perform the full cycle: audit of current configuration (SQL Server version, edition, network connectivity), design of the scheme (mode selection, witness decision), configuration of endpoints and permissions, initialization of the mirror database with relocation to new LUNs, testing of all failover scenarios (manual and automatic), and development of a runbook with monitoring scripts that alert via Telegram/Slack. Average setup time is 2–3 days including testing. For migration to Always On AG, timelines extend to 5–7 days, but downtime does not exceed 10 minutes.
Timelines and Cost
Setup of mirroring takes from 2 to 3 business days. If migration to Always On AG is required, from 5 to 7 days. Cost is determined individually after an audit, considering the SQL Server version, schema complexity, and RPO/RTO requirements. Contact us for an audit of your infrastructure — our team has implemented such transitions for over 30 clients. Get a consultation on choosing the optimal fault tolerance strategy.