Feedback requested on database mirroring…


Database mirroring is one of the new high availability features in SQL Server 2005. More details on the database mirroring feature can be found at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx. Configuring security for database mirroring involves the process of enabling each instance (principal, mirror, and witness) to communicate with each other using the database mirroring endpoint. The security configuration can be done from the SQL Server Management Studio Security Configuration Wizard in Mirror page of database properties. This wizard handles the case when all of the instances are running under the same domain user account for example. If the instances are running under a local user account and/or part of different domains, then certificate based authentication needs to be configured for the endpoints. The steps for configuring database mirroring can be found at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#EGAA or the Books Online topics.

 

To simplfy the process of evaluating this feature overall and especially the configuration of endpoints using certificate based authentication, I have created a set of scripts that will allow you perform the configuration. The scripts can be downloaded from http://umachandar.members.winisp.net/files/MirrorDb.zip. The scripts can also provide as learning aids for powerful scripting techniques using sqlcmd, and configuration of certificates. The zip file contains the following:


  1. mirrordb.cmd – Command script to configure mirroring. The script takes instance names for principal, mirror, and witness & database to mirror. For additional details, please review the usage of the script by running it without any parameters. Only tested on Windows Server 2003 since it uses new CMD extensions but it will work from Windows XP.
  2. Mirror_Init.sql – This script contains the SQLCMD variables that can be used to modify password for certificates, mirroring endpoint name and so on
  3. Mirror_Setup.sql – This script performs the necessary configuration steps for enabling database mirroring
  4. Mirror_Partner_Cleanup.sql – This script performs cleanup of the mirroring confguration on the partner (principal or mirror)
  5. Mirror_Final_Cleanup.sql – This script performs final cleanup of the mirroring configuration on the witness and files created by setup
  6. Mirror_Debug.sql – This script contains some queries that shows how to get database mirroring metadata from catalog views.

To evaluate the database mirroring feature, you can run just the “mirrordb.cmd” script. Run the script without any parameters to see usage examples, pre-requisites and description of the script. Feel free to send feedback on the database mirroring feature using the comments section of the blog entry. For questions regarding the usage of the script or problems, use the contact form in the blog or the comments section.

 

Thanks for trying out database mirroring feature and hoping to hear from you.

 


Umachandar Jayachandran

Comments (10)

  1. Michael Bruns says:

    Hello,

    seems to be a very nice piece of code. Unfortuntly I get an error from SQL Server when trying to create the endpoint with the new generated certificate.

    Msg 28055, Level 15, State 5, Line 3

    The certificate ‘xxx’ is not valid for endpoint authentication. The certificate must have a private key encrypted with the database master key and current UTC date has to be between the certificate start date and the certificate expiration date.

    The creation of the certificate works without trouble. Did you seen the same error on June CTP?

    Kindly Regards

  2. sqletips says:

    You need to ensure that all of the SQL Servers you are using for the mirroring configuration are synchronized in terms of time i.e., the system time is same. Otherwise, you will get into this situation which can result in a cert being considered as expired.



    Umachandar

  3. La configuration de bases de données en mode miroir, nouvelle option très intéressante de SQL Sever 2005, nécessite…

  4. Full of I.T. says:

    With all the focus on SQL Server 2005 our team has been doing lately, I was very glad to find this…

  5. sqletips says:

    The link to the zip file was corrected before and it should work fine now.



    Umachandar

  6. Dennis says:

    What if you don’t want a WITNESS server?  What option can you put in there?

  7. Keith says:

    Regarding the server time being the same across all the servers — Can your principal be on the east coast and the mirror be on the west coast?  Does the system use UTC time, or the local (regional) time?  Does specifying the Time Zone make mirroring work properly when the servers are in different time zones (and have different LOCAL times)?

  8. sqletips says:

    Different time zones should be fine. It has more to do with the certificate start and expiration dates. The system uses UTC date/time.



    Umachandar

  9. rrussello says:

    These scripts work great to setup database mirroring.  Only suggestion I would add to them is they do not remember the database owner and the database trustworthiness property after restoring the backup.  It’s not the scripts fault, it’s more a SQL Restore issue.  We ran into this issue with CLR code.