Cluster failover on SQL server

Posted on 2011-05-12
Last Modified: 2013-11-11
I have SQL 2005  (Bussness intelligence) and SQL 2008(biztalk) installed on the same server in cluster,
Cluster has 5 resource groups looks like this
Cluster Name
Resource Group
 Cluster Group (Cluster name ,Quorum and Quorum IP)
 MSDTC(MSDTC IP, MSDTC Network Name....)
 Group BI(SQL server 2005, SQL Agent....)
 Group BT(SQL 2008, SQL agent...)
 File_share(Folder shares)

here is question

when Cluster Group BI fails, Group BT  SQL server and agent terminates (viceversa) and i had to failover the BT to start the SQL server and agent.

Is there a possibility where if one services fail on Either on Group BI or BT, i need the other resources(all the remaining resources) to be failedover.
Question by:rajkiggal
    LVL 76

    Expert Comment

    I think each SQL application has to have its own IP resource

    cluster sql2005
    IP resource, MSDTC, networkname
    GROUP BI,SQL Agent

    Cluster SQL2008
    IP Resource, MSDTC, networkname
    GRoup BT, sql, sql agent.

    Cluster Filegroup
    IP resource, networkname
    File_share(folder share)

    I.e. if both SQL servers are using the same IP, when you fail the IP to the other node, while the sql resource remains active on the prior node, access only available by use of the Node IP  versus the Application cluster IP.
    LVL 1

    Author Comment

     Cluster sql 2005 and cluster sql 2008 has different cluster network name and cluster IP address
    Cluster file group is same as Cluster name ipaddress.
    is there way i can make dependence for each other so that entire cluster failover happens, if once resource is failed.

    LVL 76

    Expert Comment

    It depend son what resource you are talking about.  You can add dependencies and I think this might be what you are looking for.
    It is unclear to me what resource failure you want to trigger the failover and to what extend.
    Based on your comment that when you failover BI, BT is also seen as down, suggests to be that they are bound/using the same IP resource.
    LVL 1

    Author Comment

    Cluster sql 2005 and sql2008 has below resources( parameter are deferent for both)
    sql server, sql agent , sql analysis services, 8 disk drives(offers path for Database and logs), sql network name, network ip

    when sql analysis services on sql 2005 executed it will cause the sql server on the same group to fail over (virtual memory issues),
    after fail over sql won't comes up until all the resources completely  are moved to second node(for any Db application require MSDTC run from same node).

    all i want is, one sql server resource (Either on sql 2005 or sql 2008 cluster group)  fail over should trigger complete resource group failover! for this i would like to create dependence between cluster groups to facilitate the entire cluster failover!

    same happens even sql analysis services runs on sql 2008.

    hope this clears
    LVL 76

    Accepted Solution

    You have to use dependency within the group that will be seen as a failure and force the group to fail which will bring the group up on the other node

    The lack of virtual memory means that you are misallocating resources.
    IT seems that your configuration is such that both SQL application cluster groups can only run on the same node. I think that is because you have MSDTC as a standalone clustered group versus being a resource that transferrs with the sql cluster group.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now