Facebook Twitter Instagram
    TecAdmin
    • Home
    • Ubuntu 20.04
      • Upgrade Ubuntu
      • Install Java
      • Install Node.js
      • Install Docker
      • Install LAMP Stack
    • Tutorials
      • AWS
      • Shell Scripting
      • Docker
      • Git
      • MongoDB
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    Home»Uncategorized»Setup Database Mirroring in SQL Server 2012 with Certificates

    Setup Database Mirroring in SQL Server 2012 with Certificates

    RahulBy 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
    Previous ArticleIP FailOver Setup Using KeepAlived on CentOS & Red Hat
    Next Article How to Setup ATrpms Yum Repository on CentOS/RHEL and Fedora

    Related Posts

    How to Backup SQL Server Database

    Updated:June 28, 20212 Mins Read

    How To Restore SQL Server Database

    Updated:July 12, 20213 Mins Read

    How to Rename a SQL Server Database

    Updated:June 21, 20213 Mins Read

    (Fixed) Cannot drop the database because it is being used for replication. (Microsoft SQL Server, Error: 3724)

    Updated:June 23, 20211 Min Read

    How to Rename a Column in SQL Server

    Updated:June 28, 20212 Mins Read

    How to Find Currently Running Query in SQL Server

    Updated:May 11, 20171 Min Read

    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

    Recent Posts
    • What is the /etc/nsswitch.conf file in Linux
    • How to Install Ionic Framework on Ubuntu 22.04
    • What is the /etc/hosts file in Linux
    • How to Install Angular CLI on Ubuntu 22.04
    • How to Install Composer on Ubuntu 22.04
    Facebook Twitter Instagram Pinterest
    © 2022 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

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