Feedback requested on database mirroring...

  • Article

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 https://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 https://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 https://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

  • Anonymous
    August 14, 2005
    The comment has been removed
  • Anonymous
    August 16, 2005
    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
  • Anonymous
    August 23, 2005
    La configuration de bases de données en mode miroir, nouvelle option très intéressante de SQL Sever 2005, nécessite...
  • Anonymous
    September 06, 2005

    With all the focus on SQL Server 2005 our team has been doing lately, I was very glad to find this...
  • Anonymous
    May 02, 2006
    Hi Uma ,
    This link is broken

    http://umachandar.members.winisp.net/files/MirrorDb.zip

  • Anonymous
    May 31, 2006
    The link to the zip file was corrected before and it should work fine now.

    --
    Umachandar
  • Anonymous
    July 01, 2006
    What if you don't want a WITNESS server?  What option can you put in there?
  • Anonymous
    July 06, 2006
    The comment has been removed
  • Anonymous
    July 13, 2006
    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
  • Anonymous
    October 31, 2008
    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.