Restart SQL Server instance using SMO

Question

My C# application uses SMO to do various things with SQL Server instance chosen by a user. Particularly, it changes authentication mode:

ServerConnection conn = new ServerConnection(connection);
Server server = new Server(conn);

server.Settings.LoginMode = ServerLoginMode.Mixed;

After changing login more instance should be restarted. However, I cannot find any way in SMO to restart selected instance.

I've tried to google this, but only found a bunch of samples enumerating running services and comparing their names with SQL server service name. I did not like this way as it is error prone and relies on the way Microsoft currently names SQL server instances.

Is there any way to restart chosen instance in SMO?

Camphene

Answer 1

Add a reference to System.ServiceProcess.

using System.ServiceProcess;

public static void RestartService(string sqlInstanceName) {
    if (string.IsNullOrEmpty(sqlInstanceName)) {
        throw new ArgumentNullException("sqlInstanceName");
    }

    const string DefaultInstanceName = "MSSQLSERVER";
    const string ServicePrefix = "MSSQL$";
    const string InstanceNameSeparator = "\\";

    string serviceName = string.Empty;
    string server = sqlInstanceName;
    string instance = DefaultInstanceName;

    if (server.Contains(InstanceNameSeparator)) {
       int pos = server.IndexOf(InstanceNameSeparator);
       server = server.Substring(0, pos);
       instance = sqlInstanceName.Substring(pos + 1);
    }

    serviceName = ServicePrefix + instance;
    ServiceController sc = new ServiceController(serviceName, server);
    sc.Stop();
    sc.WaitForStatus(ServiceControllerStatus.Stopped, TimeSpan.FromSeconds(30));
    sc.Start();
    sc.WaitForStatus(ServiceControllerStatus.Running, TimeSpan.FromSeconds(30));
}

Furnishings

Answer 2

You have to do restart manually! at first you have to stop the service and wait for the service to change it's state and the you have to start the service and again wait for the service to change it's state.

MSDN website has a sample for this operation : http://msdn.microsoft.com/en-us/library/ms162139(v=SQL.90).aspx

Bemire