Microsoft SQL Server offers a powerful end-to-end data processing platform. In other words, we can gain data from an extensive set of sources, securely and reliably managed, transformed, processed, analyzed, and visualized under an all-in-one license.

The following diagram shows what the bigger picture of the SQL Server technology set looks like:

Fig. 1.1 – Overview of SQL Server technology set on-premises

The preceding diagram shows one of the many possible ways in which technologies within SQL Server can cooperate. SQL Server Database Engine (DE) is depicted twice in the diagram because it possibly plays two major roles within the data processing platform, as follows:

• Line-of-Business (LOB) application contention: In the diagram, at the left occurrence of SQL Server DE, SQL Server provides data ingestion from client applications or other external sources of data.
• Business Intelligence (BI) contention: In the diagram, at the right occurrence of SQL Server DE, the SQL Server instance manages data warehouses, that is, databases used for Analysis Services data model processing or for reporting purposes.

As seen in the preceding diagram, SQL Server contains many technologies used and maintained by just one person. So, the following list shows how specific roles use such technologies:

• Database Administrators (DBAs): DBAs work with SQL Server and SQL Server Agent services, ensuring the continuity of operations, security, disaster recovery (DR) strategies, and similar tasks.
• SQL developers: SQL developers are responsible for the content of databases, from database design and transaction handling to the quality and accuracy of data stored in databases.
• Extract, Transform, Load (ETL) developers: ETL developers' playground lies mainly in SQL Server Integration Services (SSIS) services. ETL developers create a whole ETL workflow and ensure the quality and integrity of data extracted from sources and uploaded to targets of the ETL flow.
• BI developers: BI developers work mainly with reports on SQL Server Reporting Services (SSRS) and with multidimensional data models hosted on SQL Server Analysis Services (SSAS).

Although our attention is focused on DBAs, it is still useful to have a brief idea of other user roles within the same huge technology set. The DBA is mainly responsible for assisting all users.

Now, we will explain all components, including SSIS and SSRS, in more detail in the following sections.

Understanding SQL Server DE

The core service in the SQL Server technology set is the SQL Server DE service. This service covers the following three responsibilities, apart from storing and manipulating data:

• Handling recovery: This responsibility means that after any sudden or a planned breakdown of the service or database, the service will recover every database to its last consistent state without any undone transactions.
• Handling transactions: A transaction is mentioned as a single unit of work, and SQL Server DE guarantees that transactions will be durable and isolated and correctly finished with COMMIT or ROLLBACK.
• Handling security: SQL Server DE resolves every request for authentication and authorization and decides if a user or application is known (authenticated) and if a user or application has permission for certain actions (authorization).

SQL Server does not provide its capabilities to end users only. Still, it's necessary to keep in mind that SQL Server DE serves as a base service for almost every other service in the SQL Server technology stack and note the following important points:

• Every BI service, such as Analysis Services or Reporting Services, is actually a client of SQL Server DE.
• Some services, such as Machine Learning Services, can be installed within or independently of SQL Server DE.
• SQL Server Agent (not seen in the previous diagram) plays an exceptional role in the SQL Server ecosystem. This service exists as an indivisible part of every SQL Server DE application. SQL Server Agent hugely helps administrators, as well as other services or components, to automate routine tasks.

Why do we need this information? It's one of the crucial moments when planning a SQL Server installation. For example, Analysis Services is a heavily resource-consuming service, and its deployment along with SQL Server DE could lead to big performance problems and user disappointment with regard to responses on their requests. From a different perspective, installing SQL Server services on separated operating systems leads to increased license expenses and more complex administration efforts.

The following sections will describe each SQL Server service in detail.

SSIS

SSIS is basically used as a data pump of SQL Server. SSIS is used to maintain data movements and transformations between a wide scale of heterogeneous data sources and destinations, as well as migrating or transforming data between several instances of SQL Server. A very common use case of SSIS is in data warehousing to extract, transform, and save data from online transactional processing (OLTP) databases to a data warehouse.

The working unit of this technology is the SSIS package. This is an executable unit of integration services, and we can think of it as a simple application. Its definition consists of two main parts: control flow and data flow. Control flow contains tasks such as creating a temporary folder (Filesystem task), accessing a File Transfer Protocol (FTP) site (FTP task), and many others. One of the most crucial tasks in control flow is called the data flow task. This data flow task contains a definition of the path that data goes through, from data source to data destination.

The integration service itself is not mandatory for SSIS package execution, but the service is used for integration services packages management. It's installed for backward compatibility with older versions of the SSIS packages deployment model. SSIS packages are now commonly placed into a database called SSISDB. The database is not often accessed directly by users or administrators; it is maintained using the Integration Services Catalog.

From an administrator's point of view, the SSIS service installation could be omitted if all existing SSIS packages are deployed to the Integration Services Catalog, which can be created anytime just by a few clicks in SQL Server Management Studio (SSMS).

Integration services often cooperate with two features for data cleansing, validating, and deduplicating. These services are called Master Data Services (MDS)and Data Quality Services (DQS).

MDS

MDS is a technology that provides a very efficient way to manage data that has to be maintained centrally for more applications (for instance, an organizational structure or chart of accounts) or data that should be cleansed and validated before it is sent to other data destinations such as a data warehouse. From an administrator's perspective, it's a database usually called MDS, MDM, or master_data_services (the administrator can choose the database name) and a website created on Internet Information Services (IIS). MDS is not installed within an SQL Server installer; a graphical tool called Master Data Services Configuration Manager is used for its installation and configuration.

Loading data into a MDS database is often done using SSIS. Then, the data is optionally cleansed by data stewards. Clean and consolidated data could be subscribed via subscription views. Definitions of these views are created through the management of the IIS website and stored in the MDS database.

DQS

DQS is a technology providing you with a way to deduplicate and correct data that originates from several sources. Actually, DQS is not a service installed within the SQL Server installer, but it's created by an independent application.

The SSIS package has a special control flow task called the DQS cleansing task that is used when some of the DQS knowledge base (a set of rules created by the data steward) has to be used for data cleansing before the data is written to a target.

Developing solutions with SSIS, and optionally with MDS and DQS, needs complex developers' expertise not primarily needed by SQL Server administrators. From the administrator's perspective, SSIS, along with MDS and DQS, is just another database maintained by SQL Server.

SSAS

SSAS is a very robust and scalable service that steps behind relational database limits by pre-calculating data that has been read from a relational data source. SSAS stores the data in multidimensional storage called a storage model.

This approach is even more efficient for further analysis and visualizations than just the usage of relational data because the multidimensional format allows users to drill down and pivot actions as well as advanced aggregations or period-to-date queries. From this perspective, SSAS forms the core component of corporate as well as self-service BI solutions.

Analysis Services can be installed within SQL Server installer, but it is not always a good idea to have both the SQL Server DE and SSAS service installed on the same computer. We must remember that SSAS is an extremely complicated engine with a lot of physical input/output (I/O) operations when accessing a storage mode. A lot of memory cache is used for data processing and data querying and entails significant central processing unit (CPU) consumption for computations. One more important thing is that results from SSAS are often consumed in applications such as decision support, management reports, and so on, and it's crucial to get responses fast without waiting.

As mentioned previously, in many cases SSAS has to be installed on its own computer. The only disadvantage is that separate installations of SQL Server services lead to separate licensing and more complex maintenance needs. In other words, the more computers that are used to spread SQL Server technologies across an infrastructure, the more licensing expenses will grow.

SSAS can be installed in two distinct modes, as follows:

• Multidimensional mode: This mode is used for centrally created data cubes and mining models.
• Tabular mode: This mode is also called in-memory mode. It's used to host PowerPivot models.

If both modes are needed, the SQL Server installer must be executed twice, and two instances of SSAS have to be installed.

Multidimensional mode of SSAS installation

The multidimensional mode is used for corporate BI scenarios. IT departments develop dimensions, data cubes, and mining models.

The multidimensional mode requires regular data processing, so its approach is for bigger centralized analysis, trend predictions, longitudinal studies, and more. The multidimensional mode is seen as a bigger, robust, and scalable mode, but often with data delay. (An existing storage model called Relational online analytical processing (ROLAP) can be used for real-time analysis but has a lot of constraints. An overview of a real-time operational analysis scenario will be described later in this book.)

Tabular mode of SSAS installation

SQL Server, as well as other Microsoft technologies, supports BI solutions created by business users. This approach is intended for users who are subject matter experts more than IT experts, who have simple but strong enough tools to create their own analysis and visualizations. The toolset is known as Power BI. A part of Power BI is the PowerPivot technology—compressed and somehow pre-calculated data used to build data models similar to data cubes.

For the possibility of sharing our own data models with other users in a well-managed and secured environment, the PowerPivot mode of SSAS was originated. Data models can be deployed with almost no adjustments to the server environment and can then be accessed by authorized users.

One big advantage of PowerPivot mode is that data models are held in memory, and when some additional data is needed to fulfill user requests, it can be read from the data source directly.

Although a detailed description of how analysis services work is beyond the scope of this book, we must know that combining analysis services—no matter which installation mode—with other SQL Server services leads to big performance problems.

SSRS

Data, either relational or multidimensional, does not have its own visible face—data is not visual. To have a complete end-to-end data management platform, Microsoft offers a service called SSRS as a part of the SQL Server technology set. This service is designated to access data from a variety of sources and visualize the data to users. SSRS is a favorite service for centralized and managed reporting.

From an architectural point of view, SSRS is a Windows (or newly Linux) service that offers HTTP/HTTPS endpoints for human-readable as well as web service content consuming. The human-readable endpoint is called Report Portal. It is just a web application for report consumption and management (formerly, Report Portal was called Report Manager).

SSRS has many useful features, including report deployment, report previews, subscriptions, or report exports to formats such as MS Excel or PDF.

Note

SQL Server 2019 Reporting Services installation is no longer a part of the SQL Server installer. From now, SSRS is installed and versioned separately. Linking to the installer is accessible from the SQL Server installation center or the setup wizard step with Feature Selection.

When SQL Server 2016 was up to date, it had two installation modes for reporting service—Native mode and SharePoint mode. However, in SQL Server 2019, SharePoint mode is no longer offered.

When installing SSRS, the web installer allows only installation of the service itself without creating the ReportServer and ReportServerTempdb databases for services metadata, as illustrated in the following screenshot:

Fig. 1.2 – Reporting Services installation step

In production environments, it is a better option to install an instance of SQL Server DE on its own computer and then install SSRS on its own computer as well. Metadata databases are created later by a visual configuration tool called Reporting Services Configuration Manager.

Machine Learning Services

Predictive analysis profits from efficient and enlarged languages such as Python or R. SQL Server 2016 was the first version of SQL Server that incorporated new features called R Services. This feature is not seen in SQL Server 2019 installation anymore because it was renamed as Machine Learning Services. The renaming reflects the new Python support in SQL Server 2017. SQL Server 2019 offers Java as a third usable language in Machine Learning Services.

Machine Learning Services can be installed via in-server mode. In this installation mode, Java, Python, and R support is incorporated directly into SQL Server DE. When the in-server mode is selected, developers can call the SQL Server stored procedure, sp_execute_external_script, with an R command, a Java command, or a Python command as a parameter.

The second possible mode of installation is the Machine Learning standalone server, which is an independent server consuming and executing R as well as Python scripts and visualizations.

SQL Server Agent

SQL Server Agent's installation is done along with the SQL Server DE installation. The only exception is that SQL Server Express Edition does not allow us to use the SQL Server Agent service. 

From an administrator's point of view, SQL Server Agent is a service to plan, execute, and monitor regular tasks (jobs). But the service is used by many other components and services of SQL Server; for instance, SSRS uses SQL Server Agent jobs to deliver reports to end users and more. The first approach that we could consider is the planning and execution of regular administration tasks such as those contained in maintenance plans (backups, reindexing, and so on). However, SQL Server and its services also need to execute other automated actions—for example, the following:

• MDS jobs for the internal maintenance of the MDS database
• Reporting Services jobs for regular subscriptions, report snapshots, and report cache housekeeping
• SQL Server replications internally represented as sets of jobs
• When data collection diagnostics are configured, collection jobs are created and executed

  Note

  We will discuss the features of SQL Server Agent throughout this book.

In this section, we have seen the application of and the need for certain SQL Server services and features. We have also seen why it is important to install the servers on different operating systems. Now, let's go on to prepare our computers to start the SQL Server 2019 installation on Windows.

The previous section described the whole set of services and features contained in SQL Server. From now on, we will pay attention to on-premises SQL Server DE installed on Windows only.

In this section, we will discuss the following topics:

• Which edition of SQL Server to buy with respect to the features and performance capabilities
• How to prepare our Windows operating system and other prerequisites
• Installation options such as installation wizard, Command Prompt, and the sysprep utility

Edition comparison

Microsoft provides SQL Server in several editions. Each edition has its supported features, and with these features, the allocation of resources will differentiate. This can be seen in terms of performance, price, runtime, and service availability. A complete edition comparison matrix is published at https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15. The core editions are as follows:

• Enterprise edition: Intended for big enterprise environments.
• Standard edition: Contains almost all services (except MDS and DQS) but has some limited hardware resource consumption as well as some internal limits in SQL Server DE.
• Developer edition: Edition containing all enterprise features, but for development purposes only! Must not be provisioned to the production environment.
• Express edition: The Express edition of SQL Server is published for free but with many limitations; for example, Analysis Services, Integration Services, and SQL Server Agent are not contained in this edition.

Pre-installation tasks

When planning to install SQL Server 2019, there are three important points to be considered, as follows:

• Amount of memory
• Disk set
• Security consequences

Planning memory

Every edition of SQL Server has its limit of maximum consumable memory. It's needed to set up the accessible memory correctly because SQL Server consumes as much memory as possible. Every request to SQL Server needs memory. When preparing the server for SQL Server installation, we must consider two main memory usages, as follows:

• Interpreted queries: This is the traditional approach where SQL Server processes user requests. Data is stored on disk, and when some portion of data is needed by any query, it's cached to a memory area called the buffer cache. The buffer cache, with many other memory portions such as the procedure cache, user connections, and others, is a matter of memory limit given by the edition of SQL Server.
• In-Memory OLTP: In-memory OLTP (with original code name Hekaton, which is still used in articles and books sometimes) is a relatively new SQL Server DE technology that was introduced with the SQL Server 2014 Enterprise edition. Later, in SQL Server 2016 SP 1, In-Memory OLTP has ceased to be an Enterprise feature, and now its memory capacity depends on memory limit determined by certain editions. For example, SQL Server Standard Edition has maximum memory set to 128 GB, and In-Memory OLTP capacity is set to 1/4 of maximum SQL Server memory per database, which means 32 GB of memory up to the regular limit for each database that uses In-Memory OLTP. The In-Memory OLTP area is used for memory-optimized tables—tabular structures for extremely fast access, especially in conjunction with natively compiled stored procedures. If any application needs to use In-Memory OLTP technology, be aware of this extra memory requirement.

When planning the amount of memory, we must keep in mind any concurrent service or application that will be present on the same server.

Planning disk capacity

No simple formula exists to calculate disk capacity. We can just estimate the amount of disk space needed from similar applications or older databases. Disk space needs to be described on MSDN as sufficient for an empty SQL Server installation, not for the production environment.

When preparing disks, we should consider the following points:

• Using directly attached disks is a very common approach. The only possible issue is that the server itself does not have a sufficient number of controllers, so disks don't have enough space for large-scale, real-world production databases. Directly attached disks are a good option when the server should be quickly provisioned—for instance, in production halls where hardware lifetime is shortened by a dusty environment.
• Usually, the best way is to use storage area network (SAN) storage, which has a sufficient number of controllers and allows you to spread every database across more disks.
• Let its own disk be present for the tempdb database; this database is used internally by SQL Server as well as explicitly by developers as an optimization helper for complicated queries (however, this is not the best practice).
• If the server has a low amount of memory (less than 64 GB) and more memory is needed, especially for read-intensive OLTP databases, the administrator can set up a buffer pool extension (BPE). This is a file supplying more memory space for so-called clean pages. SQL Server enhances the buffer cache and stores data pages intended to be read-only from the database to this file. A best practice is to place the BPE on its own solid-state drive (SSD) disk.
• Data files and log files of databases should always be separated. SQL Server uses write-ahead logging. This means that SQL Server caches data from data files, and, at the same moment, describes to the transaction log file what will be done with the data. When data and log files are not separated, overhead could occur on the disk controller.
• Even if many databases consist just of two files (primary data file and transaction log file), larger databases can be divided into more data files. As per the performance requirements, data files can be placed to more disks. This possibly causes the need for more physical disks. Dividing a database into more files will be described in further chapters.

Software and other requirements

When installing SQL Server 2019 on Windows, only a 64-bit system is supported. Supported versions of the operating system start from Windows Server 2012 and above for non-Enterprise editions; desktop operating systems—such as Windows 8.1 or Windows 10 (including Home edition)—are supported as well.

SQL Server uses the .NET Framework for some features. The .NET Framework of versions 2.0 and 3.5 SP 1 must be present before installation.

The easiest way to check whether everything is in place is to start the SQL Server Installation Center. It starts automatically when installation media is added to the server, or it can be reached from Windows Explorer by clicking on the setup.exe file. The SQL Server Installation Center provides a central point from where to find resources about SQL Server as well as tools needed for a standalone installation, cluster installation, adding or removing SQL Server components, and so on. The installation center is divided into sections, and every section contains a list of links. The first section, when the installation center starts, is called Planning. There is a link to a tool called System Configuration Checker (SCC). This section is shown in the following screenshot:

Fig. 1.3 – SQL Server Installation Center

The SCC is a tool that checks all prerequisites needed for the successful installation of SQL Server. The following screenshot shows how it looks when every requirement is fulfilled:

Fig. 1.4 – SCC successful result

Besides the requirements, the SCC checks the overall state of the server and other prerequisites, such as whether the installation is running with administrator's privileges or whether a restart is needed.

Security accounts for SQL Server

SQL Server, as well as other technologies within the SQL Server technology set, needs to log in to the operating system. From a security point of view, it is important to set an account for every service correctly. The general recommendation is to create a login account with the weakest permissions for every service of SQL Server separately. As the installation process itself is run in the administrator's security context, the installer will set local permissions for every service account correctly during the installation. The following are the most common scenarios:

• Built-in service accounts: This type of account provides less control from the administrator's side, and it's good enough for small, standalone installations of SQL Server. In a wider domain environment, it's not recommended at all.
• Dedicated domain account: This option means that the domain administrator prepares dedicated domain accounts with regular user rights (no elevated permissions are needed or recommended), and during installation (or after the installation), prepared domain accounts are set. A big concern is that such domain accounts must fulfill security policies—namely, password expiration—and SQL Server as a machine cannot create its own password for, say, every 3 months.
• Managed service accounts: Managed service accounts are domain accounts similar to regular domain accounts. Unlike domain accounts, managed service accounts create and change their passwords without any action needed from administrators. That's why a managed service account is usually the best approach to setting security accounts for SQL Server and its services.

Microsoft provides several options to install SQL Server and its technologies as simply as possible. Almost everything is done through the SQL Server Installation Center, which is opened via the autorun property of the installation media. The next chapter describes typical scenarios of installation and first post-installation checks and configurations.

Installation options

The SQL Server Installation Center provides several ways to install SQL Server. The most common method of installation is to use the wizard for a standalone installation as well as for a cluster installation of SQL Server.

For situations where more SQL Servers are propagated into the environment (for example, new departments or sales points are often created, and every department or sales point has its own SQL Server), SQL Server provides an option to be installed through the command line, which is also the only installation approach possible for installations on core editions of Windows servers, or sysprep installation.

Installation wizard

The installation wizard starts from the SQL Server Installation Center from the second tab, called Installation. There are several wizards (shortened), as follows:

• New SQL Server standalone installation
• Install SQL Server Reporting Services (new since SQL Server 2017; the version 2016 installation of SSRS was added to SQL Server's installation wizard directly)
• Install SQL Server Management Tools (means SSMS; beginning in version 2016, management and data tools are not installed within the SQL Server installation process; they are downloaded, installed, and versioned separately)

The first option, called New SQL Server standalone installation, is the right way to install SQL Server on a clean machine just with the operating system. When a user clicks on this option, the wizard starts. The installation wizard consists of many steps. The way through each step is this:

1. Global Rules: This is an automated installation step that checks the state of the operating system.
2. Product Updates: This step is also automated and checks if any available updates of SQL Server exist.
3. Install Setup Files: The runtime environment is prepared for further installation. This step also does not need any interaction with the administrator.
4. Install Rules: This step checks the registry keys' consistency. It also checks if the computer is a domain controller (installation of SQL Server is not allowed on domain controllers), and it also checks for firewall rules. If any of the checks fail, the setup process is corrupted, and the issues reported by Install Rules must be corrected.
5. Installation Type: On this page of the setup wizard, we can decide if we want to install a new instance of SQL Server, or add features to an existing instance of SQL Server.
6. Product Key: This step asks for a license key. If we do not have the key, we can select one of the free editions (Evaluation, Express, or Developer edition).
7. License Terms: We should read through and accept the end-user license agreement (EULA).
8. Feature Selection: The following screenshot shows the tree of SQL Server features:

Fig. 1.5 – Setup wizard step with Feature Selection

As shown in the preceding screenshot, the setup wizard offers a wide set of features to be installed at once. For administrators who already installed previous versions of SQL Server, the setup step writes an information message about SQL Server Reporting Services. This service is installed separately from the SQL Server installation.

When Installing SQL Server DE, the administrator selects the following options:

• Database Engine Services: This is the core component, DE itself.
• Optionally SQL Server Replication: For the ability to set up replication scenarios.
• Optionally Full-Text and Semantic Extractions for Search: Full text is a strong feature provided by SQL Server DE, and it could be very useful for users.

  Note

  Other options, such as Machine Learning Services (provides support to run external scripts on SQL Server), can be installed later or in a standalone separated setup. Features such as PolyBase are beyond the scope of this book.

In the bottom part of this setup step, the user decides where to place program files, not data files. Lets take a look at these steps:

1. Instance Configuration: In this step, the administrator decides if the default or named instance of SQL Server will be installed. SQL Server can run in multi-instance mode. This means that more than one instance of SQL Server could be installed on the same machine. It is a good example of a side-by-side upgrade when it is done on the same operating system.

   Another scenario for several instances could be when some application needs its own SQL Server configuration (for instance, SharePoint Server). That's why SQL Server provides an ability to install default instances and named instances. The default instance has no special additional name provided by the administrator during installation, whereas every named instance must have its own additional name that is unique on a certain computer. When connecting to the default instance, clients just use the computer's name or IP address (such as MYSQLSERVER01); when connecting to a named instance, users must provide the instance name (for example, MYSQLSERVER01\MYADDITIONALNAME).

2. Server Configuration: Server Configuration is divided into two tabs. Don't forget to go through both! The first tab, Service Accounts, is to set user accounts for SQL Server and all other installed features. The second tab is called Collation. The following two screenshots show both tabs on this installation step:

Fig. 1.6 – Server Configuration, Service Accounts tab

The preceding screenshot shows the first Service Accounts tab of the Server Configuration installation step. The tab is the place where accounts are selected for each SQL Server service. The step also contains a Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service checkbox. If the checkbox is checked, the service account use by SQL Server will be allowed to grow data files without the need to fill newly obtained space in the files with zeros. This privilege speeds up the growth operations of data files. Lets look at the Collation tab in the following screenshot:

Fig. 1.7 – Server Configuration, Collation tab

The preceding screenshot shows the Collation tab. The Collation setting defines which code page, sort rules, and case sensitivity will be used as the server's default way of handling string characters. This server default is inherited by databases as a database default collation, and every character column in the database has its own collation inherited from the database default collation. On the database as well as on the column level, the collation setting could be overridden, but it's not a good practice. It is crucial for the proper working of SQL Server to set the server default collation correctly. It's not simple to say which collation is the correct one. The only idea is that, if you're not sure, a better approach is to use an American National Standards Institute (ANSI)-compatible collation with case insensitivity in a combination of unicode SQL Server data types such as nchar or nvarchar.

Note

Collations contain only a configuration value that cannot easily be changed after installation! When mismatched, it leads to a reinstalling of system databases.

The next steps could vary—their set and order depend on features selected in the Feature Selection step. For our database administration purposes, the Database Engine Configuration step is the most important one. In this step, all crucial configurations are done by an administrator. As shown in the following screenshot, the step is divided into tabs (don't forget to go through all of them!):

Fig. 1.8 – Database Engine Configuration

The preceding screenshot shows the Database Engine Configuration step of the SQL Server installation wizard. Let's look at the different tabs present on the wizard, as follows:

• Server Configuration contains basic settings for SQL Server security from a client's perspective. The first setting is Authentication Mode. It has two options, as follows:

  a) Windows authentication mode: In this mode, logins to SQL Server can be established in a trusted way only. This means that only logins created from Windows (domain or local accounts of groups) can connect to SQL Server.

  b) Mixed mode: For cases when every user comes with its domain identity, SQL Server can manage Standard logins or SQL logins—logins with passwords managed directly by SQL Server. This was considered a minor security risk, but since SQL Server 2005 (this was the first version of SQL Server able to consume Group Policy Objects (GPOs)), this is not a problem anymore.

  When the administrator selects Mixed mode, they must provide a strong password for standard system SQL Server login called sa. This is the standard administrator login on SQL Server.

  Authentication mode is the configuration value that can be changed later during a SQL Server instance's lifetime. Still, it needs restarting, so it's a better approach to set it directly during installation.

  In the last part of this tab is a list of sysadmin server role members of the SQL Server instance being installed. Add yourselves to the list and add other users to the list of principals who will have administrator access to the SQL Server.

• Data directories tab is important as well. It's a set of paths to the filesystem where system databases, user databases, and backups are placed when the user does not provide their actual paths, listed as follows:

  a) Data root directory: This is the base path to all data files (with .mdf or .ndf extensions). All system databases are placed on this path (with the exception of the tempdb database). It is not recommended to use the default path to the program files, as was explained in the Planning disk capacity section; the best practice is to have a disk prepared just for data files.

  b) User database directory: This is the default base directory for user database data files. For certain data files, this path can be overridden.

  c) User database log directory: In this, all log files (with the .ldf extension) of every database are placed. Never mix data and log files in the same place.

  Users, as well as system databases, could be moved to different locations. 

• TempDB pre-calculates the best configuration settings for the tempdb database. The tempdb database has a very special position in SQL Server, as well as for developers using it in some optimization tasks. The optimized execution of the tempdb database roughly depends on the following:

  a) Number of data files

  b) Their location

  c) Their symmetric growth

  The number of data files is calculated from the number of CPUs. A best practice is to have 1/4 to 1/2 data files to the number of CPUs (even logical CPUs). The best location of data files is on a fast separate disk. In the case of tempdb failure due to a disk failure, tempdb is regenerated every time SQL Server starts.

  The symmetric growth of all data files is carried out by the SQL Server engine automatically. Unlike the prior versions that had trace flags set in startup parameters, this is not needed from SQL Server 2016.

• MaxDOP allows us to limit the maximum number of CPUs used for parallelism at the instance level. This configuration value can be adjusted later during the instance's lifetime, without the need to restart the service. We can leave MaxDOP's default value as 0. Still, SQL Server tends to overutilize CPUs in highly parallelized queries, which can lead to inaccessible CPUs for other tasks on SQL Server (this situation is known as SOS_SCHEDULER_YIELD wait). MaxDOP is also configurable at the database level, but this configuration is not part of the installation process.
• The Memory tab is new for the SQL Server 2019 installation wizard, which is why it is also shown in the preceding screenshot. Even if we can set minimum and maximum memory consumption for our instance of SQL Server later, the installation wizard helps us to estimate the right amount of maximum memory. We can select between the following two options:

  a) Recommended: The estimation of Max. Server Memory (MB) configuration. The max. server memory value limits the amount of memory used by SQL Server. Using the Recommended option also allows us to adjust Min. Server Memory (MB). This is the amount of memory that, once consumed by SQL Server, is not brought back to the operating system.

  b) Default: If the max. server memory estimation offered by the installation wizard is not used, max. server memory stays at the default value, which is unlimited.

• FILESTREAM: The FILESTREAM is a type of storage for binary data such as documents or pictures saved in the database. If you have no idea about saving FILESTREAM data at the moment of installation, the FILESTREAM should remain disabled. It can be enabled and configured later, without the need to restart SQL Server.

  After the database engine configuration, additional wizard steps could occur, depending on other features selected in Feature Selection.

  Note

  Maybe some readers are concerned about where the SQL Server Agent configuration is. SQL Server Agent doesn't have any special settings in the installation process, and its installation is automatically done along with every instance of database engine installation.

• Ready to install: This step of the installation wizard is basically the summary of selected options for review before the installation begins, but this step also contains a text field called Configuration file path. This text field contains a complete path to the configuration file. The file is very useful as a template when more instances of SQL Server need to be installed.

The installation wizard is almost complete now, and the setup operation starts and shows its progress.

Installing SQL Server from the command line

Installing SQL Server directly from the command line is possible, but when searching the Install SQL Server from the Command Prompt topic on MSDN, the user will obtain a huge set of options that need to be added to the Command Prompt (or to the .bat file) directly. This leads to a big risk of misspellings and other mistakes.

A better approach is to use configuration files for Command Prompt installations. This approach contains the same options as Command Prompt, but we can find very good working examples from any setup already run from the wizard. When the wizard setup finishes, it leaves the setup log and configuration on disk. If SQL Server is installed in the default location, the path is C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log. In this location is a file called Summary.txt that contains the actual path to the ConfigurationFile.ini file. The configuration file can be copied and adjusted as needed and then run using the following command from Command Prompt:

setup.exe /ConfigurationFile=<path to my config file>.ini /IACCEPTLICENCETERMS

The SQL Server installation wizard allows you to prepare a configuration file without installing. When the administrator goes through the wizard, everything is saved in the newly created configuration file. In the summary step of the wizard, the path to the configuration file is shown. So, the administrator can cancel the wizard without the actual installation of SQL Server.

Note

ConfigurationFile could be edited. However, we can set different instance names or service account names, for instance, but it is still needed to add the /IACCEPTLICENCETERMS command parameter to the command line.

Checking the completed installation

We have taken all the necessary steps to install a standalone instance of SQL Server DE. The next step is to check if the installation was successful and if the instance is up and running.

If any error occurs during installation, additional diagnostics are needed. Every single task of the setup process is described in the setup log (in the case of the default installation path, the log is placed on the C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Log path).

However, it is still a good practice to check whether everything works as expected. Those simple post-installation checks could be done using Sql Server Configuration Manager, as illustrated in the following screenshot:

Fig. 1.9 – Sql Server Configuration Manager

Sql Server Configuration Manager is the only visual client tool actually installed in the SQL Server setup. Configuration Manager is a snap-in to Microsoft Management Console and consists of two main sections, as follows:

• SQL Server Services: When selected in the left pane, the right detail pane shows every SQL Server service or feature installed with its Name, State, Start Mode, and Log On As account. These settings can be changed by right-clicking on a certain row and selecting Properties from the pop-up menu. The Modal Properties dialog appears, and we can go through it to correct any setting as needed.
• SQL Server Network Configuration (32-bit): In this section, administrators view a list of instances (the MSSQLSERVER instance name seen in the preceding screenshot is the internal name for the default instance of SQL Server), and when any instance on the left is clicked on, a list of network protocols appears in the right pane.

There are also other nodes in the tree shown in the left pane of Sql Server Configuration Manager such as SQL Native Client configuration, which provides the ability to set client aliases for SQL Server instances (for example, when SQL Server is accessible only via its IP address, which is almost non-readable to users). Still, the preceding two sections are the most important ones.

SQL Server services configuration node

The administrator can call for a pop-up menu from every record shown in the right pane. Special attention should be focused on SQL Server itself and SQL Server Agent.

SQL Server may be shown in several records because every instance has its own configuration, so the first good thing is to select the right record. The following screenshot shows the Properties dialog box:

Fig. 1.10 – SQL Server instance properties

As shown in the preceding screenshot, the Properties dialog box allows you to set the following:

• Log On tab: The context of the Windows account that will be used by the instance to log in to the operating system. This configuration needs restarting if changed.
• Service tab: The only setting enabled on this tab is Startup mode, which should be set to Automatic.
• FILESTREAM tab: This tab contains FILESTREAM settings. As described earlier, FILESTREAM is a special kind of storage for binary data such as pictures or documents stored in relational data directly to a database. From an administrator's point of view, FILESTREAM must be enabled for at least T-SQL Access. When enabled, databases can contain FILESTREAM file groups, which are actual representations of the binary storage. There's an enhancement called File Tables for which the second two textboxes (allow for I/O... and enable remote clients...) must be switched on.
• Startup Parameters tab: This tab contains three startup parameters as default, as follows:

  a) d: The location of the primary data file of the database master (must be reconfigured when the master database is moved).

  b) l: The location of the log file of the database master (must be reconfigured when the master database is moved).

  c) e: The default path for error logs written by SQL Server.

  d) Additional parameters such trace flags and others can be added if needed.

• AlwaysOn High Availability tab: AlwaysOn is an advanced concept of data availability and reliability built on top of Microsoft Cluster Service (MSCS). When certain instances attend to the AlwaysOn group, it must be enabled on this tab. MSCS must already be present before this configuration is done.
• Advanced tab: This tab actually has no advanced settings, just error reporting and user feedback to Microsoft.

Special attention should be given to SQL Server Agent. SQL Server Agent is installed with every single instance of SQL Server. In other words, every instance of SQL Server has its own SQL Server Agent instance. Immediately after installation, SQL Server Agent is set to Manual Startup mode, which is not good enough for production environments.

That's why one of the first post-installation configurations should be to change SQL Server Agent's startup mode to Automatic. This is because the SQL Server Agent is an invaluable service for a lot of regular administrator tasks as well as automated tasks done by SQL Server itself (for example, data collection, strong diagnostics tool, and collecting performance statistics using SQL Server Agent jobs).

Understanding the SQL Server network configuration node

SQL Server communicates with clients on its own network application protocol called Tabular Data Stream (TDS). Under this network application layer, TCP/IP and Named Pipes (now deprecated) network protocols are supported. The third option, called Shared Memory, is always enabled and allows communication between server and client when the client is running locally on the same machine as SQL Server.

SQL Server supports both 32-bit and 64-bit protocols, so configuration for both modes is the same. Under the SQL Server Network Configuration node (even if it's the 32-bit node), network protocols for every instance of SQL Server already installed on the machine are placed. The administrator selects certain instances (for example, Protocols for MSSQLSERVER, which is the default instance), and in the right pane of the Sql Server Configuration Manager, selects the property window for certain network protocols by right-clicking on Properties.

The most complex configuration has to be made on the TCP/IP protocol. When SQL Server 2019 is installed, the protocol is enabled, so the administrator just checks whether the proper TCP ports are used. The default TCP port used for SQL Server communication is port number 1433. For additional named instances, ports starting with numbers 1450, 1451, or similar are often used. The ability and the port number have to be set for every variant of the IP address of every network interface.

After this configuration is done, the instance of SQL Server needs to be restarted.

Testing connection to a fresh SQL Server

As mentioned earlier, SQL Server does not contain a client management toolset in its installation. It's a good idea to install SQL Server Management Studio directly on the server where the SQL Server service is already running because a lot of the administrator's tasks will be done directly on the server, but for a quick check whether SQL Server is accessible to clients, Command Prompt can be used. Its name is sqlcmd , and it's the only client tool installed with SQL Server directly. This tool is very useful in the following scenarios:

• When SQL Server Management Studio is not present or cannot be used (for example, when restoring the master database)
• When the Express edition of SQL Server was installed, and SQL Server Agent cannot be used (when planning regular tasks, it can be done by PowerShell or by sqlcmd in conjunction with Windows Task Scheduler)

The simplest way to use sqlcmd is shown in the following code example:

sqlcmd

When running sqlcmd as shown in the preceding code example, it tries to connect the local default instance of SQL Server using the current user's Windows account. When successfully connected, rows in the Command Prompt window start to be numbered.

A better approach is to call sqlcmd with parameters precisely set, as follows:

sqlcmd -E -S localhost

In a domain user context or with a SQL login context, you would run the following code:

sqlcmd -U <user name> -P <password> -S localhost

Let me elaborate on each of the parameters, as follows:

• The E parameter (beware that all parameters of all command-line tools provided by SQL Server are case-sensitive) says to the connection that Windows login context of the user currently logged in the desktop will be used.
• The U and P parameters are used when the user wants to connect via a mixed Authentication mode of SQL Server. Then, the user and password created on the SQL Server are used, not the Windows identity.
• The S parameter is used for the name of the server. If connected locally on a default instance of SQL Server, shortcuts such as . or (localhost) could be used.

All the preceding examples start the sqlcmd tool in interactive mode. When successfully connected, rows start numbering, and the user can start to write queries. The GO keyword must follow every query. This keyword (sometimes called batch terminator) causes the text written to the console to be sent to SQL Server and then processed.

Results returned back to the console are not so readable that the sqlcmd could be started with the command parameter, o, followed by the path to the output file. The output file is just a text file catching all results from all queries sent by the user in the session.

When the user wants to run sqlcmd in unattended mode, the i parameter followed by the path to the input file may also be very useful. A complete example is shown in the following snippet:

--   content of demo.sql file
use master
go
select @@version as VersionOfMySQL
go

The first piece of the snippet shows the correctly created input file (for example, demo.sql).

The use master line establishes the correct database context in the connection, and it is highly recommended to never commit this row because very often, the database context is not the default database context set for login.

The third line is just an example of doing something meaningful.

When an administrator wants to run a script file like this, they can add the following command to Command Prompt:

sqlcmd -E -S (localhost) -i "c:\demo.sql" -o "c:\demo_output.txt"

The command will run, and it will save all results (even if an error occurs) to the file called demo_output.txt.

There are more useful command parameters for sqlcmd , but this set, especially the first three examples, is sufficient to test an instance's accessibility locally.

For remote testing of accessibility, a very common way is to use SQL Server Management Studio. Common issues (followed by Error No. 40 - Network Related Error) are as follows:

• SQL Server instance is not running: In Sql Server Configuration Manager, this error is seen if the service is running or not. When it's not running, we can try to start it up manually and diagnose additional errors.
• TCP/IP protocol is disabled: This issue may be corrected by Sql Server Configuration Manager (requires restart after reconfiguring).
• Other than default TCP port number is used: This can be corrected on the user's side by adding the port number after the server name (for example, MYSQLSERVER:12345).
• Firewall rules are not set: This must be resolved on the firewall's side by enabling certain ports for communication.

Since SQL Server 2017, Microsoft decided to offer its distribution on Linux. SQL Server's Linux distribution helps administrators to use familiar operating systems as well as install SQL Server to containers. This section is a step-by-step walkthrough example of the sample SQL Server installation process on Ubuntu 18.04.

Preparing the test environment

For many administrators and DBAs strongly bound to Microsoft operating systems, the world of Linux seems very strange and confusing. That's why the first step is a preparation of the Hyper-V virtual machine (VM). Microsoft provides a Hyper-V option called Hyper-V Quick Create. Its usage is very simple and straightforward, as follows:

1. In the Start menu, find the Hyper-V Quick Create application.
2. In the opened window, select Ubuntu 18.04.
3. Click the Create Virtual Machine button.
4. Follow the installation instructions.

The whole installation process is almost self-managed and takes up to 15 minutes. When your Ubuntu is ready, turn it on and connect to it using Hyper-V Manager. If everything works, you are prepared for SQL Server 2019 installation on Linux.

Installing SQL Server

The installation process of SQL Server on Linux differs from the Windows installation because of the following three points:

• It uses the Linux shell.
• All services such as SQL Server Agent or SQL Server Integration Services are installed separately.
• Not every service is supported on Linux (for instance, SSRS).

Our task is to install just the database engine of SQL Server, but the process of installation of other services is very similar.

First of all, we need to look for the Linux shell. At the bottom-left corner of Ubuntu PC is an icon that looks similar to the Windows Start button. When this icon is clicked, the main screen appears, showing all installed applications. One of these applications is Terminal. Click on it and take the following steps:

1. Execute the following command in Linux Terminal:

   wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

   The command imports the public repository key needed for validity checks of the downloaded build of SQL Server.

2. The second command registers the MS SQL Server Ubuntu repository from which the installation will be downloaded. The following command does the registration:

   sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"

   The sudo command from the preceding code snippet says to the operating system that the command itself will be executed with elevated permissions. So, it's possible that you will be asked to write your password into the command line.

3. Once the repository is registered, the following two commands will install SQL Server:

   sudo apt-get update
   sudo apt-get install -y mssql-server

4. After installation, you need to go through a simple configuration. You will be asked for a password and an edition of the freshly installed instance of SQL Server. The following command runs the configuration:

   sudo /opt/mssql/bin/mssql-conf setup

5. The last step is to check if SQL Server runs and if a connection can be established to it. Checking the service status can be done using the systemctl command, as follows:

   systemctl status mssql-server

6. The preceding command should show status Active (running). If not, execute the following command:

   systemctl start mssql-server

This command starts the SQL Server service. If anything is not correct, the result of the command will show part of the SQL Server error log with a certain error.

Testing connection to SQL Server on Linux

We should test the connection to our fresh SQL Server installation from inside as well as from outside of the Linux computer. The Linux distribution of the SQL Server service does not contain client tools, or even the sqlcmd command line. So, the first step is to install the sqlcmd command line. The process of installation is very similar to the installation of SQL Server. The following script shows all commands leading to the sqlcmd installation:

wget https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update 
sudo apt-get install mssql-tools unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

When all the preceding commands are executed, we can call the sqlcmd command line with the following parameters:

sqlcmd -U sa -P <strong sa password>

If the preceding command succeeds, lines in the Terminal window start to be numbered, and we can try any SQL command such as SELECT @@VERSION.

If everything works from inside, we can make the same connection test from outside of our Linux computer. During this walkthrough, we used the Hyper-V virtualization environment, so we can install Management Studio and try to connect to the SQL Server on Linux using both Management Studio or the sqlcmd command.

Note

When testing the connection to the virtual computer, use the IP address rather than the computer's name.

SQL Server on Linux works the same way as on SQL Server on Windows. From this moment, we will use SQL Server on Windows primarily, but if you wish to, enjoy your Linux distribution of SQL Server.

Virtualization of computers hosting SQL Server is very common nowadays. Containers provide the next level of virtualization. A container itself is a lightweight computer hosted by the container environment. The question is: why use containers over virtual machines? Containers provide a simple way to prepare the environment that is often destroyed or moved between hosts. It is useful, for instance, during the development phase, when developers need to refresh their server environment frequently.

The basis of containers adopted by Microsoft lies on a Linux-based technology called Docker. It's also a prerequisite when we'd like to try provisioning. We need to have Docker Engine 1.8 or higher installed on our Linux computer, or we can install Docker for Windows as well.

Note

Installing Docker for Windows needs the Hyper-V feature of Windows installed, because Docker itself is then running on the Linux VM.

The provisioning of SQL Server to containers is a scripting task. We can use bash or PowerShell, depending on the hosting environment. The following walkthrough example will provision SQL Server on Docker for Windows, so all commands are written using PowerShell:

1. As a first step, the SQL Server 2019 container image is downloaded from the Docker Hub with the following command:

   docker run -e "ACCEPT-EULA=Y" -e "SA_PASSWORD=<strong enough password>" -p 1433:1433 -d "mcr.microsoft.com/mssql/server:2019-CU3-ubuntu-18.04"

   The preceding command downloads the image to the local computer. Consider the following facts:

   a) The SA_PASSWORD parameter must follow the SQL Server default password policy (at least eight characters, special symbols, and numbers). Otherwise, the installation fails.

   b) The -d command parameter allows different versions of the SQL Server image to be selected. All images are accessible at https://hub.docker.com/_/microsoft-mssql-server.

2. We can check the list of all downloaded containers using the following command:

   docker ps -a

If we see our SQL Server container in the list retrieved by the preceding command, we can connect the SQL Server instance using any of our preferred tools.

The SQL Server ecosystem provides you with a wide set of technologies. The first problem is to know what is the responsibility of every single technology of SQL Server. In the first section of this chapter, we cleared what is necessary to install, which helped us recognize what we need.

In the second section, we prepared our operating system and the complete infrastructure to install SQL Server. The most important decision before starting the installation is which technologies to install and how many computers will be needed to distribute SQL Server services appropriately across an infrastructure. Before installing SQL Server DE itself, we need to ensure that a sufficient set of disk storage is in place, appropriate security accounts are prepared, and all software prerequisites are fulfilled.

The installation described in the Installing SQL Server 2019 on Windows section of this chapter is quite a straightforward process, but we should still consider some settings. Even if many of the settings can be adjusted after the installation completes, the SQL Server installation wizard helps us with the correct setting. In this section, we also highlighted settings (namely, Collation) that cannot be changed after the installation of the SQL Server.

After installation, it's highly recommended to check whether SQL Server is running, and, if not, check logs in the Setup Bootstrap folder. When SQL Server is successfully running, we need to check SQL Server's accessibility locally and remotely.

Last but not least is a check of the SQL Server Agent state because, for administrators, this service is an invaluable helper when performing day-to-day administrative tasks. SQL Server Agent should have the startup mode set to automatic and should be running all the time SQL Server DE is.

It is very useful to do more configuration after installation. The next chapter talks about these configuration settings and discusses more on how to keep SQL Server healthy.