共用方式為

使用 WMI 提供者管理服務和網路設定

  • 發行項

適用於：SQL Server 2022 (16.x)

WMI 提供者是已發行的介面，由 Microsoft Management Console （MMC） 用來管理 SQL Server 服務和網路協定。 在 SMO 中 ManagedComputer ，物件代表 WMI 提供者。

物件 ManagedComputer 與與 Server SQL Server 實例建立的連接無關，並使用 Windows 認證連接到 WMI 服務。

範例

若要使用提供的任何程式代碼範例，請選擇用來建立應用程式的程式設計環境、範本和語言。 如需詳細資訊，請參閱 如何在Visual Studio .NET 中建立Visual C# SMO 專案。

對於使用 SQL Server WMI 提供者的程式，您必須包含 Imports 語句來限定 WMI 命名空間。 將 語句插入其他 Imports 語句之後，在應用程式中的任何宣告之前，例如：

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo.Wmi

在 Visual Basic 中停止並重新啟動 SQL Server 服務

此程式代碼範例示範如何使用 SMO ManagedComputer 物件停止和啟動服務。 這會提供設定管理的 WMI 提供者介面。

'Declare and create an instance of the ManagedComputer object that represents the WMI provider services.
Dim mc As ManagedComputer
mc = New ManagedComputer()
'Iterate through each service registered with the WMI provider.
Dim svc As Service
For Each svc In mc.Services
    Console.WriteLine(svc.Name)
Next
'Reference the SQL Server service.
svc = mc.Services("MSSQLSERVER")
'Stop the service if it is running and report on the status continuously until it has stopped.
If svc.ServiceState = ServiceState.Running Then
    svc.Stop()

    Console.WriteLine(String.Format("{0} service state is {1}", svc.Name, svc.ServiceState))
    Do Until String.Format("{0}", svc.ServiceState) = "Stopped"
        Console.WriteLine(String.Format("{0}", svc.ServiceState))
        svc.Refresh()
    Loop
    Console.WriteLine(String.Format("{0} service state is {1}", svc.Name, svc.ServiceState))
    'Start the service and report on the status continuously until it has started.
    svc.Start()
    Do Until String.Format("{0}", svc.ServiceState) = "Running"
        Console.WriteLine(String.Format("{0}", svc.ServiceState))
        svc.Refresh()
    Loop
    Console.WriteLine(String.Format("{0} service state is {1}", svc.Name, svc.ServiceState))

Else
    Console.WriteLine("SQL Server service is not running.")
End If

在 Visual Basic 中使用 URN 字串啟用伺服器通訊協定

程式代碼範例示範如何使用 URN 對象來識別伺服器通訊協定，然後啟用通訊協定。

'This program must run with administrator privileges.
'Declare the ManagedComputer WMI interface.
Dim mc As New ManagedComputer()

'Create a URN object that represents the TCP server protocol.
Dim u As New Urn("ManagedComputer[@Name='V-ROBMA3']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']")

'Declare the serverProtocol variable and return the ServerProtocol object.
Dim sp As ServerProtocol
sp = mc.GetSmoObject(u)

'Enable the protocol.
sp.IsEnabled = True

'propagate back to the service
sp.Alter()

在 PowerShell 中使用 URN 字串啟用伺服器通訊協定

程式代碼範例示範如何使用 URN 對象來識別伺服器通訊協定，然後啟用通訊協定。

#This example shows how to identify a server protocol using a URN object, and then enable the protocol
#This program must run with administrator privileges.

#Load the assembly containing the classes used in this example
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

#Get a managed computer instance
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer

#Create a URN object that represents the TCP server protocol
#Change 'MyPC' to the name of the your computer
$urn = New-Object -TypeName Microsoft.SqlServer.Management.Sdk.Sfc.Urn -argumentlist "ManagedComputer[@Name='MyPC']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"

#Get the protocol object
$sp = $mc.GetSmoObject($urn)

#enable the protocol on the object
$sp.IsEnabled = $true

#propagate back to actual service
$sp.Alter()

在 C 中啟動和停止服務#

程式代碼範例示範如何停止和啟動 SQL Server 的實例。

//Declare and create an instance of the ManagedComputer
//object that represents the WMI provider services.
ManagedComputer mc;
mc = new ManagedComputer();

//Iterate through each service registered with the WMI provider.
foreach (Service svc in mc.Services)
{
   Console.WriteLine(svc.Name);
}

//Reference the SQL Server service.
Service mySvc = mc.Services["MSSQLSERVER"];

//Stop the service if it is running and report on the status
// continuously until it has stopped.
if (mySvc.ServiceState == ServiceState.Running)
{
   mySvc.Stop();
   Console.WriteLine(string.Format("{0} service state is {1}", mySvc.Name, mySvc.ServiceState));
   while (!(string.Format("{0}", mySvc.ServiceState) == "Stopped"))
   {
         Console.WriteLine(string.Format("{0}", mySvc.ServiceState));
         mySvc.Refresh();
   }

   Console.WriteLine(string.Format("{0} service state is {1}", mySvc.Name, mySvc.ServiceState));
   //Start the service and report on the status continuously
   //until it has started.
   mySvc.Start();
   while (!(string.Format("{0}", mySvc.ServiceState) == "Running"))
   {
         Console.WriteLine(string.Format("{0}", mySvc.ServiceState));
         mySvc.Refresh();
   }

   Console.WriteLine(string.Format("{0} service state is {1}", mySvc.Name, mySvc.ServiceState));
   Console.ReadLine();
}
else
{
   Console.WriteLine("SQL Server service is not running.");
   Console.ReadLine();
}

在 PowerShell 中啟動和停止服務

程式代碼範例示範如何停止和啟動 SQL Server 的實例。

#Load the assembly containing the objects used in this example
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

#Get a managed computer instance
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer

#List out all SQL Server instances running on this mc
foreach ($Item in $mc.Services) { $Item.Name }

#Get the default SQL Server database engine service
$svc = $mc.Services["MSSQLSERVER"]

# for stopping and starting services PowerShell must run as administrator

#Stop this service
$svc.Stop()
$svc.Refresh()
while ($svc.ServiceState -ne "Stopped") {
    $svc.Refresh()
    $svc.ServiceState
}
"Service" + $svc.Name + " is now stopped"
"Starting " + $svc.Name
$svc.Start()
$svc.Refresh()
while ($svc.ServiceState -ne "Running") {
    $svc.Refresh()
    $svc.ServiceState
}
$svc.ServiceState
"Service" + $svc.Name + "is now started"

相關內容