Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

DB for Reporting Expand / Collapse
Author
Message
Posted Wednesday, March 27, 2013 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
In my experience the most common setup that suits a separate reporting database where the indexes can be different from the primary database is Transactional Replication with a Continuous Push Subscriptions. This means that as soon as changes are committed to the Publisher database they are immediately Pushed to the Subscriber(s) making the data available to reports.

Configuring, Modifying, and Disabling Publishing and Distribution (Replication) - SQL Server 2008 R2


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1435963
Posted Wednesday, March 27, 2013 9:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:18 AM
Points: 34, Visits: 256
Unfortunately, we are forbidden (for various, nonsensical reasons) from pulling data from the production DB outside of the overnight window.

So, would you recommend something other than snapshot replication for this? (Or, if snapshot is indeed the best method, is there a smooth way to overcome the dependencies problems I referred to in my earlier post?)

Thanks.
Post #1436002
Posted Wednesday, March 27, 2013 11:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
I have only used Snapshot for a few isolated tables, e.g. a mostly static lookup or staging tables that needed to also be copied to another server nightly, so nothing with inter-article (or is it intra) dependencies. What kind of issues are you having? Foreign key issues? You can opt not to replicate FK constraints.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1436044
Posted Wednesday, March 27, 2013 11:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 13, 2014 11:18 AM
Points: 34, Visits: 256
It was quite a few problems - some were key related, others were not.

(Also, as a side note, I have no real control over the schema of the PROD DB.)

If I opt not to replicate FK restraints, is that found on the same options page where I set the behavior of replication for existing articles?

Thanks again.
Post #1436060
Posted Wednesday, March 27, 2013 1:17 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
If I opt not to replicate FK restraints, is that found on the same options page where I set the behavior of replication for existing articles?

Sorry, I won;t be much help in deciphering how to use SSMS for this. I use T-SQL to setup Replication. In the call to sp_addarticle you'll define your options using @schema_option.

http://msdn.microsoft.com/en-us/library/ms173857(v=sql.105).aspx


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1436124
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse