Chapter 4
Provisioning databases

In this chapter, we review the process of installing and configuring a Microsoft SQL Server instance as well as the creation or migration of databases. We pay special attention to new features introduced in SQL Server 2017 and even some added since SQL Server 2016 Service Pack 1, including those features that have been expanded for the first time from the Enterprise edition to the Standard edition of SQL Server. We review some basic checklists for you to verify every time and, when necessary, direct you to where you can find more details on critical steps elsewhere in this book as well as other sources of information.

One of the basic guiding principles for a SQL Server installation is that anywhere you see “C:”, change it to another volume. This helps minimize SQL Server’s footprint on the operating system (OS) volume, especially if you install multiple SQL Server instances, which can have potential disaster recovery implications in terms of volume-level backup and restores.

If this is the first SQL Server instance you are installing on a server, you will have the opportunity to change the location of shared features files, the data root directory for the instance (which contains the system databases), default database locations for user database files, and their backups. If this is not the first SQL Server 2017 instance installation on this server, the shared features directory locations (for Program Files and Program Files x86) will already be set for you, and you cannot change it.

You should place as much of the installation as possible on other volumes. Keep in mind that a full-featured installation of SQL Server 2017 can consume more than 7 GB. You will want to move some of those binaries for feature installations to other folders.

The following sample scenario is a good starting point (the volume letters don’t matter):

• Volume C. OS only

• Volume E. SQL Server installation files, SQL Server data files

• Volume F. SQL Server log files

• Volume G. SQL Server TempDB data and log files (we look at TempDB data files in more detail later in the chapter)

• Volume H. SQL Server backups

Where do you go from here? Here are some avenues that you might take with respect to volumes:

• Use additional volumes for your largest data files (larger than 2 TB) for storage manageability

• Use an additional volume for your most active databases and their log files

• Use an additional volume for large amounts of FILESTREAM data

• Use an additional volume for large replicated database snapshot files

• When adding these volumes to Windows, there are three important volume configuration settings that you should check for yourself or discuss with your storage administrator. When creating new drives, opt for GUID Partition Table (GPT) over Master Boot Record (MBR) drive types for new SQL Server installations. GPT is a newer drive partitioning scheme than MBR, and GPT drives support files larger than 2 TB, whereas the older MBR drive type is capped at 2 TB.

• The appropriate file unit allocation size for SQL Server volumes is 64 KB, with few exceptions. Setting this to 64 KB for each volume can have a significant impact on storage efficiency and performance. The Windows default is 4 KB, which is not optimal.

  To check the file unit allocation size for an NT File System (NTFS) volume, run the following from the Administrator: Command Prompt, repeating for each volume:

  Fsutil fsinfo ntfsinfo d:
  Fsutil fsinfo ntfsinfo e:
  …

  The file unit allocation size is returned with the Bytes Per Cluster; thus 64 KB would be displayed as 65,536 (bytes).

  Correcting the file unit allocation size requires formatting the drive, so it is important to check this setting prior to installation.

  If you notice this on an existing SQL Server instance, your likely resolution steps are to create a new volume with the proper file unit allocation size and then move files to the new volume during an outage. Do not format or re-create the partition on volumes with existing data: you will lose the data.

  Note that new Azure VM drives follow the Windows default of 4 KB; thus, you must reformat them to 64 KB.

• There is a hardware-level concept related to file unit allocation size called “disk starting offset” that deals with how Windows, storage, disk controllers, and cache segments align their boundaries. Aligning disk starting offset was far more important prior to Windows Server 2008. Since then, the default partition offset of 1,024 KB has been sufficient to align with the underlying disk’s stripe unit size, which is a vendor-determined value. This should be verified in consultation with the drive vendor’s information.

  To access the disk starting offset information, run the following from the Administrator: Command Prompt:

  Click here to view code image

  wmic partition get BlockSize, StartingOffset, Name, Index

  A 1024 KB starting offset is a Windows default; this would be displayed as 1048576 (bytes) for Disk #0 Partition #0.

  Similar to the file unit allocation size, the only way to change a disk partition’s starting offset is destructive—you must re-create the partition and reformat the volume.

Following are brief descriptions for all of the editions in the SQL Server family, including past editions that you might recognize. It’s important to use the appropriate licenses for SQL Server even in preproduction systems.

• Enterprise edition. Appropriate for production environments. Not appropriate for preproduction environments such as User Acceptance Testing (UAT), Quality Assurance (QA), testing, development, and sandbox. For these environments, instead use the free Developer edition.

• Developer edition. Appropriate for all preproduction environments. Not allowed for production environments. This edition supports the same features and capacity as Enterprise edition and is free.

• Standard edition. Appropriate for production environments. Lacks the scale and compliance features of Enterprise edition that might be required in some regulatory environments. Limited to the lesser of 4 sockets or 24 cores and also 128 GB of buffer pool memory, whereas Enterprise edition is limited only by the OS for compute and memory.

  ---

  NOTE

  In case you missed it, in Service Pack 1 of SQL Server 2016, a large number of features in Enterprise edition features were moved “down” into Standard, Web, and Express editions, including database snapshots, Columnstore indexes (limited), table partitioning, data compression, memory-optimized OLTP, PolyBase, SQL Audit, and the new Always Encrypted feature. Standard and Web edition also gained the ability to use the Change Data Capture (CDC) feature.

  ---

• Web edition. Appropriate for production environments, but limited to low-cost server environments for web applications.

• Express edition. Not appropriate for most production environments or preproduction environments. Appropriate only for environments in which data size is small, is not expected to grow, and can be backed up with external tools or scripts (because Express edition has no SQL Server Agent to back up its own databases). The free Express edition is ideal for proof-of-concepts, lightweight, or student applications. It lacks some critical features and is severely limited on compute (lesser of 1 socket or 4 cores), available buffer pool memory (1,410 MB), and individual database size (10 GB cap).

• Express with Advanced Services. Similar to Express edition in all caveats and limitations. This edition includes some features related to data tools, R integration, full-text search, and distributed replay that are not in Express edition.

• Business Intelligence edition. This edition was a part of the SQL Server 2012 and SQL Server 2014 products but was removed in SQL Server 2016.

• Datacenter edition. This edition was part of SQL Server until SQL 2008 R2 and has not been a part of the SQL Server product since SQL Server 2012.

There is an exception to this rule, and that is if you have licensed all physical cores a virtual host server with SQL Server Enterprise and purchased Software Assurance. Then, you can install any number or combination of SQL Server instances and their standalone features on virtual guests.

It is important to note that you cannot downgrade a SQL Server version or licensed edition. This type of change requires a fresh installation and migration. For example, you cannot downgrade in-place from SQL Server 2017 Enterprise edition to Standard edition.

Upgrading versions in-place is supported but not recommended, if at all possible. Instead, we strongly recommend that you perform a fresh installation of the newer version and then move from old to new instances. This method offers major advantages in terms of duration of outage, rollback capability, and robust testing prior to migration. And, by using DNS aliases or SQL aliases, you can ease the transition for dependent application and connections.

Although in-place upgrades to SQL Server 2017 are not recommended, they are supported from as early as SQL Server 2008 on Windows Server 2012, assuming that the earlier versions are not 32-bit installations. Beginning with SQL Server 2016, SQL Server is available only for 64-bit platforms. SQL Server 2017 requires Windows Server 2012 or later.

You cannot perform an in-place upgrade from SQL Server 2005 to SQL Server 2017; however, you can attach or restore its databases to SQL Server 2017, though they will be upgraded to compatibility level 100, which is the version level for SQL 2008.

It’s important to note that even though you can change almost all of the decisions you make in SQL Server Setup after installation, those changes potentially require an outage or a server restart. Making the proper decisions at installation time is the best way to ensure the least administrative effort. Some security and service account decisions should be changed only via the SQL Server Configuration Manager application, not through the Services console (services.msc). This guidance will be repeated elsewhere for emphasis.

We begin by going through the typical interactive installation. Later in this chapter, we go over some of the command-line installation methods that you can use to automate the installation of a SQL Server instance.

Only one of the SQL Server instances on a server can be the default instance. All, or all but one, of the SQL Server instances on a SQL Server will be named instances. The default instance is addressable by the name of the server alone, whereas named instances require an instance name. The SQL Browser service then is required to handle traffic between multiple instances on the SQL Server.

For more information about the SQL Server browser, go to Chapter 7.

For example, you can reach the default instance of a SQL Server by connecting to servername. All named instances would have a unique instance name, such as servername/instancename.

In application connection strings, servername/instancename should be provided as the Server or Data Source parameter.

You should not run Setup with the installation media mounted over a remote network connection, via a shared remote desktop drive, or any other high-latency connection. It would be faster to copy the files locally before running Setup.

Start Setup.exe on the SQL Server Setup media, running the program as an administrator. If AutoPlay is not turned off (it usually is), Setup.exe will start when you first mount the media or double-click to open the .iso. Instead, as a best practice, right-click Setup.exe and then, on the shortcut menu that appears, click Run As Administrator.

We’ll review here a few items (not all) in the SQL Server Installation Center worth noting before you begin an installation.

In the tab pane on the left, click Planning to open a long list of links to Microsoft documentation websites. Most helpful here might be a standalone version of the System Configuration Checker, which you run during SQL Server Setup later, but it could save you a few steps if you review it now. A link to download the Upgrade Advisor (now renamed to the Data Migration Assistant) is also present, a helpful Microsoft-provided tool when upgrading from prior versions of SQL Server.

On the Maintenance page, you will find the following:

• A link to launch the relatively painless Edition Upgrade Wizard. This is for changing your existing installation’s edition, not its version (SQL 2012, 2014, 2016, 2017) or platform (x86 versus 64-bit). You can upgrade from “lower” editions to higher editions, but keep in mind that downgrading editions is not possible and would require that you install a new SQL Server. For example, you cannot downgrade from Enterprise to Standard edition without a new installation. For complete details on Edition upgrade paths, visit https://docs.microsoft.com/sql/database-engine/install-windows/supported-version-and-edition-upgrades#includesscurrentincludessscurrent-mdmd-edition-upgrade

• The Repair feature is not a commonly used feature. It’s use is necessitated by a SQL Server with a corrupted Windows installation. You might also need to repair an instance of SQL Server when the executables, .dll files, or registry entries have become corrupted before repair. A failed SQL Server in-place upgrade or cumulative update installation might also require a Repair, which could be better than starting from scratch.

• Whereas adding a node to an existing SQL Server failover cluster is an option in the Installation menu, removing a node from an existing SQL Server failover cluster is an option in the Maintenance menu.

• A link to the Update Center for SQL Server (Technet Article ff803383) provides information on the latest cumulative updates for each version of SQL Server.

• On the Tools menu, there is a link to the Microsoft Assessment and Planning (MAP) Toolkit for SQL Server, which is a free download that can be invaluable when you’re performing an inventory of your SQL Server presence in a network. It’s also capable of searching for SQL Server instances by a variety of methods and generating CIO-level reports.

• On the Advanced menu, there is a link to perform an installation based on a configuration file, which we discuss later in this chapter in the section “Automating SQL Server Setup by using configuration files.”

There are also links to wizards for advanced failover cluster installations.

We discuss Failover Cluster Instances (FCIs) in Chapter 12.

PolyBase Query Service For External Data The PolyBase Query Engine makes it possible to query Hadoop nonrelational data or Azure Blob Storage files by using the same Transact-SQL (T-SQL) language with which SQL Server developers are already familiar. You may have only one SQL Server Instance with the PolyBase feature installed on a Windows Server.

As strange as this sounds, this functionality of SQL Server requires that you install the Oracle SE Java Runtime Environment (JRE). If while on the Feature Rules page you encounter the error “Oracle JRE 7 Update 51 (64-bit) or higher is required,” you can ignore the message and, in the background, proceed with the installation of Oracle JRE 7. You do not need to restart Windows or SQL Server Setup after a successful installation of the Oracle JRE, and on the Feature Rules page, the Re-Run button should clear that error so that you can proceed with SQL Server Setup.

This is a manual installation that is not contained in the Setup components or automatically downloaded by the Smart Setup (more on that later). To download the latest version, visit the Oracle JRE site at http://www.oracle.com/technetwork/java/javase/downloads/index.html.

Choose the JRE download because the Server JRE does not include an installer for Windows Platforms. Choose the Windows x64 Offline installer, which is a self-installing .exe file, and then complete the installation.

You must configure at least six individual ports in a range (with TCP 16450 through 16460 the default) for the PolyBase Engine. You can move forward with the defaults if these ranges are unclaimed in your network.

Later, on the Server Configuration page, you will choose a service account for the SQL Server PolyBase Engine service and the SQL Server PolyBase Data Movement service. We recommend a dedicated Windows Authenticated service account, with a special note that if it is a part of a scale-out of PolyBase instances, all of the instances should use the same service account.

Finally, after SQL Server Setup is complete, if you installed both the Database Engine and PolyBase at the same time, the two PolyBase services will be in the “Change Pending” state. They are unable to connect because, by default, TCP is not an activated protocol for the SQL Server instance. This is a common post-installation to-do item for other reasons, so turning on the TCP protocol for the new SQL Server instance, followed by restarting the SQL Server service, is required.

After the new SQL Server installation is complete, review the “Hadoop Connectivity” setting by using sp_configure. The setting ranges from 0 to 7, with options 1 through 6 dealing mostly with older versions of Hortonworks Data Platform. Setting 7 allows for the connectivity with recent Hortonworks HDP versions as well as Azure Blob storage. To change this sp_configure setting, you must run the RECONFIGURE step and also restart the SQL Server service, and then manually start the two SQL Server PolyBase services.

Additional steps, including a firewall change, are needed to install this feature as part of a PolyBase Scale-Out Group of multiple SQL Server instances, with one PolyBase Engine service per Windows Server.

Machine learning features The newly named Machine Learning Services (In-Database or the standalone Machine Learning Server) feature makes it possible for developers to integrate with R language and/or Python language extensions using standard T-SQL statements. Data scientists can take advantage of this feature to build advanced analytics, data forecasting, and algorithms for machine learning. Formerly called the Revolution R engine, SQL Server 2017 installs version 9 of the Microsoft R Open Server, supported for both Windows and Linux.

This feature adds the SQL Server Launchpad service. You cannot configure the service account for the Launchpad service; it will run as a dedicated NT ServiceMSSQLLaunchpad virtual account. The standalone installation of Machine Learning Services does not create the SQL Server Launchpad service and is intended for models that do not need a SQL Server.

After the new SQL Server installation is complete, you must turn on a security option to allow external scripts. This makes it possible for you to run non-T-SQL language scripts, and in the SQL Server 2017 release of this feature, R and Python are the only languages supported. (In SQL Server 2016, only R was supported, thus the name change from R Services to Machine Learning Services.) Use sp_configure to select the External Scripts Enabled option, reconfigure, and restart the SQL Server service.

This automates what used to be a standard post-installation checklist step for SQL DBAs since Windows Server 2003. The reason to grant this permission to use instant file initialization is to speed the allocation of large database data files, which could dramatically reduce the Recovery Time Objective (RTO) capacity for disaster recovery.

This can mean the difference between hours and minutes when restoring a very large database. It also can have a positive impact when creating databases with large initial sizes, or in large autogrowth events; for example, with multiple data files in the TempDB (more on this next). It is recommended that you allow SQL Setup to turn on this setting.

For more information on instant file initialization, see Chapter 3.

The TempDB database page in SQL Server Setup provides not only the ability to specify the number and location of the TempDB’s data and log files, but also their initial size and autogrowth rates. The best number of TempDB data files is almost certainly greater than one, and less than or equal to the number of logical processor cores. Adding too many TempDB data files could in fact severely degrade SQL Server performance.

For more information on the best number of TempDB data files, see Chapter 3.

Specifying TempDB’s initial size to a larger, normal operating size is important and can improve performance after a SQL Server restart when the TempDB data files are reset to their initial size. Setup accommodates an individual TempDB data file initial size up to 256 GB. For data file initial sizes larger than 1 GB, you will be warned that Setup could take a long time to complete if instant file initialization is turned on by granting the Perform Volume Maintenance Task for the SQL Server Service Account. (This should be accomplished automatically by Setup; see the previous section.)

All TempDB files autogrow at the same time, keeping file sizes the same over time. Previously only available as a server-level setting via trace flag 1117, TempDB data files have behaved in this way by default since SQL Server 2016.

Note also the new naming convention for the second TempDB data file and beyond: tempdb_mssql_n.ndf. A SQL Server uninstallation will automatically clean up TempDB data files with this naming convention—for this reason, we recommend that you follow this naming convention for TempDB data files.

TempDB is discussed in greater detail in Chapter 3.

Figure 4-1 depicts a VM with four logical processors. Note that the number of files is by default set to the number of logical processors. The sizes, autogrowth settings, and data directories have been changed from their defaults, you should consider doing the same.

You can read more on this topic in Chapter 7, but it is important to note this decision point here.

Ideally, all authentication is made via Windows Authentication, through types of server principals called logins, that reference Domain accounts in your Enterprise edition. These domain accounts are created by your existing enterprise security team, which manages password policy, password resets, password expiration, and so on.

A redundant security model for connecting to SQL Server also exists within each instance: SQL Server Authenticated logins. Logins are maintained at the SQL Server level, are subject to local policy password complexity requirements, are reset/unlocked by SQL DBAs, have their own password change policy, and so forth.

Turning on Mixed Mode (SQL Server and Windows Authentication Mode) activates SQL Server Authenticated login. It is important to note that it is not on by default and not the preferred method of connection. By default, only Windows Authentication is turned on and cannot be turned off. When possible, applications and users should use Windows Authentication.

Turning on Mixed Mode also activates the “sa” account, which is a special built-in SQL Server Authentication that is a member of the server sysadmin role. Setup will ask for a strong password to be provided at this time.

You can learn more about the “sa” account and server roles in Chapter 7.

It is important to keep in mind that you do not need to turn on SQL Server Authentication in Setup; you can do this later on by connecting to the SQL Server via Object Explorer in SQL Server Management Studio. To do so, right-click the server name and then, on the shortcut menu that opens, click Properties, and then click the Security page. You must perform a service restart to make this change effective.

• Multidimensional mode. This is the familiar SQL Server Analysis Services setup that was first introduced in SQL 2000. This is also the mode to support data mining.

• Tabular mode. This is the newer SQL Server Analysis Services setup that was first introduced in SQL 2012, using the in-memory VertiPaq processing engine. For the first time in SQL Server 2017, this is the default installation mode selected on the Analysis Services Configuration page of Setup.

• Power Pivot mode. This mode installs SQL Server Analysis Services in the Power Pivot for SharePoint mode.

Also unlike other features, you can install SQL Server Integration Services on a 32-bit OS; however, we do not recommend this. A 64-bit version of SQL Server Integration Services is installed on 64-bit operating systems. If you worry about connecting to 32-bit servers, data sources, or applications installations (such as Microsoft Office), don’t—those connections are not dependent on the 32-bit/64-bit installation and are handled at the package or connection-string level.

A standalone installation of SQL Server Integration Services without a matching SQL Server Database Engine is possible but not recommended. For the modern Project Deployment model of SQL Server Integration Services, the storage and logging of packages will still be dependent on a SQL Server Database Engine, and the execution of packages on a schedule would still require a SQL Agent service. Isolation of the SQL Server Integration Services workload is not best isolated in this way. A dedicated installation including the SQL Server Database Engine and SQL Server Agent is a better configuration to isolate SQL Server Integration Services package runtime workloads from other database workloads.

Installations of different versions of SQL Server Integration Services are installed side by side on a server; specifically, the service SQL Server Integration Services 14.0 is compatible with prior versions.

Outside of configuring the service account, you do not need any additional configuration when installing SQL Server Integration Services during SQL Server Setup. The default virtual service account is NT ServiceMsDtsServer140. Note that this account is different from the Scale Out Master and Scale Out Worker service accounts, and is used differently. Let’s talk about the Scale Out feature now.

The master node talks to worker nodes in a SQL Server Integration Services Scale Out, with the communication over a port (8391 by default) and secured via a new Secure Sockets Layer (SSL) certificate. The SQL Server installer can automatically create a 10-year self-signed certificate and endpoint for communication at the time the master node is set up.

When adding another SQL Server Integration Services installation as a Scale Out Worker, start the new SQL Server Integration Services Manage Scale Out window via SQL Server Management Studio. Right-click the Catalog you have created, and then click Manage Scale Out. At the bottom of the page, click the + button to add a new Scale Out Worker node. Provide the server name to which to connect. If using a named instance, still provide only the server name of the node; do not include the instance name. A dialog box confirms the steps taken to add the Worker node, including copying and installing certificates between the Worker node and Master node, updating the endpoint and HttpsCertThumbprint of the worker, and restarting the Worker’s Scale Out service. After the worker node is added, refresh the Worker Manager page, and then click the new Worker node entry, which will be red. You must turn on the Worker Node by clicking Enable Worker.

You also can copy and install the certificates manually between servers. You will find them in %program files%Microsoft SQL Server140DTSBinn.

The Worker and Master nodes do not appear in SQL Server Configuration Manager (as of SQL 2017 RC2) but do appear in Services.msc.

One major security difference with Scale Out is that even though the SQL Server Integration Services Service Account doesn’t run packages or need permission to do very much, the Scale Out Master and Worker service accounts actually do run packages. By default, these services run under virtual accounts NT ServiceSSISScaleOutMaster140 and NT ServiceSSISScaleOutWorker140, but you might want to change these to a Windows-authenticated Domain service account that will be used to run packages across the Scale Out.

The “native” mode of SQL Server Reporting Services is now the only mode in SQL Server 2017. If you are familiar with Reporting Services Report Manager in the past, accessible via the URL http://servername/Reports, that is the “native mode” installation of Reporting Services.

You’ll notice the Report Server Configuration Manager in a new location, in its own Program Files menu, Microsoft SQL Server Reporting Services. After installation, start the Report Server Configuration Manager (typically installed in a path like Program Files (x86)Microsoft SQL Server140ToolsBinnRSConfigTool.exe). The Report Server Configuration Manager application itself is largely unchanged since SQL 2008.

The virtual service account “NT SERVICESQLServerReportingServices” is the default SQL Server Reporting Services service account. It is a second-best option, however: we recommend that you create a new domain service account to be used only for this service; for example, “Domainsvc_ServerName_SSRS” or a similar naming convention. You will need to use a domain account if you choose to configure report server email with “Report server service account (NTLM)” authentication.

If you choose to change the SQL Server Reporting Services service account later, use only the Reporting Services Configuration Manager tool to make this change. Like other SQL Server services, never use the Services console (services.msc) to change service accounts.

After installation, you will need to follow-up on other changes and necessary administrative actions; for example, configuring the SQL Server Reporting Services Execution Account, email settings, or backing up the encryption key using Reporting Services Configuration Manager.

SQL Server 2017 Reporting Services also can integrate with Microsoft Power BI dashboards. A page in the Report Server Configuration Manager supports registering this installation of SQL Server Reporting Services with a Power BI account. You will be prompted to sign into Azure Active Directory. The account you provide must be a member of the Azure tenant where you intend to integrate with Power BI. The account should also be a member of the system administrator in SQL Server Reporting Services, via Report Manager, and a member of the sysadmin role in the SQL Server that hosts the Report Server database.

This is recommended, and so a SQL Server 2017 Setup with internet connectivity is the easiest way to carry out installation. This also could be described as a way to “slip-stream” updates, including hotfixes and cumulative updates, into the SQL Server installation process, eliminating these efforts post-installation.

For servers without internet access, there are two Setup.exe parameters that support downloading these files to an accessible location and making them available to Setup. When starting the SQL Server 2017 .iso’s Setup.exe from Windows PowerShell or the command line (you can read more about this in the next section), you can set the /UpdateEnabled parameter to FALSE to turn off the download from Windows Update. The /UpdateSource parameter can then be provided as an installation location of unpacked .exe files. Note that the /UpdateSource parameter is a folder location, not a file.

After you proceed past the Ready To Install page, and regardless of whether Setup was a complete success, it generates a number of log files in the following folder:

%programfiles%Microsoft SQL Server140Setup BootstrapLogYYYYMMDD_HHMMSS

Here’s an example:

C:Program FilesMicrosoft SQL Server140Setup BootstrapLog20170209_071118

However, when you run Setup using the /Q or /QS parameters for unattended installation, the log file is written to the Windows %temp% folder.

A log summary file of the installation is created that uses the following naming convention:

Summary_instancename_YYYYMMDD_HHMMSS.txt

Setup generates similar files for the Component and Global Rules portions of Setup as well as a file called Detail.txt. These files might contain the detailed error messages you are looking for when troubleshooting a failed installation. The Windows Application Event log might also contain helpful information in that situation.

Finally, a System Configuration Check report .htm file is generated each time you run Setup, as well.

You’ll also find the new SQL Server instance’s first error log encoded at UTC time in this folder, showing the log from startup, similar to the normal SQL Server Error Log.

Values provided in configuration files can prepopulate or override Setup settings. They also can configure Setup to run with the normal user interface or silently without any interface.

For the purposes of the installer, ensure that you always use the Administrator level for these two prompts. The title on each page should be preceded by “Administrator: ”; for example, Administrator: Windows PowerShell.

To start Windows PowerShell or command prompt as Administrator, in the Start menu, search for the desired application, right-click it, and then, on the shortcut menu that opens, select Run As Administrator.

Figure 4-2 shows an example of starting Setup.exe from the Windows PowerShell prompt, and Figure 4-3 shows starting it from the command prompt.

Sometimes, you also might find it necessary to start Setup from the command line or Windows PowerShell because of a workaround for a specific problem.

You can start Setup.exe with a configuration file by using the /CONFIGURATIONFILE parameter of Setup.exe, or by navigating to the Advanced page of the SQL Server Installation Center that starts with Setup.exe in Windows. Then, start Setup.exe with a configuration file by selecting the Install Based On A Configuration File check box. A message appears, asking you to browse to the .ini file. After you select the appropriate file, Setup.exe will start with those options.

One thing to keep mind, however, is that configuration files generated by Setup.exe do not store the passwords you provided for any service accounts. If you do want to configure service account credentials in your configuration file, for security reasons, do not store the service account passwords in plain text in a configuration file. You should instead store them securely and provide them when you run Setup.exe. Each service’s account information is available in a Setup.exe runtime parameter, which are listed in Table 4-1.

Table 4-1 Common Setup.exe parameters and their purposes

Service	Parameter name	Description
SQL Server Database Engine	/SQLSVCPASSWORD	Password for the main SQL Server Database Engine Services service account. This is the service account for sqlservr.exe. It is required if a domain account is used for the service.
SQL Server Agent	/AGTSVCPASSWORD	Password for the SQL Server Agent service account. This is the service account for sqlagent.exe. It is required if a domain account is used for the service.
sa password	/SAPWD	Password for the sa account. It is required if Mixed Mode is selected, or when /SECURITYMODE=SQL is used.
Integration Services	/ISSVCPASSWORD	Password for the Integration Services service. It is required if a domain account is used for the service.
Reporting Services (Native)	/RSSVCPASSWORD	Password for the Reporting Services service. It is required if a domain account is used for the service.
Analysis Services	/ASSVCPASSWORD	Password for the Analysis Services service account. It is required if a domain account is used for the service.
PolyBase	/PBDMSSVCPASSWORD	Password for the PolyBase engine service account.
Full-Text filter launcher service	/FTSVCPASSWORD	Password for the Full-Text filter launcher service.

For example, in the snippet that follows, the PROD_ConfigurationFile_Install.INI has provided the account name of the of the SQL Server Database Engine service account, but the password is provided when Setup.exe runs:

Click here to view code image

Setup.exe /SQLSVCPASSWORD="securepasswordhere"
/ConfigurationFile="d:SQLInstallerConfiguration
FilesPROD_ConfigurationFile_Install.INI"

You can provide further parameters like passwords when you run Setup. Parameter settings provided will override any settings in the configuration file, just as the configuration file’s settings will override any defaults in the Setup operation. Table 4-2 lists and describes the parameters.

Table 4-2 Common Setup.exe parameters of which you should be aware

Parameter usage	Parameter	Description
Unattended installations	/Q	Specifies Quiet Mode with no user interface and user interactivity allowed.
Unattended installations	/QS	Specifies Quiet Mode with user interface but no user interactivity allowed.
Accept license terms	/IACCEPTSQLSERVERLICENSETERMS	Must be provided in any Configuration File looking to avoid prompts for installation.
R open license accept terms	/IACCEPTROPENLICENSEAGREEMENT	Similarly, must provide this parameter for any unattended installation involving either of the two R Server options.
Configuration file	/CONFIGURATIONFILE	A path to the configuration .ini file to use.
Instant file initialization	/SQLSVCINSTANTFILEINIT	Set to true to Grant Perform Volume Maintenance Task privilege to the SQL Server Database Engine Service (recommended)
TempDB data file count	/SQLTEMPDBFILECOUNT	Set to the number of desired TempDB data files to be installed initially.

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.

Click here to view code image

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

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.

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.

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.

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

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):

Click here to view code image

<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 ServerMSSQL14.MSSQLSERVERMSSQLBinn launcher.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:

Click here to view code image

RHOME=C:PROGRA~2MICROS~1MSSQL1~4.SQLR_SERV~2
MPI_HOME=C:Program FilesMicrosoft MPI
INSTANCE_NAME=SQL2K17
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:Program FilesMicrosoft SQL
ServerMSSQL14.SQL2K17MSSQLExtensibilityData
PKG_MGMT_MODE=0
MEMORY_LIMIT_PERCENT=25

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.

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.

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

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.

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 WindowsCluster folder on each cluster node, if they exist.

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.

For more about the Lock pages in memory setting, see Chapter 2.

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

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.

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

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

Click here to view code image

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:

Click here to view code image

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

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.

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.

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.

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

When the Integration Services Catalog is created, a new user database called SSISDB is also created. You should back it up and treat it as an important production database.

You should create the SSISDB catalog database soon after installation and before a SQL Server Integration Services development can take place. You will need to do this only once. Because this will involve potential Surface Area Configuration changes and the creation of a new strong password, a SQL DBA, not a SQL Server Integration Services developer, should perform this and store the password securely alongside others generated at the time of installation.

In Object Explorer, connect to your instance, right-click Integration Services Catalog, and then, on the shortcut menu, click Create Catalog. In this single-page setup, you must select the Enable CLR Integration check box, decide whether SQL Server Integration Services packages should be allowed to be run at SQL Server Startup (we recommend this due to maintenance and cleanup performed then), and provide an encryption password for the SSISDB database.

The encryption password is for the SSISDB database master key. After you create it, you should then back up the SSISDB database master key.

For more information on database master keys, see Chapter 7.

The SSISDB database will contain SQL Server Integration Services packages, their connection strings, and more data about the packages. The encryption would not allow these sensitive contents to be decrypted by a malicious user who gains access to the database files or backups. This password would be required if the database were moved to another server, so you should store it in a secure location within your enterprise.

Open the Reporting Services Configuration Manager application, connect to the newly installed SQL Server Reporting Services instance, and then review the following options, from top to bottom:

• Service Account. You can change the SQL Server Reporting Services service account here. Remember that you should use only the Reporting Services Configuration Manager tool to make this change.

• Web Service URL. The web service URL is not for user interaction; rather, it is for the Report Manager and custom applications to programmatically connect to the SQL Server Reporting Services instance.

  By default, a web service on TCP Port 80 is created called ReportServer. For named instances, the web service will be called ReportServer_instancename. The URL for the webservice would then be

  http://servername/ReportServer

  or:

  http://servername/ReportServer_instancename

  To accept defaults, at the bottom of the application window, click Apply.

  You can optionally configure an SSL certificate to a specific URL here, and the Reporting Services Configuration Manager will make the changes necessary.

• Database. Each reporting service requires a pair of databases running on a SQL Server instance. If you selected the Install And Configure check box in SQL Server Setup, the databases will have been created for you with the names ReportServer and ReportServerTempDB, or, for a named instance, ReportServer$InstanceName and ReportServer$InstanceNameTempDB. Both of these databases are important and you should create backups. The ReportServerTempDB is not a completely transient database like the SQL Server instance’s TempDB system database.

  To set the databases for a new instance, click Change Database, and then follow the Create A New Report Server Database Wizard. This will add both databases to the instance.

• Web Portal URL. The web portal URL is the user-friendly website that hosts links to reports and provides for administrative features to the SQL Server Reporting Services instance. This is the link to share with users if you will be using the SQL Server Reporting Services portal.

  By default, the URL for the web portal is /Reports

  Http://servername/Reports for the default instance

  or:

  Http://servername/Reports_InstanceName for named instances

  You can change the name from the default here if desired. To proceed, at the bottom of the application window, click Apply.

• Email Settings. You use these email settings are used for sending reports to user subscribers via email. SQL Server Reporting Services uses its own Email Settings and does not inherit from the SQL Server instance’s Database Mail settings. This setting is optional if you do not intend to send reports to subscribers via email.

  First introduced in SQL Server 2016 was the ability for SQL Server Reporting Services to authenticate to an external SMTP server using anonymous (No Authentication), Basic, or NT LAN Manager (NTLM) authentication, which will use the SQL Server Reporting Services service account to authenticate to the SMTP server.

  Prior to SQL Server 2016, authentication to external SMTP servers required the installation of a local SMTP server to provide a relay to the external, Azure-based (such as SendGrid) or cloud-based SMTP server. With SQL Server 2016 and 2017, SMTP connections can be made directly to these external SMTP servers.

  ---

  NOTE

  Keep in mind that enterprise SMTP servers have an allow list of IP addresses, and you will need to add this server’s IP to this list to send email.

  ---

• Execution Account. You can provide this domain account optionally to be used when reports are configured to run on a schedule, to run without credentials (select the Credentials Are Not Required Option) or to connect to remote servers for external images.

  The execution account should not be the same as the SQL Server Reporting Services service account.

  This account should have minimal read-only access to any data sources or remote connections that will require it. You also can give it EXECUTE permissions for data sources that use stored procedures, but you should never give it any additional SQL Server permissions or let it be a member of any SQL Server server roles, including sysadmin.

• Encryption Keys. Immediately after installation and after the two SQL Server Reporting Services databases have been created, back up this instance’s encryption keys. This key is used to encrypt sensitive information such as connection strings in the two databases. If the databases are restored to another server and this key is not available from the source server, credentials in connection strings will not be usable and you will need to provide them again for the reports to run successfully on a new server.

  If you can no longer locate the backup of a key, use the Change operation on this page to replace the key, and then back it up.

  To restore the original key to a new server to which the databases have been moved, use the Restore operation on this page.

• Subscription Settings. Use this page to specify a credential to reach file shares to which report subscriptions can be written. Reports can be dropped in this file share location in PDF, Microsoft Excel, or other formats for consumption.

  Multiple subscriptions can use this file share credential, which can be used on this page in a central location.

  This account should be different from the SQL Server Reporting Services execution account to serve its purpose appropriately.

• Scale-Out Deployment. Visit this page on multiple SQL Server Reporting Services instances to join them together. By using the same SQL Server Reporting Services databases for multiple SQL Server Reporting Services instances, multiple front ends can provide processing for heavy reporting workloads, including heavy subscription workloads. The server names can be used in a network load balancer such as a Network Load Balancing Cluster.

  Upon first installation, the Scale-Out Deployment page will show that the instance is “Joined” to a single server scale-out.

  Each scale-out instance of SQL Server Reporting Services must use the same settings on the Database page of the Reporting Services Configuration Manager. Connect to each instance in the scale-out and visit this page by opening it on each SQL Server Reporting Services instance to view the status, add servers to the scale-out, or remove servers.

• PowerBI Integration. Visit this page to associate the SQL Server Reporting Services instance to a Microsoft Power BI account, specifically to an account in Azure Active Directory. The administrator joining the Power BI instance to the SQL Server Reporting Services instance must be a member of the Azure Active Directory, a system administrator of the SQL Server Reporting Services instance, and a sysadmin on the SQL Server instance that hosts the SQL Server Reporting Services databases.

For the latest information on Power BI/SQL Server Reporting Services integration and the latest Azure authentication features, search for MT598750 or visit https://docs.microsoft.com/sql/reporting-services/install-windows/power-bi-report-server-integration-configuration-manager.

Because of the nature of SQL Server Analysis Services databases, their size, and how they are populated, typically they are not updated on a schedule, but you can do so by passing an XMLA command via a SQL Server Agent job step: type SQL Server Analysis Services Command. You also can initiate manual backups of SQL Server Analysis Services databases in Object Explorer in SQL Server Management Studio as well as restore SQL Server Analysis Services databases.

When installing SQL Server Analysis Services, a security group should have been chosen to grant permissions to SQL Server Analysis Services server administrators, granting a team full access to the server.

If you need to add a different group to the administrator role of the SQL Server Analysis Services instance, open SQL Server Management Studio, and then, in Object Explorer, connect to the Analysis Services instance. Right-click the server, and then, on the shortcut menu, click Properties. On the Security page, you can add additional windows-authenticated accounts or groups to the administrator role.

We have discussed a number of database configurations in Chapter 3, including the physical configuration of files and storage.

For information on Azure SQL Databases, refer to Chapter 5. The remainder of this chapter refers to SQL Server instance databases.

When copying a database into a new environment, keep in mind the following:

• Edition

• Version and compatibility mode

• SQL logins

• Database-scoped configurations

• Database settings

• Encryption

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

The concern for DBAs is when databases need to move down from Enterprise, Standard, or Web edition. A large number of features that had historically been exclusive to Enterprise edition were included in Standard edition for the first time with SQL Server 2016 SP1, expanding what we can do with Standard edition as developers and administrators.

You will encounter errors related to higher-edition features when restoring or attaching to an instance that does not support those editions. For example, when attempting to restore a data-compressed database to tables to an instance that does not support data compression, you will receive an error message similar to “cannot be started in this edition of SQL Server because part or all of object ‘somecompressedindex’ is enabled with data compression.” In this case, you will need to manually remove data compression from the database in the source instance and then create a new backup or detach the database again before migrating to the lower-edition instance. You cannot turn off the use of higher-edition features on the lower-edition instance.

You can foresee this problem by using a dynamic management view that lists all edition-specific features in use. Keep in mind that some features are supported in all editions but are limited. For example, memory-optimized databases are supported even in Express edition but with only a small amount of memory that can be allocated.

For example,

Click here to view code image

USE [WIDEWORLDIMPORTERS];
SELECT FEATURE_NAME
FROM SYS.DM_DB_PERSISTED_SKU_FEATURES;

returns the following rows:

feature_name
Compression
Partitioning
ColumnStoreIndex
InMemoryOLTP

For example, restoring or attaching a database from SQL 2012 to SQL 2017 will result in the database assuming the SQL 2012 (11.0) compatibility mode inside the SQL 2017 environment. This is not necessarily a problem, but it does have consequences with respect to how you can use features and improvements and potentially how it performs.

You can view the compatibility level of a database in SQL Server Management Studio. To do so, in Object Explorer, right-click a database, and then, on the shortcut menu, click Properties. In the pane on the left, click Options. On the Options page, the Compatibility Level list box displays the current setting. You can change that setting or use the ALTER DATABASE command to change the COMPATIBILITY_LEVEL setting. You can also view this setting for all databases in the system catalog via sys.databases; look for the compatibility_level column.

SQL Server provides database compatibility modes for backward compatibility to database-level features, including improvements to the query optimizer, additional fields in dynamic management objects, syntax improvements, and other database-level objects.

For the scenario in which a SQL Server 2017 (internal version 140) instance is hosting a database in SQL Server 2012 (internal version 110) compatibility mode, it is important to note that applications are still connecting to a SQL Server 2017 instance. Only database-level features and options are honored in the prior compatibility modes.

For example, some recent syntax additions such as the new STRING_SPLIT() or OPENJSON functions, added in SQL Server 2016, will not work when run in the context of a database in a prior compatibility mode. Some syntax improvements, such as DATEFROMPARTS() and AT TIME ZONE, will work in any database in any compatibility mode in SQL Server 2017.

SQL Server 2017 supports compatibility levels down to SQL Server 2008 (internal version 100).

Changing the database compatibility level does not require a service restart to take place, but we strongly recommend that you do not perform this during normal operating hours. Promoting the database compatibility mode should be thoroughly tested in preproduction environments. Even though syntax errors are extremely unlikely, other changes to the query optimizer engine from version to version could result in performance changes to the application that must be evaluated prior to rollout to a production system. When you do upgrade production from a prior compatibility level, you should do so during a maintenance period, not during user activity.

For more information on the differences between compatibility modes since SQL 2005, reference MSDN article bb510680 or visit https://docs.microsoft.com/sql/t-sql/statements/alter-database-transact-sql-compatibility-level.

This condition must be repaired before applications and end users will be able to access the database in its new location. Refer to the section “Solving orphaned SIDs” in Chapter 6.

The database owner should be included in the security objects that should be accounted for on the new server. Ensure that the owner of the database, listed either in the Database Properties dialog box or the sys.databases owner_sid field, is still a valid principal on the new instance.

For databases with Partial Containment, contained logins for each type will be restored or attached along with the database, and this should not be a concern.

For more information on Parallelism and MaxDOP, go to Chapter 9.

You should evaluate these options for each database after it is copied to a new instance to determine whether the settings are appropriate. The desired MaxDOP, for example, could change if the number of logical processors differs from the system default.

You can view each of these database-scoped configurations in SQL Server Management Studio. In Object Explorer, right-click a database, and then, on the shortcut menu, click Properties. In the pane on the left, click Options. A heading just for database-scoped configurations appears at the top of the Other Options list. You can also view database-scoped configurations in the dynamic management view sys.database_scoped_configurations.

The following is not a list of all database settings, but we will cover these and many more later in the chapter. You should pay attention to these when restoring, deploying, or attaching a database to a new instance.

• Read Only. If the database was put in READ_ONLY mode before the migration to prevent data movement, be sure to change this setting back to READ_WRITE.

• Recovery Model. Different servers might have different backup and recovery methods. In a typical environment, the FULL recovery model is appropriate for production environments when the data loss tolerance of the database is smaller than the frequency of full backups, or when point-in-time recovery is appropriate. If you are copying a database from a production environment to a development environment, it is likely you will want to change the recovery model from FULL to SIMPLE. If you are copying a database from a testing environment to a production environment for the first time, it is likely you will want to change the recovery model from SIMPLE to FULL.

For more information about database backups and the appropriate recovery model, see Chapter 11.

• Page Verify Option. For all databases, this setting should be CHECKSUM. The legacy TORN_PAGE option is a sign that this database has been moved over the years up from a pre-SQL 2005 version, but this setting has never changed. Since SQL 2005, CHECKSUM has the superior and default setting, but it requires an administrator to manually change.

• Trustworthy. This setting is not moved along with the database. If it was turned on for the previous system and was a requirement because of external assemblies, cross-database queries, and/or Service Broker, you will need to turn it on again. It is not recommended to ever turn on this setting unless it is made necessary because of an inflexible architecture requirement. It could allow for malicious activity on one database to affect other databases, even if specific permissions have not been granted. It is crucial to limit this setting and understand cross-database permission chains in a multitenant or web-hosted shared SQL Server environment.

For more on object ownership, see Chapter 6.

For more information on transparent data encryption, see Chapter 7.

Let’s compare two different simplified migration processes. Following is a sample migration checklist using a FULL backup/restore:

For more information on the types of database backups and database restores, see Chapter 11.

1. Begin application outage.

2. Perform a FULL backup of the database.

3. Copy the database backup file.

4. Restore the FULL backup.

5. Resolve any SQL-authenticated login security issues or any other changes necessary before use.

6. In applications and/or DNS and/or aliases, change connection strings.

7. End application outage.

In the preceding scenario, the application outage must last the entire span of the backup, copy, and restore, which for large databases could be quite lengthy, even with native SQL Server backup compression reducing the file size.

Instead, consider the following strategy:

1. Perform a FULL compressed backup of the database.

2. Copy the database backup file.

3. Restore the FULL backup WITH NORECOVERY.

4. Begin application outage.

5. Take a differential backup and then a log backup of the database.

6. Copy the differential backup file and the log backup file to the new server.

7. Restore the differential backup file WITH NORECOVERY.

8. Restore the transaction log backup WITH RECOVERY.

9. Resolve any SQL-authenticated login security issues or any other changes necessary before use.

10. In applications and/or DNS and/or aliases, change the connection strings.

11. End application outage.

In this scenario, the application outage spans only the duration of the differential and transaction log’s backup/copy/restore operation, which for large databases should be a tiny fraction of the overall size of the database. This scenario does require more preparation and scripting in advance, and it requires coordination with the usual backup system responsible for transaction log backups. By taking a manual transaction log backup, you can create a split transaction log backup chain for another system, for which you should take account.

Attaching copied database files can be faster than restoring a full database backup; however, it lacks the ability to minimize the outage by taking advantage of transaction log backups (see earlier).

Copying the full set of database files (remember that the database might contain many more files than just the .mdf and .ldf files, including secondary data files and FILESTREAM containers) is not faster than restoring a transaction log backup during the application outage, and it is not a true recovery method. Because database backup files can also be compressed natively by SQL Server, the data transfer duration between the Windows servers will be reduced by using the backup/restore strategy.

However, the location of the model database’s files is not used as a default for new databases. Instead, the default location for database files is stored at the server level. You can view these default storage locations, which should be changed and must be valid, in the Server Properties dialog box in SQL Server Management Studio, on the Database Settings page. There you will find the default locations for Data, Log, and Backup files. These locations are stored in the registry.

On this page, you’ll also see the default recovery interval setting, which is by default 0, meaning that SQL Server can manage the frequency of internal automatic CHECKPOINTs. This typically results in an internal checkpoint frequency of one minute. This is an advanced setting that can be (though rarely is) changed at each database level, though it should not be changed at the server level or database level except by experienced administrators.

Also on the Database Settings page of Server Properties you will find the default index fill factor and default backup compression setting. These are server-level defaults applied to each database, but you cannot configure them separately for each database. You can configure them independently at each index level and with each backup statement, respectively.

Among the settings inherited by new databases from the model database unless overridden at the time of creation are the following:

• Initial data and log file size

• Data and log file Autogrowth setting

• Data and log file Maximum size

• Recovery model

• Target Recovery Time (which would override the system default recovery interval)

• All Database-Scoped Configurations including the database-level settings for Legacy Cardinality Estimation, Max DOP, Parameter Sniffing, and Query Optimizer Fixes.

• All the Automatic settings, including auto close, auto shrink, auto create/update statistics. (We discuss each of these later in the chapter.)

Ideally, databases are not owned by named individual accounts. You might decide to change each database to a service account specific to that database’s dependent applications. You must do this after the database is created. You cannot change the database owner via SQL Server Management Studio; instead, you must use the ALTER AUTHORIZATION T-SQL statement.

For more information on the best practices with respect to database ownership and how to change the database owner, see Chapter 6.

However, adding additional log files long term is not a wise decision. There is no performance advantage to be gained with more than one transaction log file for a database. SQL Server will not write to them randomly, but sequentially.

The only scenario in which a second transaction log file would be needed is if the first had filled up its volume. If no space can be created on the volume to allow for additional transaction log file data to be written, the database cannot accept new transactions and will refuse new application requests. In this scenario, one possible troubleshooting method is to temporarily add a second transaction log file on another volume to create the space to allow the database transactions to resume accepting transactions. The end resolution involves clearing the primary transaction log file, performing a one-time-only shrink to return it to its original size, and removing the second transaction log file.

After you have configured the new database’s settings but before you click OK, you can script the T-SQL for the CREATE DATABASE statement.

Here are a few suggestions when creating a new database:

• Pregrow your database and log file sizes to an expected size. This avoids autogrowth events as you initially populate your database. You can speed up this process greatly by using the Perform Volume Maintenance Task permission for the SQL Server service account so that instant file initialization is possible.

We covered instant file initialization earlier in this chapter.

• Consider the SIMPLE recovery model for your database until it enters production use. Then, the FULL or BULK_LOGGED recovery models might be more appropriate.

For more information database backups and the appropriate recovery model, see Chapter 11.

• Review the logical and physical files names of your database and the locations. The default locations for the data and log files are a server-level setting but you can override them here. You also can move the files later on (we cover this later in this chapter).

• As soon as the database is created, follow-up with your backup strategy to ensure that it is covered as appropriate with its role.

SQL Server Data Tools can generate incremental change scripts or deploy databases directly. It also has the option to drop or re-create databases for each deployment, though this is turned off by default.

You might find it easiest to create the new database by using SQL Server Management Studio and then deploy incremental changes to it with SQL Server Data Tools.

You can view each of these settings in SQL Server Management Studio via Object Explorer. To do so, right-click a database, and then, on the shortcut menu, click Properties. In the Database Properties dialog box, in the pane on the left, click Options. You also can review database settings for all databases in the sys.databases catalog view.

The subsections that follow discuss the settings that you need to consider when creating and managing SQL Server databases.

Oftentimes databases differ from the server-level collation to enforce case sensitivity, but you can also enforce language usage differences (such as kana or accent sensitivity) and sort order differences at the database level.

The default collation for the server is decided at installation and is preselected for you based on the regionalization settings of the Windows Server. You can override this during installation. Some applications, such as Microsoft Dynamics GP, require a case-sensitive collation.

Whereas the server-level collation is virtually unchangeable, databases can change collation. You should change a database’s collation only before code is developed for the database or only after extensive testing of existing code.

Be aware that unmatched collations in databases could cause issues when querying across those databases, so you should try to avoid collation differences between databases that will be shared by common applications.

For example, if you write a query that includes a table in a database that’s set to the collation SQL_Latin1_General_CP1_CI_AS (which is case insensitive and accent sensitive) and a join to a table in a database that’s also set to SQL_Latin1_General_CP1_CS_AS, you will receive the following error:

Click here to view code image

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in the equal to operation.

Short of changing either database to match the other, you will need to modify your code to use the COLLATE statement when referencing columns in each query, as demonstrated in the following example:

Click here to view code image

… FROM
CS_AS.sales.sales s1
INNER JOIN CI_AS.sales.sales s2
ON s1.[salestext] COLLATE SQL_Latin1_General_CP1_CI_AS = s2.[salestext]

In contained databases, collation is defined at two different levels: the database and the catalog. You cannot change the catalog collation cannot from Latin1_General_100_CI_AS_WS_KS_SC. Database metadata and variables are always in the catalog’s collation. The COLLATE DATABASE_DEFAULT syntax can also be a very useful tool if you know the collation before execution.

For more information on database backups and the appropriate recovery model, see Chapter 11.

Compatibility mode is a database-level setting, and databases upgraded from an older version to a new version will retain a prior compatibility mode. For example, some new syntax additions in SQL Server 2016 such as the new STRING_SPLIT() or OPENJSON functions will not work when run in the context of a database in a prior compatibility mode. Other syntax improvements, such as DATEFROMPARTS() and AT TIME ZONE, will work in any database in any compatibility mode in SQL Server 2017.

SQL Server 2017 supports compatibility levels down to SQL Server 2008 (internal version 100), the same as SQL Server 2016.

Database compatibility does not require a service restart to take place, but we strongly recommend that you do not perform this during normal operating hours. Promoting the database compatibility mode should be thoroughly tested in preproduction environments. Even though syntax errors are extremely unlikely, other changes to the query optimizer engine from version to version could result in performance changes to the application that must be evaluated prior to rollout to a production system. When you do upgrade production from a prior compatibility level, you should do so during a maintenance period, not during user activity.

You should review database-specific settings at the time of migration, as well. You can review them from a quick scroll of the sys.databases catalog view or from the database properties window in SQL Server Management Studio.

The following is not a list of all database settings, but you should pay attention to these when restoring, deploying, or attaching a database to a new instance.

For more information about the security implications of contained databases, see Chapter 6.

Azure SQL databases are themselves a type of contained database, able to move from host to host in the Azure platform as a service (PaaS) environment, transparent to administrators and users. You can design databases that can be moved between SQL Server instances in a similar fashion, should the application architecture call for such capability.

Changing the Containment Type from None to Partial converts the database to a partially contained database, and should not be taken lightly. We do not advise changing a database that has already been developed without the partial containment setting, because there are differences with how temporary objects behave and how collations are enforced. Some database features, including Change Data Capture, Change Tracking, replication, and some parts of Service Broker are not supported in partially contained databases. You should carefully review, while logged in as a member of the sysadmin server role or the db_owner database role, the system dynamic management view sys.dm_db_uncontained_entities for an inventory of objects that are not contained.

This will have an effect only on new statistics created after this setting is turned on. When you turn it on, you should update the statistics on tables with partitions, including the INCREMENTAL = ON parameter, as shown here:

Click here to view code image

UPDATE STATISTICS [dbo].[HoriztonalPartitionTable] [PK_HorizontalPartitionTable] WITH
RESAMPLE, INCREMENTAL = ON;

You also should update any manual scripts you have implemented to update statistics to use the ON PARTITIONS parameter when applicable. In the catalog view sys.stats, the is_incremental column will equal 1 if the statistics were created incrementally, as demonstrated here:

Click here to view code image

UPDATE STATISTICS [dbo].[HoriztonalPartitionTable] [PK_HorizontalPartitionTable] WITH
RESAMPLE ON PARTITIONS (1);

For much more on Snapshot Isolation and other isolation levels, see Chapter 9.

There will be an impact to the utilization of the TempDB as well as a rise in the IO_COMPLETION and WAIT_XTP_RECOVERY wait types, so you need to perform proper load testing. This setting, however, is potentially a major performance improvement and the core of enterprise-quality concurrency.

For more on object ownership, see Chapter 6.

You can view each of these Database-Scoped Configurations in SQL Server Management Studio. In Object Explorer, right-click a database, and then, on the shortcut menu, click Properties. In the Database Properties dialog box, in the pane on the left, click Options. On the Options page, a heading just for Database-Scoped Configurations appears at the top of the Other Options list.

The current database context is important for determining which database’s properties will be applied to a query that references objects in multiple databases. This means that the same query, run in two different database contents, will have different execution plans, potentially because of differences in each database’s Max DOP setting, for example.

For more information on the Query Store, see Chapter 9.

This is an advanced topic, and one that we won’t dive into too deeply, save for one configuration option that you should change on databases that have been upgraded from versions prior to SQL Server 2016.

What is a checkpoint? This is the process by which SQL Server writes to the drive both data and transaction log pages modified in memory, also known as “dirty” pages. Checkpoints can be issued manually by using the CHECKPOINT command but are issued in the background for you, so issuing CHECKPOINT is rarely necessary and is usually limited to troubleshooting.

What is automatic checkpoint? Prior to SQL Server 2016 and since SQL Server 7.0, by default all databases used automatic checkpoint. The rate with which dirty pages were committed to memory has increased with versions, as disk I/O and memory capacities of servers have increased. The goal of automatic checkpoint was to ensure that all dirty pages were managed within a goal defined in the server configuration option Recovery Interval. By default, this was 0, which meant it was automatically configured. This tended to be around 60 seconds, but was more or less unconcerned with the number of pages dirtied by transactions between checkpoints.

What is indirect checkpoint? This is a new strategy of taking care of “dirty pages” that is far more scalable and can deliver a performance difference especially on modern systems with a large amount of memory. Indirect checkpoints manage dirty pages in memory differently; instead of scanning memory, indirect checkpoints proactively gather lists of dirty pages. Indirect checkpoints then manage the list of dirty pages and continuously commit them from memory to the drive, on a pace to not exceed an upper bound of recovery time. This upper bound is defined in the database configuration option TARGET_RECOVERY_TIME. By default, in databases created in SQL Server 2016 or higher, this is 60 seconds. In databases created in SQL Server 2012 or 2014, this option was available but set to 0, which indicates that legacy automatic checkpoints are in use.

So, even though the recovery time goal hasn’t really changed, the method by which it is achieved has. Indirect checkpoints are significantly faster than automatic checkpoints, especially as servers are configured with more and more memory. You might notice an improvement in the performance of backups specifically.

You can configure a database that was created on an older version of SQL Server to use indirect checkpoints instead of automatic checkpoints with a single command. The TARGET_RECOVERY_TIME will be 0 for older databases still using automatic checkpoint. The master database will also have a TARGET_RECOVERY_TIME of 0 by default, though msdb and model will be set to 60 starting with SQL Server 2016.

Consider setting the TARGET_RECOVERY_TIME database configuration to 60 seconds to match the default for new databases created in SQL Server 2016 or higher, as shown here:

Click here to view code image

ALTER DATABASE [olddb] SET TARGET_RECOVERY_TIME = 60 SECONDS WITH NO_WAIT;

You can check this setting for each database in the TARGET_RECOVERY_TIME_IN_SECONDS column of the system view sys.databases.

Ideally, there should be no data or log files on the OS volume, even system database files. You can move these after SQL Server Setup is complete, however.

When you’re planning to move your database data or log files, prepare their new file path location by granting FULL CONTROL permissions to the per-SID name for the SQL Server instance. (Note that this is not necessarily the SQL Server service account.) For the default instance, this will be NT SERVICEMSSQLSERVER; for default instances, it will be NT SERVICEMSSQL$instancename.

The OFFLINE option is one way to quickly remove a database from usability. It is also the most easily reversed, as demonstrated here:

SET ONLINE;

You should set maintenance activities to ignore databases that are offline because they cannot be accessed, maintained, or backed up. The data and log files remain in place in their location on the drive and can be moved. The database is still listed with its files in sys.master_files.

Taking a database offline is an excellent intermediate administrative step before you DETACH or DROP a database; for example, a database that is not believed to be used any more. Should a user report that she can no longer access the database, the administrator can simply bring the database back online—an immediate action.

You can separate a database’s files from the SQL Server by using a DETACH. The data and log files remain in place in their location on the drive and can be moved. But detaching a database removes it from sys.master_files.

To reattach the database, in SQL Server Management Studio, in Object Explorer, follow the Attach steps. It is not as immediate an action and requires more administrative intervention than taking the database offline.

When reattaching the database, you must locate at least the primary data file for the database. The Attach process will then attempt to reassociate all the database files to SQL Server control, in their same locations. If their locations have changed, you must provide a list of all database files and their new locations.

Finally, a DROP DATABASE command, issued when you use the Delete feature of Object Explorer, removes the database from the SQL Server and deletes the database files on the drive. An exception to the delete files on drive behavior is if the destination database is offline. Deleting an offline database and detaching a database are therefore similar actions.

Dropping a database does not by default remove its backup and restore history from the msdb database, though there is a check box at the bottom of the Drop Database dialog box in SQL Server Management Studio that you can select for this action. The stored procedure msdb.dbo.sp_delete_database_backuphistory is run to remove this history. For databases with a long backup history that has not been maintained by a log history retention policy, the step to delete this history can take a long time and could cause SQL Server Management Studio to stop responding. Instead, delete old backup and restore history incrementally by using msdb.dbo.sp_delete_backuphistory and/or run the msdb.dbo.sp_delete_database_backuphistory procedure in a new SQL Server Management Studio query window.

For more information on backup and restore history, see Chapter 13.

Use the following steps to move user database files:

1. Perform a manual full backup of the soon-to-be affected databases.

2. During a maintenance outage for the database and any applications that are dependent, begin by taking the user database offline and then running a T-SQL script to alter the location of each database file.

3. Here’s an example of the T-SQL statements required:

   Click here to view code image

   ALTER DATABASE database_name SET OFFLINE WITH ROLLBACK IMMEDIATE
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_data_file_name,
   FILENAME = 'locationphysical_data_file_name.mdf' );
   ALTER DATABASE database_name MODIFY FILE ( NAME = logical_log_file_name,
   FILENAME = 'locationphysical_log_file_name.ldf' );
   ALTER DATABASE database_name SET ONLINE

4. While the database is offline, physically copy the database files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.) When the file operation is complete, bring the database back online.

5. Verify that the data files have been moved by querying sys.master_files, which is a catalog view that returns all files for all databases. Look for the physical_name volume to reflect the new location correctly.

6. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the drive space.

7. After you have successfully moved the database files, you should perform a manual backup of the master database.

1. Begin by performing a manual full backup of the soon-to-be affected databases.

2. For model, msdb, and TempDB, begin by running a T-SQL script (similar to the script for moving user databases). SQL Server will not use the new locations of the system databases until the next time the service is restarted. You cannot set the system databases to offline.

3. During a maintenance outage for the SQL Server instance, stop the SQL Server instance, and then copy the database files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.) The only exception here is that the TempDB data and log files do not need to be moved—they will be re-created automatically by SQL Server upon service start.

4. When the file operation is complete, start the SQL Server service again.

5. Verify that the data files have been moved by querying sys.master_files. Look for the physical_name volume to reflect the new location correctly.

6. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the drive space.

7. After you have successfully moved the database files, perform a manual backup of the master database.

If you encounter problems starting SQL Server after moving system databases to another volume—for example if the SQL Server service account starts and then stops—check for the following:

1. Verify that the SQL Server service account and SQL Server Agent service account have permissions to the new folders location. Review the following link for a list of File System Permissions Granted to SQL Server service accounts: https://docs.microsoft.com/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions#Reviewing_ACLs

2. Check the Windows Application Event Log and System Event Log for errors.

3. If you cannot resolve the issue, if necessary, start SQL Server with Trace Flag T3608, which does not start the SQL Server fully, only the master database. You then can move all other database files, including the other system databases, back to their original location by using T-SQL commands issued through SQL Server Management Studio.

1. On the Startup Parameters page, notice that there are three entries containing three files in their current paths. (If you have other startup parameters in this box, do not modify them now.)

   Edit the two parameters beginning with -d and -l (lowercase “L”). The -e parameter is the location of the SQL Server Error Log; you might want to move that, as well.

   After editing the master database data file (-d) and the master database log file (-l) locations, click OK. Keep in mind that the SQL Server service will not look for the files in their new location until the service is restarted.

2. Stop the SQL Server service, and then copy the master database data and log files to their new location. (You will delete the old copies when you’ve confirmed the new configuration.)

3. When the file operation is complete, start the SQL Server service again.

4. Verify that the data files have been moved by querying sys.master_files, a dynamic management view that returns all files for all databases. Look for the physical_name volume to reflect the new location correctly.

5. After you have verified that SQL Server is recognizing the database files in their new locations, delete the files in the original location to reclaim the drive space.

For example, when performing a restore, the connection must have exclusive access to the database. By default, the restore will wait until it gains exclusive access. You could attempt to discontinue all connections, but there is a much easier way: setting a database to SINGLE_USER mode removes all other connections but your own.

Setting a database to SINGLE_USER mode also requires exclusive access. If other users are connected to the database, running the following statement will be unsuccessful:

Click here to view code image

ALTER DATABASE database_name SET SINGLE_USER;

It is then necessary to provide further syntax to decide how to treat other connections to the database.

• WITH NO_WAIT. The ALTER DATABASE command will fail if it cannot gain exclusive access to the database It is important to note that without this statement or any other WITH commands, the ALTER DATABASE command will wait indefinitely.

• WITH ROLLBACK IMMEDIATE. Rollback all conflicting requests, ending other SQL Server Management Studio Query window connections, for example.

• WITH ROLLBACK AFTER n SECONDS. Delays the effect of WITH ROLLBACK IMMEDIATE by n SECONDS, which is not particularly more graceful to competing user connections, just delayed.

For example:

Click here to view code image

ALTER DATABASE databasename
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Instead of issuing a WITH ROLLBACK, you might choose to identify other sessions connected to the destination database; for example, by using the following:

Click here to view code image

SELECT *
FROM sys.dm_exec_sessions
WHERE
db_name(database_id) = ‘database_name’;

And then evaluate the appropriate strategy for dealing with any requests coming from that session, including communication with that user and closing of unused connections to that database in dialog boxes, SQL Server Management Studio query windows, or user applications.

After you have completed the activities that required exclusive access, set the database back to MULTI_USER mode:

Click here to view code image

ALTER DATABASE database_name SET MULTI_USER;

You need to gain exclusive access to databases prior to a restore. This script to change the database to SINGLE_USER and back to MULTI_USER is a common step wrapped around a database restore.

For more information on database restores, see Chapter 11.