Update sys.servers after renaming SQL Server Host

Question

I'm about to upgrade my SQL Server 2012 instance to SQL Server 2014.

I've cloned the host Windows VM and renamed it from foo-2012 to foo-2014. On restart, SQL Server instance noticed this as updated its own name, so I can now log into it as foo-2014. All good.

Unfortunately the (single) entry in sys.servers is still foo-2012 which means that running

SELECT * 
FROM [foo-2012].[barDB].[dbo].tFooBarTable

fails with:

Could not find server 'RW-DB-2014' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Fine.

I run EXEC sp_addlinkedserver 'foo-2014', 'SQL Server' and get an entry.

But now the new entry has isLinked=1 (whereas the existing entry has isLinked=0).

Documentation indicates that this setting is important (especially to my app which has very strong opinions on Distr.Trans. :( )

I can't edit/add to/modify sys.servers directly. Any attempt to do so gives:

Ad hoc updates to system catalogs are not allowed.

I remove the new entry (EXEC sp_dropserver 'foo-2014') which succeeds, and try using EXEC sp_addserver 'foo-2014', 'local' which reports

The server 'foo-2014' already exists

Immediately re-running the drop then reports

The server 'RW-DB-2014' does not exist. Use sp_helpserver to show available servers.

How do I fix this?

Answer 1

You need to first remove the old server foo-2012 so that you have no rows in sys.servers, and then do the add with 'local':

EXEC sp_dropserver 'foo-2012';
GO
EXEC sp_addserver 'foo-2014', 'local'; 
GO

Then you will need to restart the MSSQLSERVER service in order for that change to take effect, as per the documentation for sp_addserver :

The local definition takes effect only after the Database Engine is restarted.