Microsoft SQL Server provides an extremely strong end-to-end data processing platform. In other words, data can be gained from a wide set of sources, securely and reliably managed, transformed, processed, analyzed, and visualized under an all-in-one license.

It's good to know what the bigger picture of the SQL Server looks as follows:

The core and most important service in the SQL Server technology set is the SQL Server Data Engine (DE). This service has the three most important responsibilities aside of storing data:

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

SQL Server does not provide its capabilities to end users only, but it's necessary to keep in mind that SQL Server DE serves as a base service not just for users, but also for almost every other service in the SQL Server technology set and it sets the next important points:

• Every BI service such as analysis services or reporting services are actually clients of SQL Server DE.
• Some services like machine learning services can be installed within or independently of SQL Server DE.
• SQL Server agent (not seen in the previous figure) plays an exceptional role in the SQL Server ecosystem. This service exists as an indivisible part of every SQL Server DE.

Why do we need this information? It's one of the crucial moments when planning SQL Server installation. For example, analysis services are heavily resource consuming and their deployment along with SQL Server DE could lead to big performance problems and user disappointment with responses on their requests.

SQL Server integration services (SSIS) are 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 is using SSIS 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. It's a runnable unit of integration services and we can think about it as one simple program. Its definition consists of the so called Control Flow task set such as creating a temporary folder, if it succeeds in downloading CSV files from some FTP site, and so on. One of the most crucial tasks in control flow is called the Data Flow task. This Data Flow task causes the execution of the SSIS package to switch to a Data Flow transform set.

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 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 integration services catalogs part of management studio.

From an administrator's point of view, the SSIS service installation could be omitted if all existing SSIS packages can be deployed to the integration services catalog, which can be created anytime just by a few clicks in the Management Studio.

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

Master Data Services (MDS) is a technology providing a very efficient way to manage data that has to be maintained centrally for more applications (for example, organizational structure) or data that should be cleansed and validated before it is sent to other data destinations like a data warehouse. From an administrator's perspective, it's a database usually called MDS, MDM, or master_data_services (administrator can choose the database name) and a website created on IIS. Master Data Services are not installed within SQL Server installer; a graphical tool called Master Data Services configuration manager is used for their installation and configuration.

Loading data into Master Data Services 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 stored in the Master Data Services database.

Data Quality Services (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 SQL Server installer, but it's created by an independent application.

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

Using SSIS, MDS and DQS is complex discipline behind topics covered by this book. The only information needed is that described ETL technologies are more like standalone applications using SQL Server databases.

The Microsoft SQL Server Analysis Services (SSAS) is a very robust and scalable service that steps behind relational database limits by precalculating data that has been read from a relational data source and saving the data to a 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, it is obvious that 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 a good idea to have both 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 I/O operations when accessing a storage model, a lot of memory cache is used for data processing and data querying, and entails big 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, it's obvious that SSAS has to be installed on its own computer. The only disadvantage is that separate installations of SQL Server services lead to separate licensing. 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:

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

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

Multidimensional mode is used for corporate BI scenarios. Dimensions, data cubes, and mining models are developed by IT departments.

Multidimensional mode requires regular data processing so its approach is for bigger centralized analysis, trend predictions, longitudinal studies, and so on. Multidimensional mode is seen as a bigger, more robust, and scalable mode but often with data delay (existing storage model called real-time 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).

SQL Server as well as other Microsoft technologies support 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 precalculated 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 then can 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.

A detailed description of how Analysis Services work is beyond this book, but combining Analysis Services--no matter which installation mode--with other SQL Server services will lead to big performance problems.

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 Reporting Services (SSRS) as a part of the SQL Server technology set. This service is designated to access data from variety of sources and visualize the data to users. Reporting Services is a favorite service for centralized and managed reporting.

From an architectural point of view, the 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, the report portal was called report manager.)

Reporting Services can be installed in two modes:

• Native mode
• SharePoint mode

Reporting Services have almost the same features in both modes, including report deployment, report previews, subscriptions, or report exports to formats such as MS Excel or PDF. The only feature not present in the native mode of installation is data alerts. A data alert is a user's option to be informed when something is changed in the report's data.

When SQL Server 2016 was up to date, it had two installation modes for Reporting Services. This approach has completely changed since SQL Server 2017. Reporting Services can still be installed in both modes, but by separate installers.

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

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.

Compared to the native mode installation, the SharePoint mode requires that SharePoint farm already exists and the downloaded installer runs on it. The installer just installs the SharePoint service application and SharePoint features containing certain SSRS web parts and features. After installation, SSRS in SharePoint mode is not working because SharePoint does not know that new components were added. The SharePoint administrator has to run two simple PowerShell commandlets to assign SSRS to SharePoint.

PowerShell commands to install SSRS in SharePoint mode are as follows:

Install-SPRSService
Install-SPRSServiceProxy

As a last step of making SSRS work on SharePoint, the content manager of certain SharePoint sites should create new custom libraries called report library. Data sources, shared datasets, report parts, and reports are then delivered to the report libraries.

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 2017 installation anymore because it was renamed to Machine Learning Services. The renaming reflects the new Python support.

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

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

SQL Server Agent is a service installation is done along with SQL Server DE installation. Only exception is SQL Server Express Edition which does not enable SQL Server Agent service.

SQL Server Agent is a service to plan, execute and monitor regular tasks (jobs). The first approach that we can imagine is the planning and execution of regular administration tasks like 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:

• Master Data Services jobs for the internal maintenance of the MDS database
• Reporting Services (in native mode) jobs for regular subscriptions
• SQL Server replications are internally represented as sets of jobs
• When data collection diagnostics are configured, collection jobs are created and executed

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

The technology overview tried to answer the principal questions: What are certain SQL Server services and features for? Do I need every service or feature? Is it a good idea to install everything on one operating system? Now let's go on to prepare our computers to start the SQL Server 2017 installation on Windows.

The previous section described the whole set of services and features contained in SQL Server. From now, we will pay attention to on-premise 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 sysprep

Microsoft provides SQL Server in several editions. Each edition has its supported features and with these features 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 on Microsoft's site. The core editions are as follows:

• Enterprise edition: Intended for big enterprise environment
• Standard edition: Contains almost all services (except Master Data Services and Data Quality Services) 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 production environment
• Express edition: Express Edition of SQL Server published for free but with a lot of limitations; for example, analysis services, integration services, and SQL Server Agent are not contained in this edition

When planning to install SQL Server 2017, there are three important points to be considered:

• Amount of memory
• Disk set
• Security consequences

Every edition of SQL Server has its limit of maximum consumable memory. It's needed to give SQL Server 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:

• 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 buffer cache. Buffer cache with many other memory areas like 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 is relatively new SQL Server DE technology that was introduced with 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 capacity is set to 1/4 of maximum SQL Server memory, which means 32 GB of memory up to the regular limit. In memory area is used for in memory tables--tabular structures for extremely fast access, especially in conjunction with native compiled stored procedures. If any application supposes to use in memory technology, be aware of this extra memory need.

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

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

When preparing disks, we have to consider the following points:

• Using directly attached disks is very common approach. Only possible issue is that the server itself does not have a sufficient number of controllers and disks don't have enough space for large scale real-world production databases.
• The best way is to use 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 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). It is a file supplying more memory area for so called clean pages. SQL Server enhances the buffer cache and stores data pages intended to be read only from database to this file. The best practice is to place the BPE on its own SSD disk.
• Data files and log files of databases should always be separate. SQL Server uses write-ahead logging. This means that SQL Server caches data from data files and, at the moment, describes to the transaction log file what will be done with the data. When data and log files are not separate, overhead could occur on the disk controller.

When installing SQL Server 2017 on Windows, a 64-bit system is mostly preferred. Supported versions of the operating system are from Windows Server 2012 higher 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. This tool provides a central point to find resources about SQL Server as well as tools needed for 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 shown when the installation center starts is called planning. There is a link to a tool called System Configuration Checker (SCC):

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

Besides the requirements, 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.

SQL Server as well as other technologies within the SQL Server technology set need 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 for every service of SQL Server separately with the weakest permissions. As the installation process itself is run in administrator security context, the installer will set permissions for every account correctly within the installation. The following are the most used 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 bigger 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 needed) 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 three 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 live administrators. That's why a managed service account is usually the best approach to setting security accounts for SQL Server and its services.