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 12345»»»

A Reporting System Architecture Expand / Collapse
Author
Message
Posted Sunday, January 20, 2008 2:55 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
Comments posted to this topic are about the item A Reporting System Architecture

Gregory A Jackson MBA, CSM
Post #445276
Posted Monday, January 21, 2008 4:22 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
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
Posted Monday, January 21, 2008 6:45 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Posted Monday, January 21, 2008 10:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 9:13 AM
Points: 81, Visits: 235
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
Posted Monday, January 21, 2008 11:08 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Posted Monday, January 21, 2008 11:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 18, 2013 10:20 AM
Points: 1, Visits: 40
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
Posted Monday, January 21, 2008 11:42 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Posted Monday, January 21, 2008 12:45 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 8:06 AM
Points: 772, Visits: 1,186
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
Posted Monday, January 21, 2008 12:56 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, September 15, 2014 2:04 PM
Points: 110, Visits: 496
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
Posted Monday, January 21, 2008 2:52 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 6, 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 »

Add to briefcase 12345»»»

Permissions Expand / Collapse