“Renaming” SQL Server created from a template VM

When a VM is created from a template machine that has SQL Server installed, that SQL instance must be told that the machine name has changed.

If a “foo” machine is created from a template named “template”, the T-SQL statement run against the default SQL instance select @@SERVERNAME returns “template” instead of the desired “foo”.

The solution is to execute the following T-SQL and then restart the SQL Server process.

select [@@SERVERNAME] = @@SERVERNAME
go
sp_helpserver --show config before
go
exec sp_dropserver 'template'
go
sp_addserver 'foo', local
go
sp_helpserver --show config after
go

------------------------------------
--RESTART SQL INSTANCE AT THIS POINT
------------------------------------

select [@@SERVERNAME] = @@SERVERNAME
go

select [@@SERVERNAME] = @@SERVERNAME

sp_helpserver --show config before

exec sp_dropserver 'template'

sp_addserver 'foo', local

sp_helpserver --show config after

------------------------------------

--RESTART SQL INSTANCE AT THIS POINT

------------------------------------

select [@@SERVERNAME] = @@SERVERNAME

Here’s a C# version of the rename and the SQL Server process restart using PowerShell and the SqlDataClient.

/*
imports
using System.Management;
using System.Management.Automation;
using System.Management.Automation.Runspaces;
using System.Data.SqlClient;
*/

//rename the default SQL server instance 
private void RenameSqlServer(string machineName, string uid, string pwd)
{
    const string sql = @"if('{0}' <> @@servername)
    begin
      declare @sql nvarchar(max);
      begin try
        set @sql = 'sp_dropserver ''' + @@servername + ''''
        exec(@sql);
      end try
      begin catch
        declare @dummy bit
      end catch

      begin try
        set @sql = 'sp_addserver ''{0}'', local'
        exec(@sql);
      end try
      begin catch
        declare @dummy2 bit
      end catch
    end";

    SqlConnection connection = null;
    try
    {
        var cb = new SqlConnectionStringBuilder()
        {
             DataSource = machineName,
             InitialCatalog = "master",
             UserID = uid,
             Password = pwd
        };

        connection = new SqlConnection(cb.ToString());
        connection.Open();
        var command = new SqlCommand(string.Format(sql,machineName), connection);
        try
        {
            command.ExecuteNonQuery();
        }
        catch { }
        connection.Close();
    }
    finally
    {
        if (connection != null) connection.Close();
    }
}

private void RestartSqlServer(string machineName)
{
    const string script = @"$svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""
    $svc.Stop()
    $svc.WaitForStatus('Stopped')
    #Write-Host Stopped

    $svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""
    $svc.Start()
    $svc.WaitForStatus('Running')
    #Write-Host Running";

    var sessionState = InitialSessionState.CreateDefault();
    using (var ps = PowerShell.Create(sessionState))
    {
       ps.Commands.Clear();
       ps.AddScript(string.Format(script, machineName));
       ps.Invoke();
    }
}

imports

using System.Management;

using System.Management.Automation;

using System.Management.Automation.Runspaces;

using System.Data.SqlClient;

//rename the default SQL server instance

private void RenameSqlServer(string machineName, string uid, string pwd)

{

const string sql = @"if('{0}' <> @@servername)

begin

declare @sql nvarchar(max);

begin try

set @sql = 'sp_dropserver ''' + @@servername + ''''

exec(@sql);

end try

begin catch

declare @dummy bit

end catch

begin try

set @sql = 'sp_addserver ''{0}'', local'

exec(@sql);

end try

begin catch

declare @dummy2 bit

end catch

end";

SqlConnection connection = null;

try

{

var cb = new SqlConnectionStringBuilder()

{

DataSource = machineName,

InitialCatalog = "master",

UserID = uid,

Password = pwd

};

connection = new SqlConnection(cb.ToString());

connection.Open();

var command = new SqlCommand(string.Format(sql,machineName), connection);

try

{

command.ExecuteNonQuery();

}

catch { }

connection.Close();

}

finally

{

if (connection != null) connection.Close();

}

private void RestartSqlServer(string machineName)

{

const string script = @"$svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""

$svc.Stop()

$svc.WaitForStatus('Stopped')

#Write-Host Stopped

$svc = get-service -ComputerName ""{0}"" -Name ""SQL Server (MSSQLSERVER)""

$svc.Start()

$svc.WaitForStatus('Running')

#Write-Host Running";

var sessionState = InitialSessionState.CreateDefault();

using (var ps = PowerShell.Create(sessionState))

{

ps.Commands.Clear();

ps.AddScript(string.Format(script, machineName));

ps.Invoke();

}