How to setup MSSQL database mirroring?

Following are the steps to set up database mirroring using certificate-based authentication. You should use the encryption while doing database mirroring unless you are sure that your network is secure. We assume that you have two servers here. One is called a principal server, and one is a mirror server. Data will be sync from principal to mirror database server.

Configure Outbound Connection for Principal Database

  1. Create a certificate for the principal server database using the below query. 

    USE master; 
    CREATE CERTIFICATE principal_cert
    WITH SUBJECT = 'principal certificate';
    GO
  2. Create a mirroring endpoint using the below query.

    CREATE ENDPOINT Mirroring 
    STATE = STARTED
    AS TCP (
    LISTENER_PORT=5022
    , LISTENER_IP = ALL
    )
    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE principal_cert
    , ENCRYPTION = REQUIRED ALGORITHM AES
    , ROLE = ALL
    );
    GO
  3. Please take a backup of the principal server certificate and copy it to the mirror server.

    BACKUP CERTIFICATE principal_cert TO FILE = 'C:\principal_cert.cer'; 
    GO
  4. Move the certificate to the mirror server.

Configure Outbound Connection for Source Database

  1. Create a certificate on the mirror server.

    CREATE CERTIFICATE mirror_cert 
    WITH SUBJECT = 'mirror certificate for database mirroring';
    GO
  2. Create a mirroring endpoint using the below query.

    CREATE ENDPOINT Endpoint_Mirroring 
    STATE = STARTED
    AS TCP (
    LISTENER_PORT=5023
    , LISTENER_IP = ALL
    )
    FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE mirror_cert
    , ENCRYPTION = REQUIRED ALGORITHM AES
    , ROLE = ALL
    );
    GO
  3. Take a backup of the source server certificate.

    BACKUP CERTIFICATE mirror_cert TO FILE = 'C:\mirror_cert.cer'; 
    GO
  4. Move the certificate to the principal server.

Configuring Inbound Connections on Principal Server 

  1. Configure login on the principal server for mirror server.

    USE master; 
    CREATE LOGIN mirror_login WITH PASSWORD = '[email protected]#';
    GO
  2. Create a user account to log in on the principal server.

    CREATE USER mirror_user FOR LOGIN mirror_login; 
    GO
  3. Configure the certificate with the created user. 

    CREATE CERTIFICATE mirror_cert 
    AUTHORIZATION mirror_user
    FROM FILE = 'C:\mirror_cert.cer'
    GO
  4. Let us provide grant permission to the created endpoint. 

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [mirror_login]; 
    GO

Configure Inbound Connections on Mirroring server

  1. Setup login on the mirror server for the principal server. 

    USE master; 
    CREATE LOGIN principal_login WITH PASSWORD = '=Sample#2_Strong_Password2';
    GO
  2. Create a user account to log in on the principal server.

    CREATE USER principal_user FOR LOGIN principal_login; 
    GO
  3. Configure the certificate with the created user. 

    CREATE CERTIFICATE principal_cert 
    AUTHORIZATION principal_user
    FROM FILE = 'C:\principal_cert.cer'
    GO
  4. Let us provide grant permission to the created endpoint. 

    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [principal_login]; 
    GO
  5. If you have a firewall enabled, make sure you allow ports 5022,5023 as an inbound connection of both the server.

Setup Mirroring Partners on both the servers

  1. Configure the mirror server to set as a partner with the principal server.

    SET PARTNER = 'TCP://Fully-qualified-domain-name of principal server:5022'; 
    GO
  2. Configure the principal server to set as a partner with the mirror server.

    SET PARTNER ='TCP://Fully-qualified-domain-name of mirror server:5023'; 
    GO

Perform Database Backup/Restore

  1. The database at both the server should be with full recovery mode. Please refer to change DB to full recovery mode.

  2. Generate the full backup of your database using the below query. Here, test_mirror is the database name.

    BACKUP DATABASE test_mirror TO DISK = 'C:\backup\test_mirror.bak';
  3. Generate the log backup of your database using the below query. 

    BACKUP LOG test_mirror TO DISK = 'C:\backup\test_mirror.trn';
  4. Move the database backup and log backup to the mirror server.

  5. Restore the database to the mirror server with the NORECOVERY option. Here, we have moved the database and log backup to the c:\backup at the mirror server.

    RESTORE DATABASE TestMirror FROM DISK = 'C:\Backup\test_mirror.bak' 
    WITH FILE = 1, MOVE test_mirror_log' TO 
    'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_mirror1.ldf', 
    NORECOVERY, NOUNLOAD, STATS = 10;
  6. Restore the database log file with the NORECOVERY option.

    RESTORE LOG test_mirror FROM DISK ='C:\Backup\test_mirror.trn' 
    WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;

Perform Database Mirroring

  1. Right-click on the database >> Task >> Mirror. 

  2. Click on Configure security.

  3. Tick to Yes, if you want to set up a witness server. Here, we haven't set up a witness server.

  4. Click on the connect button to connect the mirror database. Enter the login details and connect.

  5. We don't need to enter any service account so click on the Next >> Finish.



  6. You should receive the success status at the configure endpoint box. Click on the close button.

  7. Click on Don't start mirroring.

  8. Setup the operating mode to High safety without automatic failover (synchronous)

  9. Finally, click on start mirroring. You will see the mirroring status. The databases are fully synchronized.

  10. At the principal server, you will see the database with (Principal, Synchronized) status.

  11. You will see the database with (Mirror, Synchronized/Restoring) status at the mirror server.

  12. Verify the database mirroring using the below query. 

    SELECT state_desc
    FROM sys.database_mirroring_endpoints;

Related Articles

How to configure MSSQL server default language configuration option?

Recently, we faced strange issue wherein one of our customers was capturing error messages coming...

How to change port for MSSQL Server 2008?

In Windows VPS, 1533 is the default port for MSSQL Server. Following steps will guide you to...

How to change Database Collation in MsSQL Server?

Collation is a configuration level setting in the MSSQL. It directs how the character data will...

How do I connect to MSSQL Server 2008 from SQL Server Management Studio?

This article will guide you on how to connect to MS SQL Server 2008 from SQL Server Management...

How to shrink MSSQL Database?

Important Note: We don't recommend to shrink your database. You can involve your Database...

  • 0 Users Found This Useful

Was this answer helpful?