Configuring SQL Mirroring isn’t that hard. That is, until you need to set it up with the SQL Servers in different Domains. Or you SQL Servers do not use Domain Accounts to run their services Also needed if the services run under local system account or network.

In that case you will need to use Certificate Authentication on your Endpoints.

This requires you to set up certificates on your SQL Servers, transport them to the other Hosts, import them and assign them to users which then will need Access to your Endpoints.

All the information needed for this is available online, but I wasn’t able to find one single page that detailed the complete process.

I tried to use Query commands where I know them or it is easier using them.

So here you go:

————————————————-

 On each Host involved, (Primary Server, Mirroring Partner and Witness Server) you will first need to create an endpoint that uses certificate authentication. Replace <hostnameA> with the name of the host you are working on (you can name it whatever you want, but for me it makes sense to call them like its host)

1. Generate a master key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<strongPassword>’;
GO

2. Create the certificate

You can select the expiry date of the certificate by changing EXPIRY_DATE
 

USE master;
CREATE CERTIFICATE <hostnameA>_cert
WITH SUBJECT = ‘<HostnameA> certificate for database mirroring’,
EXPIRY_DATE = ’11/30/2016′;
GO

3. Create the endpoint and assign it the certificate

If you run multiple instances of SQL on one host and mirror both the instances, you will have to create endpoints for each of them. Also, you will need to use another port for additional endpoints. Do this by changing the LISTENER_PORT value.

CREATE ENDPOINT Endpoint_<HostnameA>
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE <hostnameA>_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4 Backup the certificate so you can move it to the other hosts

BACKUP CERTIFICATE <hostnameA>_cert TO FILE = ‘c:\<hostnameA>_cert.cer’;
GO

 

On each host you will have to create a database login and a database user for the other hosts and then assign it the other hosts certificate.
 

<hostnameA> again stands for the host you are working on, <hostnameB> is the foreign host.

1. Create a database login for the foreign host

USE master;
CREATE LOGIN <hostnameB>_login WITH PASSWORD = ‘<strongPassword>’;
GO

2. Create  a login user for the foreign host

USE master;
CREATE USER <hostnameB>_user FOR LOGIN <hostnameB>_login
GO

3. Import the foreign host’s certificate and assign it to the user

Requires that you have already copied the certificates we exported from the other hosts to this host.

USE master;
CREATE CERTIFICATE <hostnameB>_cert
AUTHORIZATION <hostnameB>_user
FROM FILE = ‘C:\<hostnameB_cert.cer>’;
GO

4. Assign access rights to the local endpoint
 

USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_<hostnameA> TO <hostnameB>_login
GO

 

After the endpoints are configured, let us start mirroring a database

1. Create a full backup of the database on your source host

USE <databaseName>;
GO
BACKUP DATABASE <databaseName>
TO DISK = ‘c:\<databaseName>.Bak’
WITH FORMAT,
MEDIANAME = ‘<Medianame>’,
NAME = ‘Full Backup of <databaseName>’;
GO

2. Copy the backup over to your mirroring partner

3. Restore the database on your mirroring partner

  • Use the exact same database Name
  • Restore the database using the NORECOVERY option

RESTORE DATABASE <databaseName>
FROM DISK = ‘c:\<databaseName>.bak’
NORECOVERY;

4. Go back to your source host and take a transaction log backup of the database

USE <databaseName>;
GO
BACKUP DATABASE LOG <databaseName>
TO DISK = ‘c:\<databaseName>.trn’
WITH FORMAT,
MEDIANAME = ‘<Medianame>’,
NAME = ‘Log Backup of <databaseName>’;
GO

5. Copy the transaction log over to your mirroring partner

6. Restore the transaction log on your mirroring partner

again, use the NORECOVERY option

RESTORE LOG <databaseName>
FROM DISK = ‘c:\<databaseName>.trn’
NORECOVERY;
GO

7. Go back to your source host and right click your database in the SQL management console. Select TASKS –> Mirroring.

Here, run through the wizard and select your source server, your mirroring partner and your witness server if needed. After the wizare finishes you will be asked if you want to start the mirroring. If all went right, the status of the source database will change to (Primary, Synchronized) and your mirror partner database will have a status of (Mirror, Synchronized).

If you receive an error about one of the involved hosts not being able to connect to TCP://hostname.domain.com:5022, it wil most probably be from incorrectly configured endpoints.

Make sure you create certificate on ALL hosts, import ALL the certificates to each other host and create users for ALL other host on EACH host involved.

Just to make it foolproof, see this:

——————————————————————————————

Source SQL Server:

  • Create a certificate
  • Create endpoint that uses that certificate
  • export certificate
  • Import certificates from mirroring SQL server and witness SQL server
  • Create logins for mirroring SQL server and witness SQL server

Mirroring SQL Server:

  • Create a certificate
  • Create endpoint that uses that certificate
  • export certificate
  • Import certificates from source SQL server and witness SQL server
  • Create logins for source SQL server and witness SQL server

Witness SQL Server:

  • Create a certificate
  • Create endpoint that uses that certificate
  • export certificate
  • Import certificates from mirroring SQL server and source SQL server
  • Create Logins for mirroring SQL server and source SQL server

 

Some other Handy Commands

Remove mirroring from an already mirrored database

ALTER DATABASE <databasename> SET PARTNER OFF

Show configured endpoints on an instance

SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints

Drop an already existing endpoint so you can recreate it in case something went wrong

DROP Endpoint <EndpointName>