With SQL Server 2012, the power of Availability Groups is arguably one of the best high availability, disaster recovery, reporting, and impact-offloading features to be released. Administrators can now offload reporting to read-only mirrors, offload backup tasks to secondary replicas, and geo-cluster and send their replicas out with asynchronous capabilities for disaster and recovery. One extremely useful method for upgrading SQL Server 2008 or 2008 R2 to SQL Server 2012 is to utilize mirroring. This lends itself to as little downtime as possible when the switch to the SQL Server 2012 database is actually performed.
With all of the availability group power added, it is common to take advantage of the new features and implement them outside of other databases that may still be on 2008 and awaiting an upgrade plan. Since availability groups and mirroring utilize the same endpoint, there is one key setting that needs to be altered in order for you to successfully setup a mirror to an existing availability group – encryption.
When setting up availability groups, the default endpoint encryption is AES or Advanced Encryption Standard. However, with mirroring prior to 2012, the endpoint that is created by default is RC4 or Rivest Cipher 4. It’s important to note, this is more than likely due to RC4 encryption being deprecated , as pointed out in the note contained in the linked article.
The difference in encryption types can cause a problem. If you have an existing availability group and the default endpoint Hadr_endpoint created, and then attempt to create a mirroring session to a database not in the availability group for upgrade purposes while letting the wizard create the default mirroring endpoint or preexisting mirroring endpoint, the encryption difference will cause the error shown below.
The error above can be caused by several things. In fact, mirroring is known to be a struggle to troubleshoot given the multiple possibilities. The mirror may not be rolled forward enough, the communication may be a problem between the instances, the security not set, and so on.
With this exact situation, the endpoint set for NODE1 is an availability group created endpoint and set to an encryption of AES. The mirroring endpoint on the principal is set to RC4. Given the encryption difference with these endpoints, the mirror is unable to establish a successful synchronization. This is also common in mirroring when a setup is performed with one side, the principal, having encryption checked but the mirror does not have encryption specified.
To fix this problem, the best solution is to not tear the availability group down by removing the endpoint or altering it. It is better to start with mirroring, since the design and implementation of the availability group is more than likely a production situation. To change the mirroring endpoint, run the ALTER ENDPOINT statement. First, script out the Hadr_endpoint in your availability group setup to ensure you have the right encryption algorithm.
Right click the endpoint in SSMS and choose, script to new query window.
CREATE ENDPOINT [Hadr_endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
Now, on the database server that is pre-2012 that you wish to setup mirroring on to use as an upgrade method, run the ALTER statement below.
ALTER ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
After running the ALTER statement, stop and start then endpoint
ALTER ENDPOINT [Mirroring] STATE=STOPPED
ALTER ENDPOINT [Mirroring] STATE=STARTED
Note that this will interrupt or change any other mirroring that you may be utilizing. If you have mirroring being utilized on the 2008 instance, it would be best to look to the availability group endpoint as a change. Ensure that no other data services are interrupted by this change. If other data services are interrupted, the concept of the upgrade with minimal downtime is negated by forcing a loss of time in preparation.
Once this is completed, mirroring from 2008 to the 2012 instance with the availability group will start successfully.
Summary
Using mirroring as a tool for upgrading databases that require high uptime is a great and highly successfully method. While all the primary administration and preparation work can be performed behind the scenes with little to no interruptions to the data services and the actual upgrade task is performed in seconds. While using this powerful method for upgrading to SQL Server 2012, if you run into a situation where availability groups have been set up and you want to upgrade a SQL Server 2008, ensure the same encryption from the mirroring endpoint to the Hadr_endpoint is being utilized. This will prevent unwanted issues when configuring your mirroring state in preparation for a minimal downtime upgrade path to SQL Server 2012.
