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.
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.
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.
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] [Netvault Litespeed (Quest)] :