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