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

Reporting on a production database - good or bad and why? Expand / Collapse
Author
Message
Posted Tuesday, August 14, 2012 6:31 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 5:24 AM
Points: 128, Visits: 490
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



Post #1344652
Posted Tuesday, August 14, 2012 6:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 5,217, Visits: 5,065
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
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

Post #1344669
Posted Tuesday, August 14, 2012 6:53 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 15, 2014 8:38 AM
Points: 3,668, Visits: 72,432
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
Post #1344676
Posted Tuesday, August 14, 2012 7:29 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:27 PM
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
Post #1344697
Posted Tuesday, August 14, 2012 8:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 2,692, Visits: 3,383
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
Post #1344776
Posted Tuesday, August 14, 2012 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:32 PM
Points: 393, Visits: 1,593
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



Post #1344789
Posted Wednesday, August 15, 2012 5:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Yesterday @ 5:24 AM
Points: 128, Visits: 490
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



Post #1345213
Posted Thursday, August 16, 2012 8:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:29 PM
Points: 4,343, Visits: 6,149
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
Post #1346005
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse