Automating the SQL Server Instance Configuring

Automating Microsoft SQL Server Instance ConfiguringWe previously discussed how to automate SQL Server Instance Builds. Continue the theme. Let's talk about Instance Configuring. There is a vast amount of configuration changes that may be appropriate for the SQL build in your specific environment. This article attempts to cover some of the most common configuration changes, but any SQL Server configuration can be scripted, so I encourage you to explore other scripted configurations that are appropriate for your enterprise.


Table of contents[Show]


 

Configure Database Scoped Configurations

Microsoft has simplified the task of configuring an instance in SQL Server 2016 by adding the ability to configure the number of TempDB files required and if the instance should use Instant File Initialization during the installation. There are also more sensible file size settings, with data and log files defaulting to 8MB instead of 1MB, and file growth defaulting to 64MB. In my opinion, these settings should still be configured based on your capacity planning, but if you do forget to configure them, your log fragmentation will not be quite as bad as in previous versions.

Microsoft has also simplified instance configuration by bringing the functionality of some common trace flags to the database scope, as opposed to the server scope, through fully supported ALTER DATABASE statements. Additionally, some instance-level configurations, such as MAXDOP and parameter sniffing, have also been implemented at the database level.

Table 1 details our required configurations for instances supporting Data Warehouse workloads and OLTP workloads.

Table 1. Instance Configurations

Configuration

OLTP Workloads

Data Warehouse Workloads

Notes

MAXDOP

8

8

MAXDOP can now be configured at the database level, as well as the instance and query levels

LEGACY_CARDINALITY_ESTIMATION

OFF

ON

When joining many tables together, as with some complex data warehouses that break data down into separate tables for month or year, instead of implementing partitioning, the new cardinality optimizer can take a very long time (sometimes hours) to complete. In SQL Server 2014, the new cardinality estimator could be configured with T9482. In SQL Server 2016, it can be toggled on or off with this ALTER DATABASE SET option.

AUTOGROW_ALL_FILES

OFF

ON

This configuration is a filegroup scoped implementation of T1117, which causes all files within a filegroup to grow, if any file within the filegroup grows.

MIXED_PAGE_ALLOCATION

ON

OFF

A database level implementation of T1118, this specifies if mixed page allocations should take place before a table reaches 64KB of used space. Unlike previous versions, this behavior is turned off by default.

We can create a PowerShell script, which will accept a parameter detailing the type of instance and then conditionally configure these settings, by using the invoke-sqlcmd cmdlet, to modify the Model database. Once the Model database has been configured, all user databases that are created will inherit its properties, unless specifically overridden. For properties that are not permitted to be configured in Model, we will conditionally create a DDL trigger, which will fire on new database creation. This is demonstrated in Listing 1.

Tip

If you are following the examples in this article, you should save this configuration file for later use. You should also change the Owner_Login_Name to match your own configuration, before executing this script.

Listing 1. Conditionally Configure Instance

param(

[string] $InstanceName,
[string] $InstanceWorkload #The expected workload of the instance

)

$ServerInstance = $env:COMPUTERNAME + "\" + $InstanceName

IF ($InstanceWorkload -eq "Data Warehouse")

{
    Invoke-Sqlcmd -Server $ServerInstance -Query "--Configure Model
USE Model

GO

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8
USE [msdb]

GO

EXEC  msdb.dbo.sp_add_job @job_name='ConfigureNewDatabase',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=2,
                @notify_level_netsend=2,
                @notify_level_page=2,
                @delete_level=0,
                @category_name='[Uncategorized (Local)]',
                @owner_login_name='ESPROD3\SQLAdmin'

GO

EXEC msdb.dbo.sp_add_jobstep @job_name='ConfigureNewDatabase', 
                @step_name='ConfigureDatabase',
                @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_fail_action=2,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem='TSQL',
                @command='DECLARE @DatabaseName AS NVARCHAR(128)

DECLARE @SQL AS NVARCHAR(MAX)

SET @DatabaseName = (
        SELECT TOP 1 name
        FROM sys.databases
        ORDER BY create_date DESC
        )

IF @DatabaseName IS NOT NULL

BEGIN

    SELECT @sql = ''ALTER DATABASE '' + @DatabaseName + '' MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES''
    EXEC(@SQL)
END',
                @database_name='master',
                @flags=0

GO

EXEC msdb.dbo.sp_add_jobserver @job_name='ConfigureNewDatabase', @server_name = @@SERVERNAME

GO

--Create the trigger
CREATE TRIGGER ConfigureNewDatabase
ON ALL SERVER

FOR CREATE_DATABASE
AS
    EXEC msdb..sp_start_job 'ConfigureNewDatabase'  

GO"

}

IF ($InstanceWorkload -eq "OLTP")

{
     Invoke-Sqlcmd -Server $ServerInstance -Query "--Configure Model
USE Model

GO

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8
USE [msdb]

GO

EXEC  msdb.dbo.sp_add_job @job_name='ConfigureNewDatabase',
                @enabled=1,
                @notify_level_eventlog=0,
                @notify_level_email=2,
                @notify_level_netsend=2,
                @notify_level_page=2,
                @delete_level=0,
                @category_name='[Uncategorized (Local)]',
                @owner_login_name='ESPROD3\SQLAdmin'

GO

EXEC msdb.dbo.sp_add_jobstep @job_name='ConfigureNewDatabase', 
                @step_name='ConfigureDatabase',
                @step_id=1,
                @cmdexec_success_code=0,
                @on_success_action=1,
                @on_fail_action=2,
                @retry_attempts=0,
                @retry_interval=0,
                @os_run_priority=0, @subsystem='TSQL',
                @command='DECLARE @DatabaseName AS NVARCHAR(128)

DECLARE @SQL AS NVARCHAR(MAX)

SET @DatabaseName = (

        SELECT TOP 1 name
        FROM sys.databases
        ORDER BY create_date DESC
        )

IF @DatabaseName IS NOT NULL

BEGIN


    SELECT @sql = ''ALTER DATABASE '' + @DatabaseName + '' SET MIXED_PAGE_ALLOCATION ON''
    EXEC(@SQL)
END',
                @database_name='master'
                @flags=0
GO

EXEC msdb.dbo.sp_add_jobserver @job_name='ConfigureNewDatabase', @server_name = @@SERVERNAME

GO

--Create the trigger
CREATE TRIGGER ConfigureNewDatabase

ON ALL SERVER

FOR CREATE_DATABASE
AS
    EXEC msdb..sp_start_job 'ConfigureNewDatabase'  
GO"


}

 

Tip

The script will only configure the PRIMARY filegroup of a new database to autogrow all files. You can use the skills that you have learned in this book so far, to extend the script to loop around all filegroups of the new database.

Configure the Port Number

If you install a named instance of SQL Server, the installer will configure the instance to use dynamic ports. When dynamic ports are configured, every time the instance starts, it will request a port number from Windows. Windows will then assign it a random available port from the dynamic range, which is from 49152 to 65535, assuming that you are running on Windows Server 2008 or later versions. For earlier versions of Windows, the dynamic port range was from 1024 to 5000, but Microsoft changed this in Windows Vista and Windows Server 2008, to comply with the IANA (Internet Assigned Numbers Authority).

If your instance is configured to use dynamic ports, configuring firewalls can be challenging. At the Windows Firewall level, it is possible to configure a specific service to communicate on any port, but this can be hard to replicate at the corporate firewall level. Instead of this approach, you have to keep the full dynamic port range open bidirectionally. This is an obvious security hole, so I recommend that the instance is configured to use a specific port.

Tip

In environments where security is the overriding priority, you may also choose to change the port number of the default instance, so that there is not a well-known port number for hackers to attack.

You can create a PowerShell script that will be called off as part of your automated build, which will assume the responsibility of changing the port number. This is achieved through SMO, as demonstrated in Listing 2.

Listing 2. Configure the Port Number

Param(

[string] $InstanceName,
[string] $Port
)

# Load SMO Wmi.ManagedComputer assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

# Create a new smo object
$m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')

#Disable dynamic ports
$m.ServerInstances[$Instance].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpDynamicPorts'].Value = ""

# Set static port
$m.ServerInstances[$Instance].ServerProtocols['Tcp'].IPAddresses['IPAll'].IPAddressProperties['TcpPort'].Value = "$Port"

# Reconfigure TCP
$m.ServerInstances[$Instance].ServerProtocols['Tcp'].Alter()

 

Updating the Inventory Database

In order to drive automation, one key aspect of the automated build will be to update the Inventory database. We will also have to update the list of target servers used by the SQL Server Agent Master Server. The script in Listing 3 will insert the details of the new instance into the Inventory database. This is a standalone server, with no DR instance. Therefore, the only tables to be populated will be ServiceAccount, Server, and Instance. Finally, it will enlist the new instance as a Target Server of our MSX Server.

Listing 3. Insert into Inventory Database

param(
[string] $InstanceName,
[string] $SQLServiceAccount,
[string] $InstanceType,
[string] $VMFlag,
[string] $Hypervisor,
[string] $ApplicationOwner,
[string] $ApplicationOwnerEmail,
[string] $saAccount,
[string] $saAccountPassword
)

import-module sqlps

#Get Windows Version
[string]$WindowsVersion = Get-CimInstance Win32_OperatingSystem | Select-Object  caption
$WindowsVersion = $WindowsVersion.substring(10,$WindowsVersion.Length-11)

#Get ServerName
$ServerName = $env:COMPUTERNAME

#Get SQL Version, LoginMode, InstanceCores, InstanceRAM, PortNumber

$ServerInstance = $env:COMPUTERNAME #+ "\" + $ServerInstance

$SQLVersion = invoke-sqlcmd -ServerInstance $ServerInstance -Query "SELECT SUBSTRING(@@VERSION,1,CHARINDEX(')',@@VERSION)) AS Version" | Select-Object -expand Version

$LoginMode = Invoke-Sqlcmd -ServerInstance $ServerInstance -Database "Master" -Query "CREATE TABLE #LoginMode
(
Value        NVARCHAR(128),
Data        TINYINT
)
INSERT INTO #LoginMode
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode'
GO

SELECT Data
FROM #LoginMode" | Select-Object -expand Data

$InstanceCores = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "SELECT  COUNT(*) AS Cores
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'" | Select-Object -expand Cores

$InstanceRAM = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "SELECT value
FROM sys.configurations
where name = 'max server memory (MB)'" | Select-Object -expand Value

$PortNumber = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query "
DECLARE       @Port   NVARCHAR(8)

CREATE TABLE #PortNumber
(
PortNumber       NVARCHAR(8)
)

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE'
,@key        = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll'
,@value_name = 'TcpPort'
,@value      = @Port OUTPUT

INSERT INTO #PortNumber  
SELECT @Port

EXEC   xp_instance_regread
@rootkey    = 'HKEY_LOCAL_MACHINE'
,@key        = 'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll'
,@value_name = 'TcpDynamicPorts'
,@value      = @Port OUTPUT

INSERT INTO #PortNumber  
SELECT @Port

SELECT PortNumber
FROM #PortNumber
WHERE PortNumber IS NOT NULL

DROP TABLE #PortNumber" | Select-Object -expand PortNumber

foreach ($Version in $SQLVersion)
{
[string]$SQLVersionShred = $SQLVersion.Version
}

foreach ($Mode in $LoginMode)
{
$LoginModeShred = $LoginMode.Data
}

foreach ($Core in $InstanceCores)
{
[string]$InstanceCoresShred = $InstanceCores.Cores
}

foreach ($RAM in $InstanceRAM)
{
[string]$InstanceRAMShred = $InstanceRAM.Value
}

foreach ($Port in $PortNumber)
{
[string]$PortNumberShred = $PortNumber.PortNumber
}

#Get Server Cores
[string]$NoOfCores = Get-WmiObject –class Win32_processor | SELECT NumberOfCores
$ServerCores = $NoOfCores.Substring($NoOfCores.IndexOf("=") + 1,$NoOfCores.Length-$NoOfCores.IndexOf("=")-2)

#Get Server RAM
$ServerRAMarray = Get-WmiObject -class Win32_physicalmemory | SELECT capacity

$ServerRAM = ($ServerRAMarray.capacity | Measure-Object -Sum).sum

#Get IP Address
[string]$IPAddress = Get-NetIPAddress -InterfaceAlias "Ethernet" -AddressFamily "IPv4" | SELECT IPAddress
$IPAddress = $IPAddress.Substring(12,$IPAddress.Length-13)

#Insert into Inventory database
Invoke-Sqlcmd -ServerInstance "ESASSMGMT1" -Database "Inventory" -Query "BEGIN TRANSACTION
    BEGIN TRY    
            DECLARE @ServerIdentityTbl TABLE(ID INT)
            DECLARE @ServiceAccountIdentityTbl TABLE(ID INT)

        DECLARE @ServiceAccountIdentity INT
        DECLARE @ServerIdentity INT

            MERGE ServiceAccount AS Target
        USING(SELECT '$($SQLServiceAccount)' AS SQLServiceAccount) AS Source
            ON (Target.ServiceAccountName = Source.SQLServiceAccount)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (ServiceAccountName)
            VALUES (Source.SQLServiceAccount)
        OUTPUT inserted.ServiceAccountID INTO @ServiceAccountIdentityTbl(ID);

        SET @ServiceAccountIdentity = (SELECT ID FROM @ServiceAccountIdentityTbl)

        IF @ServiceAccountIdentity IS NULL
        BEGIN
            SET @ServiceAccountIdentity = (SELECT ServiceAccountID FROM ServiceAccount WHERE ServiceAccountName = '$($SQLServiceAccount)')
        END

        MERGE dbo.Server AS Target
        USING (SELECT '$($ServerName)' AS ServerName) AS Source
            ON (Target.ServerName = Source.ServerName)
        WHEN NOT MATCHED BY TARGET THEN
            INSERT (ServerName, ClusterFlag, WindowsVersion, SQLVersion, ServerCores, ServerRAM, VirtualFlag, Hypervisor, ApplicationOwner, ApplicationOwnerEmail)
            VALUES('$($ServerName)',0,'$($WindowsVersion)','$($SQLVersion)','$($ServerCores)','$($ServerRAM)','$($VirtualFlag)','$($Hypervisor)','$($ApplicationOwner)','$($ApplicationOwnerEmail)')
        OUTPUT inserted.ServerID INTO @ServerIdentityTbl(ID);

            SET @ServerIdentity = (SELECT ID FROM @ServerIdentityTbl)

        IF @ServerIdentity IS NULL
        BEGIN
            SET @ServerIdentity = (SELECT ServerID FROM dbo.Server WHERE ServerName = '$($ServerName)')
        END

            INSERT INTO dbo.Instance(InstanceName, ServerID, Port, IPAddress, SQLServiceAccountID, AuthenticationMode, saAccountName, saAccountPassword, InstanceClassification, InstanceCores, InstanceRAM, SQLServerAgentAccountID)
            VALUES('$($InstanceName)',@ServerIdentity,'$($Port)','$($IPAddress)',@ServiceAccountIdentity,'$($LoginMode)','$($saAccount)','$($saAccountPassword)','$($InstanceType)','$($InstanceCores)','$($InstanceRAMShred)',@ServiceAccountIdentity)

        COMMIT
    END TRY
    BEGIN CATCH
        THROW
        ROLLBACK
    END CATCH"

  #EnlistMSX Server
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Database "MSDB" -Query "sp_msx_enlist
        @msx_server_name = 'ESASSMGMT1'  
      , @location = 'NTAM - PROD network block' ;"

 

The script uses PowerShell and T-SQL (via the Invoke-Sqlcmd cmdlet), as appropriate, to identify each of the required properties of the build, before inserting the values into the Inventory database, on our Management instance (ESASSMGMT1). Invoke-Sqlcmd will use value substitution of the scripting variables, meaning that we do not need to create a variables list.

Assuming that we save the script as InsertInventory.ps1, we can run the script from an orchestration, using the command in Listing 4.

Tip

Change the sa account’s password, to match your own configuration, before running this script.

Listing 4. Run the Inventory Update Script

.\InsertInventory.ps1 -InstanceName 'testInstance' -SQLServiceAccount 'ESASS\SQLSVCACCOUNT' -InstanceType '1' -VMFlag '0' -Hypervisor '' -ApplicationOwner 'MyOwner' -ApplicationOwnerEmail 'MyOwnerEmail' -saAccount 'SA' -saAccountPassword 'SAPassword'

 

 

Orchestrating the Build

Now that we have all of the scripts that will be required to install and configure an instance of SQL Server, we should create a final script, which will be used to orchestrate the build. This is important, as it means that we only have to pass variables once, even though they will be used by multiple scripts.

Across all of our scripts, we will require values for the following parameters:

  • $InstanceName,
  • $SQLServiceAccount,
  • $SQLServiceAccountPassword,
  • $AgentServiceAccount,
  • $AgentServiceAccountPassword,
  • $Administrators
  • $InstanceWorkload
  • $InstanceType (This will be derived from $InstanceWorkload)
  • $VMFlag
  • $Hypervisor
  • $ApplicationOwner
  • $ApplicationOwnerEmail
  • $saAccount
  • $saAccountPassword
  • $Port

We will use the Read-Host PowerShell cmdlet to prompt the user to enter a value for each parameter. We will store this input in variables and pass the variables to each script, as required. This is demonstrated in Listing 5.

 

Listing 5. Create the Orchestration

#Prompt for parameter values
$InsanceName = Read-Host -Prompt "Please enter the name of the Instance: "
$SQLServiceAccount = Read-Host -Prompt "Please enter the SQL Server service account: "
$SQLServiceAccountPassword = read-host -Prompt "Please enter the SQL Server service account password: "
$AgentServiceAccount = Read-Host -Prompt "Please enter the SQL Server Agent service account: "
$AgentServiceAccountPassword = Read-Host -Prompt "Please enter the SQL Server Agent service account password: "
$Administrators = Read-Host -Prompt "Please enter the account that should be given SQL Administrative permissions: "
$InstanceWorkload = Read-Host -Prompt "Please enter the expected instance workload (OLTP, Data Warehouse or Mixed): "
$Port = Read-Host -Prompt "Please enter the name of the TCP Port that the instance should listen on: "

IF ($InstanceWorkload = "OLTP")
{
    $InstanceType = 1
}
ELSEIF ($InstanceWorkload = "Data Warehouse")
{
    $InstanceType = 2
}
ELSEIF ($InstanceType = "Mixed")
{
    $InstanceType = 3
}

$VMFlag = Read-Host -Prompt "Please indicate if the server is a VM (0 for physical, 1 for virtual): "

IF ($VMFlag = 1)
{
    $Hypervisor = read-host -Prompt "Please enter the name of the Hypervisor: "
}

$ApplicationOwner = read-host -prompt "Please enter the application owner: "
$ApplicationOwnerEmail = read-host -Prompt "Please enter the application owner's e-mail address: "
$saAccount = Read-Host -Prompt "Please enter the name of the sa account: "
$saAccountPassword = read-host -Prompt "Please enter the password of the sa account: "

#Install the Instance
./AutoBuild.ps1 -InstanceName $InstanceName -SQLServiceAccount $SQLServiceAccount -SQLServiceAccountPassword $SQLServiceAccountPassword -AgentServiceAccount $AgentServiceAccount -AgentServiceAccountPassword $AgentServiceAccountPassword -Administrators $Administrators

#Configure the Instance
./ConfigureInstance.ps1 -InstanceName $InstanceName -InstanceWorkload $InstanceWorkload

#Configure the Port
./ConfigurePort.ps1 -InstanceName $InstanceName -Port $Port

#Insert into the Inventory database
.\InsertInventory.ps1 -InstanceName $InstanceName -SQLServiceAccount $SQLServiceAccount -InstanceType $InstanceType -VMFlag $VMFlag -Hypervisor $Hypervisor -ApplicationOwner $ApplicationOwner -ApplicationOwnerEmail $ApplicationOwnerEmail -saAccount $saAccount -saAccountPassword $saAccountPassword

 

Summary

Automating the build of SQL Server instances will reduce administrative effort and time to market of a new instance request. It will also help you to ensure consistency across your environment. It will also act as a driver for automation, by updating the Inventory database, which will act as the hub that drives automation.

There is more to automating an SQL build than simply running setup.exe You should also consider the configuration of the instance. In some cases, you may also have to configure aspects of the operating system, if your organization’s Windows Gold Build is not optimized for database servers. After installing and configuring SQL Server, be sure to update the Inventory database.

The final step of build automation is to create an orchestration. This orchestration will allow users to type parameter values only once, rather than for every script that is called. This reduces effort and minimizes the risk of human error.

Вас заинтересует / Intresting for you:

Introduction to the Family of ...
Introduction to the Family of ... 50 views Doctor Thu, 10 Oct 2019, 10:04:22
Introduction to SQL Server 201...
Introduction to SQL Server 201... 153 views Antoni Sat, 02 Mar 2019, 05:42:31
Automating Microsoft SQL Serve...
Automating Microsoft SQL Serve... 339 views Doctor Sun, 02 Dec 2018, 11:50:16
How to easily get the MS SQL S...
How to easily get the MS SQL S... 439 views Arrived Topple Thu, 23 Aug 2018, 10:24:47

Comments on Automating the SQL Server Instance Configuring

Be the first to comment
Please login to comment