I found a tricky question on http://www.sqlservercentral.com/Questions about casting strings into date vs datetime, and I want…
If you have a Database Mirroring setup or use Service Broker you may use certificates for endpoint authentication.
To create a certificate run:
CREATE CERTIFICATE [SQLService] ENCRYPTION BY PASSWORD = ‘YourStrongPassword’ WITH SUBJECT = ‘SQLService certified certificate’
When you run this a certificate is created.
If we query sys.certificates and take a look at what we created we can see the following:
select name, pvt_key_encryption_type_desc, start_date, expiry_date,is_active_for_begin_dialog from sys.certificates where name = ‘SQLService’
What we can see here is that our certificate is created and that we have a start date and expiry date. Since i didn’t set these when i created the certificate it’s created with default values which is the same start date as when i created it and the expiry date is one year later.
It might come as a surprise when your certificate suddenly has expired and your endpoints stops working and your database mirroring stops, not a very pleasant feeling.
So one thing to keep in mind is that whenever you create a new certificate make sure you set the expiry date.