August 14, 2012 at 6:31 am
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
August 14, 2012 at 6:47 am
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.
August 14, 2012 at 6:53 am
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.
August 14, 2012 at 7:29 am
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.
[font="Courier New"]Michael Connolly
[/font]
August 14, 2012 at 8:54 am
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.
Jared
CE - Microsoft
August 14, 2012 at 9:08 am
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
August 15, 2012 at 5:27 am
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
August 16, 2012 at 8:29 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply