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.

more ▼

asked Aug 18, 2011 at 06:30 AM in Default

avatar image

339 5 5 11

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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).

more ▼

answered Aug 18, 2011 at 07:46 AM

avatar image

26.2k 18 37 48

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.

Aug 18, 2011 at 08:26 AM sp_lock

Denali is the best solution, but you'll have to wait for that. :(

Aug 18, 2011 at 11:28 PM Håkan Winther
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 18, 2011 at 06:30 AM

Seen: 692 times

Last Updated: Aug 18, 2011 at 06:30 AM

Copyright 2018 Redgate Software. Privacy Policy