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 1 of 6

1 2 3 4 5 »»»

A Reporting System Architecture

Rate Topic

Display Mode

Topic Options

Author

Message

GregoryAJackson

Posted Sunday, January 20, 2008 2:55 AM

SSC-Enthusiastic

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

Comments posted to this topic are about the item A Reporting System Architecture

Gregory A Jackson MBA, CSM

Post #445276

Mike DiRenzo

Posted Monday, January 21, 2008 4:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:11 PM
Points: 143, Visits: 183

Excellent article. Thank you very much. Your opening of how you worked in many places and faced the same issues (reporting, etc.) can, I am sure, be related to by many other IT professionals.

Your concern with middle tier code crashing when adding your audit columns is completely valid, albeit, a customized concern. As you pointed out, every situation is different. For example, some middle tier code have direct attribute (table columns) to property mappings (class/object properties). There must have been a ton of modifications to your sprocs for your situation.

Your detailed model of how you went about this challenge is excellent and again, I thank you very much.

- Mike

Post #445396

GregoryAJackson

Posted Monday, January 21, 2008 6:45 AM

SSC-Enthusiastic

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

thanks for the comments Mike.

One thing that really helped us was that 100% of our middle tier code, including all of our sprocs is Code Gen'd.

It's been a lot of work creating our code gen templates, but it certainly paid off in this case and allowed us to present the business case to management that we were up to the task of such a massive endeavor....

Cheers,

GAJ

Gregory A Jackson MBA, CSM

Post #445440

agatti

Posted Monday, January 21, 2008 10:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 09, 2013 5:58 AM
Points: 77, Visits: 203

Ok Gregory, here is the question you've been waiting for. Why did you use mirroring, as opposed to replication, to move the data to the reporting server when a replicated database would have been readable without the need for a snapshot database?

Post #445579

GregoryAJackson

Posted Monday, January 21, 2008 11:08 AM

SSC-Enthusiastic

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

2 reasons really....

1) I was concerned that Replication would put a load on the Source Server (more so then simple mirroring)
2) the ease of setting up mirroring. literally click a few buttons and you're done

Gregory A Jackson MBA, CSM

Post #445585

DougS

Posted Monday, January 21, 2008 11:34 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, October 19, 2012 4:44 AM
Points: 1, Visits: 38

You say your procedures were all code gen'd? You mean you had a straight insert,update,delete, and select proc for each table?

Thanks,
Doug

Post #445596

GregoryAJackson

Posted Monday, January 21, 2008 11:42 AM

SSC-Enthusiastic

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

yes and we also have numerous versions of each "Report" sproc.

Our UI is a bit strange in that the users can pick a sorting and grouping option in the UI. this modifies the eventual output in Crystal.

We used to create dynamic SQL in a single sproc for each report based on the Sorting and Grouping options.

this was difficult to optimize on the SQL Server side.

so now, we create an xml file that lists all the reports by name, lists their parameters, their selects, and their sorting and group by options.

then the code gen process will create 1 version of the reporting sproc for each Sort\Group By combination.

There are TONS of sprocs in our DB, but they all run very quickly now. It's truely a dramatic improvement when compared to our old system.

I'm trying very hard to convince others here that we should migrate ALL grouping and sorting logic out of SQL Server and Towards the UI (Crystal) but so far I've not convinced the others on my team here that it's worth the effort.

hope this all makes sense....

GAJ

Gregory A Jackson MBA, CSM

Post #445602

Jerry Hung

Posted Monday, January 21, 2008 12:45 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 3:02 PM
Points: 768, Visits: 1,159

Any reason why you didn't choose "Log Shipping"?

1. It has easy UI feature in SQL Server as well (right-click, Log shipping)
2. It provides Log backup at set intervals (say 15 mins), good for backup purpose if not already done on Production
3. You can auto-restore logs, and the restored database will be READ-ONLY always
but at least you don't have to create snapshot. It's an issue if you need to update it

SQLServerNewbie

MCITP: Database Administrator SQL Server 2005

Post #445624

GregoryAJackson

Posted Monday, January 21, 2008 12:56 PM

SSC-Enthusiastic

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

Good Question,
Log Shipping was one of the other choices\alternatives for us.

I've used log shipping in the past, and in my experience, it seemed fragile...

It would stop shipping logs, and we'd have to reseed quite regularly etc.

this was a main reason that I decided to move to Mirroring as opposed to Log Shipping. It appeared to be more robust.

is this a reality? (honestly, probably not)...

I do have to say though....mirroring is working out quite nicely for us. I really have no complaints.

GAJ

Gregory A Jackson MBA, CSM

Post #445626

don_goodman

Posted Monday, January 21, 2008 2:52 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 06, 2010 1:30 PM
Points: 34, Visits: 82

I question the use of DTS/SSIS. The fact is you can get the data out using simple select statements qualified by your create and edit dates. The only place you will hit blocking is when the OLTP app is updating a record you are reading. But, you hit that whether you are using replication or mirroring or a script with no overhead.

There is peformance downside using DTS or SSIS over T-SQL scripts. Assuming you properly index the OLTP database, then your incremental queries fire dramatically faster than the gui intensive interfaces and/or runtime libraries of the CLR.

Except for the loading routines (which will bite you hard when all your databases get really big) you have the right idea and I WISH everyone used staging and reporting databases for reporting. There are so many opportunities to build servers using instances and memory isolation that there is never a reason NOT to have a reporting database.

Don

Post #445654

« Prev Topic | Next Topic »

52 posts, Page 1 of 6

1 2 3 4 5 »»»

Permissions