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.

What to do before installing SQL Server

Before you run the SQL Server installer, there are a number of factors and settings that you should consider, some of which you cannot easily change after installation. Pay special attention to sections in this chapter regarding server volume alignment (whether this is a physical or virtual server, or whether the volumes are physical drives or Storage-Area Network–based), version and edition choices, and new features of the SQL Server 2017 installer.

Deciding on volume usage

When you’re configuring a Microsoft Windows Server, before starting the SQL Server installer, consider the volumes. Although you can move user and system database data and log files to other locations after installation, it’s best to plan your volumes prior to installation.

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

Important SQL Server volume settings

There are some settings that you need to consider for volumes that will host SQL Server data and log files, and this guidance applies specifically to these volumes (for other volumes—for example, those that contain the OS, application files, or backup files—the default Windows settings are acceptable unless otherwise specified):

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

  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.

SQL Server editions

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.

Changing SQL Server editions and versions

Upgrading editions in-place is supported by a feature of the SQL Server 2017 installer. You can upgrade in the following order: Express, Web, Standard, Enterprise. You also can upgrade Developer edition to Enterprise, Standard, or Web edition in SQL Server 2017.

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.

Installing a new instance

In this section, we discuss how to begin a new SQL Server 2017 instance installation, upgrade an existing installation, or add features to an existing instance.

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.

Planning for multiple SQL Server instances

You can install as many as 50 SQL Server instances on a Windows Server; however, we do not recommend this. In a Windows failover cluster, the number of SQL Server instances is reduced by half if you’re using shared cluster drives.

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.

Installing a SQL Server instance

The instructions in this chapter are the same for the first installation or any subsequent installations, whether for the default or any named instances of SQL Server 2017. As opposed to an exhaustive step-by-step instruction list for installations, we’ve opted to cover the important decision points and the information you need, plus new features introduced in SQL Server 2016 and 2017.

Inside the SQL Server Installation Center

While logged in as a local Windows administrator, begin by mounting the installation .iso to the Windows server. These days, this rarely involves inserting a physical disc or USB flash drive; although you can use them if necessary. Unpacking the contents of the .iso file to a physical file folder over the network would also provide for a faster SQL Setup experience.

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.

Installing SQL Server

In the SQL Server Installation Center, in the pane on the left side, click Installation. Although what follows in this chapter is not a step-by-step walk-through, we’ll cover key new features and decision points.

Installing options and features

In this section, we discuss the installation details of other features of SQL Server, including new options in Setup that were not available in previous versions.

Data analytics and artificial intelligence features

The Feature Selection page has a pair of new options in SQL Server 2017, both greatly expanding SQL Server’s footprint into the big data field. Let’s take a look at each option.

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 Service\MSSQLLaunchpad 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.

Grant Perform Volume Maintenance Tasks feature of SQL Server Setup

On the same Server Configuration Setup page on which service accounts are set, you will see a check box labeled Grant Perform Volume Maintenance Task Privilege To The SQL Server Database Engine Service. This option was added in SQL Server 2016.

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.

Default settings for the TempDB database

Starting with SQL Server 2016, SQL Server Setup provides a more realistic default configuration for the number and size of TempDB data files. This was a common to-do list for all post-installation checklists for DBAs since the early days of SQL Server.

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.

Mixed Mode authentication

SQL Server supports two modes of authentication: Windows and SQL Server.

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.

Installing other core features

Aside from the SQL Server service itself, three common core features of the product might be common to your installations. SQL Server Analysis Services, SQL Server Integration Services, and SQL Server Reporting Services are part of the license and are provided at no additional cost. If you need them, this section covers installing these features using Setup. Later in this chapter, we review the post-installation steps necessary to use them.

Installing SQL Server Analysis Services

Installing SQL Server Analysis Services requires you to make a decision at installation time regarding the mode in which it can be installed. Each instance of SQL Server Analysis Services can be in only one mode, which means that with a single license, you can run only the classic Multidimensional mode, the newer Tabular mode (introduced in SQL 2012), or the Power Pivot mode. Ask your business intelligence decision makers which platform you should use. Following are brief descriptions of each mode:

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

Installing SQL Server Integration Services

The SQL Server Integration Services instance for SQL Server 2017 is installed once per server per version, not once per instance, like other features. However, starting in SQL Server 2017, a new Integration Services Scale Out Configuration is available. We discuss this new feature further in the next section.

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 Service\MsDtsServer140. 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.

Installing SQL Server Integration Services Scale Out configuration

A new feature in SQL Server 2017, Integration Services now supports a Scale Out configuration by which you can run a package on multiple SQL Server instances. This also allows for high availability of SQL Server Integration Services, with the secondary.

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 Server\140\DTS\Binn\.

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 Service\SSISScaleOutMaster140 and NT Service\SSISScaleOutWorker140, 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.

Installing SQL Server Reporting Services

Starting with SQL Server 2017, SQL Server Reporting Services is no longer found in the SQL Server Setup media; it is instead available as a simplified, unified installer and a small download. SQL Server Reporting Services is now a 90-MB download that launches its own installer but still needs a SQL Server Database Engine instance as part of the license and to host the two Report Server databases. Note that SQL Server Reporting Services isn’t free, and that the separate installer isn’t a licensing change. You will need to provide a license key upon installation or choose a nonproduction edition to install (Evaluation, Developer, or Express).

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 Server\140\Tools\Binn\RSConfigTool.exe). The Report Server Configuration Manager application itself is largely unchanged since SQL 2008.

The virtual service account “NT SERVICE\SQLServerReportingServices” 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, “Domain\svc_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.

“Smart Setup”

Since SQL Server 2012, the SQL Server installer has had the ability to patch itself while within the Setup wizard. The Product Updates page is presented after the License Terms page, and, after you accept it, it is downloaded from Windows Update (or Windows Server Update Services) and installed along with other SQL Server Setup files.

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.

Setting up logging

SQL Server Setup generates a large number of logging files for diagnostic and troubleshooting purposes. These logs should be the first place you go when you have an issue with Setup.

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 Server\140\Setup Bootstrap\Log\YYYYMMDD_HHMMSS\

Here’s an example:

C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20170209_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.

Automating SQL Server Setup by using configuration files

Let’s dig more into what we can do with Setup.exe outside of the user interface. You can use configuration files to automate the selection process when installing SQL Server, which helps to create a consistent configuration.

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.

Starting Setup from prompt

You can start setup.exe from either Windows PowerShell or the command prompt, providing repeatability and standardization of parameter options. You also can use it to prefill sections of the Setup wizard or to change the default behavior of Setup.

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.

Generating a configuration file

Writing a configuration file by hand is not necessary and can be tedious. Instead of going through that effort, you can let SQL Server Setup create a configuration file for you. Here’s how to do that. Work your way through the normal SQL Server Setup user interface, completing everything as you normally would, but pause when you get to the Ready To Install page. Near the bottom of this page is a path (see Figure 4-4). At that location, you’ll find a generated configuration file, ready for future use.

Installing by using a configuration file

Now that you have a configuration file that you either prepared yourself or generated by using a previous walk-through of Setup, you can take the next step to automating or standardizing your installation.

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:

Setup.exe /SQLSVCPASSWORD="securepasswordhere"
/ConfigurationFile="d:\SQLInstaller\Configuration
Files\PROD_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.