SQL Server Performance

backup disk partition not available to SQL server

Discussion in 'SQL Server Clustering' started by leeCarr, May 23, 2006.

  1. leeCarr New Member

    Hi - can you help?

    I have new installation of SQL 2K on Server 2003 on active/passive cluster with shared fibre EMC.
    I have a quoram drive using 517MB on a 1GB partition, leaving 126GB in space on this disk. However I had intended to use the free 126GB partition for backups. I can see this ok from explorer, but I cannot see it from SQL Server when setting up backup jobs, and it cannot be seen when creating a new disk resource in the cluster adminstrator. I know it is recommended to keep main data off the quoram disk, but could it be used for main backups? I would place transaction log backups onto a different disk (probably the main log file partition) and only use this for the nightly main backup files.


    Lee
  2. satya Moderator

    Have you added that remaining partition under Cluster group?
    You have to add that disk resource as dependent resource of sql server resource in cluster admin first.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. leeCarr New Member

    hi,

    I have tried to add the backup partition in the cluster administator but it does show as an option when creating a new disk resource.


    Lee
  4. leeCarr New Member

    quote:Originally posted by leeCarr

    hi,

    I have tried to add the backup partition in the cluster administator but it does show as an option when creating a new disk resource.


    Lee
    sorry this should have read.... is does NOT show as an option.....
  5. satya Moderator

    Have you used Cluster Administrator account to enable?
    Also which account is used for SQL Server services?

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. leeCarr New Member

    Originally posted by satya

    Have you used Cluster Administrator account to enable?
    Also which account is used for SQL Server services?

    Satya SKJ
    Hi,

    I am logging on to the server with main server admin account. Starting cluster admin app. Trying to create a new physical disk resource under the resources folder. I can see disk group1 that is already there active and ok - this contains a data, log and tempdb partition.
    When creating a new disk resource however no other 'disk groups' can be selected.

    If I check in computer management on the server I can see the Backup partition ok (also visible in explorer and it has data on it). It is located on a disk (130GB) - partitioned: Q: (Quorum) 518 MB NTFS and B: (Backup) 126.92 GB NTFS. Both have a status of healthy and both paritions have the same security properties and the same account ownership as each other, and the same as the other partitions - Data, Logs and Temp (these three partitions are set up as part of a Raid 10).

    The Sql Server services are run from the network domain sqlcluster account and not the server administrator account.

    At the moment this is still not a production server but soon will be - my main concerns are:

    1. Having to find alternative backup space .... so will have to backup initially to the same partions as data or logs, and copy off, or rely on Veritas agent etc purging backup data. This will cause issues with main data being tranacted on same partition as .trn & .bak files. I could rebuild the Raid10 will smaller sized partitions to allow for backup area I guess, but we were limited to physical disks and had sizes the data and log drives for future growth.

    2. Is is an issue to share the Quorom drive with ANY other data? As we have 130GB disk with only 20MB of shared Quorom data, this is a shame and a resource we could use.



    Thanks

    Lee
  7. Argyle New Member

    Not sure if I understand your setup correctly. First of all for disks to be visable in the cluster they have to be set up as "Basic" and not "Dynamic" disks. Verify that.

    Also you can not create partitions on a single disk entity and put those partitions in seperate cluster groups. If you on a single disk of the size 130GB create as you say Q: (Quorum) 518 MB NTFS and B: (Backup) 126.92 GB NTFS then both those partitions need to be in the cluster group that contains the cluster name and IP. In other words there is no use creating partitions.

    If you on the other hand had another 130GB disk available, you could make it a "Basic" disk, format it and name it X: and then add this as a cluster resource. This resource could be put in any of your cluster groups, but the best choice would of course be the sql cluster group where the sql name, ip and the other sql sever disks exist. Then this disk would be available to sql server once you add a dependancy to it from the sql server resource in the cluster.

    You can not build a backup strategy that backup data to the qurom disk or any other disk that is not in the sql cluster group. You can only backup data to disks available in the sql cluster group. There is no garuantee that seperate cluster groups will be active on the same node. When they are on separate nodes the disks won't be available.

    Example layout in cluster administrator:

    Cluster Group
    - Cluster Name
    - Cluster IP
    - Disk Q
    - MSDTC

    SQLSERVER01
    - SQL Server Name
    - SQL Server IP
    - SQL Server Service
    - SQL Server Agent
    - Disk K
    - Disk L
    - Disk X

Share This Page