Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

We're trying to create an exact copy of our prod database to later use it for reporting. I was told that some of my colleges tried to use the out of the box replication tools that SQL Server provides without any success. I believe the reason it failed was because of the version of the servers (Runtime Edition if I'm not mistaken).

Are there any good third party tools to replicate data from a SQL Server 2005 to another server?

share|improve this question
2  
In my question, I went over a variety of approaches we had investigated. Perhaps that'll serve as a starting point dba.stackexchange.com/questions/25627/… – billinkc Dec 5 '12 at 19:17

1 Answer

up vote 1 down vote

I don't think it was the run time edition that was the problem, most likely you: -Were not set to a high enough compatibility level in your DBs -Some of your tables might have not had primary keys -End nodes perhaps were not accessible

Replication is great as it gives you a lot of flexibility in what to replicate over and what not to. It also gives you very near real time reporting. It does create 6 jobs and requires you to handle schema changes differently based on scenario you use.

Something much easier to manage might be to use async mirroring (if you have enterprise). With Asynch mirroring you will not slow down commits on the primary site and you can take snapshots every day/12 hours/etc. then do your reporting off that. Everything, including DLL and DML changes are replicated over and it doesn't need to create 6 jobs.

share|improve this answer
Yes. I also think it was a user error the one that arose. About the schema, indexes and such. It is a Microsoft Dynamics DB. I'm hoping replicating a DB designed by MS should be easier – tou Dec 5 '12 at 20:28
How 'real time' does the data need to be? If it doesn't have to be up to the minute data I'd highly suggest mirroring with snapshots. Let me know your thoughts and good luck! – Ali Razeghi Dec 5 '12 at 21:39
We're targeting for 1 or 2 hours max. Data is about 100 GB more or less – tou Dec 5 '12 at 21:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.