Getting started with Database Mirroring using SQL SERVER Management Studio

SQL SERVER provides a number of fail over techniques over the time. Database mirroring is one of the popular solution from all others (Log shipping, snapshot, replication). I am going to teach you simple steps to test and verify database mirroring through step by step screen shot technique. Major problem for most of DBA's is availability of proper hardware to install any technique prior to implement in production environment.


Database Mirroring

Database mirroring works with all the standard hardware that supports SQL Server 2008 and it ensures no data loss in the event of a database failure. The mirror database will always be updated with the current transaction that's being processed on the primary database server. figure below illustrates the flow of data.

If your principal server does go down, you can rest assured that the mirror server has an exact point-in-time copy of the principal database as of the last committed transaction. Thus, the mirror is always ready to take over the principal server role.

Operating Modes

The database mirroring topology you'll use will depend on the transaction safety and operating modes you've chosen. The operating modes that are supported by database mirroring include high-safety (with or without automatic failover) and high-performance.

High-Safety (with automatic failover) This mode supports maximum database availability with synchronous data transfer and automatic failover to the mirror database. This operating mode is best used when you have fast and very reliable communication between the principal and the mirror servers and you require automatic failover for a single database. In this scheme, the principal database waits to commit a transaction until it receives a message from the mirror server that the mirror server has hardened the transaction's log to the disk.

High-Safety (without automatic failover) This mode supports maximum database availability with synchronous data transfer but without automatic failover to the mirror database. In this mode, if the mirror server instance becomes unavailable, the principal server instance continues to function but will not be able to mirror the data. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.

High-Performance In this operating mode, the transfer of data is asynchronous. The principal server does not wait for an acknowledgment from the mirror as it does in the above two modes. The mirror server does its best to keep up with the principal, but it is not guaranteed at any point that all the most recent transactions from the principal will be hardened in the mirror server's transaction log. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.

Prerequisites

You need to remember some basic settings before setup this technique.

Setting up database mirroring is a simple process if you establish a strong foundation using best practices:

• Principal and mirror servers are running on the same edition of SQL Server, You can use either the Standard or Enterprise edition.
• If you are planning to use high-safety with automatic failover, then make sure that the witness server is available 
• Make sure that the mirror server instance has the identical jobs, logins, SQL Server Integration Service (SSIS) packages and Link servers.
• If you are planning to configure database mirroring on same server then you need different endpoints and ports that must open through network and system security.
• If you are planning to configure database mirroring on different server then you can set same port but again it must enable on all servers.
• You can configure database mirror in automatic fail over (with Witness) or Manual fail over (without witness). for Auto fail over witness server needs SQL server installed on Witness machine but there is no restriction of Express, Standard or Enterprise edition.
• You database Engine must be running with Domain Admin account on Network, otherwise you need to set it with your local account that has admin rights.
• 
  

Previous Link

1. Failover techniques in SQL Server

Next Steps

1. Step by Step configuration of Database mirroring using SQL Server 2008, R2 
2. Performing Auto Fail-over using Auto fail-over configuration.