Database mirroring is a software solution, provided by SQL Server 2005, that gives you the ability to replicate your data in real-time.  Mirroring allows for increased database availability and data protection and works at the database level on databases that are set to full recovery mode.  You need two instances ofSQL Server to set up mirroring because it maintains separate copies of data of a single database across both servers.

    Let's look at a common scenario for database mirroring. Take a company that has their customer database in a data center in New York City.  They also have a backup data center in Houston. The database in NY is mirroring the data to the database in Houston.  Let's say the data center in NY loses power and they need to shut the server down.  With a "flip of a switch" they can move the database to Houston and continue as if nothing happened.  When the data center gets power again they can switch to the NY city database as soon as it's resynchronized.

Benefits

    One benefit of database mirroring is the increased data protection of your data.  As each transaction is being written to your principle database it is sending that exact transaction to the mirror database.  This provides complete or almost complete (depending on which mode you set up; I'll get into this later) data redundancy in real time.

    High-availability, this is a huge benefit for mission critical data.  Hardware failures happen and network issues arise, with a "flip of a switch' you can send the responsibility to the mirrored server to become the principled server at any time.  You can also set up automatic failover with the addition of a witness server to monitor the servers.

    Another benefit is that you have the flexibility to install updates on your server without data disruption.  Because you can manually failover to the mirror server at any time you can failover, install the updates and reboot if necessary. When you are done you can then have the new principle server failover again to restore the principle on to the primary server without any data loss or downtime and upgrade the mirror server.

    Finally, you can set up mirroring to sync in synchronous or asynchronous modes.  Synchronous or high-safety mode will write a transaction to the principle database and send that exact transaction to the mirror and then wait for it to finish committing that transaction before moving on. With high-safety you are assured that the data that is on the principle is on the mirror at the exact same time. In that you trade some latency waiting for the transaction for the true redundancy of the data.  Automatic and manualfailovers, explained more below, require the use of high-safety mode.

    Asynchronous or high-performance mode will send the stream of transactions at the mirror server and the mirror will try to keep up.  With high-performance there can be a gap where the mirror has a back log of transactions to complete that the principle already has completed.  This gap can become pretty big with heavy loads on the principle. 

Requirements

    Database mirroring requires two servers. With SQL Server 2005 you can set up mirroring with Standard or Enterprise editions.  It's very important that both servers are running the same edition.  It is also recommended that the hardware for both servers be near identical and have the ability to handle the same load.

Setting up a Database Mirror

    Let's start with some simple preparation:

Network=dbmssocn;

Network=dbnmpntw;

Data Source = 10.10.2.10/InstanceName; Failover partner = 10.10.2.11\InstanceName; Initial Catalog = databaseName; User ID = sa; Password = 12345; Network = dbmssocn;