I have a need to have a near time copy of a 30 GB database for reporting. What is the best way to synchronize changes from one database to another and the secondary be online. With Mirroring and Log Shipping both having the database in a restoring mode what are my other options other than triggers or some SSIS package to insert the changed records on some type of interval? This is on SQL 2008 Standard Ed. Any help is appreciated. Is replication an option? I haven't ever worked with it.
asked Aug 18, 2011 at 06:30 AM in Default
You could try out transactional replication. This will not have the limitation of being read-only and can be configured to run in intervals, or constantly.
Transactional Replication does have a few disadvantages though. You have to have primary keys on all tables you want to replicate (but you have those anyway, right?).
You have to take an initial snapshot of the database and also "register" each database object in the replication publication. Both of these actions are blocking actions (at one point or another) and can be quite a resource intensive process (server resources). Luckily, this is normally a one off step and should you wish to add objects to a currently running replication, you only need a snapshot of the newly added objects and not everything.
You have to be careful in how you make schema changes. You cannot drop replicated objects, they must be removed from the replication publication before doing so. This can catch you if you use the database table designer in SSMS, as it quite happily creates drop scripts in the background which fail on replicated objects.
Once a publication is up and running, you should have few issues with it. However, read up and practice with a test version of your database. This will allow you to see how it all interacts and how well the reporting system can work with the replica.
P.S. If you can wait, when Denali is officially released you will be able to use read-only replicas as a built in "automagic" functionality (although I don't know if that will be enterprise edition only).
answered Aug 18, 2011 at 07:46 AM