Restart an instance of SQL Server using SMO

Question

My C # application uses SMO to perform various actions on a user instance of SQL Server. In particular, it changes the authentication mode:

ServerConnection conn = new ServerConnection(connection); Server server = new Server(conn); server.Settings.LoginMode = ServerLoginMode.Mixed; 

After changing the login, you must restart the instance more. However, I cannot find a way in SMO to restart the selected instance.

I tried Google to do this, but found only a set of samples listing the services that were running and comparing their names with the SQL Server service name. I did not like this because it is error prone and relies on how Microsoft currently calls SQL server instances.

Is there a way to restart the selected instance in SMO?

Answer 1

Add a link 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)); } 

Answer 2

You need to restart manually! first you need to stop the service and wait for the service to change its state, and you need to start the service and wait again until the service changes its state.

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