[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Can you Mirror an sql database once its gone live?

Posted on 2008-01-31
13
Medium Priority
?
1,025 Views
Last Modified: 2012-06-27
I have a clustered SQL database running on SQL 2005. I've been asked to set up the mirroring of it. I know how to mirror, but what i was wondering was, can you successfully mirror a database once its live. Do both sides of the Mirror have to be the same before you start the mirror process?

The reason i ask is this: I'll take a backup of my DB, and restore it to another server, then i can backup my log files and restore them to the other server, BUT when i start the Mirror process off, the two databases will be inconsistent, will the Mirroring process be ok with this, and if so, how?

The last time i done a mirror, i done it on a pre-production DB and therefore had them offline before starting the mirror process which meant that they were exactly the same.

Thanks in advance for any advice
0
Comment
Question by:rtl_support
  • 6
  • 5
11 Comments
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20794734
The following FAQ from Microsoft has some information: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

The mirroring process is continuous.  As the primary database changes, the changes propagate to the other mirror at designated intervals, so you can do this with a production database.

However, do you realize that the witness (mirror copy) database is read-only?  It is basically a hot backup.  It is not a form of replication.

0
 

Author Comment

by:rtl_support
ID: 20805180
Hello,

i've restored our production db to a test server, and then restored the production db from a day earlier to another test server. In doing this i've replicated the conditions i'll face on the live system. I now have two copies of the same DB, the principle from fridays backup, and the mirror from thursdays backup (in a non-operational state).
When i go through the mirror wizard, it all goes successfull until the last process, when it tries to mirror the databases. I get the following error:

The remote copy of the database "test" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)

This to me sounds like the databases have to be in the same state at the start of the mirroring process? Which was one of my original questions.

Any ideas?
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20805253
I'd setup the mirror on the test server first with empty databases, then restore the primary copy of production on the primary copy on test.
0
2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

 

Author Comment

by:rtl_support
ID: 20809951
That sounds like a good idea, and it may work in my test environment, but i wouldn't be able to do this on the current production database, as its already setup and I cannot take the database down, or start moving it to empty databases.  

I think the problem relates to the log files and the way the backup application purges them, i'll keep trying to search for that error code and see what i get.

Cheers
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20810216
Hmm, well back to your original question.  I think you should be able to mirror a running database.  Rather than trying to make the witness copy an exact match to the principal maybe you can simply make the witness a blank database and let the mirroring process do the duplication.
0
 

Author Comment

by:rtl_support
ID: 20832080
hello,

I'm still having the same problems, even though i got the difference in the Database time stamps down to 15 minutes!
I backed up our production db yesterday at 14:30, and 22:15, and i scheduled a transaction log backup every hour from 16:00.
I restored the 22:15 backup to the Principle server, and the 14:30 to the mirror server. I then restored all the transaction logs up until 22:00 to the mirror server. Now as far as i'm aware that leaves only a 15 minute difference in the restored databases.
After i finished mirroring the DB's, i got the same error:
The remote copy of the database "test" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)

Currently the only way i've managed to get mirroring working is by having exact copies of the database. and i'll never be able to achieve this on a production database, without taking it offline for an hour.
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 20860162
Why are you trying to restore the witness database?  DId you try what I suggested and only restore the principle database, then let the mirroring functionality do it's job and duplicate the witness database?
0
 

Author Comment

by:rtl_support
ID: 20865038
There is no witness database. I never said i was restoring that.

I'm just backing up the live production database (principle) and restoring it to another instance (mirror).

Then using the mirror function to make the two databases get to the same state, and be synchronised.

You cannot mirror one database with a blank database
0
 

Author Comment

by:rtl_support
ID: 22000545
This problem was never resolved. So will close.
0
 
LVL 51

Accepted Solution

by:
Ted Bouskill earned 500 total points
ID: 22004198
My SQL DBA said the following article is excellent.

http://www.sql-server-performance.com/articles/clustering/mirroring_2005_p3.aspx
0
 
LVL 51

Expert Comment

by:Ted Bouskill
ID: 22012915
The last link I provided aanswers this question.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question