May 7, 2009 at 4:11 pm
Hi All
I am looking for ideas here in terms of implementation, I currently have a live billing database box and the idea is to have a reporting database which is strictly for reporting purposes coming off the billing box.
I’m looking at implementing a solution which would be very efficient and allowing fort querying the data in a very fast and efficient manner, I wouldn’t also mind if this reporting database could also serve as some form of resilience.
In other words, how do other experienced users implement a reporting system off a live production database, as we don’t really want users querying the billing database to pull off data.
Ideas will be appreciated here.
May 7, 2009 at 9:32 pm
Unless reporting users are looking for up to the minute or second data, log shipping should suffice. You could also look into mirroring.
May 7, 2009 at 10:18 pm
What's your hardware architecture? Do you have a SAN?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2009 at 1:07 am
Hi
Hardware is pretty beefed up, in terms of storage. there are options local or SAN.
Can you give me options for the 2 types of storage, then it would be far more easier to make a call.
cheers
May 8, 2009 at 1:14 am
Personally I don't like the idea of having a reporting database. I try to keep it all together, but I don't give the reporting tool direct access to the tables: I use a separate schema for reporting purposes, populated by stored procedures, one for each report. When the report is unloaded, the data tied to that elaboration is deleted.
I dont' know what reporting tool you're using, but I suggest yuo never give Crystal Reports direct access to your tables, unless you want your DB to get almost locked by "mysterious" processes spawned by reports...
Regards
Gianluca
-- Gianluca Sartori
May 8, 2009 at 5:23 am
Hi,
I see your point in this matter, the situation here is that I cant have reporting users querying the billing database at thesame time, as the reports are not monitored in any way, what I mean is that reports are requested in the form of web requests which then queries the database, the data is pulled out and .NET is used to wrap XML around it, so in theory customers can run various reports with different kinds of parameters.
Having them query the billing database will mean chaos, as we have applications constantly using the live system as well.
thx
May 8, 2009 at 8:17 am
We use Transactional Replication and Run our reports on the subscriber.
It has worked very well for us.
* Noel
May 8, 2009 at 9:19 am
In an attempt to create harmony, I use trasnactional replication, preventing the OLTP side of the house from interfering with the BA side on larger databases. In some cases we are using triggers which, when fired perform an assortment of calculations which place this data in reporting databases.
I have found over the years that even having seperate tables/schemas within the same database can be problematic in high transaction databases, as you are still completing for resources whether it is locks, cache, disk io, etc. Having 2 seperate and distinct systems has relieved this issue. As for the comments on Crystal...;-)
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
May 8, 2009 at 9:20 am
Are there any gotchas when using this replication model and how about the need to optimise code i.e having indexes on the code, and the index on the publisher might not necessarily be what the subscriber needs as its a reporting box.
whats the strategy here ?
May 8, 2009 at 9:27 am
Two databases connected by automated ETL package updating the reporting database with changes in the billing database as needed. This saves all the resources needed to run replication very efficient without replications hardware cost, if you have many users a 64bits Agent could be more scalable.
😉
Kind regards,
Gift Peddie
May 8, 2009 at 9:38 am
Dean Jones (5/7/2009)
Hi AllI am looking for ideas here in terms of implementation, I currently have a live billing database box and the idea is to have a reporting database which is strictly for reporting purposes coming off the billing box.
I’m looking at implementing a solution which would be very efficient and allowing fort querying the data in a very fast and efficient manner, I wouldn’t also mind if this reporting database could also serve as some form of resilience.
In other words, how do other experienced users implement a reporting system off a live production database, as we don’t really want users querying the billing database to pull off data.
Ideas will be appreciated here.
Sounds like you need a Data Warehouse!
A Data Warehouse will give you
- fast and efficient reports
- An easy schema for creating reports
- An easy schema for giving certain people (i.e. data analysts) SQL access
- A centralised data store which can be expanded for other areas of the business later
- A good data source for building cubes with SSAS
A data warehouse will not give you any form of resilience though
May 8, 2009 at 9:55 am
Gotchas are the expense in having multiple servers, as for indexing, each table is indexed to meet it's needs whether on the OLTP or BA side.
ETL tools may work - little experience/knowledge - just now getting started in this area. But, my understanding is just that, it would eleminate the replication as well as triggers. A little hesitant (on myself) to jump in with out a bit more information and testing.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
May 8, 2009 at 10:02 am
Sounds like you need a Data Warehouse!
A Data Warehouse will give you
- fast and efficient reports
- An easy schema for creating reports
- An easy schema for giving certain people (i.e. data analysts) SQL access
- A centralised data store which can be expanded for other areas of the business later
- A good data source for building cubes with SSAS
A data warehouse will not give you any form of resilience though
Datawarehouse more expensive that replication with limited benefit if you don't have dimension modeling expert to use your data for cubes, it also comes with the risk for the project failing.
Kind regards,
Gift Peddie
May 8, 2009 at 10:02 am
Hi gift,
Just wondered how this would be done when you have loads of tables on the database.
How would you track the following:
Inserts
Deletes
Updates
Its fine when the changes coming from the billing db to the reporting db is just inserts, that is kind of straight forward as you can easily have a marker on the table or use the new CDC on sql 2008, but i would be inclined to know how you would do this in situations where you have read/writes on the billing db.
thx.
May 8, 2009 at 10:03 am
sjimmo (5/8/2009)
Having 2 seperate and distinct systems has relieved this issue. As for the comments on Crystal...;-)
I simply try to avoid it because I can't prevent the reporting tool from issuing absurd queries on which I have no control: it simply moves the problem away from production environment, but it's no solution.
I have found that running a stored procedure to populate a reporting table with data joined, formatted and grouped exactly how the reporting tool expects it is an efficient way to avoid fancy data accesses on my production db. Maybe reporting users will have to wait a bit if there's some lock on the source tables, but no data entry user will wait because Crystal Reports or Some other reporting tool is running a RBAR query on production tables.
If you still have any doubt, yes... I hate Crystal Reports!
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply