Skip to content

Database Mirroring

    Database Mirroring is process having a redundant copy of a single database at another location to ensure continuous data availability in case of a disaster on the principal database.

    Database mirroring ensures that one viable copy of a database will always remain accessible during disaster recovery or down time needed for the principal server

    The principal server is the source server and the mirror is the destination server

    There are two types of operation modes when using database mirroring:

    Synchronous operation mode:

    This is used when very real time accuracy is required; which means that the system must immediately copy every change in the principal’s content to the mirror and vice-versa (this is referred to as a hot standby)

    Asynchronous operation mode:

    This is used when the content is not fully synchronized, and thus may result in some data loss (this is referred to as a warm standby)

    Advantages:

    • Relatively easy to set up
    • Database mirroring is an automatic failover process
    • All application connection can be redirected automatically with proper configuration
    • There will not be data transfer latency (synchronous mode)

    Disadvantages:

    • Can only have one to one relationship with principal and mirror
    • Cannot be used for reporting solution (mirror in restoring state)
    • Mirroring supports only Full Recovery (not bulk or simple mode)

    SQL Script for Database Mirroring

    --1 primary
    
    alter database mirror
    set recovery full
    go
    
    --2 primary
    backup database mirror
    to disk = 'c:\s\full.bak'
    go
    
    --3 primary
    
    backup log mirror
    to disk = 'c:\s\tlog.trn'
    go
    
    --4 primary
    
    create endpoint endpoint_principal
    state = started
    as tcp (listener_port = 5022)
    for database_mirroring (role = partner)
    go
    
    --5 primary
    
    alter database mirror
    set partner = 'tcp://server1:5022'
    go
    
    --6 mirror
    
    USE [master]
    RESTORE DATABASE [mirror] 
    FROM  DISK = N'C:\d\full.bak' 
    WITH  FILE = 1,  
    MOVE N'mirror' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mirror.mdf',  
    MOVE N'mirror_log' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mirror_log.ldf',  
    NORECOVERY,  
    NOUNLOAD,  
    STATS = 5
    GO
    
    --log mirror
    
    RESTORE LOG [mirror] 
    FROM  DISK = N'C:\d\tlog.trn' 
    WITH  FILE = 1,  
    NORECOVERY,  
    NOUNLOAD,  
    STATS = 10
    GO
    
    --7 mirror
    
    create endpoint endpoint_mirror
    state = started
    as tcp (listener_port = 5023)
    for database_mirroring (role = partner)
    go
    
    --8 mirror
    
    alter database mirror
    set partner = 'tcp://server2:5023'
    go
    
    --ENABLE SQLCMD MODE TO SWITCH BETWEEN DIFFERENT SQL SERVERS WTIH A A QUERY PANE
    --QUERY - SQLMCMD MODE 
    
    --:CONNECT SQLSERVER1
    --Or for a non-default instance 
    --:CONNECT DESKTOP-QMOOH4U\DEV
    
    --DROP DATABASE MIRROR
    
    USE MASTER
    GO
    
    CREATE DATABASE MIRROR
    GO
    
    USE MIRROR
    GO
    
    CREATE TABLE CHOCOLATES
    (NAME VARCHAR (25))
    
    INSERT INTO CHOCOLATES
    VALUES ('GODIVA'),('MARS'),('HERSHYS'),('DOVE'),('KITKAT')
    
    SELECT * FROM CHOCOLATES
    
    BACKUP DATABASE [MIRROR] TO  DISK = N'C:\Mirror\CHOC.BAK' WITH iNIT
    
    BACKUP LOG [MIRROR] TO  DISK = N'C:\Mirror\CHOC.TRN' WITH iNIT
    
    USE [master]
    GO
    
    --CHANGE CONNECTIONS TO DEV SERVER: THEN RUN
    
    :CONNECT DESKTOP-QMOOH4U\DEV
    
    RESTORE DATABASE [MIRROR] 
    FROM  DISK = N'C:\Mirror\CHOC.BAK' 
    WITH  FILE = 1,  
    MOVE N'MIRROR' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\MIRROR.mdf',  
    MOVE N'MIRROR_log' 
    TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.DEV\MSSQL\DATA\MIRROR_log.ldf',  
    NORECOVERY
    GO
    
    RESTORE LOG [MIRROR] 
    FROM  DISK = N'C:\Mirror\CHOC.TRN' 
    WITH  FILE = 1,  
    NORECOVERY
    GO
    
    
    --GO TO SECURITY - LOGINS - (RC) [NT AUTHORITY\SYSTEM] - PROPERTIES - SERVERROLE (SYSADMIN) - SELECT DB FOR DBM - DB OWNER
    
    --CHANGE CONNECTIONS TO DEV SERVER: THEN RUN
    
    :CONNECT DESKTOP-QMOOH4U\DEV
    
    INSERT INTO CHOCOLATES
    VALUES ('OH HENRY'),('ALMOND JOY')
    
    SELECT * FROM CHOCOLATES
    
    ALTER DATABASE MIRROR SET PARTNER OFF
    DROP DATABASE MIRROR
    
    USE MASTER
    GO
    
    CREATE DATABASE MIRROR
    GO
    
    USE MIRROR
    GO
    
    CREATE TABLE CHOCOLATES
    (NAME VARCHAR (25))
    
    INSERT INTO CHOCOLATES
    VALUES ('GODIVA'),('MARS'),('HERSHYS'),('DOVE'),('KITKAT')
    
    SELECT * FROM CHOCOLATES
    
    --1 primary
    
    alter database mirror
    set recovery full
    go
    
    --2 primary
    backup database mirror
    to disk = 'c:\s\full.bak'
    go
    
    --4 primary
    
    backup log mirror
    to disk = 'c:\s\tlog.trn'
    go
    
    --6 primary
    
    create endpoint endpoint_principal
    state = started
    as tcp (listener_port = 5022)
    for database_mirroring (role = partner)
    go
    
    
    --9 primary
    
    alter database mirror
    set partner = 'tcp://server1:5022'
    go
    
    
    --SQL SCRIPTS FOR MONITORING AND INVESTIGATING  DATABASE MIRRORING:
    
    --INFORMATION ABOUT DATABASE MIRRORING
    
    select DB_NAME(database_id) dbname,mirroring_state_desc,mirroring_role_desc,
    mirroring_safety_level_desc,mirroring_safety_sequence
    mirroring_partner_name,mirroring_partner_instance,
    mirroring_witness_state,mirroring_witness_state_desc,
    mirroring_failover_lsn,mirroring_connection_timeout,mirroring_redo_queue,
    mirroring_end_of_log_lsn,mirroring_replication_lsn,*
    from master.sys.database_mirroring
    where mirroring_state is not null
    
    --INFORMATION ABOUT DATABASE MIRRORING CONNECTIONS
    
    select state_desc,connect_time,login_time,authentication_method,principal_name,
    remote_user_name,last_activity_time,is_accept,login_state_desc,
    receives_posted,sends_posted,total_bytes_sent,total_bytes_received,
    total_sends,total_receives,*
    from sys.dm_db_mirroring_connections
    
    --INFORMATION ABOUT DATABASE MIRRORING ENDPOINTS
    
    select name,endpoint_id,protocol_desc,type_desc,state_desc,role_desc,
    connection_auth_desc,*
    from sys.database_mirroring_endpoints
    
    --SCRIPT TO INDICATE WHICH DATABASE HAS BEEN MIRRORED:
    
    SELECT DB_NAME(database_id) AS mirrored
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    ORDER BY DB_NAME(database_id);
    
    --SCRIPT TO INDICATE WHICH DATABASE IS IN SYNC MODE
    
    SELECT DB_NAME(database_id) AS synchronous_mode
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    --AND mirroring_role_desc = 'PRINCIPAL'
    --AND mirroring_role_desc = 'MIRROR'
    AND mirroring_safety_level_desc = 'FULL'
    ORDER BY DB_NAME(database_id);
    
    --SCRIPT TO INDICATE WHICH DATABASE IS IN ASYNC MODE
    
    SELECT DB_NAME(database_id) AS asynchronous_mode
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    --AND mirroring_role_desc = 'PRINCIPAL'
    --AND mirroring_role_desc = 'MIRROR'
    AND mirroring_safety_level_desc = 'OFF'
    ORDER BY DB_NAME(database_id);
    
    --SCRIPT TO INDICATE WHICH DATABASE HAS FULLT SYNCHRONIZED OR NOT
    
    SELECT 
     DB_NAME(database_id) AS fully_synchronized
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    --AND mirroring_role_desc = 'PRINCIPAL'
    --AND mirroring_role_desc = 'MIRROR'
    AND mirroring_state_desc = 'SYNCHRONIZED' 
    ORDER BY DB_NAME(database_id);
    
    --SETTING THE ASYNCHRONOUS MODE OFF AND ON
     
    SELECT 
      'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY OFF;'
    + ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to asynchronous mirroring mode.'';'
      AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    AND mirroring_role_desc = 'PRINCIPAL'
    AND mirroring_safety_level_desc = 'FULL'
    ORDER BY DB_NAME(database_id);
    
     SELECT 
      'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SAFETY FULL;'
    + ' PRINT ''[' +  DB_NAME(database_id) + '] has been set to synchronous mirroring mode.'';'
      AS command_to_set_mirrored_database_to_use_synchronous_mirroring_mode
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    AND mirroring_role_desc = 'PRINCIPAL'
    AND mirroring_safety_level_desc = 'OFF'
    ORDER BY DB_NAME(database_id);
    
     --TO SUSPEND THE DATABASE MIRRORING
    
     SELECT 
     'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER SUSPEND;'
    + ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring paused.'';' 
      AS command_to_pause_mirroring_for_the_mirrored_database 
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    AND mirroring_role_desc = 'PRINCIPAL'
    AND mirroring_state_desc <> 'SUSPENDED'
    ORDER BY DB_NAME(database_id);
    
     --TO RESUME THE DATABASE MIRRORING 
    
    SELECT 
     'ALTER DATABASE [' + DB_NAME( database_id ) + '] SET PARTNER RESUME;'
    + ' PRINT ''[' +  DB_NAME(database_id) + '] has had mirroring resumed.'';' 
      AS command_to_resume_mirroring_for_the_mirrored_database 
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    AND mirroring_role_desc = 'PRINCIPAL'
    AND mirroring_state_desc = 'SUSPENDED'
    ORDER BY DB_NAME(database_id);
    
    --SETTING TO FAILOVER DATABASE MIRRORING
     
    SELECT 
     'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FAILOVER;'
    + ' PRINT ''[' +  DB_NAME(database_id) + '] has been been manually failed over.'';' 
      AS command_to_manually_failover_the_mirrored_database
    FROM master.sys.database_mirroring 
    WHERE 1=1
    AND mirroring_guid IS NOT NULL
    AND mirroring_role_desc = 'PRINCIPAL'
    AND mirroring_safety_level_desc = 'FULL'
    AND mirroring_state_desc = 'SYNCHRONIZED'
    ORDER BY DB_NAME(database_id);
    
    -- HISTORY OF RESULTS
    
    Exec msdb..sp_dbmmonitorresults 'db',1,0
    
    --use on mirror db to stop miroring and drop db
    alter database mirror set partner off
    restore database mirror
    drop database MIRROR
    
    COMPARISON
    LOG SHIPINGDATABASE MIRRORING
    It automatically sends transaction log backups from one database On another server. two copies of a single database on different server instances of SQL Server Database Engine.
    T-Logs are backed up and transferred to secondary server.Individual T-Log records are transferred using TCP endpoints.
    It can be configured as one to many One principal server to one mirror server.
    requires a manual failoverAutomatic or manual
    You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.In a continous revoery state: Mirrored DB can only be accessed using snapshot DB.
     Supports both Bulk Logged Recovery Model and Full Recovery Model. Supports only Full Recovery model.
    The restore can be completed using either the NORECOVERY or STANDBY option.The restore can be completed using with NORECOVERY.
    No limit. Can log ship to many serverGenerally good to have 10 DB’s for one server.
    There will be data transfer latency. >1min.There will not be data transfer latency.
    Both committed and uncommitted transactions are transferred to the secondary database.Only committed transactions are transferred to the mirror database.
    It provides a warm standby solution that has multiple copies of a database and require a manual failover.When a database session is synchronized, database provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

    To configure database mirroring, you can use the following procedure:

    1. Connect to the primary server.

    SQL Server Database Mirroring

    2. Ensure the database is at Full recovery model.

    SQL Server Database Mirroring


    3. Enable database mirroring.

    SQL Server Database Mirroring

    4. Configure database mirroring security.

    SQL Server Database Mirroring

    5. Define whether or not a witness server is used for automatic failover. Since we do not want automatic failover, select “No” and continue to next step.

    SQL Server Database Mirroring


    6. Define database mirroring endpoint for the principal server. Be sure to open the listener port in the server firewall.

    SQL Server Database Mirroring


    7. Connect and define the endpoint for the mirror server. In order to successfully connect to the secondary server, we must ensure the instance listening port and SQLBrowser listening port are open.

    SQL Server Database Mirroring
    SQL Server Database Mirroring

    8. Define the security account for principal and mirror server. This service account should be part of the sysadmin SQL server role if it’s not already granted so.

    https://isqlplus.com/sql-server/database-mirroring/

    9. Click Finish to commit the endpoint configuration.

    https://isqlplus.com/sql-server/database-mirroring/

    10. Start mirroring session. You would only do so if a full database backup and a transaction log backup from the principal server have been restored on the mirror server with “no recovery”.

    SQL Server Database Mirroring


    11. If all is configured correctly, the database mirroring session is now active.

    SQL Server Database Mirroring