Can you Mirror an sql database once its gone live?

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
rtl_supportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
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
rtl_supportAuthor Commented:
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
Ted BouskillSenior Software DeveloperCommented:
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
Get expert help—faster!

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

rtl_supportAuthor Commented:
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
Ted BouskillSenior Software DeveloperCommented:
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
rtl_supportAuthor Commented:
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
Ted BouskillSenior Software DeveloperCommented:
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
rtl_supportAuthor Commented:
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
rtl_supportAuthor Commented:
This problem was never resolved. So will close.
0
Ted BouskillSenior Software DeveloperCommented:
My SQL DBA said the following article is excellent.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ted BouskillSenior Software DeveloperCommented:
The last link I provided aanswers this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.