Best Practices for Configuring Newly Installed SQL Server Instances

Often we install SQL Server on clients or we get to clients where they have pre-installed SQL Server Instances. As a DBA we have the Primary responsibility of keeping the Database Up and Running. This responsibility leads to optimizing, performance tuning and many other stuff related to database’s internal objects. While keeping the other aspects in mind many of us have to go through the configuration of the newly installed or pre-installed but un-configured instances. Configuring SQL Server newly instances are not an easy task and a DBA might want to configure an Instance in many ways.

Here, I am going to discuss some basic and most important configurations of the SQL Server Instance and Database Level which might be ignored and can cause performance issues. Although this is not the complete list of configurations as that would be too long and it’s not a good idea to discuss all the settings under a single article but some of them are basic and critical for initial setup.

Mentioned below are the items discussed one by one which we should configure according to the organization’s or client’s requirement on a new instance.

1. SQL Server Maximum Memory Setting:

By default, SQL Server is allowed to use up to 2 Peta Byte maximum SQL Server Memory. As we all know, normally, we do not have this much of memory in the SQL Server Box so we need to keep this amount much lower than the default. So it would be good to keep 20% or 20 GB (whichever is lower) for the operating system and give rest of the amount to SQL Server by specifying that particular amount in MBs in the Maximum Server Memory option shown below:

2. Database Default Locations:

Ideally speaking we should configure the instance to have separate locations for Data, Log and Backup files. These default locations on instance will be used to auto fill the default location for creating new databases. Before assigning the locations make sure you have allocated enough space for the new databases and mention the default values for each option i.e. Data Files, Log Files and Backup Files as shown below:

3. Maximum Degree of Parallelism:

It’s one of the critical settings to be configured and setup. To oversimplify things, I would suggest to use the 8 as a thumb rule number. Just keep a simple formula in your mind that if you have 8 or more Logical Processor in one NUMA Node (Physical Processor) then use 8 as the MAXDOP settings number. If you have less than 8 Logical Processor in one NUMA Node, then use that number instead.

4. TempDB File growth and location settings:

TempDB File growths are by default set in percentage, unlimited and created at C Drive and have only 1 Data file. This configuration is not appropriate though in newer versions of SQL Server 2016 or higher we have better defaults but not all the organizations use the latest version. So, first the TempDB should be on a separate drive preferably on an SSD with at least 4 files. The file growth has many views but I would prefer to go with extending the initial size of the TempDB file to be maximum possible like if you have 10 GB drive for TempDB then the Data files should be around 2 GB each for data file and 2 GB for log file. Here I want to be clear that I am not giving an equal amount of file size to data and log. The data files should all be equally sizes but log file can be different depending upon your needs.

5. Service Accounts

The Service Account by default are NTService and this should be changed to a dedicated SQL Server Domain account which should have file and system level access. The Services like SQL Server Instance and SQL Server Agent should be separately handled. Keep in mind that changing the SQL Server Service account needs a Service Restart so change the SQL Server Service Accounts when the SQL Server is not deployed to production or if it’s already in production then do plan ahead the downtime. The downtime should not exceed couple of minutes but to be on the safer side get a 10-minute downtime if possible.

6. SQL Server Port

SQL Server uses the default port of 1433 which is widely known and most often DBAs don’t change it. This is again a bad practice to use the defaults. Although one can argue that the SQL Server Boxes are secured by firewalls and antiviruses and all that fancy stuff but still it would be good to change the default port to someone else and get an additional security.

You can change the port in the TCP/IP settings under the SQL Server Network Configuration settings by opening up the SQL Server Configuration Manager.

7. SQL Server Model Database Settings:

As you have set the default database file locations at instance level, now you also need to set the default settings for any new database you create. There are a lot of settings which can be tweaked here but some of them are critical which will benefit you in the longer term.

You can set the initial size of the Model database to be 10 GB, Auto Growth to be 1 GB instead of percentage (Prior to SQL Server 2016) or 64MB (SQL Server 2016) which causes problem like extended growths and high volume of VLFs.

In Addition to the above mentioned settings you can also make the database level settings like Read Committed Snapshot Isolation Level and some of the newly introduced settings at database level (my personal favorites) like MAXDOP and Legacy Cardinality Estimator Options.

8. Registered Servers and Multi-Server Query:

There is a very rare chance in a life of a Senior DBA that he/she gets a job in which only single instance is to be managed. If that is the case, you are blessed . But this is not the case with me. So, I have to deal with 20,30 or even 100s of instances at different clients. So, connecting, executing queries and deploying scripts on multiple instances if really a painful task. The solution is to use the Registered Servers and query your instances using Multi-Server Query option.

First you need to create group based on your requirements like Development, Staging and Production as shown below. This option is available under View tab.

Right Click on the Local Server Groups and add appropriate Groups as shown below:

Then Right Click on the newly created group and add your Servers one by one as shown in the pictures below:

Finally, the multi-Server query is here. It’s really very simple and effective. Once you have registered your desired instances under one group. You simply right click on that particular group and click on the New Query option. Then a new query window will open as normal query window with one exception and that would be the Pink Bar in the connection status. That will give you the total servers and the connected servers count. For example, if you have 3 Servers and one of those is not available it will show you 2/3 meaning 2 servers are connected out of 3 total instances. So, if you have all the desired instances connected then you can issue any query which will be executed of all the instances and will give you results.

So, now you have a handful of settings which you can configure beforehand and it will be a long term benefit for you and your organization. Though, you don’t get limited to these configurations only but most common are listed here. We always have special scenarios where we need customized solutions and that is why experienced DBAs are hired so we can always tweak these and other settings ahead.

Enjoy!

Musab Umair

Musab is one of the two SQL Server Featured Speakers of Professional Association for SQL Server in SQLPASS Pakistan General Conference.

He has 9+ Years of Database Development & Administration experience with Medical Billing, Startup & Financial Companies. He is a Microsoft Certified Expert for Data Platform (SQL Server 2012/2014).

Currently, performing duties as Senior SQL Server Consultant at multiple Saudi Private and Governmental Organizations.

View all posts by Musab Umair