locked
ALTER DATABASE SET PARTNER OFF RRS feed

  • Question

  • I've read that when this run's, it removes all db mirroring information on that db. What exactly does it remove?

     

    Here's my senario:

     

    We are using SQL 2005’s db mirroring process. We are using the certificate method of authentication between the principle and the mirror db’s.

     

    My question is that when the ALTER DATABASE dbname SET PARTNER OFF is run, does it remove these certificate settings as well? In other words when I want to enable the db mirroring, will I need to recreate these certificates or just recreate the endpoints to use these certificates?

    Wednesday, October 25, 2006 4:11 PM

Answers

  • Yes they do expire. By default, SQL will set the certificate to expire one year after the certificate was created. To manually set the expireation date, you will need to add the EXPIRY_DATE line.

    Example:

    CREATE CERTIFICATE <unique name>

      WITH SUBJECT = '<unique name description>',

      EXPIRY_DATE = '<mth/day/year>'

    Why Microsoft dosen't include this little tid-bit when explaining how to use db mirroring w/ certificates is a mystery. I am just glad I found this out now rather than later.

    Tuesday, November 7, 2006 9:03 PM
  • No, certificates and endpoints are their own objects and do not belong to a specific mirroring session. You don't have to recreate any of the endpoints, certificates, or certificate related logins. If you are creating the scripts to create the mirroring; however, I would include a check for existence and create them if they don't.
    Wednesday, October 25, 2006 7:35 PM

All replies

  • No, certificates and endpoints are their own objects and do not belong to a specific mirroring session. You don't have to recreate any of the endpoints, certificates, or certificate related logins. If you are creating the scripts to create the mirroring; however, I would include a check for existence and create them if they don't.
    Wednesday, October 25, 2006 7:35 PM
  • Do db mirroring certificates expire?
    Friday, November 3, 2006 10:51 PM
  • Yes they do expire. By default, SQL will set the certificate to expire one year after the certificate was created. To manually set the expireation date, you will need to add the EXPIRY_DATE line.

    Example:

    CREATE CERTIFICATE <unique name>

      WITH SUBJECT = '<unique name description>',

      EXPIRY_DATE = '<mth/day/year>'

    Why Microsoft dosen't include this little tid-bit when explaining how to use db mirroring w/ certificates is a mystery. I am just glad I found this out now rather than later.

    Tuesday, November 7, 2006 9:03 PM