Question :
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?
Answer :
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.