Microsoft SQL Server 2017 technology overview

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:

Overview of SQL Server technology set on-premise

Understanding SQL Server DE

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

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

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

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.

SQL Server Analysis Services

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 of SSAS installation

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

PowerPivot mode of SSAS installation

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.

SQL Server Reporting Services

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.

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

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.

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

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.

Conclusion

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.