Best SQL Configuration

 

This is an exmaple of my SQL onboarding. When we create a new SQL server, this is the configuration I use for every SQL instance. I always start off with a basic next next next install and then configure SQL afterwards. I mostly use PowerShell dbatoolsto perform some of the configuration sitems for me.

This is list of all the configuration items that are required when installing and configuring a SQL server.

1	This step is part of the server request and must be done before starting the remaining steps: Confirm that the requirement for SQL server. (version, disk size, port number, CPU and memory) Set up a service account for the SQL service in Active Directory (AD).
2	Copy files for SQL server installation and maintenance.
3	Network adapter power management is turned Off
4	Disks are formatted to 64K allocation size
5	Service Browser is disabled
6	Default port of 1433 is being used unless specified
7	SQL service account is set as SeManageVolumePrivilege and SeLockMemoryPrivilege
8	Server and Instance are added to the inventory
9	Server has been added to the SQL CMS
10	SQL Agent is set to Auto Start
11	Default paths are set.
12	Standard accounts have been added to Local Administrator group
13	Permissions are set correctly on the default paths
14	Remote Access is enabled
15	MAX DOP has been changed from the default of
16	Min/Max has been changed from default
17	Backup default is set to compress
18	Error logs are set to 20 logs
19	Security is set to mixed security
20	Standard accounts have been added to Sysadmin
21	Database Mail is been configured
22	Operator Name Data Management has been created
23	Alerts are setup
24	Model database is set size to 10 MB, data growth 50MB, log  growth 20 MB
25	Move TempDB datafiles to the T drive
26	Remove TempDB files from the C Drive
27	Create SQL account for admin access and make sure it is setup correctly
28	Account sa has been renamed and disabled
29	Ole configuration is disabled
30	SQL Instance has been hidden
31	Startup procs are not being used unless requested
32	Xp_cmdshell is disabled unless requested
33	Audits are enabled for failed logins
34	Active Directory service accounts are being used instead of built-in group users
35	Review whether the following must be enabled: Install Avamar client and configure backup for the SQL server. In CMS move registered server to  correct folder. Default is CORP DEV. Change inventory to correct environment and alert flag. Default is CORP DEV and false. Set sa password and verify that account is disabled. Enable and set password for MSSQL_DBA SQL account. Add server to monitoring applications.  Verify min/max memory is set correctly
Port 1433, with new firewall in place port 1433 is block. Create a ticket and assign to group.
36	END