Database Mirroring "using Certificates" SQL Server 2005

First a few notes.

Servers should be at the same Service Pack Level

Mirroring was not supported prior to SP1 (you must use the T 1400 flag on startup)

The mirror server must be restored from the Principle and at least one transaction log restored, all restores must be NORECOVERY.

Both databases must be set for Full Recovery (not simple)

This Sample setup uses certificates. Certificates work like keys, You create a certificat on one server and pass it to the other server.

It is important the the mirror server partner is created before the principle.

Apply certificates to the Master and the database being mirrored.

Steps to Follow.

1. Create a new database on the Mirror Server with the same name as the principle database, set for full recovery.
2. Restore the most recent backup and transaction logs from the principle set all restores for norecovery.
3. Replace Acme with your company name.
4. Execute these commands in order on the Principle Server (Create a certificate and share with other servers to connect)
   

use master
create master key encryption by password = 'Acme@06022009';
GO

use master
create certificate HOST_A_cert with subject = 'HOST_A certificate';
GO

use **Mirrored Database**
create certificate HOST_A_cert with subject = 'HOST_A certificate';
GO

use master
Backup certificate HOST_A_cert to file = 'c:\TEMP\HOST_A_cert.cer';
GO

use master
alter endpoint mirroring_endpoint state = started
as tcp(listener_port = 7022, listener_ip = all) -- Port does not have to be 7022, encryption probably is not required within the domain
for database_mirroring (authentication = certificate HOST_A_cert, encryption = REQUIRED ALGORITHM AES, role = all);
GO

5. Execute these commands in order on the Mirrored Server (Create a certificate and share with other servers to connect)

use master
create master key encryption by password = 'Acme@06022009';
GO

use master
create certificate HOST_B_cert with subject = 'HOST_B certificate';
GO

use ** Mirrored Database **
create certificate HOST_B_cert with subject = 'HOST_B certificate';
GO

use master
create endpoint mirroring_endpoint state = started
as tcp(listener_port = 7022, listener_ip = all)
for database_mirroring (authentication = certificate HOST_B_cert, encryption = REQUIRED ALGORITHM AES, role = all);
GO

use master
Backup certificate HOST_B_cert to file = 'c:\TEMP\HOST_B_cert.cer';
GO

6. ** If using a witness server: Execute these commands on the witness server** (Create a certificate and share with other servers)

    use master
    create master key encryption by password = 'Acme@06022009';
    GO

    use master
    create certificate HOST_W_cert with subject = 'HOST_W certificate';
    GO
   
    use master
    create endpoint mirroring_endpoint state = started
    as tcp(listener_port = 7022, listener_ip = all)
    for database_mirroring (authentication = certificate HOST_W_cert, encryption = REQUIRED ALGORITHM AES, role = witness);
    GO
   
    use master
    Backup certificate HOST_W_cert to file = 'c:\TEMP\HOST_W_cert.cer'; -- To location accessable by the other servers
    GO
   

7. Execute these command in order on the Principle Server

Copy the HOST_B_cert from the Mirrored server to a location accessible by the principle (c:\Temp) for this example.

USE master;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'AcmeMirror@06022009';
GO

use master
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO

use ** Mirrored Database **
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
GO

use master
CREATE CERTIFICATE HOST_B_cert
   AUTHORIZATION HOST_B_user
   FROM FILE = 'C:\Temp\HOST_B_cert.cer'  -- where you saved the file
GO

use Master
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [HOST_B_login];
GO

    7A ** If using a witness Server **
    USE master;
    CREATE LOGIN HOST_W_login WITH PASSWORD = 'AcmeMirror@06022009';
    GO
   
    use master
    CREATE USER HOST_W_user FOR LOGIN HOST_W_login;
    GO
   
    use master
    CREATE CERTIFICATE HOST_W_cert
       AUTHORIZATION HOST_W_user
       FROM FILE = 'C:\Temp\HOST_W_cert.cer'  -- where you saved the file this is the certificate saved on the witness
    GO
    use Master
    GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [HOST_W_login];
    GO

8. Execute these commands in order on the Mirrored Server

Copy the HOST_A_cert.cer file to a location accessible by this server.

USE master;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'AcmeMirror@06022009';
GO

USE master;
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO

USE ** Mirrored Database **
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO


CREATE CERTIFICATE HOST_A_cert
   AUTHORIZATION HOST_A_user
   FROM FILE = 'C:\Temp\HOST_A_cert.cer' -- File you saved
GO

USE master;
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [HOST_A_login];
GO


    8A. ** If using a witness server **
    USE master;
    CREATE LOGIN HOST_W_login WITH PASSWORD = 'AcmeMirror@06022009';
    GO
   
    use master
    CREATE USER HOST_W_user FOR LOGIN HOST_W_login;
    GO
   
    use master
    CREATE CERTIFICATE HOST_W_cert
       AUTHORIZATION HOST_W_user
       FROM FILE = 'C:\Temp\HOST_W_cert.cer'  -- where you saved the file this is the certificate saved on the witness
    GO
    use Master
    GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [HOST_W_login];
    GO

9. Execute these commands on the Witness Server

            use master;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'AcmeMirror@06022009';
GO
   
use master
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
GO

use master;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'AcmeMirror@06022009';
GO
   
use master
CREATE USER HOST_B_user FOR LOGIN HOST_W_login;
GO

use master
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:\Temp\HOST_A_cert.cer'  -- where you saved the file this is the certificate saved on the witness
GO

use master
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'C:\Temp\HOST_A_cert.cer'  -- where you saved the file this is the certificate saved on the witness
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [HOST_B_login];
GRANT CONNECT ON ENDPOINT::Mirroring_Endpoint TO [HOST_A_login];

            10. Time to try and start

On the mirrored server
alter database MyDatabase set partner = 'TCP://MyMirror.Mydomain.acme.net:7022'; -- substitute MyMirror for your server, MyDatabase for your database.
-- this will set the mirrored server in listning mode waiting for the principle. if in doubt PING the alias and use the fully qualified name that is returned.

Notes: The value of partner_server is a server network address. This has the following syntax:
TCP://<system-address>:<port>
where
•    <system-address> is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.
•    <port> is a port number that is associated with the mirroring endpoint of the partner server instance.

On the principle server
alter database MyDatabase set partner = 'TCP://MyprincipleServer.Mydomain.acme.net:7022'; -- substitue MyPrincipleServer for your server

    10A. ** If using a witness **
       
    alter database MyDatabase set witness = 'TCP://MyWitness.Mydomain.acme.net:7022'; -- substitue MyWitness for your witness server

You should now be complete. open enterprise and monitor the session.