A Reporting System Architecture

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Gregory Jackson » A Reporting System Architecture

52 posts, Page 5 of 6

«««2 3 4 5 6 »»

A Reporting System Architecture

Rate Topic

Display Mode

Topic Options

Author

Message

GregoryAJackson

Posted Thursday, September 04, 2008 7:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

darabaf

Posted Thursday, January 29, 2009 12:21 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, November 12, 2012 6:37 PM
Points: 1, Visits: 8

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

GregoryAJackson

Posted Thursday, January 29, 2009 12:23 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

Hadn't considered it but it certainly is a good idea

Gregory A Jackson MBA, CSM

Post #646286

Rowland Gosling

Posted Friday, August 07, 2009 8:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 12:43 PM
Points: 33, Visits: 257

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! Smile

Post #766965

GregoryAJackson

Posted Friday, August 07, 2009 10:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

Rowland Gosling

Posted Friday, August 07, 2009 11:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, March 08, 2013 12:43 PM
Points: 33, Visits: 257

Neat gun collection too :) I'd bet those surly developer types don't give you a lot of crap w00t

Post #767149

GregoryAJackson

Posted Friday, August 07, 2009 11:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

lol

yeah. I usually leave em at home.

thanks again

GAJ

Gregory A Jackson MBA, CSM

Post #767159

SequelDBA

Posted Thursday, October 08, 2009 12:35 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:53 PM
Points: 35, Visits: 762

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

GregoryAJackson

Posted Thursday, October 08, 2009 1:13 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

grahamc

Posted Wednesday, October 06, 2010 3:37 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:21 AM
Points: 563, Visits: 943

Thread resurrection time Cool

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 »

52 posts, Page 5 of 6

«««2 3 4 5 6 »»

Permissions