Wednesday, November 23, 2011

Setting Up Database Mirroring Using Certificates

You have to follow these 4 steps for each partner (Principal, Mirror, and Witness) for configuring database mirroring through using Certificates.
1.    First you need to create a database Master Key, in the master database.
2.    In the master database, create an encrypted certificate on the SQL Server Instance.
3.    Then you have to create an endpoint for the SQL server instance using its certificate.
4.    Back up the certificate and securely copy the backup file it to the other systems.

You must complete these above 4 steps for each partner and the witness, if there is a witness server.  

Step1. Run below script for creating encryption key, certificate and end-points on Principal Server.....

 1.1   On the master database, create the database master key, if needed through below script.
 
                USE MASTER
                GO
                CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
                GO

1.2   Make a certificate for this Principal server.

                USE MASTER
                GO
                CREATE CERTIFICATE Principal_Cert
                      WITH SUBJECT = 'Principal Certificate';
                GO

1.3   Create a mirroring endpoint for server instance using the certificate.

                 CREATE ENDPOINT End_Mirroring
                    STATE = STARTED
                    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
                    FOR DATABASE_MIRRORING
                    (
                        AUTHENTICATION = CERTIFICATE Principal_Cert,
                        ENCRYPTION = REQUIRED ALGORITHM RC4,
                        ROLE = ALL
                    )
                GO

1.4   Back up the certificate and copy it to the Mirror Server, in order to configure inbound connections on the other servers.

                USE MASTER 
                GO 
                BACKUP CERTIFICATE Principal_Cert     
                TO FILE = 'C:\Certificate\Principal_Cert.cer' 
                GO


Step 2. Run below script for creating encryption key, certificate and end-points on Mirror Server.....

2.1   On the master database, create the database master key, if needed through below script.

                USE MASTER
                GO
                CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password'
                GO

2.2   Make a certificate for this Mirror server.  

                USE MASTER 
                GO 
                CREATE CERTIFICATE Mirror_Cert 
                WITH SUBJECT = 'Mirror Certificate';
                GO

2.3   Create a mirroring endpoint for server instance using the certificate. 

                CREATE ENDPOINT End_Mirroring 
                STATE = STARTED 
                AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL) 
                FOR DATABASE_MIRRORING 
                ( 
                AUTHENTICATION = CERTIFICATE Mirror_Cert, 
                ENCRYPTION = REQUIRED ALGORITHM RC4, 
                ROLE = ALL 
                ) 
                GO

2.4   Back up the certificate and copy it to the Principal Server, in order to configure inbound connections on the other servers.

                USE MASTER 
                GO 
                BACKUP CERTIFICATE Mirror_Cert 
                    TO FILE = 'C:\Certificate\Mirror_Cert.cer' 
                GO

Step 3. Run below script for creating encryption key, certificate and end-points on Witness Server..... 

3.1   On the master database, create the database master key, if needed through below script.

                USE MASTER
                GO
                CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password' 
                GO

3.2   Make a certificate for this Witness server.  

                USE MASTER
                GO
                CREATE CERTIFICATE Witness_Cert
                    WITH SUBJECT = 'Witness Certificate';
                GO

3.3   Create a mirroring endpoint for server instance using the certificate. 

                CREATE ENDPOINT End_Mirroring 
                    STATE = STARTED 
                    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL) 
                    FOR DATABASE_MIRRORING 
                    ( 
                        AUTHENTICATION = CERTIFICATE Witness_Cert, 
                        ENCRYPTION = REQUIRED ALGORITHM RC4, 
                        ROLE = ALL 
                    ) 
                GO

3.4   Back up the certificate and copy it to the Principal and Mirror Server, in order to configure inbound connections on the other servers.

                USE MASTER
                GO 
                BACKUP CERTIFICATE Witness_Cert 
                    TO FILE = 'C:\Certificate\Witness_Cert.cer' 
                GO

Step 4. Run below script in Principal Instance for creating login, user and associate certificate with user on Principal Server. 

Now copy the Mirror_Cert.cer and Witness_Cert.cer on to the Principal Instance.

4.1   We are creating a SQL Login here, for Windows logins, use the Grant Login instead of Create Login...

                USE MASTER
                GO
                CREATE LOGIN Mirror_login WITH PASSWORD = 'Password'
                GO

                USE MASTER
                GO
                CREATE LOGIN Witness_login WITH PASSWORD = 'Password'
                GO

4.2   Create a user for that login.

                USE MASTER
                GO
                CREATE USER Mirror_User FOR LOGIN Mirror_login
                GO

                USE MASTER
                GO
                CREATE USER Witness_User FOR LOGIN Witness_login
                GO

4.3   Associate the certificate with the user.
                USE MASTER
                GO
                CREATE CERTIFICATE Mirror_Cert
                    AUTHORIZATION Mirror_User
                    FROM FILE = 'C:\Certificate\Mirror_Cert.cer'
                GO

                USE MASTER
                GO
                CREATE CERTIFICATE Witness_Cert
                    AUTHORIZATION Witness_User
                    FROM FILE = 'C:\Certificate\Witness_Cert.cer'
                GO

4.4   Grant CONNECT permission on the login for the remote mirroring endpoint.

                USE MASTER
                GO
                GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login] 
                GO

                USE MASTER
                GO
                GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login] 
                GO

Step 5. Run below script in Mirror Instance for create login, user and associate certificate with user.

Now copy the Principal_Cert.cer and Witness_Cert.cer on to the Mirror Instance.

5.1   We are creating a SQL Login here. For Windows logins, use the Grant Login instead of Create Login...

                USE MASTER
                GO
                CREATE LOGIN Principal_login WITH PASSWORD = 'Password'
                GO

                USE MASTER 
                GO
                CREATE LOGIN Witness_login WITH PASSWORD = 'Password'
                GO

5.2   Create a user for that login.

                USE MASTER 
                GO
                CREATE USER Principal_User FOR LOGIN Principal_login
                GO

                USE MASTER 
                GO
                CREATE USER Witness_User FOR LOGIN Witness_login
                GO

5.3   Associate the certificate with the user.

                USE MASTER 
                GO
                CREATE CERTIFICATE Principal_Cert
                    AUTHORIZATION Principal_User
                    FROM FILE = 'C:\Certificate\Principal_Cert.cer'
                GO

                USE MASTER 
                GO
                CREATE CERTIFICATE Witness_Cert
                    AUTHORIZATION Witness_User
                    FROM FILE = 'C:\Certificate\Witness_Cert.cer'
                GO

5.4   Grant CONNECT permission on the login for the remote mirroring endpoint.

                USE MASTER 
                GO
                GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principal_login]
                GO

                USE MASTER 
                GO
                GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Witness_login]
                GO

Step 6. Run below script in Witness Instance for create login, user and associate certificate with user.

Now copy the Principal_Cert.cer and Mirror_Cert.cer on to the Witness Instance.

6.1   We are creating a SQL Login here. For Windows logins, use the Grant Login instead of Create Login...

                USE MASTER 
                GO
                CREATE LOGIN Principal_login WITH PASSWORD = 'Password'
                GO

                USE MASTER 
                GO
                CREATE LOGIN Mirror_login WITH PASSWORD = 'Password'
                GO

6.2   Create a user for that login.

                USE MASTER 
                GO
                CREATE USER Principal_User FOR LOGIN Witness_login
                GO

                USE MASTER 
                GO
                CREATE USER Mirror_User FOR LOGIN Witness_login
                GO

6.3   Associate the certificate with the user.

                USE MASTER 
                GO
                CREATE CERTIFICATE Principal_Cert
                    AUTHORIZATION Principal_User
                    FROM FILE = 'C:\Certificate\Principal_Cert.cer'
                GO

                USE MASTER 
                GO
                CREATE CERTIFICATE Mirror_Cert
                    AUTHORIZATION Mirror_User
                    FROM FILE = 'C:\Certificate\Mirror_Cert.cer'
                GO

6.4   Grant CONNECT permission on the login for the remote mirroring endpoint.

                USE MASTER 
                GO
                GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Principal_login]
                GO

                USE MASTER 
                GO
                GRANT CONNECT ON ENDPOINT::End_Mirroring TO [Mirror_login]
                GO

Step 7. Create the Mirrored Database on the Mirror Server using backups from the Principal Server.

7.1   Run below script against the Principal Instance

                USE MASTER
                GO
                BACKUP DATABASE AdventureWorks
                    TO DISK = 'C:\Backups\AdventureWorks_Full_01012011.bak'
                GO
                BACKUP LOG AdventureWorks
                    TO DISK = 'C:\Backups\AdventureWorks_Log_01012011.trn'
                GO

7.2   Copy AdventureWorks_Full_01012011.bak and AdventureWorks_Log_01012011.trn to the Mirror Server.

7.3   Execute this below script against the Mirror Instance.

                USE MASTER 
                GO
                RESTORE DATABASE AdventureWorks
                    FROM DISK = 'D:\Backups\AdventureWorks_FullBackup.bak'
                    WITH NORECOVERY
                GO
                RESTORE LOG AdventureWorks
                    FROM DISK = 'D:\Backups\AdventureWorks_LogBackup.trn'
                    WITH NORECOVERY
                GO

Step 8. Configuring the Mirroring Partners 

8.1   Change the server name as per yours and run the below script on Mirror server.

                ALTER DATABASE AdventureWorks
                    SET PARTNER = 'TCP://your_principal_server_name:5022'
                GO


8.2   Change the server name as per yours and run the below script on principal server
                ALTER DATABASE AdventureWorks 
                   SET PARTNER = 'TCP://your mirror server name:5023' 
                GO 
                
                ALTER DATABASE AdventureWorks 
                   SET WITNESS = 'TCP://your witness server name:5024' 
                GO

NOTE: When you copying a certificate to another system or server, use a secure copy method. Be extremely careful to keep all of your certificates secure and safe.

Setting Up Database Mirroring Using Certificates with examples. Use Certificates for a Database Mirroring Endpoint . Configure a Database Mirroring Session. Setting up database mirroring with certificates. Certificate Expiration on Database Mirroring and Recreating. How to replace expired certificates used in database mirroring. Implementing Database Mirroring in SQL Server 2008 R2. Implementing Database Mirroring in SQL Server 2012. Run SQL Server 2008 database mirroring with certificates. How to Allow Database Mirroring to Use Certificates for Inbound. How to Remove Database Mirroring. Sample script for configuring SQL Server database mirroring. Database Mirroring Setup Overview. step by step tutorial. step by step guide. Step by Step Installation. Step-by-step procedure. Database Mirroring step by step. Step by Step Guide to. sql server step by step. sql server step by step ebook. microsoft sql server step by step ebook. sql server step by step tutorial. step by step installation guide. microsoft sql server 2008 step by step

0 comments:

Post a Comment