SQL Server Performance

SQL 2008 DR failover and SQL code failover

Discussion in 'SQL Server 2008 Clustering' started by marrow, May 19, 2011.

  1. marrow New Member

    Dear all DB experts,

    We are now doing DR drill on SQL server 2008 cluster, but after we failover we found out that some query need to use the linked server create problem as the remote server name is HARDED CODED. That remote server in the primary site supposed to be disappeared.

    The SQL CODE running at the problem is:
    --------------------------------------------------------------------------------------
    CREATE view [dbo].[cap_baoa_status] as
    SELECT
    aob.betting_account_number
    ,baoa.bank_code
    ,baoa.bank_account_no
    ,CASE baoa.correspondence_language
    WHEN 'en-us' THEN 'English'
    WHEN 'zh-hk' THEN 'Traditional Chinese'
    END AS correspondence_language
    ,baoa.baoa_creation_business_date
    ,baoa.baoa_createion_datetime
    FROM [STCUSTDBCUSTDB].[cust_db].[dbo].[arrangement_on_betting] aob
    INNER JOIN [STCUSTDBCUSTDB].[cust_db].[dbo].[arrangement_on_betting_baoa] baoa
    ON baoa.arrangement_on_betting_id = aob.arrangement_identifier
    WHERE DATEDIFF(month,baoa.baoa_createion_datetime ,GETDATE()) < 3
    -------------------------------------------------------------------------------------------------------------------------

    As far as we know, we try to use the @@servername to find out the current running server (DR SQL server) then try to use logic like IF and ELSE to give the a new name to a server in order to replace to character "[STCUSTDBCUSTDB]', which is the linked server, to point to a local database with the same data.

    But this is a view and we can't include if and else into it, then how can we fix this kind of problem in order to transpanent to the application level.

    I can only think of changing DNS to make sure that if the code is asking for the server name, it just gives the changed IP address, then it will be done.

    Other question, when we are doing SQL server 2005 cluster, we can have the primary SQL server 2005 cluser server name resourece offline and then DR SQL server 2005 cluster name resource with the same name online, this can fix the shit as the server name will be the same and no coding change, right? someone tell me that SQL server 2008 can't do this as MS has confirm that this is not going to work in 2008 as this will give other BIG problem, rigjht? any link to verify this ?

    Please share how you guys handle this situation, if the same cluister DR method works find in SQL 2008, then this problem solved.

    DBA100.

  2. marrow New Member

    How about the option 'provstr' when adding linkedserver? it seems it is only work for mirror session, right? can we utilize this option to get ride of our coding problem ?

    What if our data is replicate using SAN's data replication to the DR cluster? this is not a mirrored session. Will this works ?

    DBA100.
  3. marrow New Member

    once the @provstr string specified, should @catalog option needs to be define on this?
    DBA100
  4. marrow New Member

    once the @provstr string specified, should @catalog option needs to be define on this?

Share This Page