SQL Server 2008 Database Mirroring – Key Points


Database Mirroring in SQL Server 2008 some of hot-key points to consider:

The database that will be mirrored must be in the Full Recovery Model. Both the Principal and Mirror instances must be of the same edition e.g. both are Enterprise edition.   The Witness instance can run on another  SQL Server edition: Standard, Enterprise, Workgroup, or Express

* The database to be mirrored cannot be a system database (i.e. master, model, msdb, temp).

* The Mirror instance can handle the same amount of I/O with the Principal instance and that it has adequate free space as well.

* The database does not contain FILESTREAM filegroups.

In a 32-bit system, a maximum of 10 mirrored databases are considered a supported configuration. In a 64-bit system this limitation does not apply.

Database mirroring is not supported with either cross-database transactions or distributed transactions.

If the database that will be mirrored is being replicated, you need to take more considerations and follow some additional steps.
  For complete details, read this article: Visit here

** If you plan to setup Database mirroring on a failover cluster:
  The Principal and Witness will be setup on two diefferent clusters
  If you wish to mirror a database in high-safety mode with automatic failover, a two-cluster configuration is recommended for the partners (the witness could reside on an unclustered computer).
  If you wish to mirror a database in high-performance mode, you can place the principal server on a cluster and place the mirror server on a different unclustered server).

 If you plan to mirror a database that is encrypted:
  You must provide the password used to encrypt the master key to the mirror server instance by using the stored procedure called sp_control_dbmasterkey_password
 Creating a master key credential for an encrypted database will enable sa (and other privileged server principals) to decrypt the database.

** Regarding user logins on the Principal and on the Mirror:
 User logins with connect permissions on the Principal instance must also exist on the Mirror instance. 
The configuration is more simple if the Principal and Mirror instances run using the same domain user account as the correct user logins exist automatically in both master databases. 
If the partner server instances run as different user accounts, user logins on the Principal server instance must be manually reproduced on the Mirror server instance.

** If you run SQL Server under a non-domain account, you must use certificates for the database mirroring: http://msdn.microsoft.com/en-us/library/ms191477.aspx
   First you must configure the outbound connections to use certificates: http://msdn.microsoft.com/en-us/library/ms186384.aspx
   Then you must configure the inbound connections to use certificates: http://msdn.microsoft.com/en-us/library/ms187671.aspx
   By default the Configure Database Mirroring Security Wizard always uses Windows Authentication. To use certificates:
     The mirroring endpoint must already have been configured to use certificates on each of the server instances.
     All the fields of the wizard’s Service Accounts dialog box must remain empty.

** For database mirroring to work, each instance requires its own, dedicated database mirroring endpoint:
  All mirroring connections on a server instance use a single database mirroring endpoint. 
  Client connections to the principal server do not use the database mirroring endpoint.
  If the computer running SQL Server has a firewall, the firewall configuration must allow both incoming and outgoing connections for the port specified in the endpoint.

** Do not reconfigure a database mirroring endpoint that is in use:
  o If the endpoint is reconfigured, it might appear to be an error to the other server instances involved in the mirroring.
  o In high-safety mode with automatic failover, reconfiguring the endpoint on a partner can even cause a failover to occur.

To allow multiple server instances on a single server to participate in database mirroring you need to specify a server network address in the form: TCP :// < system-address> : < port>

**  When you create the mirror database on the Mirror server, you must:
 Restore the backup of the Principal database specifying the same database name WITH NORECOVERY
 Apply all log backups WITH NORECOVERY, which were created after the backup was taken.

 ** Take care when stopping the mirroring:
 Apply any subsequent log backups on the Mirror database, taken from the Principal database.
 Then you can start the mirroring again.
 When a database mirroring session is stopped, the transaction log cannot be truncated.
    – The log will eventually become full making the database unavailable.
    – A method to avoid this possible issue is to remove mirroring before stopping it.

**  Database mirroring works with any supported database compatibility level (i.e. 80 and 90).

 ** If you plan on using Forced Service:
 Using Forced Service might lead to data loss in some scenarios. The database can become damaged as well.
 It is strongly suggested that you understand the possible scenarios before implementing it:

http://msdn.microsoft.com/en-us/library/ms189977.aspx

 ** Exploring database mirroring the safe way:
 It is recommended to start the mirroring in high-performance mode.
    – This is the least sensitive to the network environment.
    – It also provides the best configuration for exploring how mirroring works.
    – Test that this mode of operation is working without problems (e.g. network errors).
 Then go for the High-Safety modes which provides better data protection. Test that this mode of operation is working without problems.
 The next step is to use High-Safety mode with automatic failover for high availability.

To make yourself more familiar with database mirroring:
 Seeing is believing! Follow the steps described in this example to setup database mirroring for testing purposes: http://msdn.microsoft.com/en-us/library/ms189047.aspx

For more details information, you may reference and find out more with those links, as per your needs:

 Prerequisites, Restrictions, and Recommendations for Database Mirroring http://msdn.microsoft.com/en-us/library/ms366349.aspx
 How to: Prepare a Mirror Database for Mirroring http://msdn.microsoft.com/en-us/library/ms189047.aspx
 How to: Configure a Database Mirroring Session http://msdn.microsoft.com/en-us/library/ms188712.aspx
 Replication and Database Mirroring http://msdn.microsoft.com/en-us/library/ms151799.aspx
 Database Mirroring and Failover Clustering http://msdn.microsoft.com/en-us/library/ms191309.aspx
 Database Mirroring High-Safety Mode http://msdn.microsoft.com/en-us/library/ms179344.aspx
 Database Mirroring High-Performance Mode http://msdn.microsoft.com/en-us/library/ms187110.aspx
 Setting Up an Encrypted Mirror Database http://msdn.microsoft.com/en-us/library/ms188292.aspx 
sp_control_dbmasterkey_password http://msdn.microsoft.com/en-us/library/ms182754.aspx
 Setting Up Login Accounts for Database Mirroring http://msdn.microsoft.com/en-us/library/ms366346.aspx
 Database Mirroring Endpoint http://msdn.microsoft.com/en-us/library/ms179511.aspx
 Using Certificates for Database Mirroring http://msdn.microsoft.com/en-us/library/ms191477.aspx
 How to: Allow Database Mirroring to Use Certificates for Outbound Connections http://msdn.microsoft.com/en-us/library/ms186384.aspx
 How to: Allow Database Mirroring to Use Certificates for Inbound Connections http://msdn.microsoft.com/en-us/library/ms187671.aspx
 How to: Create a Mirroring Endpoint for Windows Authentication http://msdn.microsoft.com/en-us/library/ms190456.aspx
 Specifying a Server Network Address http://msdn.microsoft.com/en-us/library/ms189921.aspx
 Pausing and Resuming Database Mirroring http://msdn.microsoft.com/en-us/library/ms190664.aspx
 Removing Database Mirroring http://msdn.microsoft.com/en-us/library/ms180801.aspx
 Forced Service http://msdn.microsoft.com/en-us/library/ms189977.aspx

 Knowledge is to share not for hide, welcome any comments.

Mehboob

MCTS

 

2 thoughts on “SQL Server 2008 Database Mirroring – Key Points

  1. If you wish for to grow your know-how just keep visiting this site and be updated with the most recent information posted
    here.

  2. Αttraϲtijve compߋnent to content. I just
    stuimblеd upon ƴoour wеb sitye and in accessioո capital to claim that I acquire actuɑlly lovd
    account уour blog posts. Anyway I will be subscribing on your augment
    and even I fulfillment yyou get right of entry to persistеntly fast.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s