Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Reporting System Architecture


A Reporting System Architecture

Author
Message
GregoryAJackson
GregoryAJackson
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 505
Comments posted to this topic are about the item A Reporting System Architecture

Gregory A Jackson MBA, CSM
Mike DiRenzo
Mike DiRenzo
SSC-Enthusiastic
SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)SSC-Enthusiastic (181 reputation)

Group: General Forum Members
Points: 181 Visits: 210
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
GregoryAJackson
GregoryAJackson
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 505
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
agatti
agatti
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 241
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?



GregoryAJackson
GregoryAJackson
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 505
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
DougS
DougS
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 43
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
GregoryAJackson
GregoryAJackson
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 505
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
Jerry Hung
Jerry Hung
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 Visits: 1208
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
GregoryAJackson
GregoryAJackson
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 Visits: 505
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
don_goodman
don_goodman
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search