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 04, 2008 7:21 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 12:33 PM
Points: 108, Visits: 490
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 05, 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: Wednesday, August 28, 2013 12:33 PM
Points: 108, Visits: 490
Hadn't considered it but it certainly is a good idea

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 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 07, 2009 10:01 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 12:33 PM
Points: 108, Visits: 490
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 07, 2009 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 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 07, 2009 11:30 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 12:33 PM
Points: 108, Visits: 490
lol

yeah. I usually leave em at home.

thanks again


GAJ


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


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:49 PM
Points: 35, Visits: 849
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 08, 2009 1:13 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 28, 2013 12:33 PM
Points: 108, Visits: 490
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 06, 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: Wednesday, March 05, 2014 4:14 AM
Points: 563, Visits: 971
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