Facebook X (Twitter) Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook X (Twitter) Instagram
    TecAdmin
    You are at:Home»Uncategorized»Setup Database Mirroring in SQL Server 2012 with Certificates

    Setup Database Mirroring in SQL Server 2012 with Certificates

    By RahulSeptember 26, 20133 Mins Read

    Many times we faced issues with mirroring setup with authentication, So in this article we are going to configure database mirroring by creating endpoint and required certificates for authenticate and authorization.

    Follow below steps to setup database mirroring in sql server with 1 Principal server and 1 Mirror server. This article does not include witness server. So if you want automatic failover setup this article would not help you. Read this article for role of witness server

    Step 1. Setup Endpoint and Certificates

    With the first step we are going to create endpoint on both server and configure certificates for authentication and authorization.

    Create Certificate and end-points (On Principal Instance):
    use master;
    GO
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secure_Password';
    GO
    
    CREATE CERTIFICATE principal_cert WITH SUBJECT = 'Principal Server Certificate', START_DATE = '2013/01/01', EXPIRY_DATE = '2020/01/01';
    GO
    
    CREATE ENDPOINT Mirroring STATE = STARTED
        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
        FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE principal_cert, ENCRYPTION = disabled, ROLE = ALL);
    GO
    
    BACKUP CERTIFICATE principal_cert TO FILE = 'D:certificatesprincipal_cert.cer'
    GO
    
    Create Certificate and end-points (On Mirror Instance):
    USE master
    GO
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Secure_Password';
    GO
    
    CREATE CERTIFICATE mirror_cert WITH SUBJECT = 'Mirror Server Certificate', START_DATE = '2013/01/01', EXPIRY_DATE = '2020/01/01';
    GO
    
    CREATE ENDPOINT Mirroring STATE = STARTED
        AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
        FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE mirror_cert, ENCRYPTION = disabled, ROLE = ALL );
    GO
    
    BACKUP CERTIFICATE mirror_cert
        TO FILE = 'D:certificatesmirror_cert.cer';
    GO
    
    Create User and Associate Certificate (On Principal Instance):

    At this stage we need to exchange certificates to other instance. Copy Principal instance certificate on mirror server and Mirror instance certificate to Principal server manually.

    USE MASTER
    GO
    
    CREATE LOGIN mirror_login WITH PASSWORD = 'Secure_Password';
    GO
    
    CREATE USER mirror_user FOR LOGIN mirror_login;
    GO
    
    CREATE CERTIFICATE mirror_cert
        AUTHORIZATION mirror_user
        FROM FILE = 'D:certificatemirror_cert.cer';
    GO
    
    GRANT CONNECT ON ENDPOINT::Mirroring TO [mirror_login];
    GO
    
    Create User and Associate Certificate ( On Mirror Instance):
    USE MASTER
    GO
    CREATE LOGIN principal_login WITH PASSWORD = 'Secure_Password';
    go
    
    CREATE USER principal_user FROM LOGIN principal_login;
    go
    
    CREATE CERTIFICATE principal_cert
    	AUTHORIZATION Principal_user
    	FROM FILE = 'D:certificateprincipal_cert.cer';
    Go
    
    GRANT CONNECT ON Endpoint::Mirroring TO [Principal_Login];
    go
    
    Step 2. Copy Database on Mirror Server

    After configuring authentication do following steps given below screen shots.

    Update Principal database to Full Recovery Mode:
    ALTER DATABASE MirrorDBName SET RECOVERY FULL;
    GO
    
    Dump database on Principal Server:
    USE MASTER
    GO
    
    BACKUP DATABASE MirrorDBName
        TO DISK = 'D:BackupsMirrorDBName_full.bak'
    GO
    
    Dump Log on Principal Server:
    USE MASTER
    GO
    BACKUP LOG MirrorDBName
        TO DISK = 'D:BackupsMirrorDBName_log.trn'
    GO
    

    After taking backup copy the database backup and log backup to Mirror server manually. then after go to next step.

    Restore Backup on Mirror Server with No Recovery option:
    USE MASTER
    GO
    
    RESTORE DATABASE MirrorDBName
        FROM DISK = 'D:BackupsMirrorDBName_full.bak'
        WITH NORECOVERY
    GO
    
    Restore Log on Mirror Server with No Recovery option:
    USE MASTER
    GO
    
    RESTORE LOG MirrorDBName
        FROM DISK = 'D:BackupsMirrorDBName_log.trn'
        WITH NORECOVERY
    GO
    
    Step 3. Enable Database Mirroring in SQL Server

    After completing step 1 and 2, now we are ready to enable mirroring between databases using following commands.

    Execute this on Principal Database Instance.
    ALTER DATABASE MirrorDBName
        SET PARTNER = 'TCP://MIRROR-INSTANCE:5023'
    GO
    
    Execute this on Mirror Database Instance.
    ALTER DATABASE MirrorDBName
        SET PARTNER = 'TCP://PRINCIPAL-INSTANCE:5022'
    GO
    

    Database Mirroring in SQL Server

    SQL SErver SQL Server Mirroring
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    PowerShell Script to Backup SQL Server Databases

    Understanding the difference between ‘git pull’ and ‘git fetch’

    How to Backup SQL Server Database

    How to Backup SQL Server Database

    View 1 Comment

    1 Comment

    1. User on May 19, 2016 9:10 am

      You have a few errors in your scripting. For instance, to enable database mirroring, you need to run the alter command on the mirror instance first and then the principal instance.

      Also, when creating the users using the certificates which were created in the previous step, you are not consistent with the naming on the certificates (i.e. certificatesprincipal_cert.cer vs. certificateprincipal_cert.cer). To help laymans like me banging their head on the wall, I would suggest making these updates. Otherwise the instructions were very simple to follow.

      Reply

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • Difference Between Full Virtualization vs Paravirtualization
    • Virtualization vs. Containerization: A Comparative Analysis
    • Using .env Files in Django
    • Using .env File in FastAPI
    • Setting Up Email Notifications for Django Error Reporting
    Facebook X (Twitter) Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.