asked
Replacing Logshipping with Replication/Mirrioring in SQL 2005
Hi, We are currently using Log shipping for HA. However, since the DB size and processes running at all times gives me a hard time to have the backup job completed during the night. So I would like to have full backup and tran backup jobs to be done at the DR site. Is that a possibibilty if I switch to Replication/Mirroring. Also how will that affect the performace? I dont want to switch it and affect any of the performance on the prod server.
Remember, this site works off of votes. Please mark all helpful posts by clicking on the thumbs up next to those posts. Please mark the solution by clicking on the check box next to it.
Firstly I am surprised that you cant get backups to complete. What is your backup configuration currently? ie Log backups, Full backups etc... How long do they each take? Are you supporting a fully 24/7 system? Are you writing the backup to a local disk or a network location? After those questions - some more questions!! What edition of SQL Server are you using? Is your Log Shipping target in a different location and if so what network capacity do you have between the two locations? Mirroring will not let you take backups from the mirror so that will not be a solution to meet part of your question.
Thanks. Fatherjack. Yes I am supporting a 24*7 System which has Db of size of around 800 Gigs. I have a Diff backup every night, that ideally runs for 1.5 hrs without any of the application processes runnning. However, with the app procs running it takes 7-8 hrs and bleeds into morning prime time. Backup is being taken to Network location for space constraints. Yes LS is on another Server and the network capacity is good dont know exact BW. Mirroring will not allow me to take backups? then no point thinking ahead- Will replication allow me that? Or what will help me to have the nightly backup process a success. :|
There are a couple of things to try without/before spending $$$. Backups will probably write more quickly if you backup to more than one file, I have seen a 45 minute process drop to under 10 by using 4 files instead of 1. Could you cope with Log backups through the week and a Full backup at the weekend? Log backups will be smaller than the Diff and there fore write more quickly.
Another take on this.... If the system is business critical system (24/7 and 800GB and HA - sounds like it), then maybe you should look at upgrading to a newer version of SQL (2008 R2 onwards). Not only is 2005 out of mainstream support now, but way you can take advantage of native backup compression, which would speed up backups. Yes it will cost, but at what price is your business willing to run without backups? If money is no object, look at using SQL2012 Availability Groups - you can backup from a readable secondary replica.
Yes. Sir already in thoughts and talks about using the high versions of SQL. I am using SQL native backup on all other systems. This is the only one left behind and for some other issues. So until then upgrade is not an answer to my problem. :(
If backup is the biggest problem and edition upgrade is not an option right now, why not buy your way out of your problems with third party backup software? You get compression that speeds up your backups and makes backup size smaller (depending on the data). Also there are tools included that helps you to setup log shipping that is integrated with the compression feature. Now compression make's your cpu work harder, but there are settings to test to minimize this effect. Some products: [Redgate - SQL Backup Pro][1] [Netvault Litespeed (Quest)][2] [1]:
http://www.red-gate.com/products/dba/sql-backup/ [2]:
http://www.quest.com/litespeed-for-sql-server/
Thanks Raadee. Already using RedGate HB, have contatced them for their inputs and any new upgrades that might help. After Compression the file backup size is about 80 gigs and diff backup everynight is about 20 to 30 gigs. I know that the other app processes are a bit too much, otherwise the diff backups finishes up in about 1.5 hrs. but thats the way cookie crumbels. Trying out all the options that I can.
17 People are following this question.
