Install and configure SQL Server instances and features

Post-installation server configuration

After you install SQL Server, there are several changes to make or confirm on the OS and in settings for SQL Server.

Post-installation checklist

You should run through the following checklist on your new SQL Server instance. The order of these items isn’t necessarily specific. Many deal with SQL Server and/or Windows configuration settings. You want to evaluate whether these are appropriate for your environment, but you should consider and apply them to most SQL Server installations.

• Check your SQL Server patch level version and apply patches if necessary.

• Review maximum server memory settings for other features.

• Review the surface area configuration facet.

• Set up SQL Agent.

• Turn on TCP/IP if needed.

• Verify server power options.

• Configure antivirus exclusions for SQL Server processes and files.

• Evaluate whether Lock Pages in Memory is necessary.

• Review the size and location of the Windows page file.

• Set up scheduled backups, index maintenance, log retention maintenance, and integrity checks.

• Back up service master and database master keys.

• Increase SQL Agent and SQL Error Log retention from the defaults.

• Suppress successful backup messages.

• Increase default SQL Agent history retention.

Let’s look at each of these in more detail in the following subsections.

Check your SQL Server patch level version and apply patches if necessary

After you install SQL Server, check the version number against the latest cumulative updates list—especially if you did not opt to or could not use Windows Update during SQL Server Setup. You can view the version number in SQL Server Management Studio’s Object Explorer or via a T-SQL query on either of the following built-in functions:

SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion');
SELECT SERVERPROPERTY('Edition');

While you’re at it, double-check that you installed the right edition of SQL Server, too!

Review maximum server memory settings for other features

Other features of SQL Server have their own maximum server memory settings. As you will notice by their default settings, for servers on which both the Database Engine and SSAS and/or SSRS are installed, competition for and exhaustion of memory is possible. It is recommended that you protect the Database Engine by lowering the potential memory impact of other applications.

Limit SSAS memory

SQL Server Analysis Services (SSAS) has not just one maximum server memory limit, but five, and you can enforce limits by hard values in bytes or by a percentage of total physical memory of the server.

You change these memory settings via SSMS by connecting to the SSAS instance in Object Explorer. To start, right-click the server and select Properties on the shortcut menu. Some of the memory settings described here are identical for Multidimensional and Tabular installations, whereas others are for Tabular mode only:

• LowMemoryLimit. A value that serves as a floor for memory, but also the level at which SSAS begins to release memory for infrequently used or low-priority objects in its cache. Below this level, no memory maintenance is performed by SSAS. The default value is 65, or 65 percent of total server physical memory (or technically the virtual address space, but SSAS, among other features, is no longer supported on 32-bit systems, so this is not a concern).

• TotalMemoryLimit. A value that serves as a threshold for SSAS to begin to release memory for higher priority requests. This is not a hard limit. The default is 80 percent of total server memory.

• HardMemoryLimit. A hard memory limit that leads to more aggressive pruning of memory from cache and potentially to the rejection of new requests. By default, this is displayed as 0, and is effectively the midway point between the TotalMemoryLimit and the server physical memory. The TotalMemoryLimit must always be less than the HardMemoryLimit.

• VertiPaqMemoryLimit. For SSAS installations in Tabular mode only, a value that serves as a memory limit for the VertiPaq processing engine. The default is 60, or 60 percent of server physical memory. Above this percentage, and only if VertiPaqPagingPolicy is turned on (it is by default), SSAS begins to page data to the hard drive using the OS page file. Paging to a drive can help prevent out-of-memory errors when the HardMemoryLimit is met.

• QueryMemoryLimit. A value that can limit the amount of memory used by individual DAX queries, preventing any one query from dominating memory. For any individual query, this setting can be overridden by a new XMLA property, DbpropMsmdRequestMemoryLimit, specified for the query connection. This setting can be specified as a percentage (values <=100) or as a number of bytes greater than 100. The default setting of 0 implies no limit to the memory of individual queries.

Figure 4-4 shows the General page of the Analysis Server Properties dialog box, as started in Object Explorer in SSMS, and the locations of the preceding memory configuration properties with their defaults in SQL Server 2019 for a Tabular mode installation of SSAS. Note that the Show Advanced (All) Properties check box is checked.

Figure 4-4 The General page in the Analysis Server Properties dialog box showing the default settings.

Limit SSRS memory

Four options are available for limiting SQL Server Reporting Services (SSRS) memory: MemorySafetyMargin, MemoryThreshold, WorkingSetMaximum, and WorkingSetMinimum. All four are based on numbers contained in tags within a config file, so be sure to make a backup of it before editing. You can configure memory settings only in the RSReportServer.config file, which is a text file stored at %ProgramFiles%\Microsoft SQL Server Reporting Services\SSRS\ReportServer.

Two of the settings are in the config file by default; two more are available to administrators to use in advanced scenarios. Let’s look at each one:

• MemorySafetyMargin. The percentage of WorkingSetMaximum that SSRS will use before taking steps to reduce background task memory use and prioritize requests coming from the web service, attempting to protect user requests. User requests could still be denied.

• MemoryThreshold. The percentage of WorkSetMaximum at which SSRS will deny new requests, slow down existing requests, and page memory to a hard drive until memory conditions improve.

Two more settings are given values automatically upon service startup, but you can override them in the config file. Two older memory settings from SQL Server 2005 with which SQL DBAs might be familiar are MemoryLimit and MaximumMemoryLimit, but these two values have been ignored since SQL Server 2008.

• WorkingSetMaximum. By default, this is the server’s total physical memory. This setting does not appear by default in the config file, but you can override it to reduce the amount of memory of which SSRS will be aware. This value is expressed in kilobytes of memory.

• WorkingSetMinimum. By default, this value is 60 percent of WorkingSetMaximum. If SSRS needs memory below this value, it will use memory and not release it due to memory pressure. This setting does not appear by default in the config file, but you can override it to increase the variability of SQL SSRS’s memory use.

These four settings can appear in the rsreportserver.config file. As demonstrated here, you could override the default settings to 4 GB maximum and 2 GB minimum (each expressed in KB):

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>
<WorkingSetMaximum>4194304</WorkingSetMaximum>
<WorkingSetMinimum>2097152</WorkingSetMinimum>

Limit Machine Learning Server memory

Like SSAS and SSRS, the Machine Learning Server has a config file at %ProgramFiles%\Microsoft SQL Server\MSSQL15.instancename\MSSQL\Binn\rlauncher.config.

By default, Machine Learning Server is similar to 20 percent of total server memory. You can override this by adding a tag to the config file to provide a value for MEMORY_LIMIT_PERCENT. This value is not in the config file by default.

Remember to make a backup of this config file before editing. The following is an example of the contents of the rlauncher.config file, with the default memory limit changed to 25 percent:

RHOME=C:\PROGRA~2\MICROS~1\MSSQL1~4.SQL\R_SERV~2
MPI_HOME=C:\Program Files\Microsoft MPI
INSTANCE_NAME=SQL2K22
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:\Program Files\Microsoft SQL
Server\MSSQL16.SQL2K22\MSSQL\ExtensibilityData
PKG_MGMT_MODE=0
MEMORY_LIMIT_PERCENT=25

Review the surface area configuration

If you are a veteran SQL Server DBA, you will remember when the SQL Server Surface Area Configuration was a separate application. Now, surface area settings are a facet, accessed via the Facets dialog box in SSMS starting with SQL Server 2008.

To view surface area configuration settings in SSMS, open Object Explorer, connect to the SQL Server, right-click the server, and select Facets on the shortcut menu. (The Facets window sometimes takes a moment to load.) Then, in the dialog box that opens, change the value in the list box to Surface Area Configuration.

Most of these options should remain off unless needed because they present a specific potential for misuse by an administrator or unauthorized user. In typical installations of SQL Server 2022, however, you should consider enabling three of these options:

• Database Mail. This should be enabled on most instances to allow SQL Server to, at the very least, send out a message in case of a high-severity incident or job failure, and to allow developers to send custom email messages using the system procedure sp_send_dbmail. You also can turn this setting on or off via the Database Mail XPs option in sp_configure. (More about this setting in Chapter 9, “Automate SQL Server administration.”)

• Remote Dedicated Admin Connection. This could be particularly useful for bypassing a malfunctioning login trigger or Resource Governor. You also can turn this setting on or off via the remote admin connections option in sp_configure. (More on this setting in Chapter 13: “Protect data through classification, encryption, and auditing.”)

• CLR Integration. Turn on if you need to use SSIS or to write CLR objects. You also can turn this setting on or off via the clr_enabled option in sp_configure.

You should turn on other options in the Surface Area Configuration only if they are specifically required by an application and you are aware of the potential security concerns.

Set up SQL Agent

There are several post-installation tasks to set up in SQL Agent before SQL Server can begin to help you automate, monitor, and back up your new instance.

• Chapter 8, “Maintain and monitor SQL Server,” and Chapter 9 cover SQL Agent and monitoring topics in detail.

You will likely want to do the following:

1. Change the SQL Agent service from Manual to Automatic startup.

2. Set up a Database Mail account and profile (see Chapter 9) to send email notifications for alerts or job status notifications.

3. Set up an operator for a distribution group of IT professionals in your organization who would respond to a SQL Server issue.

4. Configure SQL Server Agent to use Database Mail, including a fail-safe operator.

5. Set up SQL Server Alerts for desired errors and high severity (Severity 21+) errors.

At the very least, these steps are put in place so that SQL Server can send out a call for help. Even if you have centralized monitoring solutions in place, the most rare and severe of errors should be important enough to warrant an email.

You can choose to configure many Windows Management Instrumentation (WMI) conditions, Performance Monitor counter conditions, and SQL Server Error messages by number or severity in SQL Server Alerts. However, do not overcommit your inboxes, and do not set an inbox rule to Mark As Read and file away emails from SQL Server. By careful selection of emails, you can assure yourself and your team that emails from SQL Server will be actionable concerns that rarely arrive.

• For much more information on maintaining and monitoring SQL Server, see Chapter 8.

Turn on TCP/IP if needed

Depending on the edition you have installed, the common network protocol TCP/IP is off by default. The only protocol that is on is Shared Memory, which allows only local connections. You will likely not end up using Shared Memory alone to connect to the SQL Server for common business applications that use multiple servers for database, web, and application tiers.

When you connect to SQL Server using SSMS while local to the server, you connect to the Shared_Memory endpoint whenever you provide the name of the server, the server\instance, localhost, dot character (.), (local), .\instance, or (local)\instance.

TCP/IP, however, is ubiquitous in many SQL Server features and functionality. Many applications will need to use TCP/IP to connect to the SQL Server remotely. Many SQL Server features require TCP/IP to be enabled, including the Remote Dedicated Admin Connection (DAC), the availability groups listener, and Kerberos authentication.

To configure TCP/IP, open the SQL Server Configuration Manager application locally on the server. Then, in the left pane, select SQL Server Network Configuration. Browse to the protocols for your newly installed instance of SQL Server. The default instance of SQL Server, here and in many places, will appear as MSSQLSERVER.

You can also enable TCP/IP for a SQL Server instance with PowerShell:

Import-Module SqlServer
$wmi = new-object('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')
#Path to the local server
$path = "ManagedComputer[@Name='$env:COMPUTERNAME']/"
$path = $path+"ServerInstance[@Name='SQL2K22']/ServerProtocol[@Name='Tcp']"
#Enable the TCP protocol on the local server, on the named instance SQL2K22
$TCPIP = $wmi.GetSmoObject($path)
$TCPIP.IsEnabled = $true
$TCPIP.Alter()
$TCPIP.IsEnabled
#Restart SQL Server Database Engine service to apply the change

After turning on TCP/IP, regardless of what method you use, you need to restart the SQL Server Database Engine service for it to take effect.

Verify server power options

The Windows Server Power Options setting should be set to High Performance for any server hosting a SQL Server instance.

In other power plans, Windows might not operate the processor at maximum frequency during normal or even busy periods of SQL Server activity. This applies to physical or virtual Windows servers.

Review this setting and ensure that the group policy will not change it back to Balanced or another setting. Also ensure that group preferences are configured with High Performance selected for new SQL Servers. Finally, you may also need to check that the BIOS is also configured for High Performance.

Configure antivirus exclusions for SQL Server processes and files

Configure any antivirus software installed on the SQL Server to ignore scanning files with extensions used by your SQL Server data and log files. Typically, these will be MDF, LDF, and NDF files.

Also, configure any antivirus programs to ignore folders containing SQL Server files. These could include:

• Full-text catalog files

• Backup files

• Replication snapshot files

• SQL Server trace (TRC) files

• SQL Audit files

• Analysis Services database

• Log and backup files

• FILESTREAM and FileTable folders

• SSRS temp files and log files

Processes might also be affected, so set antivirus programs to ignore the programs for all instances of the SQL Server Database Engine service, Reporting Services service, Analysis Services service, and R Server (RTerm.exe and BxlServer.exe).

In SQL Server FCIs and availability groups, configure antivirus software to exclude the MSCS folder on the quorum drive if in use, the MSDTC directory on the MSDTC share, and the Windows\Cluster folder on each cluster node, if they exist.

Optimize for ad hoc workloads

The server-level setting Optimize for Ad Hoc Workloads doesn’t have the most intuitive name. We are not optimizing ad hoc queries; we are optimizing SQL Server memory usage to prevent ad hoc queries from consuming unnecessary cache.

• For more about the Optimize for Ad Hoc Workloads setting, see Chapter 3.

For the unlikely scenario in which a large number of queries are executed only two times, setting this option to True would be a net negative for performance. Enabling this setting can also affect performance tuning for single-use queries.

• For more about cached execution plans, read Chapter 14.

Evaluate whether Lock Pages in Memory is necessary

Consider using the Lock Pages in Memory policy for environments in which instances of SQL Server are expected to experience memory pressure due to other applications, server limitations, or overallocated virtualized systems. This is an in-depth topic to be carefully considered.

• For more about the Lock Pages in Memory setting, see Chapter 2, “Introduction to database server components.”

• For more about the Windows page file, see Chapter 3.

SELECT sql_memory_model_desc
FROM sys.dm_os_sys_info;
--CONVENTIONAL = Lock pages in memory privilege is not granted
--LOCK_PAGES = Lock pages in memory privilege is granted
--LARGE_PAGES = Lock pages in memory privilege is granted in Enterprise mode
--  with Trace Flag 834 ON

Review the size and location of the Windows page file

The page file is used to page out system memory. It can also capture a system memory dump for crash forensic analysis, a factor that dictates its size on modern operating systems with large amounts of memory. Therefore, the general recommendation for the system page file is that it should be at least the same size as the server’s amount of physical memory. This is also why the page file is best moved to its own volume, away from the OS volume, so that it does not unexpectedly grow and create space issues.

• For more guidance on the operating system page file, see the section “Configure the operating system page file” in Chapter 3.

Set up scheduled backups, index maintenance, log retention maintenance, and integrity checks

Backups are a critical part of disaster recovery. They should begin as soon as possible after installation, and before users or applications begin to use the SQL Server.

Generate database backups, at least of the master and msdb system databases, right away. You should also back up other SQL Server Setup–created databases, including ReportServer, ReportServerTempDB, and SSISDB, as soon as possible.

• For more information on backups, index maintenance, and monitoring, see Chapter 11.

As soon as your new SQL Server instance has databases in use, regularly perform selective index maintenance and integrity checks that consider the current fragmentation levels of indexes rather than performing index maintenance on entire databases. In many cases, statistics maintenance may be more effective in the shorter term.

• For more information on automating maintenance, see Chapter 9.

Back up service master and database master keys

You should back up service master keys and any database master keys as they are created, securely storing their information.

• For more information on service master and database master keys, see Chapter 13.

To back up the instance service master key, use the following command:

BACKUP SERVICE MASTER KEY TO FILE = 'localfilepath_or_UNC'
ENCRYPTION BY PASSWORD = 'complexpassword'

As soon as database master keys come into existence in each user database—for example, as you implement features like transparent data encryption (TDE) or column data encryption, back up individual database master keys as follows:

BACKUP MASTER KEY TO FILE = 'localfilepath_or_UNC' ENCRYPTION BY PASSWORD =
'complexpassword'

If you implement TDE, Always Encrypted, native backup encryption, column encryption, or any other native or external solutions that generate certificates, keys, and/or passwords, develop a secure storage and retrieval method inside your enterprise. Failure to back up master and database master keys could compromise future disaster recovery attempts!

Increase SQL Agent and SQL error log retention from the defaults

By default, SQL Server maintains the current SQL Server Error Log plus six more historical error logs. Logs are cycled each time the SQL Server service is started.

One fun weekend of server troubleshooting or maintenance where the SQL Server service is restarted many times could wipe out a significant amount of your error history. This could make the task of troubleshooting periodic or business cycle–related errors difficult or impossible.

You need visibility into errors that occur only during a monthly processing, monthly patch day, or periodic reporting, for example. Follow these steps:

1. In SQL Server Management Studio, in Object Explorer, connect to the SQL Server instance.

2. Expand the Management folder, right-click SQL Server Logs, and select Configure in the shortcut menu.

3. Select the Limit the Number of Error Logs Before They Are Recycled check box.

4. For the Maximum Number of Error Log Files setting, type a value larger than 6. You might find that a value between 25 and 50 will result in more useful log history contained for multiple business cycles.

On SQL Server instances that generate a large amount of log noise, consider other options to reduce the clutter of the SQL Server Error Log, including Trace Flag 3226 to suppress the logging of successful backup operations. (Much more on this in the next section.)

You may also choose to configure a SQL Agent Job to manually cycle the SQL Server Error Log using sp_cycle_errorlog so that no one log file contains so much data it becomes unwieldly for scan and analysis. Consider scheduling sp_cycle_errorlog to execute weekly, and keep 50 SQL Agent error jobs, leaving at most 50 weeks of history.

Suppress successful backup messages

By default, SQL Server writes an event to the SQL Server error log upon a successful database backup, whether it be FULL, DIFFERENTIAL, or TRANSACTION LOG.

On instances with many databases, and with many databases in the full recovery model with regular transaction log backups, the amount of log activity generated by just their successful frequent log backups could flood the log with clutter, lowering log history retention.

• For more on backups, see Chapter 10, “Develop, deploy, and manage data recovery.”

SQL Server Trace Flag 3226 controls an option at the instance level to suppress successful backup notifications.

There are many trace flags available to administrators to alter default behavior—many more options than there are user interfaces to accommodate them in SQL Server Management Studio. Take care when turning them on and understand that many trace flags are intended only for temporary use when aiding troubleshooting. Because Trace Flag 3226 is intended to be a permanent setting, simply enabling the trace flag by using DBCC TRACEON is not sufficient, as the trace flag will no longer be active following a SQL Server service restart. Instead, add the trace flag as a startup parameter to the Database Engine service by using SQL Server Configuration Manager. In the Properties of the SQL Server service, go to the Startup Parameters tab, and use the syntax -Tflagnumber. This field is essentially adding parameters that are passed to the sqlserver.exe executable. For example, enter -T3226, then select Add. The change will not take effect until the SQL Server Database Engine service is restarted.

• For more information on SQL Server Configuration Manager, refer to Chapter 1, “Get started with SQL Server tools.”

Increase default SQL Agent history retention

Similarly, you might find that the SQL Server Agent history is not sufficient to cover an adequate amount of job history, especially if you have frequent job runs.

You can use SSMS to change the job history settings for SQL Server Agent. In Object Explorer, connect to the SQL Server instance. Then right-click SQL Server Agent, select Properties from the shortcut menu, and select the History page.

This page is not intuitive and can be confusing. The first option, Limit Size of Job History Log, is a rolling job history retention setting. Consider adding zeros to increase the maximum log history size in rows from the default of 1,000 to 10,000 or more, and increasing the maximum job history per job in rows from the default of 100 to 2,000 or more. This data is stored in the msdb system database and will cause that database to grow larger over time. Consider pre-allocating some additional file space to the msdb data file now.

Heads up: The second option on the History page, Remove Agent History, along with its corresponding Older Than text box, is not a rolling job history retention setting. Rather, it is an immediate and manual job history pruning. Select this second check box and select OK. When you return to the History page, you will find the second check box is cleared. Behind the scenes, SQL Server Management Studio immediately ran the msdb.dbo.sp_purge_jobhistory stored procedure to remove the job history once.