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?

Marcello Miorelli
  • 16,170
  • 52
  • 163
  • 300
Carlos Blanco
  • 301
  • 2
  • 10
  • 2
    In my question, I went over a variety of approaches we had investigated. Perhaps that'll serve as a starting point http://dba.stackexchange.com/questions/25627/sql-server-database-synchronization – billinkc Dec 05 '12 at 19:17

1 Answers1

1

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.

Ali Razeghi - AWS
  • 7,518
  • 1
  • 25
  • 38
  • 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 – Carlos Blanco Dec 05 '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 - AWS Dec 05 '12 at 21:39
  • We're targeting for 1 or 2 hours max. Data is about 100 GB more or less – Carlos Blanco Dec 05 '12 at 21:45