Strategy for Backup SQL Server | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Strategy for Backup SQL Server

Hi, Can you please advise a good strategy for a backup SQL2K Server? Scenario: ServerA is the production DB server. ServerB is the standby server (has the same DB but is not used). When ServerA fails, ServerB will be put online with a reasonnable downtime. There a 6 DBs on ServerA. Minor updates could happen during a week and heavier updates every 3 months. Updates include table structure change, delete some obsolete objects, new and updated objects (mostly tables, stored proc, constraints). A Cluster would be ideal, but is also the most expensive solution. Can you give your opinion about REPLICATION and LOG SHIPPING? Is it realistic to use these methods for a stand by server? In case we want to go with either replication or log shipping, what are the possible incenveniences that we should expect? Thank you very much for your help.
You can happily choose log shipping in this scenario as you do not need online recovery of database in which case you can go for clustering. Search under this website for more information and articles on Log Shipping in order to clear out all your doubts. I’m tempted to lend you the linkhttp://www.sql-server-performance.com/sql_server_log_shipping.asp anyway. If you strike with any issues while setup then feel free to come back here.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks very much Satya for your advice. Can you please help me to clarify: Q1. What is the advantages Log Shipping over Replication? In other words, any limitations with replication? Q2. In case of DB schema change (new, upd, delete tables, constraints, indexes, SP) on the Source server. Can the log shipping cop with this automatically or should we resync manually the target DB and reconfigure log shipping?
1. Replication is more granular based solution to replicate objects of database. Whereas Log shipping will port the transaction log from primary to secondary server and easy to manage, administer and deal any issues.
2. Log shipping will take care of all kinds of transactions occurred in source server database to secondary server according to the log shipping interval. One more point is if any database is under SIMPLE recovery model then you must deploy manual way to copy the database backup between the servers as this is not allowed under lgo shipping. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>