{"id":6073,"date":"2022-08-31T10:20:53","date_gmt":"2022-08-31T10:20:53","guid":{"rendered":"https:\/\/pariswells.com\/blog\/?p=6073"},"modified":"2022-08-31T10:20:53","modified_gmt":"2022-08-31T10:20:53","slug":"sql-best-practice","status":"publish","type":"post","link":"https:\/\/pariswells.com\/blog\/research\/sql-best-practice","title":{"rendered":"SQL Best Practice"},"content":{"rendered":"
  1. Create 4 Drives\u00a0
    1. Data for SQL DB F:\\<\/li>
    2. Logs E:\\<\/li>
    3. TempDB G:\\<\/li>
    4. Backups H:\\<\/li><\/ol><\/li><\/ol>

      Install DB to F:\\MSSQLSERVER\\<\/p>

      \"\"<\/p>

      Install Client Tools by Defaults and Anything else you might need<\/p>

      \"\"<\/p>

       <\/p>

      Setup GMSA service accounts for SQL Service and Agent<\/strong><\/p>

      [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”\/]<\/p>

      You might need to create RootKeys<\/p>

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

      Setup Service to this :<\/p>

      \"\"<\/p>

       <\/p>

      \"\"<\/p>

       <\/p>

      \"\"<\/p>

      \"\"<\/p>

      \"\"<\/p>

      \"\"<\/p>

      Create New\u00a0New DB called DBA<\/strong><\/p>

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

      Run this SQL query against DBA http:\/\/whoisactive.com\/<\/a><\/p>

      Run this SQL query against DBA https:\/\/www.brentozar.com\/blitz\/<\/a><\/p>

       <\/p>

      Run These Queries<\/strong><\/p>

      [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”\/]<\/p>

      Enable Trace Flags on Startup<\/strong><\/p>

      SQL Server Trace Flags – Complete list<\/a><\/blockquote>