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?

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));
}