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.

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.

The SQL Server installation center provides several ways on how to install SQL Server. The most common method of installation is to use the wizard for standalone installation as well as for cluster installation of SQL Server.

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

The installation wizard is started from the SQL Server installation center from the second tab called Installation. There are several wizards (shortened):

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

The first option called New SQL Server stand-alone 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 first two steps of installation ask for the license key and EULA acceptation. In the third step, the Global Rules installer checks the state of the computer again. Two product update steps try to connect Microsoft update for news. Until now, everything is very clear and almost automated, the administrator just uses the Next button.

The fifth step called Install setup files sets up the installation runtime. After this, the setup wizard checks that the computer is not a domain controller and then the actual installation begins:

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 newly installed separately from SQL Server installation.

When Installing SQL Server DE, administrator selects these options:

• Database Engine Services: This is the core component, DE itself
• Optionally SQL Server Replication: For the possibility of setting 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.

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. So in the System Configuration Checker successful result default screenshot, location is set.

After the Feature Selection step, the instance configuration step appears that enables you to set an instance name. 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's a good for example for side-by-side upgrades when on the same operating system. Another scenario for several instance could be when some information system needs its own SQL Server configuration. 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 a default instance, clients just use the computer name or IP address (such as MYSQLSERVER01); when connecting to a named instance, users must provide the instance name (for example, MYSQLSERVER01\MYADDITIONALNAME).

The next step is server configuration. Server configuration is divided into two tabs. Never miss going through both! The first tab is to set user accounts for SQL Server and all other installed features. The second tab is called collations.

The setting called collation describes 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 database default collation. On database as well as on column level, the collation setting could be overridden, but it's not 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 not sure, it's a better approach to use ANSI compatible collation with case insensitivity in a combination of unicode SQL Server data types such as nchar or nvarchar.

The next steps could vary--their set and order depends on features selected in the Feature Selection step. For our database administration purposes, the step called database engine configuration 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. Never miss going through all these tabs!

In the first tab called Server Configuration are basic settings for SQL Server security from a client's perspective. The first setting is Authentication Mode. It has two options:

• Windows authentication mode: In this mode, logins to SQL Server can be established in a trusted way only. It means that only logins created from Windows (domain or local accounts of groups) can connect to the SQL Server.
• 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 as a minor security risk, but since SQL Server 2005 (this was the first version of SQL Server able to consume Group Policy Object (GPO)), this is not a problem any more.

When the administrator selects mixed mode, he 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 SQL Server instance's lifetime, but then 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 another users to the list of principals who will have administrator access to the SQL Server.

The Data directories tab is very important as well. It's a set of paths to the filesystem where system databases, user databases, and backups will be placed when the user will not provide their actual paths:

• Data root directory: This is the base path to all data files (with .mdf or .ndf extensions). All system databases will be placed to this path (with the exception of the tempdb database). It's 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.
• User database directory: This is the default base directory for user database data files. For certain data files this path can be overridden.
• User database log directory: In this, all log files (with the .ldf extension) of every database will be placed. Never mix data and log files in the same place.

User as well as system databases could be moved to a different location.

The third tab called TempDB precalculates 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:

• Number of data files
• Their location
• Their symmetric growth

The number of data files is calculated from the number of CPUs. The 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.

The last tab in the database engine configuration is called FILESTREAM. The FILESTREAM itself is a type of storage for binary data like 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 properly later without the need for restarting SQL Server.

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

The installation wizard is almost complete now. After a short recap of what was selected by the administrator, the setup operation starts and shows its progress.

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. It leads to a big risk of misspellings and other mistakes.

A better approach is to use configuration files for command prompt installations. It contains the same options as the command prompt itself, 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\140\Setup Bootstrap\Log. In this location is a file called Summary.txt, which contains the actual path to the ConfigurationFile.inifile. The configuration file can be copied and adjusted as needed and then run using the following command from the command prompt:

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

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.

With the release of SQL Server 2017, administrators are very excited about the following new installation scenarios:

• SQL Server on Linux
• SQL Server in a container

The SQL Server on Linux installation scenario was a big highlight when SQL Server 2017 was announced for the first time. The installation process is managed via the command prompt, from downloading the SQL Server installer to starting the newly installed instance. Access to the instance is done by the sqlcmd command line almost in the same way as on Windows. Right now, SQL Server DE, as well as SSRS and SSIS services, support running on Linux.

Container is quite a new term supported by Microsoft. We can think about containers as lightweight virtual machines with less isolation of processes (for example, service running in a container can be stopped from the host operating system). Another approach of container is to create a sandbox for an isolated amount of system resources. Container installation of SQL Server is done via command prompt.

When SQL Server is installed on Linux or in some container, it's accessed by clients in exactly the same way as any other instance of SQL Server. More detailed information about these new installation scenarios is beyond the scope of this book.

We have taken all the necessary steps to install standalone instance of SQL Server DE. Next step is to check if the installation was successful, if the instance is up and running. After the installation completes, administrators have to check whether everything is working as expected.

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

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

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

• SQL Server Services: When selected in the left pane, the right detail pane shows every SQL Server service or feature installed with its state, start-up mode, and logon account. These settings can be changed by right-clicking on a certain row and selecting Properties from the pop-up menu. The properties modal 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 Sql Server Configuration Manager picture above is the internal name for the default instance of SQL Server) and, when clicked on any instance in the left, 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), but the preceding two sections are the most important ones.

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 on SQL Server Agent.

SQL Server may be shown in several records because every instance has its own configurations so the first good thing is to select the right record. Following image shows the properties dialog box.

As shown on preceding image, Properties dialog box for every instance 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 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 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:
  • d: The location of the primary data file of the database master (must be reconfigured when the master database is moved)
  • l: The location of the log file of the database master (must be reconfigured when the master database is moved)
  • e: The default path for error logs written by SQL Server
  • Additional parameters like 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 would 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 usage 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. 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 because 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).

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 2017 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 port number has to be set for every variant of IP address of every network interface.

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

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 SQL Server service is already running because a lot of administrator's tasks will be done directly on the server, but for a quick check whether SQL Server is accessible to clients, the 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 some 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 most simple way to use sqlcmd is as follows:

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:

sqlcmd -E -S localhost

In domain user context or with SQL login context:

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

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; U and P parameters are used when user wants to connect via mixed authentication mode of SQL Server. Then user and password created on the SQL Server are used, not the Windows identity.

The S parameters 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. Every query must be followed by the GO keyword. This keyword (sometimes called batch terminator) causes the text written to the console to be sent to SQL Server and then processed with some result.

Results returned back to the console are not so readable in many cases, so 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 may look the following. The first piece of code shows the correctly created input file (for example, demo.sql):

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

The first line (use master) 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 the administrator wants to run a script file like this, he can add the following command to the 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 error will occur) to the file called demo_output.txt.

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

For remote testing of accessibility, 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 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: It can be corrected on user's side by adding the port number after server name (for example, MYSQLSERVER:12345).
• Firewall rules are not set: It must be resolved on firewall's side by enabling certain ports for communication.

The SQL Server ecosystem provides you with a wide set of technologies and the first problem is to know what is the responsibility of every single technology of SQL Server. When this is clear and we recognize our needs, we can start preparing 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 sufficient set of disk storage is in place, that appropriate security accounts are prepared, and that all software prerequisites are fulfilled.

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 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 when SQL Server DE does.

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