共用方式為


在 SMO 中設定 SQL Server

在 SMO 中,Microsoft SQL Server 執行個體的 Information 物件、Settings 物件、UserOptions 物件和 Configuration 物件的設定和資訊。

SQL Server 具有多個描述已安裝之執行個體行為的屬性。這些屬性描述啟動選項、伺服器預設值、檔案和目錄、系統和處理資訊、產品和版本、連接資訊、記憶體選項、語言和定序選取項目,以及驗證模式。

SQL Server 組態

Information 物件屬性包含有關 SQL Server 執行個體 (例如處理器和平台) 的詳細資訊。

Settings 物件屬性包含有關 SQL Server 執行個體的詳細資訊。除了郵件設定檔和伺服器帳戶外,您也可以修改預設的資料庫檔案和目錄。在連接持續時間會保留這些屬性。

UserOptions 物件屬性包含目前與算術、ANSI 標準和交易相關之連接行為的詳細資訊。

此外也有一組由 Configuration 物件所代表的組態選項。其中包含一組屬性,代表可由 sp_configure 預存程序修改的選項。Priority BoostRecovery IntervalNetwork Packet Size 之類的選項可以控制 SQL Server 執行個體的效能。這其中許多選項都可以動態變更,但在某些情況下,當 SQL Server 的執行個體重新啟動時,會先設定值然後再加以變更。

每個組態選項都有 Configuration 物件屬性。您可以使用 ConfigProperty 物件來修改全域組態設定。許多屬性都擁有最大和最小值,這些値也會儲存為 ConfigProperty 屬性。這些屬性需要 Alter 方法才能認可對 SQL Server 執行個體的變更。

Configuration 物件中的所有組態選項都必須由系統管理員變更。

範例

在下列的程式碼範例中,您必須選取用於建立應用程式的程式設計環境、程式設計範本和程式設計語言。如需詳細資訊,請參閱<如何:在 Visual Studio .NET 中建立 Visual Basic SMO 專案>和<如何:在 Visual Studio .NET 中建立 Visual C# SMO 專案>。

在 Visual Basic 中修改 SQL Server 組態選項

此程式碼範例會顯示如何在 Visual Basic .NET 中更新組態選項,也會擷取並顯示有關指定組態選項最大及最小值的詳細資訊。最後,此程式會通知使用者變更是否是以動態方式進行,或者在 SQL Server 的執行個體重新啟動之前都會儲存變更。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Display all the configuration options.
Dim p As ConfigProperty
For Each p In srv.Configuration.Properties
    Console.WriteLine(p.DisplayName)
Next
Console.WriteLine("There are " & srv.Configuration.Properties.Count.ToString & " configuration options.")
'Display the maximum and minimum values for ShowAdvancedOptions.
Dim min As Integer
Dim max As Integer
min = srv.Configuration.ShowAdvancedOptions.Minimum
max = srv.Configuration.ShowAdvancedOptions.Maximum
Console.WriteLine("Minimum and Maximum values are " & min & " and " & max & ".")
'Modify the value of ShowAdvancedOptions and run the Alter method.
srv.Configuration.ShowAdvancedOptions.ConfigValue = 0
srv.Configuration.Alter()
'Display when the change takes place according to the IsDynamic property.
If srv.Configuration.ShowAdvancedOptions.IsDynamic = True Then
    Console.WriteLine("Configuration option has been updated.")
Else
    Console.WriteLine("Configuration option will be updated when SQL Server is restarted.")
End If

在 Visual Basic 中修改 SQL Server 設定

此程式碼範例顯示 InformationSettings 中有關 SQL Server 執行個體的詳細資訊,並且會修改 SettingsUserOptions 物件屬性中的設定。

在此範例中,UserOptions 物件和 Settings 物件兩者都擁有 Alter 方法。您可以個別地為這些執行 Alter 方法。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Display information about the instance of SQL Server in Information and Settings.
Console.WriteLine("OS Version = " & srv.Information.OSVersion)
Console.WriteLine("State = " & srv.Settings.State.ToString)
'Display information specific to the current user in UserOptions.
Console.WriteLine("Quoted Identifier support = " & srv.UserOptions.QuotedIdentifier)
'Modify server settings in Settings.

srv.Settings.LoginMode = ServerLoginMode.Integrated
'Modify settings specific to the current connection in UserOptions.
srv.UserOptions.AbortOnArithmeticErrors = True
'Run the Alter method to make the changes on the instance of SQL Server.
srv.Alter()

在 Visual C# 中修改 SQL Server 設定

此程式碼範例顯示 InformationSettings 中有關 SQL Server 執行個體的詳細資訊,並且會修改 SettingsUserOptions 物件屬性中的設定。

在此範例中,UserOptions 物件和 Settings 物件兩者都擁有 Alter 方法。您可以個別地為這些執行 Alter 方法。

//Connect to the local, default instance of SQL Server.

{
            Server srv = new Server();
            //Display all the configuration options. 

            foreach (ConfigProperty p in srv.Configuration.Properties)
            {
                Console.WriteLine(p.DisplayName);
            }
            Console.WriteLine("There are " + srv.Configuration.Properties.Count.ToString() + " configuration options.");
            //Display the maximum and minimum values for ShowAdvancedOptions. 
            int min = 0;
            int max = 0;
            min = srv.Configuration.ShowAdvancedOptions.Minimum;
            max = srv.Configuration.ShowAdvancedOptions.Maximum;
            Console.WriteLine("Minimum and Maximum values are " + min + " and " + max + ".");
            //Modify the value of ShowAdvancedOptions and run the Alter method. 
            srv.Configuration.ShowAdvancedOptions.ConfigValue = 0;
            srv.Configuration.Alter();
            //Display when the change takes place according to the IsDynamic property. 
            if (srv.Configuration.ShowAdvancedOptions.IsDynamic == true)
            {
                Console.WriteLine("Configuration option has been updated.");
            }
            else
            {
                Console.WriteLine("Configuration option will be updated when SQL Server is restarted.");
            }
        }

在 PowerShell 中修改 SQL Server 設定

此程式碼範例顯示 InformationSettings 中有關 SQL Server 執行個體的詳細資訊,並且會修改 SettingsUserOptions 物件屬性中的設定。

在此範例中,UserOptions 物件和 Settings 物件兩者都擁有 Alter 方法。您可以個別地為這些執行 Alter 方法。

# Set the path context to the local, default instance of SQL Server.
CD \sql\localhost\
$srv = get-item default

#Display information about the instance of SQL Server in Information and Settings.
"OS Version = " + $srv.Information.OSVersion
"State = "+ $srv.Settings.State.ToString()

#Display information specific to the current user in UserOptions.
"Quoted Identifier support = " + $srv.UserOptions.QuotedIdentifier

#Modify server settings in Settings.
$srv.Settings.LoginMode = [Microsoft.SqlServer.Management.SMO.ServerLoginMode]::Integrated

#Modify settings specific to the current connection in UserOptions.
$srv.UserOptions.AbortOnArithmeticErrors = $true

#Run the Alter method to make the changes on the instance of SQL Server.
$srv.Alter()

在 PowerShell 中修改 SQL Server 組態選項

此程式碼範例會顯示如何在 Visual Basic .NET 中更新組態選項,也會擷取並顯示有關指定組態選項最大及最小值的詳細資訊。最後,此程式會通知使用者變更是否是以動態方式進行,或者在 SQL Server 的執行個體重新啟動之前都會儲存變更。

#Get a server object which corresponds to the default instance replace LocalMachine with the physical server
cd \sql\LocalMachine
$svr = get-item default

#enumerate its properties
foreach ($Item in $Svr.Configuration.Properties) 
{
 $Item.DisplayName
}

"There are " + $svr.Configuration.Properties.Count.ToString() + " configuration options."

#Display the maximum and minimum values for ShowAdvancedOptions.
$min = $svr.Configuration.ShowAdvancedOptions.Minimum
$max = $svr.Configuration.ShowAdvancedOptions.Maximum
"Minimum and Maximum values are " + $min.ToString() + " and " + $max.ToString() + "."

#Modify the value of ShowAdvancedOptions and run the Alter method.
$svr.Configuration.ShowAdvancedOptions.ConfigValue = 0
$svr.Configuration.Alter()

#Display when the change takes place according to the IsDynamic property.
If ($svr.Configuration.ShowAdvancedOptions.IsDynamic -eq $true)
 {  
   "Configuration option has been updated."
 }
Else
{
    "Configuration option will be updated when SQL Server is restarted."
}