May 20, 2011 at 3:25 am
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 creats 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 [STCUSTDB\CUSTDB].[cust_db].[dbo].[arrangement_on_betting] aob
INNER JOIN [STCUSTDB\CUSTDB].[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 user the @@servername to find out the current running server name(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 "[STCUSTDB\CUSTDB]', 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 make change and transpanent to the application level (no coding change).
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 is: 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 method is not going to work in 2008 as this will give other BIG problem, right? 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.
May 20, 2011 at 3:30 am
How about the option 'provstr' when adding linkedserver? it seems it is only work for mirror session, right? can it solve our problem ?
Once the @provstr string specified, should @catalog option needs to be define on this?
What if our DR partner is separated on other site and the data is replicate using SAN's data replication? this is not a mirrored session and will this option solve our problem ?
May 20, 2011 at 8:47 am
well I sort of have this problem with linked server calls ( but not with views ) you can name your linked server to be anything .. in my situation I want to call my prod server from DR. IF my linked server is called PROD on DR and is set to ip 11.22.3.45 then my call works.
when roles are reversed and DR is PROD I still have a linked server on DR called PROD but this maps to 11.22.3.48 say.
Sure you can possibly do the same. So my call will work the same from either server.
Hope this makes sense, not sure if I explained it very well.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply