When database mirroring is used between two servers that are not in a domain, as can often be the case when using hosting services from another vendor, certificates are used to secure endpoints.
The MSDN article - Example: Setting Up Database Mirroring Using Certificates (Transact-SQL) explains how to do this.
What it does not mention is that SQL server generated certificates only last a year by default. A year later all your mirroring starts to fail!
The following event log error helped me diagnose the situation -
“…Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85…”
To rectify you have to generate new certificate(s), copy them across the mirrored servers, and change the endpoint setting to use the new certificates.
This is explained here -http://www.sql-server-performance.com/faq/database_mirror_certificate_expired_p1.aspx
Note –
1) I had to specify a start date when specifying an end date else I got a warning.
2) I used the following command to change the certificate -
ALTER ENDPOINT Endpoint_Mirroring FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE cert_Mirror_01)
I then had to restart the mirroring process again for all databases as I’d disable the mirroring.
So – if your creating certificates for mirroring endpoints – give them a nice big expiration date!
No comments:
Post a Comment