Edit

Share via

Configuring SQL Server in SMO

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL database in Microsoft Fabric

In SMO, the Information object, the Settings object, the UserOptions object, and the Configuration object contain settings and information for the instance of Microsoft SQL Server.

SQL Server has numerous properties that describe the behavior of the installed instance. The properties describe the startup options, the server defaults, files and directories, system and processor information, product and versions, connection information, memory options, language and collation selections, and the authentication mode.

SQL Server Configuration

The Information object properties contain information about the instance of SQL Server, such as processor and platform.

The Settings object properties contain information about the instance of SQL Server. The default database file and directory can be modified in addition to the Mail Profile and the Server Account. These properties remain for the duration of the connection.

The UserOptions object properties contain information about the current connections behavior relating to arithmetic, ANSI standards, and transactions.

There is also a set of configuration options that is represented by the Configuration object. It contains a set of properties that represent the options that can be modified by the sp_configure stored procedure. Options such as Priority Boost, Recovery Interval and Network Packet Sizecontrol the performance of the instance of SQL Server. Many of these options can be changed dynamically, but in some cases the value is first configured and then changed when the instance of SQL Server is restarted.

There is a Configuration object property for every configuration option. Using the ConfigProperty object you can modify the global configuration setting. Many properties have maximum and minimum values that are also stored as ConfigProperty properties. These properties require the Alter method to commit the change to the instance of SQL Server.

All of the configuration options in the Configuration object must be changed by the system administrator.

Examples

For the following code examples, you will have to select the programming environment, programming template and the programming language to create your application. For more information, see Create a Visual C# SMO Project in Visual Studio .NET.

Modifying SQL Server Configuration Options in Visual Basic

The code example shows how to update a configuration option in Visual Basic .NET. It also retrieves and displays information about maximum and minimum values for the specified configuration option. Finally, the program informs the user if the change has been made dynamically, or if it is stored until the instance of SQL Server is restarted.

'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

Modifying SQL Server Settings in Visual Basic

The code example displays information about the instance of SQL Server in Information and Settings, and modifies settings in Settings and UserOptionsobject properties.

In the example the UserOptions object and the Settings object both have an Alter method. You can run the Alter methods for these individually.

'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()

Modifying SQL Server Settings in Visual C#

The code example displays information about the instance of SQL Server in Information and Settings, and modifies settings in Settings and UserOptionsobject properties.

In the example the UserOptions object and the Settings object both have an Alter method. You can run the Alter methods for these individually.

//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.");  
            }  
        }

Modifying SQL Server Settings in PowerShell

The code example displays information about the instance of SQL Server in Information and Settings, and modifies settings in Settings and UserOptionsobject properties.

In the example the UserOptions object and the Settings object both have an Alter method. You can run the Alter methods for these individually.

# 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()

Modifying SQL Server Configuration Options in PowerShell

The code example shows how to update a configuration option in Visual Basic .NET. It also retrieves and displays information about maximum and minimum values for the specified configuration option. Finally, the program informs the user if the change has been made dynamically, or if it is stored until the instance of SQL Server is restarted.

#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."  
}