SQL Best Practice

  1. Create 4 Drives 
    1. Data for SQL DB F:\
    2. Logs E:\
    3. TempDB G:\
    4. Backups H:\

Install DB to F:\MSSQLSERVER\

Install Client Tools by Defaults and Anything else you might need

 

Setup GMSA service accounts for SQL Service and Agent

[pastacode lang=”powershell” manual=”New-ADServiceAccount%20gmsa_sql02%20-DNSHostName%20gmsa_sqlinstance.domain.local%20-PrincipalsAllowedToRetrieveManagedPassword%20sqlservername%24%0A%0ANew-ADServiceAccount%20gmsa_sql02agent%20-DNSHostName%20gmsa_sql02agent.domain.local%20-PrincipalsAllowedToRetrieveManagedPassword%20sqlservername%24″ message=”” highlight=”” provider=”manual”/]

You might need to create RootKeys

[pastacode lang=”powershell” manual=”Add-KdsRootKey%20%E2%80%93EffectiveTime%20((get-date).addhours(-10))” message=”” highlight=”” provider=”manual”/]

Setup Service to this :

 

 

Create New New DB called DBA

Run this SQL query against DBA SQL Server Backup, Integrity Check, Index and Statistics Maintenance (hallengren.com) Schedule everything apart from DB Backups , weekly

Run this SQL query against DBA http://whoisactive.com/

Run this SQL query against DBA https://www.brentozar.com/blitz/

 

Run These Queries

[pastacode lang=”sql” manual=”exec%20sp_configure%20’show%20advanced%20options’%2C%201%0A%0Areconfigure%0A%0Aexec%20sp_configure%0A%0A%0A%0A%0Aexec%20sp_configure%20’remote%20admin%20connections’%2C%201%0A%0Areconfigure%0A%0Aexec%20sp_configure%0A%0A%0A%0A%0Aexec%20sp_configure%20’show%20advanced%20options’%2C%200%0A%0Areconfigure%0A%0Aexec%20sp_configure” message=”” highlight=”” provider=”manual”/]

Enable Trace Flags on Startup

SQL Server Trace Flags – Complete list

 

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...