May 19, 2008 at 5:05 pm
Hi,
I wanted to see if I could get some feedback on using log shipping to create a standby database for our users to use to run reports. We have a cluster setup for failover so I am thinking of using log shipping primarily to offload heavy queries being run on our production server that is causing some performance issues. Our primary database is about 90GB and is on a server running Windows 2003 SQL 2005 SP2. The standby server is running Windows 2008 SQL 2005 SP2 64bit. I have log backups on my primary database every 15 minutes and they are between 50-100MB in size. I'm not sure about the timing of how often I will have the logs applied to the secondary server yet but I thought I would just check and see if this was the best solution to use. Anyone out there use log shipping have any feedback? Is there a better way to create a read-only query database that is a copy of our production database?
Thanks!
Isabelle
Thanks!
Bea Isabelle
May 19, 2008 at 10:32 pm
The biggest problem with log shipping for a reporting database is that every time you restore the logs to the reporting database, you need to kick everyone off that database because the restore process requires exclusive use of the database.
You indicated that you are trying offload some heavy report users to the reporting database. To me, this tends to suggest that these users are likely to be running large queries that probably take quite a while to execute. This will be a problem when the log restore process needs to run.
Certainly, log shipping can be easy to administer etc since every action in the source database logically gets applied to the destination.
For reporting, I would consider transactional replication as an alternative. This works well if your schema is fairly stable. Additionally, you have the option to create additional indexes that better support your reporting queries.
May 20, 2008 at 1:25 am
Isabelle,
We did something similar at one of our sites. We had an application that hammered the main SQL Server cluster with some of the reports it ran. We had log shipping set up for DR and decided to create a reporting database on this server to take the load off the cluster.
What we did was created a duplicated copy of our production database and placed it on the DR server. We then 'paused' log shipping twice a day for an hour (00:00-01:00 and 12:00-13:00) so we could run some SQL Jobs to copy data (only new inserts) from the Log Shipped database to our reporting database. THis meant the users could stay logged into the reporting database 24x7 and it wouldnt affect our DR setup. Okay their was potentially an hours worth of logs that needed restoring should we require the DR server database for anything, but the business was happy to take this risk.
The overall setup reduced the queries on our cluster by about 15% and also allowed reports to be generated a hell of a lot quicker.
Matt.
May 20, 2008 at 11:40 am
Hi,
Thanks for the replies! So Matt, what you are saying is that you didn't use the log shipping functionality but instead just copied modified data over twice a day via SQL scripts...and then applied the logs later? Sorry, didn't quite understand. For our situation we do not need this for DR because we have our failover cluster for that. I did see that users need to be kicked off in order for the logs to be applied and I am currently testing how long it takes to apply logs if we did it once a day, twice a day, every 4 hours. Yes, there are potential big jobs that may need to run for a longer time than what our log shipping restore schedule may allow, but we hope to be able to work with the users to come to an acceptable schedule. And maybe if someone need to kick off a long job that we know about ahead of time, I can just pause the log shipping until the job is done. I'm not sure if this will work or not but we are hoping so.
I will also look at transactional replication option as well. 😀
Thanks,
Isabelle
Thanks!
Bea Isabelle
May 20, 2008 at 12:45 pm
You may also want to consider mirroring and a snapshot, if you are using Enterprise Edition.
May 20, 2008 at 1:24 pm
Hi Isabelle,
Sorry, i hope i have worded it a bit better this time.
We had our production cluster "Cluster A" that housed our live systems. We then used log shipping to create a mirror of the cluster (or live node) on our "DR Server". I think our logs were shipped from "Cluster A" to "DR Server" and applied every ten minutes or so.
Also on the "DR Server" we had a copy of the log shipped database (a one of restore) that was kept upto date via two SQL Server Agent Jobs that queried the log shipped database. We did this by pausing the applying of logs during two 1 hour windows.
I'll knock up a Visio diagram tomorrow if you want and put together a few more notes?
Matt.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply