How To Configure Database Mirroring For Microsoft Sql 2005+ Standard+
Database mirroring allows you to set up a primary and secondary server which have the same database. The primary database is the only one you can use until you’re faced with a disaster and the primary server cannot be used. In addition to the two ‘partner servers’ (principal and mirror) a database mirroring session may have an optional third server, called the witness. The witness server’s role is to enable automatic failover. When database mirroring is used for high availability, if a principal server suddenly fails, if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available within a few seconds.
Some important items to note about database mirroring:
· The principal database must be in the FULL recovery model.
· The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.
· The mirror database must have the same name as the principal database.
· Because the mirror database is in a recovering state, it cannot be accessed directly.
If you don’t want to use High Availability you can control fail over to DR via a High Protection operating mode. High Protection Operating mode is a Synchronous Transfer manual fail over which does not require a witness server and has FULL transaction safety.
Database Snapshots and the Mirror Database
As the mirror database is in a recovering state, it is not accessible and not readable. With SQL Server 200x Enterprise Edition and Developer Edition, you can create database snapshots to read the mirror database at a point in time. Database snapshots provide a read-only view of a database, exposing data that is consistent at the point of the snapshot creation. Note that you CANNOT create a snapshot if the primary server is unavailable so it is not a valid DR or DR Test method.
Steps for installation
Prereq: SQL Server service must be set to run as the domain account that has permissions to control the mirror.
1. Backup Primary Database (FULL and then TRANSACTION LOG)
2. Restore Primary Database to the Mirror Server WITH NORECOVERY (same locations must exist)
3. Wait for the Mirror database to revert to the status In Recovery
4. On the primary server right click the database Tasks > Mirror
5. Click Configure Security
6. For auto failover, you will need to specify a 3rd server as a witness. Manual failover is recommended.
7. Select the Primary server, Mirror server (you’ll need to connect) Just keep the defaults.
8. Type in the domain accounts that have rights to both servers
9. Start Mirroring
10. If you get a 1418 error you need to ensure that port 5022 is available from both servers and also that you performed your backup/restore properly (ie: did an additional backup on top of the full that appended the transaction logs)
11. Note that the mirror will always be in a (Mirror, Synchronized / Restoring…) state because the mirror is effectively constantly ‘restoring’ production changes to DR as they occur. You cannot use this database at DR for a DR test until you mount a point in time.
Using your database Mirror
Now that you have an active synchronised database mirror you might want to use it in a DR test. If you only want a read only copy you can create a database snapshot. To create a snapshot run this query (red text is variable based on what you want to call your snapshot, where you want to put it and also what your database name is. Note that snapshots can only be created in Enterprise (or developer) editions of SQL Server
CREATE DATABASE SnapshotName ON
( NAME = N’ImportantDatabase’, FILENAME = N’C:YourSnapshotDirectorySnapshotImportantDatabase.snap’)
AS SNAPSHOT OF ImportantDatabase
Note: Manual failover can be initiated only from the principal server (which is stupid for a DR situation where Prod DB Server dies)
Note: The database must be online to have a database snapshot (which is stupid for a DR situation where Prod DB Server dies)
Bringing up the DR Mirror
From the DR Database mirror server you need to run this query
ALTER DATABASE ImportantDatabase SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
This will set the ImportantDatabase at DR to be the Principal and you will be able to point your application servers to this database. WAIT for this mirror to have the status (Principal, Disconnected) before pointing your DR apps to it
Do your DR tests and now you have a choice. What of the 2 primary SQL servers is the master? Were changes made in production during the DR test?
Option 1: Make the DR SQL Server the Primary after the DR Test or Real DR
If you re-establish the link to your production database Mirroring will be suspended. You can right click > Tasks > Mirroring > Resume to mirror the changes that were made at DR back to production. There is a screenshot of the choice you are given by SQL here: View Mirror Resume SQL Message. If you choose to resume mirroring you can quickly change the primary back to the production server by clicking FAILOVER in the same mirror configuration screen.
Option 2: Destroy Changes to DR SQL Server, Re-Mirror the Production server to DR
If the changes made at DR aren’t needed anymore you can right click > Tasks > Mirroring > Remove Mirroring on both the DR and Prod SQL servers. This will leave you with two non-mirrored databases with the same name on each server. You should take a backup of the production database and restore it over the top of the DR database using the same methods discussed at the beginning of this article (the norecovery full and logs backups).
For more information please visit http://technet.microsoft.com/en-us/library/cc917680.aspx
About the Author
This article was written by Tim Roadley, creator of the new website www.askearn.com where you can get paid to solve peoples problems, or pay to present them 
Tarkovsky’s Mirror Set to Arvo Pärt’s Mirror in the Mirror
|
|
Cuisinart MCP-12 MultiClad Pro Stainless Steel 12-Piece Cookware Set $229.95 The kitchens of France were the inspiration behind the elegant Cuisinart MultiClad Pro Stainless Cookware collection. Cuisinart has included everything you need in this spectacular 12-piece set to perfectly perform all the basic cooking tasks, large or small. From reheating a single portion of soup to preparing appetizers, entre?es and side dishes for the entire family, you?ll find every tool you … |
|
|
Cuisinart 77-10 Chef’s Classic Stainless Steel 10-Piece Cookware Set $118.99 The CUISINART 77-10 Cookware Set includes every piece required for everyday, basic cooking tasks. It’s the perfect set for a couple just setting up house, or for a country or second home. Contents: Cooking Surface Does Not Discolor, React With Food, Or Alter Flavors Unsurpassed Heat Distribution – Aluminum Encapsulated Bases Heat Quickly and Spread Heat Evenly, Eliminating Hot Spots Cool Grip Hand… |
|
|
Frieling Polished Stainless French Press … |
|
|
Snow White and the Seven Dwarfs (Three-Disc Diamond Edition Blu-ray/DVD Combo + BD Live w/ Blu-ray packaging) $38.99 Studio: Buena Vista Home Video Release Date: 10/06/2009… |
|
|
Tinker Bell and the Lost Treasure (Two-Disc Blu-ray/ DVD Combo) $18.39 Tinkerbell is back for a whole new season of adventure in Tinkerbell and the Lost Treasure. Pixie Hollow is all abuzz with preparations for autumn and Tinkerbell is hard at work on her latest invention when she is unexpectedly summoned by Queen Clarion and the Minister of autumn. Tinkerbell fears that she’s in trouble again, but is instead delighted to find out that Fairy Mary has recommended her … |
|
|
Star Trek: The Original Series – Season Two [Blu-ray] $73.44 All 26 episodes from the series’s second season–including “Amok Time,” “Mirror, Mirror,” “Journey to Babel,” “The Trouble with Tribbles,” and “Assignment: Earth”–are featured in a seven-disc set. Includes the broadcast versions of the episodes and the remastered versions featuring all-new CGI special effects. 21 3/4 hrs. total. Standard; Soundtracks: English DTS HD 7.1 Master Audio, Dolby Digita… |
|
|
RoomMates RMK1470SCS Disney Princess Peel & Stick Wall Decals with Gems $9.24 … |
|
|
Command 17203 Small and Medium Picture Hanging Strips, 4 Small 8 Medium Sets of Strips $6.30 Make any space yours with the Command Small and Medium Picture Hanging Strips, a damage-free solution for decorating your home or office. Ideal for frames and other items 12 pounds or less, these handy strips will lock your pictures securely onto the wall. Thanks to the innovative Command Adhesive strips, you can mount and remount them without damaging your walls–no nails, tacks, or tape required… |
|
|
Command 17204 Medium Picture-Hanging Strips, 6-Sets $1.69 Make any space yours with the Command Medium Picture Hanging Strips, a damage-free solution for decorating your home or office. Ideal for frames and other items 12 pounds or less, these handy strips will lock your pictures securely onto the wall. Thanks to the innovative Command Adhesive strips, you can mount and remount them without damaging your walls–no nails, tacks, or tape required.Medium Pi… |
|
|
Alice in Wonderland (Two-Disc Special Un-Anniversary Edition) $16.35 Walt Disney seems to have had a special affection for Lewis Carroll’s “Alice” stories. “Alice’s Wonderland” (1923), a short about a live-action little girl in a cartoon world, led to his first successful series, the “Alice” comedies (collected on Walt Disney Treasures: Disney Rarities–Celebrated Shorts, 1920s -1960s). During the early ’30s, he talked about making an animation/live-action feature … |
