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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

closed as off-topic by Paul White, Phil, Aaron Bertrand Jan 26 '15 at 14:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, Phil, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

In my question, I went over a variety of approaches we had investigated. Perhaps that'll serve as a starting point… – billinkc Dec 5 '12 at 19:17

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 – omar 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 – omar Dec 5 '12 at 21:45

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