Setting up certificate based communication between SQL server endpoints for SQL mirroring


SQL mirroring is a popular DR option for SQL databases. It provides a warm standby server where a database can be recovered quickly. Although SQL mirroring is being deprecated by Microsoft in SQL 2016 in lieu of Availability Groups, they share several elements of the underlying technologies.

SQL mirroring popularity is often attributed to

  • It does not require shared storage like clustering
  • It does not require a common file share like log-shipping
  • It can be configured for automatic failover (synchronous mode only) with the configuration of a SQL Witness (3rd server). This option requires that the application/client be mirror-aware (include ‘failover partner=xxxx’ in connection string)
  • It can be configured in safety/synchronous mode (default), where a transaction is written to both servers before a commit is returned to the client. This requires low latency between the 2 servers.
  • It can be configured in performance/asynchronous mode, where the primary server sends commit back to client as soon as the transaction is written to the send queue. This may lose data if the primary fails before the transaction makes it to the secondary server redo queue.
  • It can be configured across distant geographical locations (recommend asynchronous mode and certificate based authentication in this scenario)
  • It can be configured between 2 servers that belong to different AD domains using certificate authentication.

This Powershell script automates the last scenario of setting up certificate based communication for a pair SQL 2014 servers in Azure. The script can be slightly modified to work on on-premises SQL servers as well. This script has been tested on SQL 2014 SP1 but should work on SQL 2008 R2 and above.

Region ‘Initialize’ output may look like:

SQL-Mirroring-02

Region ‘configure outbound connections’ output may look like:

SQL-Mirroring-03

Region ‘configure inbound connections’ output may look like:

SQL-Mirroring-04

Finally, region ‘Final Cleanup’ deletes the certificate files and closes open sessions to the 2 SQL servers:

SQL-Mirroring-05

To undo this setup, you can run this TSQL script on the Principal server:

DROP CERTIFICATE Vertitech1SQL2_cert
DROP user Vertitech1SQL2_user
DROP login Vertitech1SQL2_login
DROP endpoint Endpoint_mirroring
DROP CERTIFICATE Vertitech1SQL1_cert

SELECT * FROM sys.sysusers where name = ‘Vertitech1SQL2_user’
SELECT * FROM sys.server_principals where name = ‘Vertitech1SQL2_login’
SELECT * FROM sys.certificates
SELECT name,port FROM sys.tcp_endpoints

and this TSQL script on the Secondary server:

DROP CERTIFICATE Vertitech1SQL1_cert
DROP user Vertitech1SQL1_user
DROP login Vertitech1SQL1_login
DROP endpoint Endpoint_mirroring
DROP CERTIFICATE Vertitech1SQL2_cert

SELECT * FROM sys.sysusers where name = ‘Vertitech1SQL1_user’
SELECT * FROM sys.server_principals where name = ‘Vertitech1SQL1_login’
SELECT * FROM sys.certificates
SELECT name,port FROM sys.tcp_endpoints

Advertisements

One response

  1. Pingback: Managing Azure VMs using Powershell from your local desktop | Sam's Corner

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s