I have been managing all of the Microsoft SQL server instances in our organization. Part of our SQL server process is to review the standard settings and tweaks after installation. If you don’t already have a post-installation checklist, I highly encourage you to create one for your team.
Having a post-installation checklist helps to make sure all of your SQL instances are following your standards. Today, I will start with the server properties checklist.
- In your SQL server inventory list, add the new instance you have just deployed.
- Record information relating to hostname of the box, the version of SQL you have deployed, operating system, allocated CPU and memory and disk layout. You can get this information in the General page of the server properties window.
- In the Memory page of the server properties window, review the minimum and maximum server memory settings. In our case, we also manage the SAP SQL servers.
- In the Processors page of the server properties window, make sure both checkboxes under Enable processors are checked. We do not change any of the Threads setting and leave them as default.
- In the Security page, we choose the SQL Server and Windows Authentication mode.
- In the Database Settings page, we make sure the Compress backup is checked. Make sure to adjust the Database default locations unless you have already defined this correctly during installation.
- In the Advanced page, we set the value for Optimize for Ad hoc Workloads to True. We also set the Cost Threshold for Parallelism to 50. If you are hosting SharePoint or SAP databases in this instance, both vendors recommend to set the Max Degree of Parallelism (MaxDOP) to 1.
The goal is to have consistency in your SQL server deployments and that can only be achieved if you have a post-installation checklist. Normally, another person conducts the review of the post-installation.
In the next blog entry, I will talk about the different Trace flags we use and the different database settings we set for both the Model and TempDB databases.
I hope you learned something new today. Share your comments on what you do with in your organization or feel free to share. See you on the next blog post.