none
How to backup a SQL 2008 r2 cluster solution in a windows 2008 R2 Cluster

    Question

  • We got W2K8R2, SQL2K8R2, 2 nodes, 8 instances, A/A

    C:\ = os+bin (Local Disc)

    D:\ = Mount Point Container for the following Mount Points: data,log,tempdb,backup for instance 01 (4 separate LUNs on SAN)

    E:\ = Mount Point Container for the following Mount Points: data,log,tempdb,backup for instance 02 (4 separate LUNs on SAN)

    etc untill instance 08

    M:\ = MSTDC (LUN on SAN)

    W:\ = Witness (LUN on SAN)

    Z:\ = Backup (Local Disc)

    What would be the best backup strategy? Which product do you use?

     

    Thanks to Vincent Hu, Microsoft Contigent Staff for pointing out what to backup:

    http://blogs.msdn.com/b/john_daskalakis/archive/2009/02/27/9448408.aspx

     

    I like to have a backup on internal disk besides the external backup solution, although some pro's thinks that this is somewhat ridiculous.
    Anyway, this means that I have to have the backup on both nodes for all instances, which again means double up on storage usage.

    Regards Tonny


    /torpo
    Tuesday, December 20, 2011 11:57 AM

Answers

  • Hi Tonny,

    If you need backup solutions, I would recommend the Ola  Hallengren's Database Maintenance Solutions which includes Database Backup/Index Defrag/Integrity Check functionalities. I utilize in my production servers, and it works very well.

    Regarding to backup strategy, I would like to recommend you define your recovery strategy rather than backup strategy. That's because, some backup strategies look great, but when we need to recover our databases/businesses, they just sucks. You may need to know your business Service-Level-Agreements (SLAs), such as Recovery-Point-Objective (RPO), and Recovery-Time-Objective (RTO), and design your recovery/backup strategy accordingly.


    Alex Feng | SQL Server DBA, ALIBABA.COM
    My Blog | MCTS: SQL Server 2008, Implementation and Maintenance
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Peja Tao Wednesday, December 21, 2011 9:11 AM
    • Marked as answer by Peja Tao Wednesday, December 28, 2011 7:40 AM
    Tuesday, December 20, 2011 1:18 PM
  • In general, I prefer backing up to a network share that has no resources in common with the cluster.  The disks can be older and somewhat slower.  If necessary, that system can be the source for further tape-based backups. 
    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    • Proposed as answer by Peja Tao Wednesday, December 21, 2011 9:11 AM
    • Marked as answer by Peja Tao Wednesday, December 28, 2011 7:40 AM
    Tuesday, December 20, 2011 3:32 PM

All replies

  • Hi Tonny,

    If you need backup solutions, I would recommend the Ola  Hallengren's Database Maintenance Solutions which includes Database Backup/Index Defrag/Integrity Check functionalities. I utilize in my production servers, and it works very well.

    Regarding to backup strategy, I would like to recommend you define your recovery strategy rather than backup strategy. That's because, some backup strategies look great, but when we need to recover our databases/businesses, they just sucks. You may need to know your business Service-Level-Agreements (SLAs), such as Recovery-Point-Objective (RPO), and Recovery-Time-Objective (RTO), and design your recovery/backup strategy accordingly.


    Alex Feng | SQL Server DBA, ALIBABA.COM
    My Blog | MCTS: SQL Server 2008, Implementation and Maintenance
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Peja Tao Wednesday, December 21, 2011 9:11 AM
    • Marked as answer by Peja Tao Wednesday, December 28, 2011 7:40 AM
    Tuesday, December 20, 2011 1:18 PM
  • In general, I prefer backing up to a network share that has no resources in common with the cluster.  The disks can be older and somewhat slower.  If necessary, that system can be the source for further tape-based backups. 
    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    • Proposed as answer by Peja Tao Wednesday, December 21, 2011 9:11 AM
    • Marked as answer by Peja Tao Wednesday, December 28, 2011 7:40 AM
    Tuesday, December 20, 2011 3:32 PM
  • I have never had a problem backing up to a dedicated backup shared disk in the cluster and using backup agent to lift the files to tape -obviously it does however mean that you may need a policy set for each node in the cluster (depending on the software/ agent). Another negative to this is that with a dedicated cluster drive for backups, you have one extra dependency to a resource (that disk) which means that it is one extra thing to fail.

    Backing up to a remote path is actually a good strategy and means that you can change things behind the scenes without having to make changes to the Cluster or SQL configuration after rolling it out. I disagree to an extent with Geoff about using older slower disks since I personally believe that the backup drives are the most important disks of a database server. Older will potentially mean more prone to failure. You want backups to be fast AND have integrity. I know the point he is making though so I am pointing this side of the argument out so that you can use your own judgement on this.

    The product that I have seen used for many years with relatively no problems for native backup -> tape is Netbackup, but at the end of the day each backup solution has their own positives and negatives so it is really down to personal preference on that score.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    Thursday, December 22, 2011 12:43 PM
  • I am with Mark on this. You can use NetBackup in a few ways.

    1. Although you are installing NetBackup agent on all the nodes, you would be specifying the virtual name associated with the database instance in backup policies. This way, your backups are always cataloged from the correct node where the instance is active. Recovery is a bliss.

    2. If you want to avoid using LAN altogather for backups, you could setup each node as a SAN Client and send the backups to a Fibre Transport media server directly through high speed SAN. Now-a-days, it is so easy to deploy a Fibre Transport media server using NetBackup 5220 appliances. You just zone these appliances in the SAN and you are ready to go.

    3. There are customers installing NetBackup media server on each node (known as SAN Media servers) and backup the instances as a application cluster.

    Disclaimer: I work for Symantec. Comments are my own. I can be reached on Twitter @AbdulRasheed127

    Warm regards,

    Rasheed

     

    Tuesday, January 03, 2012 5:08 PM
  • Hi all

    Thanks alot for sharing.

    To Alex . I've allready implemented Ola's solultion. The first thing I worked on :-)

    I will scrutinize the SLA's OLA's and other agreements to see what to be expected.

    Also try the Netbackup solution

    Have a nice one :-)


    /torpo

    Wednesday, March 21, 2012 1:04 PM