mikelanders avatar image
mikelanders asked

Near Time Reporting DB

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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

WilliamD avatar image
WilliamD answered
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).
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
I agree with @WilliamD (+1), but one thing to consider is that you will need a secondary SQL license for replication if it is on a sep' box. Just to let you know, we use P2P on 3 nodes. 1 and 2 for load balancing and 3 for failover and reporting. Server 1 and 2 have identical hardware, with 3 containing some extra mem etc to handle reporting and transaction load if 1 or 2 fails.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
Denali is the best solution, but you'll have to wait for that. :(
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.