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.
http://www.edgeventures.com/KB/content.aspx?g=8B03A690-F509-4037-8740-B6570EB3ADA4

 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