April 1, 2010 at 12:27 pm
I am running SQL 2005 Ent edition. We have an application that runs on our SQL 2005 server and when users try to run reports off that database, it slows down production. I want to use the same production database on a different server and have the users run reports off that server so it doesn't impact performance to the other users. What would be the best way to do this. I know you can do replication, mirroring, log shipping but i am a newbie to SQL and still learning and looking for expert advice. Thanks in advance.
April 1, 2010 at 12:34 pm
What I'd recommend is replication. Or even using SSIS and building a data warehouse of sorts.
Log shipping works, but when it restores the logs, it kicks people out of the database. Mirroring + snapshot does the same thing. Replication allows you to move data over regularly, and then run reports on that server.
One thing I'd be cautious of is that contiuous data movement can end up with reports changing quickly. So if two people run a report 5 minutes apart, they can get different results. I prefer to move data once a day, and try to live with that, but if people are looking for more real time updates, I'd try to limit it as much as possible, perhaps once an hour, to move the data.
April 1, 2010 at 12:38 pm
Thanks for the quick response. I think I will try replication and maybe do it at different times during the day like 8am and then 12n. That way it is not affecting people that much. Question on replication, will that cause a performance issue at all in terms of users running queries against our production database?
April 1, 2010 at 12:45 pm
Thanks for the quick response. I think I will try replication and maybe do it at different times during the day like 8am and then 12n. That way it is not affecting people that much. Question on replication, will that cause a performance issue at all in terms of users running queries against our production database?
April 1, 2010 at 1:03 pm
There's some overhead to replication, but it's likely a more stable overhead than having people run queries. It reads through the transactions and moves them across.
April 1, 2010 at 1:06 pm
I would start by checking requirements with business.
If business is Okay with getting reports on "yesterday's" data depending on database size you can just restore a full backup on "reporting server" in a daily basis - better saying in a nightly basis - then set that database in readonly mode and you are done.
If business wants near real-time data then you have to go with replication.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 1, 2010 at 1:15 pm
We employ two methods. Both methods permit up to 24hour old data (business is ok with it). Method 1 is a full backup and restore nightly to a different server. The second method is an ETL process where data is refreshed once a day via DTS and SSIS. You might even be able to get away with a daily snapshot refresh in a replicated environment.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 1, 2010 at 1:23 pm
There is another option mirroring with a daily snapshot.
April 1, 2010 at 1:23 pm
They are not looking for real time but close to it. The database size is around 200gb so i think I am going to look at replication.
April 1, 2010 at 1:39 pm
keith.littlejohn (4/1/2010)
They are not looking for real time but close to it. The database size is around 200gb so i think I am going to look at replication.
Good Luck. There will be a little overhead - but could be manageable.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2010 at 8:46 am
For close-to-real-time reporting, I agree that transactional replication is a good choice.
Be sure to place the Distributor on the reporting server, not the production system.
Steve Jones - Editor (4/1/2010)
Log shipping works, but when it restores the logs, it kicks people out of the database. Mirroring + snapshot does the same thing.
Application of log records at the mirror is a continuous process, and does not require the snapshot to disconnect its users. You are right about that being the case with a log shipped database though.
April 4, 2010 at 11:53 am
keith.littlejohn (4/1/2010)
I am running SQL 2005 Ent edition. We have an application that runs on our SQL 2005 server and when users try to run reports off that database, it slows down production. I want to use the same production database on a different server and have the users run reports off that server so it doesn't impact performance to the other users. What would be the best way to do this. I know you can do replication, mirroring, log shipping but i am a newbie to SQL and still learning and looking for expert advice. Thanks in advance.
More specifically...
when users try to run reports off that database, it slows down production
My gut tells me that's the real problem. Although I agree that reporting is frequently better done on a "parallel" system (we used SAN "clones" or SAN "snapshots"), the next complaints will be "when users try to run reports off that databases, it slows down reporting".
My recommendation is to first make sure that the code for those reports is as effective as it can be and I'll also say that, for the most part, it's usually not.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply