A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

Server Setup Basics - Considerations and Good Practices

1. Lock Pages in Memory. 


2. Set default data and log directories. (Different LUN's)


3.  Set SQL to High Priority on all Processors


4. Boost SQL Server Priority on Windows


5. Allocate a maximum amount of memory


6. Spread your data across LUNS


7. PCI and DISA (STIG) Compliance


PCI-DSS Compliance - MS-SQL High Object Permission Granted to PUBLIC or Guest Revoke any object privileges assigned to PUBLIC or GUEST
From the query prompt (repeat for each database):
use <database name>
revoke <privilege> on <object name> from '<public or guest>'
repeat for each object privilege assigned to public or guest.



7. Create a custom group for SYSADMIN functions.

     -add authorized users to the custom group.

      -add the group the SYSADMIN fixed server role

      -remove BUILTIN\Administrators from the role.


8. Move TempDB onto its own Drives. 1 file for each processor.


9. Separate System Databases from User Databases.


10. Change default Sql Ports


11. Use windows logins and disable sql logins. Users and apps should use active directory controlled logins.


12. Setup Backups


13. Setup Maintenance Plans


14. Setup Performance Counter Log and Perfmon.msc counter


15. Separate logs and data to different drives.


16. Install SQL Performance Dashboard RDL’s


17.       Set up a system to collect Database size change statistics for storage planning


18.       Set up a system to collect table row count change statistics for scalability planning


19.       Set up a system to collect disk IO statistics for scalability planning

20.       Set up Performance Monitor Log collection system and analyze the log to get SQL performance baseline for scalability planning


21.       Set up a system to collect blocking and deadlock statistics for trouble shooting and proactively response to possible production issues


22. Setup poor performing query trace.

 23. Perform Volume Maintenance Security Policy : File Growth Performance.

 Add the SQL Server Service User to this Group Policy
GPEDIT.MSC -> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment