Remote servers in sysservers
-
What, if any, would be the problem with altering the datasource column is sysservers to point a linked SQL server to a different machine?
The problem is thus: We have two live servers called Live1 and Live2. Each has a linked server pointing to the other. In a TEST disaster recovery scenario, we need to restore databases onto machines of different names such as DR1 and DR2.
This means that the linked servers are now broken - but I have discovered that changing the datasource value allows the linked server to be called by the same name but access a differently named server.
i.e. Change:
srvname
srvproduct
providername
datasource
Live1
SQL Server
SQLOLEDB
Live1
to:
srvname
srvproduct
providername
datasource
Live1
SQL Server
SQLOLEDB
DR1
I know that this sounds dodgy and I would be grateful for alternatives. We currently use log shipping to back up and, for all previous DR tests, we have brought the backup machine on line in the DR site under the original live server name. We are now moving to Active Directory (at last) and I am assured that we cannot rename the servers because, in reality, the original server never gets taken off the network.
Your help or comments would be greatly appreciated
-
I can't help you with your problem, and yes it does sound dodgy.
However, I can assure you that in AD you can rename a server and bring it back on-line with the different name. I don't know all of the details behind your situation but our DR practices have included chaning the static IP and rebooting the box. Note that your SA may be hung up on the step of removing the old entry from AD.
Everett Wilson
ewilson10@yahoo.com -
mmm system table editing..... <insert disaster here>
sp_addlinkedserver allows you to do basically what you are already doing here, have a linked server name point to a different data source.
Using EM, I created a new linked server, chose the OLEDB for SQL Server provider and datasrc = 'Dev1' with the server name = 'DevX'.
So what you are doing should work fine, but as we know with SQL Server, sometimes objects can go all mucky and things go weird. So I'd probably recommend removing the server with sp_dropserver and then add the link with sp_addlinkedserver. That way you can be confident SQL Server will have the story straight.
Julian Kuiters
juliankuiters.id.au -
Guys, Thanks very much for your input.
I KNOW this isn't really a good idea but it would save the potential disaster of having a second linked server under the new name and just hoping, in a DR situation, that they had been kept in step.
The REAL problem is indeed our SA's. They want me to reprogramme all of the linked servers and DTS's to have two copies - one for live and one for DR. Tell me again which one has disaster written all over it!?
I am still trying to convince people that renaming the machines is by far the best option (we can't afford clustering!)
By the way, is it possible to programme DTS's with dynamic connections to other SQL servers which can get resolved at run time?
-
use sp_setnetname to modify the datasource property of sysservers. You don;t have to hack the system tables.
Our linked servers are all "virtual" - so our DR server is called "SPARESERVER" but it's real name is "SQLSPARE", set by using sp_setnetname. This blew up so we ran sp_setnetname again to use another server.
All 4 part object names still use "SPARESERVER"...
BTW, don't bother renaming AD objects as there is a KB article stating that the old name still lingers. SPNs break, trusted security over tcp can break etc. Our server chaps built "SLQ1" one day, all worked though, they renamed it to "SQL1" and it all went down hill, so I renamed it back. This is Win2K domain, maybe win2k3 is better.
When our client apps need to refer to "LIVE1" for exmaple after it has blown up and they are using the DR/standby server, all you need is a DNS entry to point "LIVE1" to "DR1" so they ping LIVE1 and hey presto they see DR1 afetr a reboot. Thus NO client changes needed, just a reboot or "ipconfig /flushdns". Damn sight easier than trying to update 1000 client PC registries with an alias
If DR1 needs to think it is "LIVE1" for drive mappings etc, then on DR1 add this reg value
“HKLM\System\CurrentControlSet\Service\lanmanserver\Parameters”
“OptionalNames”
REG_MULTI_SZ
“LIVE1”
and reboot.
This works. Guaranteed. If you still use named pipes, it won't, but we don't so it aint an issue for us.
Your smugly 😎
Shawn
-
Thanks gbn. Something else for me to take to the SA's. Unfortunately we DO use named pipes as these were (are) what Microsoft recommended at the time. MS seem to vacillate between named pipes and TCP/IP as the preferred option - currently stating that it depends on your network speed.
sp_setnetname looks useful though.
-
The named pipes issue happens because a server listens on
"\\LIVE1\pipe\sql\query"
I tried and failed to get (my equivalent) DR1 to do the same thing.
If your clients are XP or have MDAC 2.6 or above, they will default to TCP then NP to connect to SQL, otherwise this will have to be configured. I'm not sure about Win2000 or Office service packs and how they update MDAC.
You may get round it by making your SQL servers listen on TCP as well as NP and the clients should start using TCP if you're lucky...
-
All clients using W2K, not XP. Servers do indeed listen on TCP as well as NP. Some clients on the WAN use TCP.
Thanks again for all your input.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply