February 12, 2018 at 8:32 am
Goal: To create a "template" of a VM (in Hyper-V) that could be "cloned" for multiple production servers.
Problem: Renaming "Template" to "Production" -- SQL server retains "Template" in sys.servers, server_id =0 --> NOT "Production" -- and inability to rename the host server after SQL server has been installed.
I have seen a number of solutions, but they all require a re-install of SQL Server to get @@SERVERNAME to return the correct response: sp_addlinkedserver (since sp_addserver has been discontinued); or using powershell script.
There has to be a way to build a VM w/ necessary SQL Server settings, and then rename the VM and SQL Server @@SERVERNAME without a re-install. (I hope!)
February 14, 2018 at 2:21 pm
joseph.uher - Monday, February 12, 2018 8:32 AMGoal: To create a "template" of a VM (in Hyper-V) that could be "cloned" for multiple production servers.Problem: Renaming "Template" to "Production" -- SQL server retains "Template" in sys.servers, server_id =0 --> NOT "Production" -- and inability to rename the host server after SQL server has been installed.
I have seen a number of solutions, but they all require a re-install of SQL Server to get @@SERVERNAME to return the correct response: sp_addlinkedserver (since sp_addserver has been discontinued); or using powershell script.
There has to be a way to build a VM w/ necessary SQL Server settings, and then rename the VM and SQL Server @@SERVERNAME without a re-install. (I hope!)
You can still rename SQL Server. sp_addserver was only discontinued for remote servers. Otherwise, it's still available - refer to the remarks section:
sp_addserver (Transact-SQL)
Sue
February 15, 2018 at 7:36 am
Sue:
Thanks for reply. Apparently I am missing something.
When I execute sp_addserver @server ='TEST' -- I get the following error message:
Msg 15663, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 60 [Batch Start Line 3]
Feature "sp_addserver" is no longer supported. Replace remote servers by using linked servers.
1. I am not able to update sys.servers or sys.sysservers for server_id =0 -- that is the (local) server; the reason I want to do that is that the name of the physical server / VM has been changed, but the old server name is retained in these two system tables in the [name] and [srvname] fields, respectively.
2. If I use sp_dropserver <old server name> -- note this is still functional in SQL Server 2016/Windows OS 2016, and I follow up with sp_addserver, I get the error message above and, of course, the two system tables no longer show any listing for server_id =0.
3. I am not trying to replace a 'remote' server; I'm trying to rename a 'local' server within the SQL Server system tables environment.
4. Is there a setting in the registry, where the old server name is persisted, or is that in SQL Server's resouce database, itself (which of course can't be touched) ?
I R Confused in 2016 !
February 15, 2018 at 8:03 am
joseph.uher - Thursday, February 15, 2018 7:36 AMSue:Thanks for reply. Apparently I am missing something.
When I execute sp_addserver @server ='TEST' -- I get the following error message:Msg 15663, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 60 [Batch Start Line 3]
Feature "sp_addserver" is no longer supported. Replace remote servers by using linked servers.1. I am not able to update sys.servers or sys.sysservers for server_id =0 -- that is the (local) server; the reason I want to do that is that the name of the physical server / VM has been changed, but the old server name is retained in these two system tables in the [name] and [srvname] fields, respectively.
2. If I use sp_dropserver <old server name> -- note this is still functional in SQL Server 2016/Windows OS 2016, and I follow up with sp_addserver, I get the error message above and, of course, the two system tables no longer show any listing for server_id =0.
3. I am not trying to replace a 'remote' server; I'm trying to rename a 'local' server within the SQL Server system tables environment.
4. Is there a setting in the registry, where the old server name is persisted, or is that in SQL Server's resouce database, itself (which of course can't be touched) ?
I R Confused in 2016 !
The error could be from specifying the server name alone when using sp_addserver. Try using --
sp_addserver 'NewServername', 'local'
If it's a named instance,
sp_addserver 'NewServername\InstanceName', 'local'
After passing the server name to the stored proc, the stored procedure expects the next argument of 'local' or nothing (null). I believe leaving off the local means it is a remote server.
Sue
February 15, 2018 at 8:13 am
Sue:
Thanks. We have already destroyed the original VM and created a new one with the correct server name, and re-installed SQL Server -- so I don't have a spare VM to test this on.
However, I did try "exec sp_adserver 'ServerName', 'local'" and ... did not get the error message (1st time!). Instead, the message was "The server 'ServerName' already exists." So your suggestion may be the answer I've been looking for.
This problem / situation comes up often where I work -- so I will try this in future situations.
Thanks, very much.
February 15, 2018 at 8:23 am
joseph.uher - Thursday, February 15, 2018 8:13 AMSue:Thanks. We have already destroyed the original VM and created a new one with the correct server name, and re-installed SQL Server -- so I don't have a spare VM to test this on.
However, I did try "exec sp_adserver 'ServerName', 'local'" and ... did not get the error message (1st time!). Instead, the message was "The server 'ServerName' already exists." So your suggestion may be the answer I've been looking for.
This problem / situation comes up often where I work -- so I will try this in future situations.
Thanks, very much.
Thanks for posting back - I think that likely was the issue. Something in the process would have led to that second error - didn't drop or executed the add twice. Just make sure to do the drop first and then the add. And restart after doing the name change.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply