SQL 2008 clustering with Windows 2008

Discussion in 'microsoft.public.sqlserver.clustering' started by OceanDeep, Sep 30, 2008.

  1. OceanDeep

    OceanDeep Guest

    I am new to SQL clustering. Does SQL 2008 clustering as far as functionality
    remain the same as SQL 2005? I am looking for any 'best practises for
    setting up SQL clustering.

    For setting up windows 2008 clustering , I only found the step-by-step guide
    for configuring a two-node Fileserver Failover cluster. Does it mean the
    same steps applied for the SQL database setup?

    OceanDeep, Sep 30, 2008
    1. Advertisements

  2. OceanDeep

    Linchi Shea Guest

    for configuring a two-node Fileserver Failover cluster. Does it mean the
    Setting up SQL2008 in Windows 2008 is documented in SQL2008 Books Online

    Linchi Shea, Oct 1, 2008
    1. Advertisements

  3. Conceptually, clustering is the same. Microsoft did change the
    implementation a lot. Now, the wizard will determine whether your system
    can support clustering. Before your hardware had to be on the HCL (or the
    Windows Catalog for Clustering as it was renamed) and purchased/configured
    as a cluster.

    There are enough differences that I would do some serious reading before
    building a cluster, unless you have a lot of time for "do-overs".
    Geoff N. Hiten, Oct 1, 2008
  4. Geoff,

    Thank for the reply.

    As far as the quorum configuration, it seems SQL 2008 still has the same
    recommendation as SQL 2005's. If you know differently, please let me know.
    In our subsystem, we have three set of physical disks. One is for the
    database (raid 10), one is for the Tempdb (raid 1), and one is for the tran
    log (raid 1). Unfortunately, we don't have a separate set of physical disks
    for the quorum. Our database size is about 45 GB. Under performance
    counter SQLServer:Databases - transaction per sec for our production database
    is 24/sec. The same counter for tempdb is about 235/sec. The read and
    write queue length counter for the log file drive is very low, < 0.01.

    Based on our disk setup, which set of physical disks is more preferable to be
    used for creating a logical disk for the quorum? Based on the IO activity
    from the performance monitor, the tran log disk has the least IO activity and
    it could be the set I should use. Please advice.

    OceanDeep via SQLMonster.com, Oct 1, 2008
  5. The problem is not that the quorum requires a lot of I/O, it is that it must
    respond to I/O requests quickly. I would use the log drives for the quorum
    slice, mainly because most of the log activity is write while the quorum
    checks are mostly reads.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 1, 2008
  6. Thank for the input. It is good to know that I am on the right track.
    From the SQL online, it recommends the quorum disk should be at least 500 MB
    (based on SQL 2005). Being on a conservative side, I allocate 5 Gbtyes for
    this new version of SQL 2008 and any future changes. Do you think it is
    enough? I have some basic understanding of quorum drive and what its role is
    but not sure what is being stored on it. Please advice.

    OceanDeep via SQLMonster.com, Oct 1, 2008
  7. I usually set up a 2GB quorum drive, mainly because I put MSDTC on the
    quorum. That is no longer necessary since Windows 2008 supports multiple
    MSDTC instances per cluster. 5GB will be more than adequate.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 1, 2008
  8. Geoff,

    I am glad that you brought up the MSDTC thing. During the SQL 2008
    installation, the setup wizard did warm me about non-existance of MSDTC and
    may cause some application not working if it is not clustered. Correct me if
    I am wrong, since our cluster and the production db's are all located in a
    single location, in fact in a single SAN, so there is no distributed
    transactions and there foreI don't think we need MSDTC. But based on BOL, I
    need it if I am installing the database engine and SSIS and workstation
    Component. Why do SSIS and workstation component require MSDTC? I will
    have SSIS packages as a scheduled job in the job agent but other than that I
    won't do too much with SSIS on the clustered nodes.

    If I do need MSDTC, there are some KB articles to guide me through it but
    they are based on SQL 2005. Basically, I need to enable DTC and then create
    resource group, etc. Are these steps still valid for SQL 2008? Based on
    your comment, it seems to be much less work in SQL 2008. Please advice.

    OceanDeep via SQLMonster.com, Oct 1, 2008
  9. SSIS and some workstation tools require DTC because it is how SQL and
    non-SQL transactions work together.

    ODBC clients require MSDTC to enact transactions.

    As far as MSDTC and Windows 2008, here is another blog post on the subject


    As you may have guessed, I do some blog posts so I don't have to rewrite
    complex answers repeatedly.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 1, 2008
  10. Thank for the information and appreicate greatly for your help and time. Our
    clustering setup is two nodes active/passive. THe link seems to emphasize on
    the Active/Active type of setup for MSDTC. Do you have any link that
    descriibes MSDTC on A/P clustering? OR does A/P need MSDTC?

    OceanDeep via SQLMonster.com, Oct 1, 2008
  11. You will still need MSDTC. Just create an MSTDC resource in the SQL
    instance and anchor it to your normal SQL data disk.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 2, 2008
  12. Geoff,
    I thought I understand the logical drive concept but apparently not. I need
    some pointers. I am having problem isolating the logical drive for Quorum or
    MSDTC usage. As I described in my original email, I have three sets of Raid.
    I alloicated a slice (logical partition) of the raid for the transaction log
    to the quorum or MSDTC usage. So say disk 1 (2 disks) is the logical Raid 1
    for the transaction log. In disk 1, I allocate 130 G for log (drive E) and 6
    gbyte for quorum/MSDTC (drive F). I did that using IBM system managment tool
    designed for SAN configuration). However, when I run the MSDTC configuration
    from the cluster manager to add MSDTC to the cluster, the cluster manager
    only sees three raid, it doesn't see that 6 Gbytes I created. I believe the
    problem is the IBM tool doesn't care the logical partition within a raid. It
    only cares the three 'phsyical' raid array. The other problem is that say I
    go ahead to allocate the transaction log raid to MSDTC. Then this raid disk
    won't be usable or seen under disk management in Windows. Is what I am
    seeing normal?

    OceanDeep via SQLMonster.com, Oct 2, 2008
  13. Not logical drive -> physical drive.
    Failover Clustering uses actual physical drives (as you see them in Disk
    Management with a "DiskX" number)

    You should NOT partition the drive, you should have 1 partition on a disk, 1
    volume on a disk.

    Use 1 Disk with 1 partion per "Physical Disk Resource" in a cluster
    as above, do not partition the disk into more partitions, 1 partition per
    you should present more disks to your Windows server
    (carving up, not on the Windows host, but carving up in your storage system)

    Edwin vMierlo [MVP], Oct 3, 2008
  14. The problem, as Edwin pointed out, is that Clustering sees physical disks.
    True SANs can slice logical chuinks off of physical RAID sets and present
    those to a host OS as a physical disk (LUN). Looks like yours just uses
    Windows disk partitioning to fake it. You have three clustered storage
    resources. Use them carefully.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 3, 2008
  15. I didn't use windows disk management tool to do any partition. I use the IBM
    storage management tool which comes with the SAN to configure the RAID sets.
    When t slice part of the physical RAID set out from Disk 1 which has LUN 0,
    it give the new slice a new LUN 1. From the IBM tool GUI, Disk 1 contains
    two 'partitions' but each has its own LUN. I think I understand Edwin's
    explanation but I though if the disk (or logical disk) has its own LUN, the
    Windows Clustering would see them as well but apparently it is not. Maybe I
    can't rely on the LUN assigned to the 'disk', what it matters is the actually
    physical disks in each RAID configured by the SAN tool. I can't rely on the

    Since I only have three sets of Physical RAID, I really didn't want to lose
    one set for just the quorum or MSDTC. Is there way that quorum/MSDTC can co-
    exist with the transaction log on one raid set? Any other suggestion is

    OceanDeep via SQLMonster.com, Oct 3, 2008
  16. A Disk to be used in cluster is a Disk (with a Disk number) as you see them
    in Disk Management

    A Disk in Disk Management should only have 1 partition

    (and how you do this on your storage is of no concern to cluster, as long as
    you stick to the above two rules)

    Edwin vMierlo [MVP], Oct 3, 2008
  17. The IBM tool evidently writes Windows partitioning information to the disk,
    but still presents the entire array as a LUN to the hosts.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 3, 2008
  18. Edwin,

    Yes, I did what you described. The Disk management sees four disks(disk 1,
    disk 2, disk 3, and disk 4) besides the disk 0 for the C drive. Each disk
    has 1 partition and that's it.

    When I bring the disk on line from the disk management, I initialize it and
    then format it with simple NTFS volume using 'Quick format'. For the
    clustering to work properly, is there some time period I have to wait before
    the disks are ready because of 'Quick Format'? Or if I use the 'destroy
    cluster' to redo my clustering, is there something I should know like reboot
    before I destroy the cluster?

    Once I destroy the cluster, I first bring all the disk on line again and
    format them again with quick format and assign drive letter and volume name
    to each on the main node. Then I go to the fallover node and bring each disk
    on line also. The drive letter and name comes up automatically and is the
    same as the main node. Then I use the Failover cluster management to test
    the configuration (success) and create cluster. Do you see any problem with
    my steps or do I miss some steps in between?

    OceanDeep via SQLMonster.com, Oct 3, 2008
  19. Geoff,

    Let say during the clustering setup using the failover cluster management
    tool, it picks the correct quorum. Now let's say I move on to set up the
    MSDTC sevice, the wizard doesn't see the quorum disk any more. Is it because
    it is supposed to be that way so I can't put quorum and MSDTC together on the
    same disk? If so, if I need to assign a disk for MSDTC, I should also set up
    another disk for it just like I did for quorum. In other words, instead of
    four disks from the Raid disks I have, I should set up five disks (with five
    different LUN) from my current RAID disk sets. Please advice.

    OceanDeep via SQLMonster.com, Oct 3, 2008
  20. Since you are on Windows 2008, create the MSDTC resource, put it in the SQL
    resource group, then set teh dependency to the SQL data drive.

    Geoff N. Hiten
    Principal SQL Infrastructure Consultant
    Microsoft SQL Server MVP

    Geoff N. Hiten, Oct 3, 2008
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.