Introduction

A common problem with newly built SQL Server or acquired existing SQL Server is server settings or effectively lack of custom setttings with SQL Server running defaults.

Problem

Apply best practices to server level configurations

Solution

https://github.com/steverezhener/DBA/blob/master/Jobs/_Maintenance_00_NormalizeNewServerConfig_01.sql

Regular Schedule: Every day at 8AM

Solution: Native T-SQL

  1. Optimize server TempDB database files
  2. Change server authentication to a Mixed mode
  3. Enable server SA
  4. Enable server DB Mail
  5. Add server Job Operators
  6. Add server SQL Alerts
  7. Enable server Agent Notification
  8. Configure server SQL Server Error Logs
  9. Find server Optimal Custom Backup Configs
  10. Change server Max Degree of Parallelism (MAXDOP)
  11. Change server Max Memory
  12. Change server Optimize for adhoc workloads
  13. Change server Cost Threshold for Parallelism (CTFP)
  14. Enable server Remote Dedicate Administrator Connection (”DAC”)
  15. Change server Backup Checksum Default
  16. Change server Backup Compression Default
  17. Enable server Common Language Runtime (aka “CLR”)
  18. Enable server trace flags
  19. Change server PowerShell Jobs default
  20. Disable server xp_cmdshell
  21. Test server (all) Linked Servers
  22. Change server Remote Query Timeout
  23. Change server Min Memory
  24. Change server blocked process threshold
  25. Change default login language
  26. Check server Instant File Initialization (aka “IFI”)
  27. Enable server PolyBase
  28. Check server recommended VM resources
  29. Check server for database files stored on a C drive
  30. Check server for encrypted connection (TLS)
  31. Check server Power Plan
  32. Check server Lock Pages in Memory (aka “LPIM”) setting
  33. Find server Maintenance Plans
  34. Collect server estimated tempdb for checkdb
  35. Change server linked server collation
  36. Change server ADR cleaner
  37. Change server lightweight pooling
  38. Change server priority boost
  39. Change server soft-NUMA
  40. Change server default database file location
  41. Collect server TLS info

Checks procedure versioning


DECLARE @procedure_version NVARCHAR(10); 
DECLARE @schema_name NVARCHAR(50);
DECLARE @procedure_name NVARCHAR(50);

SET @schema_name=N'bestpractices'
SET @procedure_name=N'usp_optimize_server_tempdb'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_authentication'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_enable_server_sa'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_setup_server_db_mail'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_add_server_job_operators'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_add_server_sql_alerts'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_enable_server_agent_notification'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_limit_server_error_logs'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_limit_server_error_logs'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_find_server_optimal_backup_configs'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_optimize_server_database_backups'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_max_dop'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_max_memory'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_optimize_for_adhoc_workloads'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_cost_threshold_for_parallelism'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_remote_dac'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_backup_checksum'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_backup_compression'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_clr'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_trace_flags'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_adjust_server_sub_system_default_max_worker_threads'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_xp_cmdshell'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_test_server_linked_servers'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_remote_query_timeout'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_min_memory'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_blocked_process_threshold'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_change_server_login_default_language'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_check_server_ifi'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_enable_server_polybase'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_check_server_required_resources'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_check_server_database_file_drive'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]

SET @procedure_name=N'usp_check_server_encrypted_connection'
EXEC DBA.maintenance.usp_locate_procedure_version @schema_name=@schema_name, @procedure_name=@procedure_name
, @version_keyword=N'DECLARE @Version VARCHAR(10)',@procedure_version=@procedure_version OUTPUT SELECT @procedure_name as [procedure_name], @procedure_version as [version_number]