MICROSOFT SQL SERVER 2017

Agio’s Guide to the Proper Installation & Configuration of Database Engine Services for the Enterprise Environment

1. Overview

SQL Server installation is a straightforward process that is made even simpler by the elaborative design of the Installation Wizard graphical user interface (GUI). By following the instructions in the wizard, one doesn’t need to be a DBA to successfully install SQL in a Windows environment; but, as with most software, a successful installation doesn’t always result in proper functionality of the program. Ensuring a properly functioning SQL environment depends on a series of pre-installation, installation, and post-installation configurations, especially when working with high-volume transactional environments like many of our hedge fund clients.

This guide covers some of Agio’s thoughts around basic configuration settings and concepts that are deployed while initializing a new Microsoft SQL Server 2017 instance. The validity of some of the recommendations discussed is based on the following assumptions:

  1. The Windows Server is dedicated only for SQL Server.
  2. SQL Server Enterprise Edition is installed.
  3. Windows Server resources are not shared with other Microsoft database-related services, such as:
    1. SQL Server analysis services
    2. SQL Server integration services
    3. SQL Server reporting services.
  4. The hardware and software components of the Windows Server have been properly configured by experienced server, network, and storage experts.

The last section of this guide contains Agio’s custom scripts, which can be used to complete tedious tasks that may be required to achieve a well-structured, functional SQL environment.

Note: All scripts are run at your own risk, and while every script has been written with the intention of minimizing the potential for unintended consequences and has been tested to achieve a desirable outcome, the author and contributors cannot be held responsible for any misuse or script problems.

2. Drive Layout & Configuration

By default, a SQL Server installation is configured to host all of its components—binaries and user objects—in the default Windows System drive (the C: drive). This isn’t a best practice in a production environment, as it allows for a single point of failure. It is imperative to separate the database files from the SQL Server binaries. Agio recommends adding four additional drives to the Windows Server to host database objects as illustrated below:

  • C: drive: SQL Server binaries (usually C:)
  • Drive 1: SQL database data files
  • Drive 2: SQL user database transaction log files
  • Drive 3: Tempdb data files
  • Drive 4: Tempdb transaction log files
  • Backup drive: Used when backups are taken locally or for ad hoc purposes. Whenever possible, it is recommended to make backups in a network location.

During SQL Server installation, there is an option to select the drives where the SQL instance components will be hosted. The screenshots below show the sections of the GUI Installation Wizard where the respective configuration changes can be made.

Figure 1: Default Directory Configurations

Figure 2: Custom Directory Configurations

  • Figure 1 illustrates the default directory configuration. By default, the data root directory is set to nest the subfolders delineating the product name, version and instance name, service type, and data folder inside the program files folder on C: drive. This configuration doesn’t allow for easy manageability, especially in disaster recovery (DR) situations where the DR server might be running an instance of SQL Server with a different version.
  • Figure 2 highlights custom directory configurations intended to remove the default subfolders and, instead, map the system database files, user database data, log files, and backup files to their respective drives and subfolders.

 

3. tempdb Considerations

3.1 Drive Separation

As mentioned above, the tempdb data and log files should be stored on their respective drives indicated in Section 2 above. There is a debatable performance advantage to having the files in this layout, but ease of manageability is a very good reason to have the data and log files separated. Having tempdb data files on a dedicated drive allows files to be pre-sized to fill the disk without affecting other files on the drive, and placing the tempdb log file on its own drive precludes the possibility of having other database files affected by any spontaneous activity that may cause the tempdb log file to grow erratically. Where possible, an overflow drive for tempdb can help mitigate the impact of such activities.

3.2 Number of tempdb Data Files

By default, one transaction log (TLOG) and four tempdb data  files are created during SQL 2017 installation. There is much debate on how many tempdb data files should be created per CPU core; the official recommendation from Microsoft is a one-to-one ratio for tempdb data files and logical CPU cores. However, the performance gain of this rule is your milage may vary, because the performance gain depends on the type of contention experienced by the SQL instance in tempdb:

  • PAGEIOLATCH_XX waits on tempdb usually indicate I/O subsystem contention and should be mitigated by creating extra tempdb files

while

  • PAGELATCH_XX wait issues on tempdb suggest a problem with in-memory allocation bitmaps.

Since SQL server uses a round-robin pattern (i.e., an even distribution attempt) of allocation per filegroup, and only one filegroup is allowed in tempdb, the allocation process workload will always match the number of tempdb data files, irrespective of the number of threads required for a certain task. Too many tempdb data files in some cases may have a detrimental effect, since every workload (small or large) requires a comparable amount of complex allocations involving weight balancing and recalculations across the number of files in the single tempdb filegroup.

A safe practice when there are more than eight logical cores of CPU—typical in an Enterprise Production environment—is to follow the one-to-one ratio rule to create up to eight tempdb data files initially and subsequently add additional files if PAGEIOLATCH_XX waits on tempdb becomes an issue.

Figure 3: Database Engine Configuration

4. Instant File Initialization (IFI)

Space allocation in SQL Server involves an initial process of zero-writing to reclaim the space that was previously used by deleted files; the allocated space is filled with zeroes before new data is written to it. For large autogrowth operations, the zero-writing process can consume an undesirable amount of time, thereby posing a performance concern. This issue is alleviated by enabling the IFI feature for the SQL Service account. This feature can be enabled during installation by checking the option to grant SQL Server account permissions to Perform Volume Maintenance Tasks as shown in Figure 4.

Figure 4: Server Configuration

  • IFI only works with data files (backup files, database datafiles, etc.); zero-writing will still occur when creating/growing transaction log files.
  • IFI isn’t functional where Transparent Data Encryption (TDE) is utilized.

5. Moving System Database Files

Several scenarios exist where internal relocation of system database files may be required:

  1. Upgrading to a new drive
  2. Drive separation – If the files were not mapped correctly during installation
  3. SQL installation on Azure or Amazon Web Services (AWS) – Where only C: drive is available during initialization.

The procedure to move system drives is different for the master database and other systems databases; both require a restart of the SQL Server instance. Moving system database files, especially master database files, is a very intricate task that may result in a quandary that could require significant downtime to resolve.

When used correctly, the PowerShell scripts provided in Appendix B can simplify the tedious process of system database file relocation and reduce downtime.

5.1 Moving System Database Files: tempdb, model & msdb

Moving system database files  involves the following steps:

  1. Changing the metadata to reflect the new location where the files will be relocated
  2. Stopping the SQL instance
  3. Moving the files to the new location
  4. Restarting the SQL instance.

Figure 5 is an excerpt from the PowerShell script to move system database files (Appendix B), which indicates the parameters that must be supplied to successfully execute the task.

Figure 5: Script Excerpt: Required Parameters for Moving System Database Files

5.2 Moving Other Master Database Files

Moving the master database is a bit more complex than moving other system databases, since it involves altering the Startup parameters for the SQL service. It involves the following steps, which should be executed in the order listed:

  1. Alter Startup parameters, and change the location of the master db files.
  2. Stop the SQL instance.
  3. Move the master db files to the new location.
  4. Restart the SQL instance.

Figure 6 is an excerpt from the PowerShell script to move master database files (Appendix A), which indicates required parameters that must be supplied to successfully execute the task.

Figure 6: Script Excerpt: Required Parameters for Moving Master Database Files

After successful execution of the PowerShell script, execute the following SQL script in the running instance to verify that the job was done.

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id <= 4

6. Memory Configuration

The maximum amount of memory allocated to SQL Server by default is 2,147,483,647 MB, which is essentially unlimited, since a typical server doesn’t have this amount of RAM. This value should be configured to limit the amount of memory allocated to SQL Server to prevent issues with the Operating System (OS). Allowing SQL Server the capability to use all memory on the server can result in a dearth of memory required for the OS to functional properly, resulting in a crash.

A safe practice is to reserve 1 GB of RAM to the OS for every 4 GB of total available RAM from 4–16 GB, and 1 GB to the OS for every 8 GB of total available RAM greater than 16 GB.

7. Database Mail Configuration

SQL Server employs the Database Mail (db mail) feature as an enterprise solution for sending email messages using the standard Simple Mail Transfer Protocol (SMTP). This was introduced to eventually replace the SQL Mail feature that was previously used for the same purpose. Db mail can be either in cleartext or in HTML format and can contain file attachments. For cybersecurity purposes, this feature is disabled by default; therefore, proper configuration of db mail accounts and profiles is required to achieve both functionality and data safety.

7.1 Database Mail CyberSecurity

Only users who are members of the msdb’s DatabaseMailUserRole role are authorized to send db mail. A public mail profile can be used by all users having access to send dbmail, while private profiles restrict access to specified db mail users only. When creating a db mail profile, specify whether the profile should be private or public.

7.2 File Type Restriction

Db mail provides the functionality to restrict the types of files that can be attached to an email by maintaining a list of prohibited files extensions. This list can be altered to fit the organization’s cybersecurity policy by using sysmail_configure_sp in the msdb database.

The steps required to configure db mail are listed below:

  • Enable db mail feature.
  • Create db mail account.
  • Create db mail profile.
  • Add db mail account to mail profile.
  • Grant profile access, and make it public or private.
  • Test db mail.

Appendix C provides a custom SQL script to execute the tasks required to set up db mail with a public profile on a SQL Server instance, once the appropriate parameters are supplied.

Figure 7 is an excerpt of the PowerShell script to configure db mail (Appendix C), which indicates required parameters that must be supplied to successfully execute the task.

Figure 7: Script Excerpt: Required db mail Parameters

8. Cybersecurity Settings

One of the most crucial cybersecurity configurations of a SQL Server instance is performed during installation.

8.1 Server Authorization

  1. Grant server-level permissions to user-defined server roles, not to individual logins.
    User-defined server roles have many uses. There are dozens of instance-level rights that can be granted to a user-defined server role to simplify managing these rights. This is in accordance with the best practice policy that advocates granting permissions to groups, rather than to individuals.
  1. Nesting server roles is not allowed.
  2. The sysadmin fixed server role should only contain SQL Server service accounts and the DBA Admin Active Directory (AD) group.
    The sysadmin fixed server role should only contain the DBA AD group and the SQL Server service accounts. All other accounts must be removed, with the exception of specifically approved vendor logins. Application service accounts may be provided as an exception with management approval; however, it is strongly discouraged, as database-level permissions should be sufficient.
  1. Servers must be configured to use Mixed Authentication Mode only.
  2. The sa account must be disabled after installation is complete.

8.2 Database Authorization

  1. Permissions must be granted to database roles, not to individual database users.
    All permissions within the database should be using a database role. This not only improves the clarity of the permissions structure, but it also simplifies moving the database across DEV, QA, UAT, and PROD environments, as different users can exist in the same database roles in each environment. Roles should have a clear naming convention that identifies their function and relevance.
  1. The public role is reserved for utility functions and procedures. It must not be granted any explicit application-specific permissions.
    Every account in the database is part of the public database role. Therefore, granting permissions on an object to the public role grants those permissions to everyone in the database. This is only appropriate for objects with no application-specific restrictions and that are meant to be shared across applications.
  1. The guest database user must be disabled in each application database.
    The guest user account allows login without a user account to access a database. A login assumes the identity of the guest user when both of the following conditions are met:

    • The login has access to an instance of Microsoft SQL Server but does not have access to the database through his or her own user account.
    • The database contains an enabled guest user account.
  1. Every database must be owned by the sa login unless otherwise required by a vendor application.
  2. Permanently empty database roles and empty database schemas should be removed.
    An empty database role is one with no members. An empty database schema is one with no objects defined in it. Monitoring of empty rows and schemas will be managed by the DBA team and communicated to the development owners prior to removal.
  1. Database users (AD groups and service accounts) must have a corresponding SQL Server login.
    A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log into the instance. Such a user is said to be an orphaned user of the database on that server instance. A database user can become orphaned if the corresponding SQL Server login is dropped. Also, a database user can become orphaned after a database is restored or is attached to a different instance of SQL Server. Orphaning can happen if the database user is mapped to a Security ID (SID) that is not present in the new server instance.
  1. Cross-database ownership chaining option is enabled by default.
    Cross-database ownership chaining comes into play when an object (e.g., a stored procedure, function, or view) in one database depends on an object (e.g., a table or view) in another database. When cross-database ownership chaining is turned on, and both the calling and dependent objects have the same owner (login account), grants are not required on the target object(s), as the calling object is trusted. Since every database is owned by the sa login by default, permissions (grants) only need to be applied to directly accessed database objects (stored procedures, views, tables, etc.) and not to the underlying objects. Three important things to note:

    • If cross-database ownership chaining is not desired, it may be turned off at the database level.
    • The calling user must be a user in the target database regardless of whether cross-database ownership chaining is on or off.
    • Cross-database ownership chaining only applies to databases that reside on the same server. When making calls across servers, explicit permissions must be applied to the objects being called.
  1. Nesting database roles is not allowed.
    Similar to the standard specified for Server roles, database roles should not be nested.

8.3 Linked Servers

  1. Linked servers must use only pass-through authentication (i.e., by selecting the “be made using the login’s current security context” option).
    Specify that connections will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, Windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, the login name and password will be used to connect to the remote server. In this case, a login with the exact same name and password must exist on the remote server.
  1. No hard-coded login mappings are permitted.

8.4 SQL Server Agent Jobs

  1. SQL Server agent jobs must be owned by sa and the jobs run under the service account of the SQL Server Agent service.

8.5 Access to OS & Network Resources

  1. Applications are not permitted to access the OS or network resources through SQL Server (e.g., through xp_cmdshell). Access the OS and network resources directly from the application instead. This restriction does not apply to DBA jobs.

9. Summary

As we’ve discussed in detail, installing a Microsoft SQL Server instance may seem simple, but it requires tremendous attention to detail to get it right. At Agio, we’re constantly evaluating and honing installation best practices to ensure our asset management clients (hedge fund, private equity, investment banks) maintain the most secure and reliable databases. In general, database administration requires experience and depth of team to achieve optimal performance. We have both, and, as a result, our clients are thrilled with the level of service they receive. Contact us for further discussions about installing, managing, and securing your database environment.

  • The Server configuration tab in the screenshot above provides the options to choose between the two authentication modes. AD group users can also be added to the sysadmin server role, which possesses similar privileges to the sa account.

Appendices—Custom Scripts

Disclaimer
All scripts are run at your own risk. While every script has been written with the intention of minimizing the potential for unintended consequences, the author and contributors cannot be held responsible for any misuse or script problems.

Appendix A: Script – Move Master Database

#This includes the following steps
#1: Setting and applying the Startup Parameters for SQL Service
#2: Stop SQL Service
#3: Move the master db files
#4: Start SQL Service

#Set variables
$Instance = $env:COMPUTERNAME #specify instance name if it is a named instance”InstanceName”
$NewmasterfilesLocation = “<New_masterfiles_Folder>” #eg.”F:\MSSQL14.MSSQLSERVER\MSSQL\Data\”
$StartupParameters = (‘-d’+$NewmasterfilesLocation+’master.mdf;-e’+$NewmasterfilesLocation+’ERRORLOG;-l’+$NewmasterfilesLocation+’mastlog.ldf’)
$masterdata = Invoke-Sqlcmd -ServerInstance $Instance -Database “master” -Query “select physical_name from sys.master_files where database_id = 1 AND file_id = 1”
$masterlog = Invoke-Sqlcmd -ServerInstance $Instance -Database “master” -Query “select physical_name from sys.master_files where database_id = 1 AND file_id = 2”

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SqlWmiManagement’)| Out-Null
$var= New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($Instance)
$sqlservice = $var.Services | Where-Object {$_.Type -eq “SqlServer”}
$sqlservice.Refresh()

#1
$sqlservice.set_StartupParameters($StartupParameters)
$sqlservice.alter()

#2
$sqlservice.Stop()
sleep 20

#3
Move-Item $masterdata.physical_name, $masterlog.physical_name $NewmasterfilesLocation

#4
$sqlservice.Start()
sleep 20

 

Appendix B: Script – Move System db: tempdb, msdb, model

#This includes the following steps
#1: Modifying the file location for the database files
#2: Stop SQL Service
#3: Move the db files
#4: Start SQL Service

#Declare variables

$Instance = $env:COMPUTERNAME
$NewTempDataFolder = “<New_tempdb_data_location>” #eg “T:\MSSQL\Data\”
$NewTempLogFolder = “<New_tempdb_log_location>” #”U:\MSSQL\Tlog\”
$NewsysdbFolder = “<New_systemdb_file_location>” #”F:\MSSQL14.MSSQLSERVER\MSSQL\Data\”

$tempdb_datainfo = Invoke-SQLCMD -ServerInstance $Instance -Database “master” -Query “SELECT m.name AS TempData, d.name AS TDName, m.physical_name AS TempDataFile FROM sys.master_files m JOIN sys.databases d ON m.database_id = d.database_id WHERE m.database_id = 2 AND type_desc = ‘ROWS'”

$tempdb_loginfo = Invoke-SQLCMD -ServerInstance $Instance -Database “master” -Query “SELECT m.name AS TempLog, d.name AS TLName, m.physical_name AS TempLogFile FROM sys.master_files m JOIN sys.databases d ON m.database_id = d.database_id WHERE m.database_id = 2 AND type_desc = ‘LOG'”

$sysdb_datainfo = Invoke-SQLCMD -ServerInstance $Instance -Database “master” -Query “SELECT m.name AS SysLogical, d.name AS SName, m.physical_name AS SysFiles FROM sys.master_files m JOIN sys.databases d ON m.database_id = d.database_id WHERE m.database_id IN (3, 4)”

$Tempdb_Data_files = Invoke-Sqlcmd -ServerInstance $Instance -Database “master” -Query “SELECT physical_name AS TempDataPhysical FROM sys.master_files WHERE database_id = 2 AND type_desc = ‘ROWS'”

$Tempdb_Log_file = Invoke-Sqlcmd -ServerInstance $Instance -Database “master” -Query “SELECT physical_name AS TempLogPhysical FROM sys.master_files WHERE database_id = 2 AND type_desc = ‘LOG'”

$Systemdb_files = Invoke-Sqlcmd -ServerInstance $Instance -Database “master” -Query “SELECT physical_name AS SysPhysical FROM sys.master_files WHERE database_id IN (3, 4)”

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SqlWmiManagement’)| Out-Null
$var= New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer($Instance)
$sqlservice = $var.Services | Where-Object {$_.Type -eq “SqlServer”}

#1
Foreach ( $db in $tempdb_datainfo)
{
$filename = Split-Path $db.TempDataFile -leaf
$TempDataQuery = (‘ALTER DATABASE ‘+$db.TDName+’ MODIFY FILE (NAME = ‘+$db.TempData+’, FILENAME = ”’+$NewTempDataFolder+$filename+”’)’)
write-output $TempDataQuery
Invoke-SQLCMD -ServerInstance $Instance -Database “master” -Query $TempDataQuery
}

#1b
Foreach ( $db in $tempdb_loginfo)
{
$filename = Split-Path $db.TempLogFile -leaf
$TempLogQuery = (‘ALTER DATABASE ‘+$db.TLName+’ MODIFY FILE (NAME = ‘+$db.TempLog+’, FILENAME = ”’+$NewTempLogFolder+$filename+”’)’)
write-output $TempLogQuery
Invoke-SQLCMD -ServerInstance $Instance -Database “master” -Query $TempLogQuery
}

#1c
Foreach ( $db in $sysdb_datainfo)
{
$filename = Split-Path $db.SysFiles -leaf
$SysDBQuery = (‘ALTER DATABASE ‘+$db.SName+’ MODIFY FILE (NAME = ‘+$db.SysLogical+’, FILENAME = ”’+$NewsysdbFolder+$filename+”’)’)
write-output $SysDBQuery
Invoke-SQLCMD -ServerInstance $Instance -Database “master” -Query $SysDBQuery
}

#2
$sqlservice.Stop()
sleep 20

#3
Move-Item $Tempdb_Data_files.TempDataPhysical $NewTempDataFolder
Move-Item $Tempdb_Log_file.TempLogPhysical $NewTempLogFolder
Move-Item $Systemdb_files.SysPhysical $NewsysdbFolder

#4
$sqlservice.Start()
sleep 20

 

Appendix C: Script – Database Mail Setup

— Enable Database Mail

USE master;
EXEC sp_configure’show advanced’, 1
GO
RECONFIGURE
GO
EXEC sp_configure ‘Database Mail XPs’, 1
GO
RECONFIGURE
GO

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder = 0
GO

–Declare Variables
DECLARE @servername SYSNAME
DECLARE @dp_name VARCHAR (50)
DECLARE @account_name VARCHAR (50)
DECLARE @profile_name VARCHAR (50)
DECLARE @profile_id INT
DECLARE @profile_description SYSNAME
DECLARE @mailserver_name VARCHAR (50)
DECLARE @email_address VARCHAR (50)
DECLARE @replyto_address VARCHAR (50)
DECLARE @port_number INT
DECLARE @testmail_subject VARCHAR(100)
DECLARE @testmail_body_text NVARCHAR(MAX)
DECLARE @testmail_recipients VARCHAR(100)

–Set Variables
SET @servername = (SELECT @@SERVERNAME)
SET @dp_name   = ‘SQL DBMAIL (‘+ @servername +’)’
SET @account_name = ‘<insert account name>’
SET @profile_name = ‘<insert profile name>’
SELECT @profile_id = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profile
SET @profile_description = ‘Database Mail Profile for ‘ + @servername
SET @mailserver_name = ‘<insert mailserver name>’
SET @email_address = ‘<insert sender email address>’
SET @replyto_address = ‘<insert reply to address>’
SET @port_number = ”–insert port number without quotes
SET @testmail_subject = ‘Database Mail Test’
SET @testmail_body_text = ‘This is a test e-mail sent from Database Mail on ‘ +@servername
SET @testmail_recipients = ‘<insert recipients email address>’

–Create db mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @account_name,
@description = @profile_description,
@email_address = @email_address,
@replyto_address = @replyto_address,
@display_name = @dp_name,
@mailserver_name = @mailserver_name,
@port = @port_number;

— Create db Mail Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @profile_name,
@description = @profile_description;

— Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @profile_name,
@account_name = @account_name,
@sequence_number = @profile_id;

— Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = @profile_name,
@principal_id = 0,
@is_default = 1 ;

–Check Configuration–
SELECT * FROM msdb.dbo.sysmail_profile;
SELECT * FROM msdb.dbo.sysmail_account;

— Send a Test db Mail–
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile_name,
@recipients = @testmail_recipients,
@subject = @testmail_subject,
@body = @testmail_body_text;

— SQL Agent Properties Configuration–
EXEC msdb.dbo.sp_set_sqlagent_properties
@databasemail_profile = @profile_name,
@use_databasemail=1

GO