Installing and configuring SQL Server instances and features

Post-installation server configuration

After you install SQL Server, there are a number of changes to make or confirm on the Windows Server 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, and many deal with SQL server and/or Windows configuration settings. Evaluate whether these are appropriate for your environment, but you should consider and apply them to most SQL Server installations.

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

2. Review Maximum server memory settings for other features.

3. Review Surface Area Configuration.

4. Set up SQL Agent.

5. Turn on TCP/IP if needed.

6. Verify power options for the server.

7. Configure antivirus exclusions for SQL Server processes and files.

8. Enable the server setting “optimize for ad hoc workloads.”

9. Evaluate whether Lock pages in memory is necessary.

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

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

12. Backup service master and database master keys.

13. Increase SQL Agent and SQL Error log retention from the defaults.

14. Suppress successful backup messages.

Let’s take a look at each of these in more detail in the subsections that follow.

Check your SQL Server version

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 the following built-in functions:

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

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

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 SQL Server Database Engine and SQL Server Analysis Services and/or SQL Server Reporting Services 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.

Limiting SQL Server Analysis Services 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 can change these memory settings via SSMS by connecting to the SSAS instance in Object Explorer. Right-click the server, and then, on the shortcut menu, click Properties. Some of the memory settings described here are identical for Multidimensional and Tabular installations, some 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 the Virtual Address Space technically, but SSAS, among other features, is no longer supported on 32-bit systems, and 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. It’s important to note that this is not a hard limit. The default is 80 percent of total server memory.

• HardMemoryLimit. This is a hard memory limit that will lead 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, this limit has a default of 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. New to SQL Server 2019, this 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-6 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 option to “Show Advanced (All) Properties” is checked.

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

Limiting SQL Server Reporting Services memory

Four options are available for limiting SQL Server Reporting Services memory utilization, and all 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 that is 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 take a look at each one:

• MemorySafetyMargin. The percentage of WorkingSetMaximum that SQL Server Reporting Services will use before taking steps to reduce background task memory utilization 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 SQL Server Reporting Services will deny new requests, slow down existing requests, and page memory to a hard drive until memory conditions improve.

Two more settings are instead 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 those two values have been ignored since SQL Server 2008.

• WorkingSetMaximum. By default, this is the total server’s 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 SQL Server Reporting Services will be aware. This value is expressed in kilobytes of memory.

• WorkingSetMinimum. By default, this value is 60 percent of the WorkingSetMaximum. If SQL Server Reporting Services 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 Server Reporting Service’s memory utilization.

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>

Limiting Machine Learning Server memory

Similar to 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=SQL2K19
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:\Program Files\Microsoft SQL
Server\MSSQL15.SQL2K19\MSSQL\ExtensibilityData
PKG_MGMT_MODE=0
MEMORY_LIMIT_PERCENT=25

The previous sample script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads.

Surface Area Configuration

If you are a veteran SQL Server DBA, you will remember when SQL Server Surface Area Configuration was a separate application. Surface Area Configuration is now considered a facet, accessed via the Facets dialog in SSMS starting with SQL Server 2008.

To view Surface Area Configuration in SSMS, in Object Explorer, connect to the SQL Server, right-click the server, and then, on the shortcut menu, click Facets. (Note that this window sometimes takes a moment to load.) In the dialog box that opens, change the value in the list box to Surface Area Configuration.

Keep in mind that 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 2019, however, you should consider enabling three of these options:

• Database Mail (more about this in Chapter 9: “Automating SQL Server administration”). This should be enabled on most instances, to allow SQL Server at the very least to send out a message in case of a high severity or job failure, and also to allow developers to send custom email message 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.

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

• CLR Integration, which you will need to turn on if you need to use SQL Server Integration Services, or if you need 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 Surface Area Configuration only if they are specifically required by an application and you are aware of the potential security concerns.

Setting 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.

• Chapters 8 and 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. To send email notifications for alerts or job status notifications, you must set up a Database Mail account and profile (see Chapter 9).

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 a large number of Windows Management Instrumentation (WMI) conditions, Perfmon 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 Managing and Monitoring SQL Server, see Chapter 13.

Enabling the TCP/IP network protocol

The common network protocol TCP/IP is off by default, and 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, the dot character (“.”), or (local), or .\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 Always On availability groups listener, and Kerberos authentication.

To configure the TCP/IP protocol, open the SQL Server Configuration Manager application locally on the server, in the left pane, click 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 the TCP/IP protocol 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='SQL2K19']/ServerProtocol[@Name='Tcp']"
#Enable the TCP protocol on the local server, on the named instance SQL2K19
$TCPIP = $wmi.GetSmoObject($path)
$TCPIP.IsEnabled = $true
$TCPIP.Alter()
$TCPIP.IsEnabled
#Restart SQL Server Database Engine service to apply the change

The previous sample script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads.

After turning on the TCP/IP protocol via any method, you need to restart the SQL Server Database Engine service for it to take effect.

Verifying 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 this setting back to Balanced or another setting. Also ensure that group preferences are configured for High Performance for new SQL Servers.

Configuring antivirus exclusions

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.

Also, configure any antivirus programs to ignore folders containing SQL Server files. This 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

• SQL Server Reporting Services 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 Failover Cluter Instances (FCIs) and availability groups, also 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.

Enable 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 2: Introducing database server components.

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. However, like other design concepts in databases, we find that there are either one or many. There is no two.

• To read more about cached execution plans, see Chapter 14.

Lock Pages in Memory

You should consider using the Lock Pages in Memory setting 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.

• 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

The previous sample script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads.

Backups, index maintenance, and integrity checks

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

Begin taking database backups, at least of the master and msdb databases. You should also back up other SQL Server Setup-created databases, including ReportServer, ReportServerTempDB, and SSISDB right away.

• 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 take into account the current fragmentation levels of indexes, rather than performing index maintenance on entire databases.

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

Backing 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 6.

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

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

And 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'

The previous sample script and all scripts for this book are available for download at https://www.MicrosoftPressStore.com/SQLServer2019InsideOut/downloads.

If you implement Transparent Data Encryption, 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!

Increasing default error and agent history retention

By default, SQL Server maintains the current SQL Server error log plus six more error logs of history. Logs are cycled each time the SQL Server service is started.

One eventful, 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.

In SQL Server Management Studio, in Object Explorer, connect to the SQL Server instance. Expand the Management folder, right-click SQL Server Logs, and then, on the shortcut menu, click Configure. Select the Limit The Number Of Error Logs Before They Are Recycled check box and 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 Servers that generate a large amount of log “noise”, consider other options to reduce the clutter of the SQL Error Log, including Trace Flag 3226 to suppress the logging of successful backup operations (much more on that in the next section).

You may also choose to configure a SQL Agent Job to manually cycle the SQL Error Log using sp_cycle_errorlog, so that no one log file contains too much data to be unwieldly for scan and analysis.

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.

To change the history settings for SQL Server Agent, in SSMS, in Object Explorer, connect to the SQL Server instance. Right-click SQL Server Agent, and then click Properties. Click the History page. This page is not intuitive and can be confusing. The first option, Limit Size Of The Job History Log, is a rolling job history retention setting. Consider increasing the maximum log history size in rows from the default of 1,000 to 10,000 or more, and also increase 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 now some additional file space to the msdb data file.

The second option, Remove Agent History, along with its companion 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 then click OK and return to this page; you will find the second check box is cleared. Behind the scenes, SQL Server Management Studio ran the msdb.dbo.sp_purge_jobhistory stored procedure to remove job history manually.

Suppress successful backup messages

By default, SQL Server writes an event to the 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 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, “Developing, deploying, and managing 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.

As Trace Flag 3226 is intended to be a permanent setting, simply starting the trace by using DBCC TRACEON is not sufficient, given that 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 SQL Server 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, add “-T3226”, then click Add. The change will not take effect until the SQL Server Database Engine service is restarted.

• For more information on SQL Server Configuration Manager, see Chapter 1.