Thursday, March 27, 2008

Mirroring Databases in SQL Server 2005

Introduction

This tutorial will show you how to mirror one database from one instance of SQL Server 2005 to another. I will be using the AdventureWorks database for this demonstration and will not be using a Witness server (only a Principal and Mirror server [i.e., Source and Target]).

Please note that both the Principal and Mirror servers must be of the same SQL Server edition. If they are not, mirroring will not work.

Enable Mirroring in SQL Server 2005 if SP1 is Not Installed

On computer/instance mirror A:

  1. Open the SQL Server Configuration Manager for SQL Server 2005, typically, this is located in the Programs menu of the Start button (Microsoft SQL Server 2005, then Configuration Tools).
  2. Select the SQL Server 2005 Services node.
  3. In the right pane, right-click on the SQL Server service, then click on Properties.
    SQL Server Configuration Manager for SQL Server 2005
  4. Click on the Advanced tab.
  5. Locate the Startup Parameters field, then append trace flag 1400 to it: ; -T 1400
  6. Right-click on the SQL Server service, then click on either Restart or Start.

On computer/instance mirror B:

  1. Repeat the steps outlined for On computer/instance mirror A for this computer.

Please note mirroring doesn't seem to work if you use this trace flag with SQL Server 2005 without any Service Packs installed. The trace flag seems to work ONLY with older RTM versions. So, if you have SQL Server 2005 with no Service Packs installed, please install them on all running instances.

Preparing Databases for Mirroring

  1. Using SSMS (SQL Server Management Studio), set the Recovery Model of the source database to Full, if it's not already.
  2. Backup the source database AND transaction log(s), a full backup first, then a transaction log(s) backup.
  3. Restore the just-backed up source database AND transaction log(s) to the target server/instance using the WITH NORECOVERY option for each restore(s), starting with the full database restore, then transaction log(s) restore(s), ensuring that the data file paths are not physically the same (i.e., do not overwrite the source data files with this restore).

Configuring and Starting Mirroring

  1. Using SSMS, launch the Mirror... task on the source database.
  2. Click on the Configure Security... button.
  3. Click on Next.
  4. Select No to NOT include a witness server.
  5. Click on Next.
  6. Click on Next to save the security configuration.
  7. Select the source server, which should already be selected, in the Principal server instance field, set or leave the port, then specify a unique Endpoint name.
  8. Click on Next.
  9. Select the target server in the Mirror server instance field, set or leave the port, then specify a unique Endpoint name.
  10. Click on Next.
  11. Set the Principal and Mirror Service Accounts, preferrably, they will be the same account.
  12. Click on Next.
  13. Click on Finish.

    If you run into problems, re-click on the Configure Security button to change the parameters. You should get Successful results for both Endpoints.
  14. Click on Do Not Start Mirroring.
  15. Start mirroring by clicking on the Start Mirroring button. If successfull, the Pause button should be enabled.

Using the Database Mirroring Monitor

  1. In SSMS, right-click on the source database, navigate to Tasks, then click on Launch Database Mirroring Monitor...
  2. Click on Register mirrored database.
  3. Click on the Connect button, then select the Mirror/target server.
  4. Click on the Register checkbox in the Mirrored databases list.
  5. Click on OK.
  6. In the left pane, expand the Database Mirroring Monitor node by clicking on the plus sign.
  7. Click on the mirrored database. You should now see the status of the mirror.

Failing Over to the Mirrored/Target Database

  1. Execute the following T-SQL command at the mirror:ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Issues to Consider About Database Mirroring and this Tutorial

  1. The source/Principal and target/Mirror SQL Server Service account(s) must be granted CONNECT permission on the target/Mirror and source/Principal servers respectively. If not, mirroring will fail upon start.
  2. Encryption - By default, data is encrypted, sent to the mirror, then decrypted. This uses up more server resources and bandwidth than it would without encryption. So, ONLY use encryption if the data being mirrored is sensitive and should be encrypted.
  3. Witness - This tutorial doesn't use a Witness server. A witness server can provided automated failover capabilities.
  4. Asynchronous mirroring is only available in SQL Server 2005 Enterprise & Developer Editions, Synchronous mirroring is available in all others, except for Express Edition. So, fully evaluate the performance implications when using mirroring with SQL Server 2005 Standard Edition, as performance might be negatively impacted in a production environment.

No comments: