SQL Clone
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
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23592 Visits: 6519
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
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7122 Visits: 72521
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-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

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

Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13098 Visits: 3697
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
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
MWise
MWise
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1026 Visits: 1803
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
SSChasing Mays
SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)SSChasing Mays (629 reputation)

Group: General Forum Members
Points: 629 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
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31274 Visits: 8670
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 on googles mail service
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