This is part three of a four-part series on Microsoft SQL server deployment. Click to view part one and part two.

Now that you have Microsoft SQL server installed as seen in part two of this series, let’s address some final post-deployment settings.

Firewall

The Microsoft SQL Server installer does not modify the Windows Firewall, so if you still have it tuned on you need to create the appropriate rules. You can create a port rule for TCP 1433 (and 1434 if you’re leveraging the SQL Browser Service) or you can create a rule based on the sqlservr.exe application (this is the SQL engine).

Configure SQL Max Memory

By default, SQL is configured to use as much memory as it “wants to” (the default value is 2,147,483,647 MB,  2 Peta-bytes). You’re going to want to ensure there is enough memory available to perform operating system tasks at all times.

The rule of thumb here is to reserve 10% or 4GB, whichever is greater, to the operating system. Put differently, if you have a server with 96GB RAM, configure SQL server to use 86GB (rounded). Note that in their infinite wisdom, Microsoft has the unit of measure in this dialog set to MB, so make sure you convert, otherwise your SQL server will be running on 86 MB.

SQL Server Max Degree of Parallelism (MAXDOP)

The “max degree of parallelism” (MAXDOP) option determines how many processors can be used to run a single statement in parallel. SQL server will use this value when determining if there are benefits to distributing execution plans across multiple processors. Prior to SQL Server 2019, which includes automatic recommendations, the default value is set to 0, which allows SQL Server to use all available processors (up to 64). However, this is not the recommended value in most cases, specifically if the SQL Server system has more than 8 physical cores/processors.

The recommended value actually depends on the number of processors contained in each processor Non-Uniform Memory Access (NUMA) node. SQL server does its own grouping here (Soft-NUMA), unless it’s disabled. To find out how many NUMA nodes and processors SQL Server “sees”, right-click on the instance in SQL Server Management Studio, choose “Properties”, and then the Processors tab. Use the information to look up the recommended MAXDOP value here.

To set the MAXDOP value, navigate to the Advanced tab in the same dialog.

A MAXDOP setting of 1 suppresses parallel plan generation. This is only recommended if there are specific vendor or application requirements.

Verify SPNs

To enable Kerberos authentication for SQL Server Service Principal Name registrations must be created in Active Directory. The SQL Server installer will attempt to do this automatically, but depending on permission sets for computer and service accounts this fails on occasion. So, it’s always a good idea to verify. You could verify the existence of the correct SPNs by using the setspn.exe command line app, but you also have to know what you’re looking for.

A much easier alternative is using the Kerberos Configuration Manager diagnostic tool. This tool is provided by Microsoft and can be downloaded here. The Kerberos Configuration Manager is a GUI tool that collects information on installed SQL Server roles and diagnoses and fixes potential problems with SPNs and delegations.

Install Ola Halengren’s Scripts

Do yourself a favor and just make this step a part of every one of your Microsoft SQL Server deployments. Ola Hallengren is a SQL Server guru and Microsoft MVP and has written a collection of very useful SQL scripts for SQL Server backups, integrity checks, and maintenance. Best of all, these are all free!

Download the MaintenanceSolution.sql script and run it on your instance. This will create all the necessary objects on your server. You can find documentation and download links on Ola’s website here.

Bookmark and Subscribe to Brent Ozar

If you’re not familiar with Brent Ozar, he is another Microsoft SQL Server wizard and one of the few Microsoft Certified Masters out there. Brent’s services are not free, but his website is a treasure trove of SQL Server knowledge, tips and tricks. Make sure you check out and download his “First Responder Kit” (free) which includes health check scripts, worksheets and checklists. Also, I highly recommend subscribing to his newsletter.

Stay tuned for part four which goes over Microsoft SQL Server deployment tips for success.

As always, we’d love to hear your questions and comments in the Cireson Community.