Standard SQL Server Machine

Posted onAuthorStormAdminLeave a comment

When you build a plain old Vanilla Windows Machine supporting a SQL Server instance, what does your run book look like? My run book is an excel spreadsheet with lots of formulas that update or make visible items based on selections in the first ten rows or so. it has maybe one hundred rows in the sheet that shows any DBA, step by step, all the things we expect them to do as part of a new server build…complete with links to screenshots of examples. It is the most complete build document I have ever encountered.

After you’ve installed SQL Server do you do all the things I do? My Deployment is always open to improvements, but that this time, this is what I am currently doing:

Via PowerShell, On The Server:

  • Set Some BasicSettings like _FolderOptions and The TimeZone
  • Disable Unneeded services like Cortana, CEIP, CDPUSerSvc and more
  • Set PS_003_Distributed Transaction Coordinator Settings to be optimized for Linked Servers
  • Set Perform Volume Maintenance Tasks for the SQL service account, in case it was missed.
  • Install Net3.5 for DatabaseMail support
  • Set SQL Service and SQL Agent Services to Automatic and AutoStart
  • Create SQL Backup Directory on the fileshare I keep Backups(never local)
  • Run a Chocolatey script to install the following on each server:
    • ‘7zip.install’, #Zip and SFTP and password Utilities
    • ‘7zip.commandline’, #Zip Utilities
    • ‘GoogleChrome’, #browsers
    • ‘vscode’, #VSCode
    • ‘sql-server-management-studio’, #SSMS
    • ‘made2010’, #Microsoft ACE drivers,
    • ‘sqlserver-odbcdriver’, #SQL ODBC Drivers
    • ‘dotnetfx’ #.NET
    • ‘notepadplusplus.install’ #text editor

Via SQL Server Management Studio, all these custom objects are deployed

  • Script that Sets permissions for Monitoring software’s logins.
  • Create extended properties to have the cname and the business’s Distribution List Email.
  • Create a Custom Server Role ReadOnlyAdmin and assign an AD group to it.
  • Create a DBA Database.
  • Set the backup directory to match yjr \\uncpath the PowerShell created for us.
  • Server Changes and DDL Changes Custom Tracking(Extended Event, Table, Procedures and Job to populate it)
  • Configure Database Mail
  • Create a Custom view for JobSchedules for easier reference.
  • Application user Errors Custom Tracking(Extended Event, Table, Procedures and Job to populate it)
  • Set up Typical Server Settings based on formulas and best practices
    • Enable Query Store
    • set cost threshold for parallelism
    • set backup compression default = true
    • set max text repl size
    • Set default trace = enabled
    • set default fill factor
    • set remote access
    • set blocked process threshold
    • set Agent XPs
    • set max degree of parallelism
    • set Optimize for Adhoc workloads
    • set Min Memory Per Query
    • set Min Server Memory
    • set Max Server Memory, sharing the memory with any other instances on the same server.
  • Create a role db_execute in every database
  • Create a custom job to Selectively update statistics every hour.
  • Deploy Adam Machanic’s sp_whoisactive
  • Setup SQL Agent Details
    • Set Default Operator
    • Set Failsafe Operator
    • Set Error Logging Level
    • Set idle_cpu_percent
    • Set max history rows
    • Set max in history per job
    • Set history retention
  • Server Trigger to Prevent Drop or Alter of a database
  • Script to change the owner of all databases to [sa]
  • Script to change the owner of all jobs to [sa]
  • Deploy latest copy of Brent Ozars Blitz/First Responder
  • Deploy the latest Glenn Berry’s SQL Server Diagnostic Information Queries, wrapped as a custom procedure named sp_glenn
  • Deploy a custom version of Ola Hollengrens Maintenance Solution
  • First/Latest Login Custom Tracking(Extended Event, Table, Procedures and Job to populate it)
  • Script to Assign DBA or Business Operators to every job
  • Stack of 20 Custom stored procedure in master, marked as system objects

Leave a Reply