Since its introduction, in Sql Server 2005, Database Mirroring has been one the popular features. We have database mirroring configured on several production databases. It is easy to configure, maintain and very efficient in transporting transactions from Principal server to the Mirror. With more new improvements in later versions, the transfer of transactional log to Mirror is more optimized. That is a topic for some other time.

Today, let us look at renewing/re-configuring expired certificate on Database Mirroring.

The option of creating mirroring using certificates is not too common, but it has a lot of benefits of its own. When we create certificates for this purpose, the usual syntax used it something like this:

This works, but the problem is when you do not specify ‘EXPIRY_DATE’ parameter the certificate defaults to one year. So come next year, you’ll need to change the certificate. And this is how to do it:

Steps to configure new certificate

Phase 1

• Step 1: On Principal, create new certificates (C1) with longer duration
• Step 2: Backup the certificate (on Principal)
• Step 3: Copy the Principal certificate backup to Mirror server

Phase 2

• Step 4: On Mirror, create a new certificate (C2) with longer duration
• Step 5: Backup the certificate (on Mirror)
• Step 6: Copy the Mirror certificate backup to Principal server

Phase 3

• Step 7: Restore new certificate (C2) from Mirror on to Principal server. Make sure the owner of this new certificate is the same as the old certificate owner login.
• Step 8: Alter endpoint on Principal to Principal to use new certificate (C1)

Phase 4

• Step 9: Do same as Step 7 on Mirror server (C1). Make sure the owner login stays the same as owner of previous certificate
• Step 10: Alter endpoint on Mirror to use new certificate (C2)

Phase 5

• Step 11: DROP both the old certificates on Principal server
• Step 12: DROP both the old certificates on Mirror server

Now sample code is below:

Phase 1

Phase 2

Phase 3

Phase 4

Phase 5

Points:

• Mirroring session does not need to be brought down for this operation.
• Production database will be live and running while this operation is conducted in the background. But it is a good idea to do this during a maintenance window.

Hope this helps,
_Sqltimes