Configuring SQL Server environment
Once you have installed your SQL Server and performed the patching to the current patch level required, you need to configure basic settings on the SQL Server and also on the Windows Server itself. There are several settings on the Windows Server that have an impact on your SQL Server's performance and security, and these need to be updated before you put the server into production. The following are the basic options that you need to configure on the operating system:
- Configuring security rights for your SQL Server account
- Configuring power settings
- Configuring firewall rules
Configuring security rights for your SQL Server account
During the installation of the SQL Server, you're choosing an account that will be used to run all SQL Server services. There are quite a few considerations for a proper choice but, in this chapter, we'll focus more on the follow-up configuration. Such an account needs to have proper rights on the system. Since SQL Server 2016, you can add one specific system right to your SQL Server account directly during installation. The SeManageVolumePrivilege right can either be granted directly by the installer or you can modify system settings later to customize the assignment of rights. Two other important system rights (Lock pages in memory, Generate security audits) cannot be granted via the SQL Server installer and you must modify the system settings manually, as described in the following paragraph.
In the following screenshot, you can see a dialog from SQL Server 2019 Setup, where you can configure SQL Server Service Accounts for services that you're installing. On the same dialog, you can grant the aforementioned system rights:
Fig. 2.5 – Server configuration during SQL Server setup
System rights can be configured via Group Policy Editor in the Computer Configuration segment of the policy. To open up the console for the rights' configuration, perform the following steps:
- Run gpedit.msc.
- Expand Computer Configuration | Windows Settings | Security Settings | Local Policies.
- Double-click on the system right that you want to edit.
- Add the account or group to which you want to grant the rights.
The first one will be Perform volume maintenance tasks. This right can be granted directly during installation of the SQL Server, but if you skip this, here's where and how you can add this right to your SQL Server account. The reason for granting this right is to enable Instant File Initialization, which can speed up disk operations to allocate new space for data files on the disk. Instant File Initialization does not work for log files, which in any case have to be zeroed out.
Instant File Initialization is used when the data file for the database is growing and allocating new space on the disk drive and also during the restoring of the database to create all files on the disk, before data can be copied from backup to the data files, as illustrated in the following screenshot:
Fig. 2.6 – Windows rights' configuration
Another system right that we will assign as part of the post-installation configuration will be Generate security audits. As you can see in the previous screenshot, this right is granted to two accounts: LOCAL SERVICE and NETWORK SERVICE. Our SQL Server is running with a different account and this account needs to be added to the list. This right will, later on, allow our SQL Server to store audit events in the Windows Event Log to the Security Log. This may come in handy once we see how SQL Server Audit is working and what the options to audit are.
The last system right that we will assign is Lock pages in memory. This right will allow SQL Server to lock the memory pages and prevent the Windows operating system from paging out memory in the case of memory pressure on the operating system. This one has to be taken into careful consideration, with more configuration on the SQL Server engine and proper system monitoring. We'll talk about the SQL Server settings later.
Configuring power settings
When you install a Windows Server operating system, you need to check for power settings that are configured on such systems. There are several options for how you can verify which power setting plan is currently in use. If you open Command Prompt or PowerShell, you can use the powercfg utility to see which plan is used on your server, as follows:
powercfg.exe -list
By default, you will see a Balanced plan selected, which is great for most servers and offers a lot of power-saving features, but this plan is not usually optimal for SQL Servers. SQL Server can put quite some load on the central processing unit (CPU), and switching between CPU speeds may cost you precious time as well as performance issues. If you open the Task Manager tool, you can see that your CPU is not running at the maximum speed and may be running with a much lower value.
As an example, you can see the following screenshot from one of the physical servers with a 2.40 GHz CPU, which is running on 1.25 GHz due to a power-saving plan:
Fig. 2.7 – Task Manager load and CPU speed
You can verify this with tools such as CPU-Z or similar, and the best option we have here is to update the power plan to high performance, which is common for SQL Server workloads. To update the power plan setting, you can either use a control panel where you can find settings for power options or you can use a command line again. When we listed the plans on the server with the previous command, you saw in the output that they come with name and GUID. To update the plan via the command line, we need to use the GUID with the powercfg tool, as follows:
powercfg.exe -SETACTIVE <GUIDofThePlan>
The following screenshot gives an illustration of updating the plan via the command line:
Fig. 2.8 – Power plan configuration via PowerShell
Once the power plan is updated, the CPU is no longer using any power-saving mode and runs at full speed, and possibly even turbo boot for extreme loads, while performing complex queries on your server.
Configuring firewall rules
Each SQL Server instance running on your server is using a different port number to listen for incoming connections, but during the installation of the SQL Server, there are no firewall rules created on your local firewall. You can even see this during installation of SQL Server, where the installer is presenting you with a warning that you have to configure your firewall to include rules for SQL Server services. So, SQL Server is perfectly accessible locally, but not from remote hosts if the local firewall is active. You can run with a built-in firewall on the Windows Servers or have some third-party software in your environment that requires additional configuration.
In the following screenshot, you can see a generated warning by SQL Server Setup that provides you information about the need to configure the firewall rules:
Fig. 2.9 – SQL Server Setup firewall warning
During the installation of the SQL Server, you had to make a choice between deploying SQL Server as a default instance or a named instance. The SQL Server default instance is listening on port 1433 by default, which you can verify in the SQL Server Configuration Manager tool. This port is set as static and will not change over time. Named instances, on the other hand, use a randomly selected port that may not be fixed and can change after a system reboot, because named instances use dynamic ports as a default option.
Tip
It's advised to change the dynamic port to static so that the port number does not change, and this does not have any impact for any security configuration such as the service principal name, which we'll discuss in another chapter.
The following screenshot gives a good idea of the default instances:
Fig. 2.10 – SQL Server Configuration Manager port configuration
Once we know which port our SQL Server instance is listening to, we need to configure the firewall to allow the traffic to our SQL Server service. Windows Server comes with a built-in firewall that can be controlled via a Graphical User Interface (GUI), the command line, and PowerShell.
We will add three different rules to the firewall with the PowerShell tool, as follows:
- The first rule is for the SQL Server service with the proper port number. We have seen the port number in the configuration manager. For a default instance, this is 1433; for a named instance, the port number would be mostly random on each system.
- The second rule is used for the dedicated admin connection (DAC), which is used for troubleshooting the system. Enabling just the firewall rule does not allow you to remotely connect to the DAC session; this also has to be turned on in the SQL Server configuration, and we'll cover this topic later.
- The third rule is for a service called SQL Server Browser, which is used for connection to the named instances.
All three rules can be seen in the following code snippet:
New-NetFirewallRule -DisplayName "SQL Server Connection" –Protocol TCP -Direction Inbound –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName "SQL Server DAC Connection" –Protocol TCP -Direction Inbound –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Server Browser Service" –Protocol UDP -Direction Inbound –LocalPort 1434 -Action allow
If you're running more instances on the server or any other services such as Analysis Services or Reporting Services, or you use any solutions for HA/DR such as mirroring or always on, then you need to carefully examine which firewall rules are needed, and the list may get much longer than the three basic rules we have seen. The complete list of ports required by each service is available on the documentation site at https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-ver15.
Also, keep in mind that two specific editions of SQL Server–Express and Developer—restrict remote communication to the SQL Server Database Engine by default. This can be configured on SQL Server via using a sp_configure stored procedure, as illustrated in the following code snippet:
sp_configure 'remote access', 1
GO
RECONFIGURE
Also, in the SQL Server Configuration Manager tool, check for allowed protocols for connection to your SQL Server. For remote connectivity, you need the TCP/IP protocol enabled. Reconfiguring the available protocols requires a restart of your SQL Server service.
SQL Server post-installation configuration
So far, we have configured our Windows Server and we have made a few configurations related to SQL Server, but we haven't configured any Structured Query Language (SQL)-specific items inside SQL Server itself. For the post-installation configuration, there are plenty of settings worth exploring, some of course with careful consideration.
When the server is deployed, many configuration values are configured with defaults that may be modified for your environment. We'll start with some basic configuration for the databases. During the installation, you had to enter the paths for data, log, and backup file locations, which you can later modify if you need to update the location of the default files.
In the Database Settings section of the server configuration, you can again configure all three paths, as shown in the following screenshot:
Fig. 2.11 – Path configuration
On this same settings page, you can configure two additional important parameters. The first one is the Compress backup option. We'll talk more about the backup settings and methods to perform backup in a different chapter, but as part of post-installation configuration, you can configure this setting on most servers.
Note
Bear in mind that turning on backup compression puts additional load on the CPU while performing the backup, so this may cause higher peaks in the performance monitor. Also, if the server is under heavy load, causing additional load by backup compression might not be ideal in terms of response times for users. On the other hand, compression has its benefits, combining a smaller backup size stored on the disk and the time needed to create a backup. Actually, there's one more important benefit, and this one is the time needed to restore, which is also decreased with compressed backup versus an uncompressed one, because the system gets to read a smaller file from the disk or network.
If you would like to configure these settings on just one server, you'll be fine with the GUI of our SQL Server Management Studio, but if you are preparing a script for a post-deployment configuration on more servers, you will most probably use an SQL script that can perform such a configuration. Most of the configuration at the server level is performed with a stored procedure called sp_configure.
If you just run the procedure without any parameters, it will display all basic parameters and their values that are configured on the server, as shown in the following screenshot:
Fig. 2.12 - Configuration options through sp_configure
You don't need to memorize all the options as we won't configure all of these. It's just a basic set of the items; as you can see, second from the bottom is an option called show advanced options, which will display more of the items for configuration. Backup compression is listed in the basic set and can be configured with the following code:
USE master
GO
EXEC sp_configure 'backup compression default',1
GO
RECONFIGURE
GO
Some other options that we will explore are visible only when you display the advanced features. To display all the advanced features, you can simply run sp_configure again and you'll set the option for show advanced options, as in the previous example. With advanced options displayed, SQL Server will let you configure 85 (on SQL Server 2019; with other versions, this may be different) options, in contrast to 25 when you display only the basic set.
With regard to post-installation configuration, we'll configure the memory and CPU settings for our server too. By default, SQL Server is allowed to use as much memory as possible and the Windows operating system won't make any larger reserve of other applications or even for itself, so you can limit the amount of memory available to SQL Server. You should reserve memory to the operating system so that it does not get unresponsive under heavy load on SQL Server. The following screenshot shows how to configure SQL Server memory from SQL Server Management Studio:
Fig. 2.13 – SQL memory configuration
There have been many situations when DBAs could not connect to the SQL Server operating system because all the memory was allocated to SQL Server itself. You can limit the memory available to the SQL Server with a setting called Maximum server memory (in MB). This setting has to be considered carefully as you need to keep some memory for the operating system. As a general guideline, you need to keep 1 to 2 gigabytes (GB) for the operating system and then 1 GB for each 8 to 16 GB on the system. So, for a SQL Server with 256 GB Random-Access Memory (RAM), you would configure the max server memory setting to a value between 224 and 240 GB. The code to perform the configuration is as follows (don't forget that the procedure is using megabytes (MB) as a unit of measure):
sp_configure 'max server memory',245760
Note
SQL Server editions provide different limits to use system memory on SQL Server. The Standard edition can use only up to 128 GB RAM for the SQL Server buffer pool, whereas the Enterprise edition can use all the system memory available on the server. You can find different limits for the editions available in the online documentation. The differences in the editions are not only about RAM, but also about CPU and core support for different SQL Server stock keeping units (SKUs).
Another situation where configuring SQL Server memory is very important is in a multi-instance and multi-service environment. Consider that you are running multiple instances of SQL Server on the same host, where you would like to limit and control how much system memory can be used by each instance. This also applies in scenarios where you run multiple different services such as Database Engine, Analysis Services, and Reporting Services, where you can limit the amount of memory used by Database Engine. Not all services have the feature to limit memory usage, so you need to consider all performance impacts that can be caused by your configuration.
There is another setting that can be very useful when you're troubleshooting your SQL Server and it gets unresponsive—this is called DAC. By default, such a connection is not allowed remotely, and you can only connect to DAC locally while being logged on to the server. If the system faces performance issues and even the Windows Server won't allow you to connect via Remote Desktop, you can connect to DAC remotely if you have enabled this setting. To enable remote DAC, you need to run the following procedure:
sp_configure 'remote admin connections',1
GO
RECONFIGURE
Additional items that we will configure have an effect on the performance of SQL Server and require a deeper understanding of your workload, hardware, and requirements of your applications. These will include configuring parallelism on your server.
There are two main configuration items that we're using to control parallelism at the server level, and these are as follows:
- Max degree of parallelism (default is 0)
- Cost threshold for parallelism (default is 5)
The first one sets the maximum amount of threads to be used in a query when it's processed in parallel. This does not mean that each query will be processed with multiple threads, and, if it will be, it can be a lower amount. It's a default server setting that you can later override on different levels, but as a general option, it's a good idea to configure this value. What is the proper value depends greatly on several factors, and they are as follows:
- Your hardware: CPUs and cores
- Your workload: Online Transaction Processing (OLTP) versus Online Analytical Processing (OLAP)
- Your environment: Physical versus virtual
In most cases, you can examine the number of CPUs and cores on your system and assign a value that determines the number of cores on one CPU. So, for example, if you have two eight-core CPUs used for your SQL Server, you will configure the max degree of parallelism to the value of eight. Again, you can use SQL Server Management Studio or the sp_configure procedure. At the same time, in the GUI, you can also update the cost threshold for parallelism value, which is something such as the virtual cost of a query when the query optimizer component is generating a serial or parallel plan.
If the cost is too low there might be too many parallel plans, which can increase the load on the CPU and memory of your SQL Server. This configuration is subject to testing, but you can start with values ranging from 20 to 50 and evaluate the load on your SQL Server and your application performance. Let's look at the following screenshot from SQL Server Management Studio, where we can see Server Properties – sql01:
Fig. 2.14 — SQL Server Management Studio — Server Properties
Once you have deployed and configured your SQL Server, you can create a performance baseline.