tigertoolbox/SQLConfigurations/Configure_SQLConfig.ps1

#  Copyright (c) Microsoft Corporation.  All rights reserved.
#  
# THIS SAMPLE CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
# WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
# WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
# IF THIS CODE AND INFORMATION IS MODIFIED, THE ENTIRE RISK OF USE OR RESULTS IN
# CONNECTION WITH THE USE OF THIS CODE AND INFORMATION REMAINS WITH THE USER.

# The purpose of this script is 
# 1. Configure the SQL Configuration Options - Max Server Memory, MAXDOP, Remote Admin Connection
# 2. Enable/Disable the following on the Model Database
#   2a. AutoShrink, AutoClose, Limit Autogrow
Try
{
        Write-Host "*************************  SQL Configuration Options *************************"
        $TSQLScript1 = "
        exec sp_configure 'show advanced options',1;
        reconfigure with override"
        $TSQLScript2 = "
        exec sp_configure 'remote admin connections',1;
        reconfigure with override"
        $TSQLScript3 = "
        exec sp_configure 'max server memory (MB)',5000;
        reconfigure with override"
        $TSQLScript4 = "
        exec sp_configure 'max degree of parallelism',1;
        reconfigure with override"

        # Execute the T-SQL script against the SQL Server instance
        Invoke-SqlCmd -ServerInstance . -Query $TSQLScript1 -Database "master"
        Invoke-SqlCmd -ServerInstance . -Query $TSQLScript2 -Database "master" 
        Invoke-SqlCmd -ServerInstance . -Query $TSQLScript3 -Database "master" 
        Invoke-SqlCmd -ServerInstance . -Query $TSQLScript4 -Database "master" 

        Write-Host "*************************  Model Database Options *************************"
        
        Invoke-SQLcmd -Query "ALTER DATABASE MODEL SET AUTO_CREATE_STATISTICS ON (INCREMENTAL = ON );" -ServerInstance . -Database "master" 
        Invoke-SQLcmd -Query "ALTER DATABASE MODEL SET AUTO_UPDATE_STATISTICS ON;" -ServerInstance . -Database "master" 
        Invoke-SQLcmd -Query "ALTER DATABASE MODEL SET AUTO_UPDATE_STATISTICS_ASYNC OFF;" -ServerInstance . -Database "master" 
        Invoke-SQLcmd -Query "ALTER DATABASE MODEL SET AUTO_SHRINK OFF;" -ServerInstance . -Database "master" 
        Invoke-SQLcmd -Query "ALTER DATABASE MODEL SET AUTO_CLOSE OFF;" -ServerInstance . -Database "master" 
}
Catch
{
      Write-Host "********** Erorr Configuring SQL Config Options ************" -ForegroundColor Red
}