In any modern day production environment, it is difficult to find an isolated instance of Microsoft SQL Server. Most enterprise class products are interfaced with different, remote systems.
In another scenario, many IT departments use pre-created images to quickly setup development and quality assurance environments. When they change the name of the server after restoring the image, the SQL Server instance name remains unchanged. In these cases, we need to rename the instance of SQL Server.
sp_addserver – Register a SQL Server instance
To register an instance of Microsoft SQL Server as a remote server to any given instance of SQL server, the system stored procedure: sp_addserver needs to be used. This system stored procedure can be used to define the name of the local or remote instance of SQL Server. Typical usage of this system stored procedure is something like the following:
--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO
--Syntax: sp_addserver 'instance name', local, duplicate_ok
--Add a remote server
sp_addserver 'WIN2K8\SQL2K8'
GO
--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO
SQL Server 2012 – sp_addserver for remote servers is discontinued
Microsoft SQL Server, had a provision to add remote servers until SQL Server 2008 R2. From Microsoft SQL Server 2012, this functionality is now deprecated, and we now need to add "linked” servers instead. If a user does attempt to use the sp_addserver syntax to add a remote server, system will result in the following error:
Msg 15663, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 60
Feature "sp_addserver" is no longer supported. Replace remote servers by using linked servers.
The solution is to use the sp_addlinkedserver system stored procedure, using which the script would require changes as under:
--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO
--Syntax: sp_addlinkedserver (http://msdn.microsoft.com/en-us/library/ms190479.aspx)
--Add a remote server
sp_addlinkedserver 'WIN2K8\SQL2K8','SQL Server'
GO
--Check the instances registered with this instance of Microsoft SQL Server
--An id=0 indicates that this is a local instance
sp_helpserver
GO
Please note that after one uses sp_addserver to rename a local instance of Microsoft SQL Server, a service restart is required for the changes to take effect.
References:
Do leave your feedback before you go. Do you use or have you ever encountered a situation where you used one of these system stored procedures? If you are an IT guy, do you ensure that your deployment strategies rename the instances of SQL Server underneath whenever you rename the host computer? Please let us know.
Until we meet next time,
Be courteous. Drive responsibly.