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


Reporting on a production database - good or bad and why?


Reporting on a production database - good or bad and why?

Author
Message
Mark Eckeard
Mark Eckeard
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 505
My development group is currently debating a new process we wish to implement and I'd like to get input to see what others thought.

We're about to move over to an in-house system that'll store financial transactional data in sql server. We need to write reports for senior management at some point in the future.

I don't have full details but I believe we'll be using log shipping to a seperate system for DR. The main system should be virtualized and clustered.

Anyhow, someone suggested we generate reports from the DR sql server since it'll have live data for real-time reporting. I said no to this since I consider it a production system and wouldn't want to cause issues.

My suggestion was to set up a second server and somehow copy data from the production system. Maybe ssis or by some other means.

Am I wrong in thinking the DR system is production and shouldn't be touched? Is there another way to accomplish real-time reporting without touching a production database? The basis for the argument seems to be real-time reporting although I think we should set the precedence for using data 1 day behind so that we don't have to touch a production system.

I look forward to suggestions.

Regards,
Mark



anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
Well we go into the whole how much do you have to spend on the project.

As soon as you start using your DR servers for user queries (in this case the reports) you have to license the DR SQL servers, so if they are not already licensed it could cost you dearly.

If your company has been cleaver and licensed all the virtual hosts CPU's correctly you could spawn as many virtual servers on that one virtual host, the only cost would be for the additional storage, but you dont have the additional SQL costs.

In the past environments I have worked on, reports where always 1 day behind. There was the odd exception to the rule, but that required sign off from DBA's as well as the board and CTO as to why that business user wanted real time reporting. Typically around 1 in 100 reports where real time.

Personally, I always go on never run reports off live, always use a copy, currently working on a project where a DB has been created on the same server and data transfered into it over night via SSIS as the server isnt under load, but want to reduce the footprint in the live DB.

But thats just my opinion, others may differ or agree.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4101 Visits: 72512
I'm in the boat with Anthony here.

Not to mention if you're log shipping, that the database has to be in Standby mode, and will kick the users out every time a log ships (which isn't fun to explain to management).

Better to derive a Data Warehouse or Data Mart and enable reporting from there.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
sqlharmony
sqlharmony
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 64
Log shipping is the real thing here that is killing your idea as others have mentioned you will not be able to report against the databases while the transactions logs are being applied, not to mention that the reporting activity could impact the log shipping processes also. Why are you choosing to use log shipping to maintain your DR databases? If you are running sql 2000 that might explain it but assuming you are running one of the later versions (and also assuming you are running the enterprise version) you could take advantage of the mirroring feature. And, you can create immediate snapshots of your mirrored databases which you could report against. So you could run a job every x minutes that would build new snapshots and drop the old. This would give you a near real time reporting environment. Of course, there is also the downside of the reporting users' connections being terminated each time you generate a new snapshot but this is a different story. My point being, log shipping is a very bad technology to use to maintain a real time reporting environment. Mirroring is better but both have their downsides.

Michael Connolly

SQLKnowItAll
SQLKnowItAll
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2796 Visits: 3681
sqlharmony (8/14/2012)
Log shipping is the real thing here that is killing your idea as others have mentioned you will not be able to report against the databases while the transactions logs are being applied, not to mention that the reporting activity could impact the log shipping processes also. Why are you choosing to use log shipping to maintain your DR databases? If you are running sql 2000 that might explain it but assuming you are running one of the later versions (and also assuming you are running the enterprise version) you could take advantage of the mirroring feature. And, you can create immediate snapshots of your mirrored databases which you could report against. So you could run a job every x minutes that would build new snapshots and drop the old. This would give you a near real time reporting environment. Of course, there is also the downside of the reporting users' connections being terminated each time you generate a new snapshot but this is a different story. My point being, log shipping is a very bad technology to use to maintain a real time reporting environment. Mirroring is better but both have their downsides.
+1 Also, if you have the money... You can simply set up a reporting server that acs as a subscriber to your main database. The reality is, you have to assess the situation completely. If you system is is not loaded down, why not just report from the production database? I used to work at a company where the OLTP database was used for real-time reporting non-stop, but the actual transactions to the database were only in the 1000's per day. Some of these reports were pretty large and took a lot of processing. However, they never slowed down other reports or processing. Again, it depends on your company and the usage on the server. In most cases, I would not recommend reporting off of the production database. However, if the company is unwilling to spend the money for a proper setup, you have to weigh the benefits and risks of other routes.

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
MWise
MWise
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 1747
What is the load on your production server? If there is capacity you could always create a reporting db on the same instance and use SSIS to move data over on a daily (or more frequent) basis. That way you reduce licensing and hardware costs. If you get to the point that you need to move the reporting db to its own server, that is pretty easily done as you can just point the SSIS connections to a new server name (using configuration settings in SSIS makes this easy). I would also highly recommend that you actually create a reporting database architecture and not just report off of copies of your OLTP tables. You will want to design for fast read access and should build out dimensions like Date, Account, Person, Organization, Product. You'll want those when you need to create a report that shows monthly sales trends for all products by salesperson and you want every month and salesperson to show up on the report even if they didn't have sales.

Good luck,
MWise



Mark Eckeard
Mark Eckeard
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 505
The load on the server shouldn't be high but we have almost 50 total locations, our network isn't exactly lightening fast and we have financial data that simple cannot be lost or interrupted. And sadly, I don't think there's money for upgrades.

My main concern is mostly the lack of expertise with this. We have yet to hire a FT DBA and the person (not me) writing the reports may not be well versed using SQL Server.

This is good advice and thanks to everyone who posted. I'll take everything in to consideration when we discuss this again.

Mark



TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5937 Visits: 8298
Mark Eckeard (8/15/2012)
The load on the server shouldn't be high but we have almost 50 total locations, our network isn't exactly lightening fast and we have financial data that simple cannot be lost or interrupted. And sadly, I don't think there's money for upgrades.

My main concern is mostly the lack of expertise with this. We have yet to hire a FT DBA and the person (not me) writing the reports may not be well versed using SQL Server.

This is good advice and thanks to everyone who posted. I'll take everything in to consideration when we discuss this again.

Mark


1) As other's have said, if you are doing log shipping you have to manage your restores since you can't have reports running and do a restore at the same time. It is doable though, just a PITA.

2) Mirroing can get you point-in-time copies of the data, but again you have to manage the naming scheme and connections to each snapshot. This can lead to much more performance problems than the log shipping scenario since it means multiple copies of the same data pages in memory.

3) You REALLY REALLY need to get a professional on board to help you understand what your needs really are from an HA/DR/Reporting perspective and help you chose and implement the correct option(s). I will offer my services to help with this as well as other DBA activities (maintenance, perf analysis/tuning, backups, etc). There are a number of other qualified consultants on SSC.com too. I know of more than one company that thought they could do things without a DBA and something went south ... and the company went out of business. Don't let that be you!!

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
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