Provisioning databases

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

2. Configure the Maximum Server Memory setting.

3. Surface Area Configuration.

4. Set up SQL Agent.

5. Turn on TCP/IP.

6. Verify power options.

7. Configure antivirus exclusions.

8. Optimize for ad hoc workloads.

9. Lock pages in memory.

10. System page file.

11. Backups, index maintenance, and integrity checks.

12. Backup service master and database master keys.

13. Default log retention.

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

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

Configure the Maximum Server Memory setting

We discussed the Maximum Server Memory setting in Chapter 3, in the section “Configuration settings,” but it is definitely on the list to set post-installation for any new SQL Server instance.

This setting is accessible via SQL Server Management Studio, in Object Explorer, on the Server Properties page. On the Memory page, look for the Maximum Server Memory (In MB) box. This value defaults to 2147483647, which does not limit the amount of memory SQL Server can access in the Windows server. This value is also available in sp_configure, when Show Advanced Options is turned on, under the configuration setting Max Server Memory (MB).

You can read more about why you should limit the Max Server Memory setting in Chapter 3.

An example of configuring a Windows Server with one SQL Server instance and 64 GB of memory, to use a Max Server Memory setting of 58982 MB, as illustrated in Figure 4-5.

Figure 4-5 The Server Properties dialog box with the Memory page selected.

Keep lowering this number if you have other applications on the server that will be consuming memory, including other SQL Server instances. We discuss this more in the next section. If you observe over time that during normal operations your server’s total memory capacity is nearly exhausted (less than 2 GB free), lower the Maximum Server Memory setting further. If there is sufficient padding available (more than 8 GB free), you can consider raising this setting.

Lowering this setting after installation and during operation does not return SQL Server memory back to the OS immediately; rather, it does so over time during SQL Server activity. Similarly, increasing this setting will not take effect immediately.

Just above the Maximum Server Memory setting is the Minimum Server Memory setting, which establishes a floor for memory allocation. It is not generally needed. You might find this setting useful for situations in which the total system memory is insufficient and many applications, including SQL Server instances, are present. The minimum server memory is not immediately allocated to the SQL Server instance upon startup; instead, it does not allow memory below this level to be freed for other applications.

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 likely in environments with a significant amount of activity. 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 has not just one maximum server memory limit, but four, 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 via SQL Server Management Studio by connecting to the SQL Server Analysis Services instance in Object Explorer. Right-click the server, and then, on the shortcut menu, click Properties. The memory settings described here are available and nearly identical for Multidimensional and Tabular installations:

• LowMemoryLimit. A value that serves as a floor for memory, but also the level at which SQL Server Analysis Services begins to release memory for infrequently used or low-priority objects in its cache. The default value is 65, or 65 percent of total server physical memory (or the Virtual Address Space technically, but Analysis Services, among other features, is not supported on 32-bit systems, and so this is not a concern).

• TotalMemoryLimit. A value that serves as a threshold for SQL Server Analysis Services 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 SQL Server Analysis Services installations in Tabular mode only, the Low Memory setting is similarly enforced by the VertiPaqMemoryLimit, which has a default of 60, or 60 percent of server physical memory. After this threshold is reached, and only if VertiPaqPagingPolicy is turned on (it is by default), SQL Server Analysis Services 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.

Figure 4-6 shows the General page of the Analysis Server Properties dialog box, as started in Object Explorer, and the locations of the preceding memory configuration properties.

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

Limiting SQL Server Reporting Services memory 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.

Four options are available for limiting SQL Server Reporting Services memory utilization, and all are based on numbers contained in tags within this .config file, so be sure to make a backup of it before editing.

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 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 Services’ memory utilization.

These four settings can appear in the rsreportserver.config file. As demonstrated here, you should 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 SQL Server Analysis Services and SQL Server Reporting Services, the Machine Learning Server has a .config file at %ProgramFiles%\Microsoft SQL Server\MSSQL14.MSSQLSERVER\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. 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=SQL2K17
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:\Program Files\Microsoft SQL
Server\MSSQL14.SQL2K17\MSSQL\ExtensibilityData
PKG_MGMT_MODE=0
MEMORY_LIMIT_PERCENT=25

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 was moved to the Facets menu starting with SQL Server 2008.

To view Surface Area Configuration in SQL Server Management Studio, 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 all of these options should remain off unless needed and properly configured because they present a specific potential for misuse by an administrator or unauthorized user. In typical installations of SQL Server 2017, however, you will need three of these options:

• Database Mail (more about this in Chapter 14. You also can turn this setting on or off via the Database Mail XPs option in sp_configure.

  NOTE

  Keep in mind that enterprise Simple Mail Transfer Protocol (SMTP) servers have an “allow list” of IP addresses; you will need to add this server’s IP to this list to send email.

• Remote Dedicated Admin Connection (more on this in Chapter 7). 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 to use SQL Server Integration Services. 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 a third-party application and you are aware of the potential security concerns.

Setting up SQL Agent

There are a number of 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 7 and Chapter 14 cover these topics in greater detail.

You need 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 14).

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.

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

Turning on TCP/IP

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 to connect to the SQL Server for common business applications; rather, you’ll use it only for local connections in the server.

When you connect to SQL Server using SQL Server Management Studio while signed in 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).

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 it to be turned on, including the Dedicated Admin Connection (DAC), the AlwaysOn availability groups listener, and Kerberos authentication.

In the SQL Server Configuration Manager application, 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.

After turning on the TCP/IP protocol, you need to do a manual restart of the SQL Server service.

Turning on Named Pipes is not required or used unless an application specifically needs it.

Verifying power options

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

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 the policy and ensure that the group policy will not change this setting back to Balanced or another setting.

Configuring antivirus exclusions

Configure any antivirus software installed on the SQL Server to ignore scanning 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 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 FCIs (and also for availability groups), also configure antivirus software to exclude the MSCS folder on the quorum drive, the MSDTC directory on the MSDTC share, and the Windows\Cluster folder on each cluster node, if they exist.

Optimizing for ad hoc workloads

The server-level setting to 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.

For the unlikely scenario in which a large number of queries are called 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 9.

Lock pages in memory

You should consider using this 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; however, this is an in-depth topic to be considered carefully.

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
--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
from sys.dm_os_sys_info;

Backups, index maintenance, and integrity checks

Backups are a critical part of your disaster recovery, and they should begin right away.

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

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

As soon as your new SQL Server instance has databases in use, you should be performing selective index maintenance and integrity checks, regularly.

For more information on automating maintenance, see Chapter 13.

Backing up service master and database master keys

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

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 they come into existence as needed, in each user database, back up individual database master keys, as follows:

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

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, which should be rare, but you also can manually cycle them via the sp_cycle_errorlog.

However, one eventful, fun weekend of server troubleshooting or maintenance 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.

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.

In SQL Server Management Studio, 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. You might find it a good start to simply add a 0 to each value, increasing the maximum log history size in rows from the default of 1,000 to 10,000, and also increase the maximum job history per job in rows from the default of 100 to 1,000.

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.

For more information about SQL Server Agent job history, see Chapter 13.

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 mode 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 11.

SQL Server Trace Flag 3226 is an option that you can turn on 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 should 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.

Use the syntax -Tflagnumber, as illustrated in Figure 4-7.

Figure 4-7 Specifying a startup parameter in the SQL Server Properties dialog box.

After you specify the trace flag, click Add. The change will not take effect until the SQL Server Database Engine service is restarted. Figure 4-8 shows that Trace Flag 3226 is now a part of the startup.

Figure 4-8 The Startup Parameters tab in the SQL Server Properties dialog box, with Trace Flag 3226 now appearing in the Existing Parameters box.

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