May 5 2009

Setting Up Mirroring on SQL Server 2008

Posted by Mike Brunt at 5:14 PM
- Categories: .NET | DataBase | ColdFusion

This article shows a detailed overview of the steps needed to set up mirroring on SQL Server 2008 and also add a third element, a "Witness Server" who's job it is to monitor the servers in the mirror.  It is not an exhaustive set of instructions but we have covered the major steps we employ in carrying out this procedure for clients.  In this scenario we assume there are two SQL Server boxes which will be switched to running in active/passive mode and a third box which will run as the Witness Server. This previous article discusses the principles of mirroring in SQL Server. 

Review current database servers including all back-up and restore maintenance plans.

Identify the backup regime for all current production databases: some database may not need incremental backup regimes and therefore will not need to be on full recovery mode, unless the databases need to be mirrored. If we cannot answer this question, we will set the default recovery mode to full. Full recovery allows database mirroring, log shipping. Full recovery requires close supervision to the log file as it does not archive automatically.

Implement Full recovery mode to databases to be incremented. Restore all databases from both servers to either one of the current production servers SQL Server licensing allows the use of SQL Server in non-production, non-active roles.

For one or two days, monitor log activities on critical databases. When the logs reach a certain file size, a script will empty the log by backing up/archiving the log. This is a critical piece to do in order to avoid runaway log growth and need to ascertain exactly the minimum frequency of log backups to implement.

Run a test scenario to ascertain the effective time required to render the deployment active. This will tell us what, if anything, could go wrong during an actual restore. Testing is key! Downtime for database can be reduced dratically if testing has been done by the application server engineers and load testing via the web application. Obviously, the ideal will be to restore the most recent log before stopping the current production server. Once it is stopped, we will generate, as quickly as possible, the "tail" logs. Those will be tiny and the copy and then restore to the new box will be fairly fast. The testing will also validate all SQL server objects we might have forgotten to migrate or any kind of bugs due to migration within the application server connection string or other items of that nature. This is a critical piece! 

Deploy the restore regime (run asynchronously for high performance on the current production server) on the new SQL server box (this box will be on passive mode and databases will not be accessible as this will be done on a "recovering" state). 

Keep 1 week of activity without the database mirroring this will let us monitor performance of the new box without the mirroring. Mirroring may have an impact on performance so it is important that we gauge this.

Database mirroring is the function which will keep each of the two servers databases and objects synchronized so that if a switch is made from passive to active clients will not be impacted by differences in data. We will be able to put in place database mirroring database by database starting with the less critical pieces.

Set up Witness Server.  The witness server "watches" the active/passive SQL Servers to ensure that the active server is able to service requests and will switch over to the passive server if the active server has problems.  We need a copy of SQL Server installed on the Witness Server (Express can be used with scripts we created).

This article was co-written with Clement Huge our DBA and we will follow this up with an article on clustering mirrors in SQL Server.


Devesh Kumar

Devesh Kumar wrote on 07/09/12 4:47 AM

Setting up mirror server with same database, It will be help full for recovery.(if possible, I am not sure if this can happen).
replica rolex

replica rolex wrote on 08/20/13 6:52 PM

Females will usually discover worth in these sorts of bags. Even so, with fake and counterfeit luggage effortlessly passing as genuine replicas, additional caution is suggested on individuals intending to buy reproduction bags. For ladies who love top of the range issues, the replica luggage are the strategy to go.
prada outlet

prada outlet wrote on 03/20/14 12:27 AM

I am happy to find this post very useful for me, as it contains lot of information.
fendi outlet

fendi outlet wrote on 03/20/14 12:29 AM

I put a link to my site to here so other people can read it. My readers have about the same happy. wrote on 11/05/14 7:41 PM

We have been trying to figure out adequate ways to test what a virtual environment would behave like, performance-wise and stumbled upon a new product that looks interesting in this regard.

click wrote on 12/26/14 10:26 PM

I am happy to find this post very useful for me, as it contains lot of information. dfvsdvsds wrote on 08/24/15 3:47 AM

find this post very useful for me, as it contains lot of information. dddd

Write your comment

(it will not be displayed)

Leave this field empty: