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 «««23456»»

A Reporting System Architecture Expand / Collapse
Author
Message
Posted Thursday, September 4, 2008 7:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
good question...

that was exactly why we went with mirroring.

SQL Server Mirroring automatically copies the transaction logs from your source to the target.

the logs are backed up as part of normal OLTP operations and the copying of the logs from the OLTP server to a "Staging" Server has zero impact to the OLTP.

Once the logs are copied to the staging server, the staging server does the work to move the data (ETL) to the reporting system.

This is how we do it without impacting the OLTP.

The article shows a picture and provides the details.

If you have more questions, please feel free to contact me directly. Contact info is provided in my profile.

GAJ


Gregory A Jackson MBA, CSM
Post #563787
Posted Thursday, January 29, 2009 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 5, 2013 5:09 PM
Points: 1, Visits: 13
On using ReportingDB1 and ReportingDB2, did you consider appliances like F5 Big-IP using iConnect so that your reporting layer would always point to a single virtual IP and ETL to a different Virtual IP and the swap happening without the knowledge of the codes on either side?
Post #646285
Posted Thursday, January 29, 2009 12:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Hadn't considered it but it certainly is a good idea

Gregory A Jackson MBA, CSM
Post #646286
Posted Friday, August 7, 2009 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:39 PM
Points: 33, Visits: 268
We've all been there: This is a classic problem and really could use more discussion. Thank you for taking the time to write this article and sharing your solution. Nice job!
Post #766965
Posted Friday, August 7, 2009 10:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Thanks Rowland,

I agree.
I'd love to hear more discussion on this topic.

Never an easy one to handle....


cheers,

Greg J


Gregory A Jackson MBA, CSM
Post #767076
Posted Friday, August 7, 2009 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:39 PM
Points: 33, Visits: 268
Neat gun collection too :) I'd bet those surly developer types don't give you a lot of crap
Post #767149
Posted Friday, August 7, 2009 11:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
lol

yeah. I usually leave em at home.

thanks again


GAJ


Gregory A Jackson MBA, CSM
Post #767159
Posted Thursday, October 8, 2009 12:35 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 35, Visits: 910
Great Post!

Any thoughts on the best architecture for creating a standby instance for reporting? Our prod database is over 800GB.

Reporting requirements are during business hours (8:00-5:00).

Log Shipping?

Thank you!



KU
Post #800268
Posted Thursday, October 8, 2009 1:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:41 PM
Points: 110, Visits: 497
Yeah It would depend on the requirements for the reporting environment (mostly the lag time that is acceptable).

LogShipping would be a great solution in most cases.


gaj


Gregory A Jackson MBA, CSM
Post #800292
Posted Wednesday, October 6, 2010 3:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
Thread resurrection time

Great article... this has been an issue at every place I have worked!

Anyone got experience with the larger DB side of things? Our PROD server is 700GBs. Requirements would be very little (if any) "lag" between the PROD and REPORTING. What would the ideal solution be (no real budget constraints)?


My thoughts are pretty much leaning towards transactional replication. Are there other options I should be considering?
Post #999088
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse