Functions/Set-SdtDbaConfigurations.ps1
|
function Set-SdtDbaConfigurations { <# .SYNOPSIS This function sets various configuration for SQL Server .DESCRIPTION This function sets following configurations:- Create DBA database Set SQL Server Max Memory Set SQL Instance DOP Configure Secondary Multiple TempDb Files Set Sql Instance Configurations like xp_cmdshell, database mail, cost threshold of parallelism Set Database Mail Account/Profile/Default Agent Profile Set Model database with Optimal Settings Setup Self-Service Modules like whoisactive, sp_HealthCheck, etc Setup WhoIsActive Baselining Compile Ola Scripts Setup IndexOptimize Jobs .PARAMETER SqlInstance Sql Server Instance on which various configurations are to be set up. .EXAMPLE Set-SdtDbaConfigurations -SqlInstance 'testvm' This command sets various configurations on server 'testvm'. For example max memory, dop, etc. .LINK https://github.com/imajaydwivedi/SQLDBATools #> [CmdletBinding(SupportsShouldProcess=$true, ConfirmImpact='High')] Param( [Parameter(Mandatory=$true)] [Alias('Server','Instance')] [string]$SqlInstance ) $ServerName = $SqlInstance.Split('\')[0]; # Create Database TSQL $tsql_CreateDb = @" SET NOCOUNT ON; IF DB_ID('DBA') IS NULL CREATE DATABASE DBA; "@; #Write-Debug "Start of executions" Write-Verbose "Create if not exists [DBA] database"; Invoke-DbaQuery -SqlInstance $SqlInstance -Query $tsql_CreateDb -Verbose:$false | Out-Null; # Set PowerPlan to High Performance Write-Verbose "Make sure PowerPlan is set to 'High Performance'"; Set-DbaPowerPlan -ComputerName $ServerName -PowerPlan 'High Performance' -Verbose:$false | Out-Null; # Grant User Rights Assignments Policy permissions for ServiceAccount Write-Verbose "Grant User Rights Assignments Policy permissions for ServiceAccount"; Grant-SdtSqlAccountRequiredPrivileges -SqlInstance $SqlInstance -Verbose:$false | Out-Null; # Set SQL Server Max Memory Write-Verbose "Set Max SQL Server Memory to ideal value suggested by 'Test-DbaMaxMemory' cmdlet"; Set-DbaMaxMemory -SqlInstance $SqlInstance -WarningAction SilentlyContinue -Verbose:$false | Out-Null; # Set SQL Instance DOP Write-Verbose "Set Degree of Parallelism (DOP) to ideal value suggested by 'Test-DbaMaxDop' cmdlet"; Set-DbaMaxDop -SqlInstance $SqlInstance -WarningAction SilentlyContinue -Verbose:$false | Out-Null; # Configure Secondary Multiple TempDb Files Write-Verbose "Set TempDbConfiguration as per suggestions by 'Test-DbaTempDbConfig' cmdlet"; Set-DbaTempdbConfig -SqlInstance $SqlInstance -DataFileSize 8000 -WarningAction SilentlyContinue -Verbose:$false | Out-Null; # Set Sql Instance Configurations Write-Verbose "Set CostThresholdForParallelism to 50"; Set-DbaSpConfigure -SqlInstance $SqlInstance -Name CostThresholdForParallelism -Value 50 -Verbose:$false; Write-Verbose "Enable XPCmdShellEnabled, IsSqlClrEnabled, AdHocDistributedQueriesEnabled, OptimizeAdhocWorkloads, DatabaseMailEnabled, RemoteDacConnectionsEnabled"; Set-DbaSpConfigure -SqlInstance $SqlInstance ` -Name XPCmdShellEnabled, IsSqlClrEnabled, AdHocDistributedQueriesEnabled, ` OptimizeAdhocWorkloads, DatabaseMailEnabled, RemoteDacConnectionsEnabled ` -Value $true -WarningAction SilentlyContinue -Verbose:$false | Out-Null; # Set Database Mail Account/Profile/Default Agent Profile Write-Verbose "Set MailProfile"; Set-SdtDbaMailProfile -SqlInstance $SqlInstance -Verbose:$false; # Set Model database with Optimal Settings Write-Verbose "Set [model] database with Optimal Settings"; Optimize-SdtModelDatabase -SqlInstance $SqlInstance -WarningAction SilentlyContinue -Verbose:$false | Out-Null; # Setup Self-Service Modules Write-Verbose "Create Self-Service Modules like sp_WhoIsActive, sp_HealthCheck, sp_Kill etc"; Set-SdtSelfServiceModules -SqlInstance $SqlInstance -Verbose:$false | Out-Null; # Setup WhoIsActive Baselining Write-Verbose "Setup WhoIsActive Baselining"; Set-SdtBaselineWithWhoIsActive -ServerInstance $SqlInstance -Verbose:$false | Out-Null; # Compile Ola Scripts Write-Verbose "Compile Ola Hallengren Maintenance Scripts"; Install-SdtOlaHallengrenMaintenanceScripts -SqlInstance $SqlInstance -Verbose:$false | Out-Null; # Create DBAGroup Operator Write-Verbose "Create DBAGroup Operator"; Add-SdtSqlAgentOperator -SqlInstance $SqlInstance -OperatorName 'DBAGroup' -EmailId 'dba-group@YourOrg.com' -Verbose:$false | Out-Null; # Setup IndexOptimize Jobs Write-Verbose "Create IndexOptimize Jobs"; Set-SdtIndexOptimizeJobs -SqlInstance $SqlInstance -Verbose:$false | Out-Null; # Setup DatabaseBackup Jobs Write-Verbose "Create DatabaseBackup Jobs"; Set-SdtDatabaseBackupJobs -SqlInstance $SqlInstance -Verbose:$false | Out-Null; # Create Blocking Alert Write-Verbose "Create Blocking Alert Job"; Set-SdtBlockingAlert -SqlInstance $SqlInstance -Verbose -Confirm:$false | Out-Null; } |