During the installation process, you have to make many choices. As a general guideline, it is best to familiarize yourself with the effects of each option before installing your system. At the beginning, you should answer the following questions:
- Which operating system will be used?
- Which SQL Server components should be installed?
- Where will the root directory be stored?
- Should multiple instances of the Database Engine be used?
- Which authentication mode for the Database Engine should be used?
The following subsections discuss these topics.
1. Which Operating System Will Be Used?
In the past, SQL Server has run only on Windows. Since SQL Server 2017, the instances of the system can be installed and used additionally on Linux and in Docker containers, which opens the possibility of running SQL Server on macOS. With extension tools now available as native Linux and macOS applications, users can choose one of several operating systems available for running SQL Server. (This book describes the installation and usage of SQL Server on Windows and on the Linux derivative called Ubuntu.)
2. Which SQL Server Components Should Be Installed?
Before you start the installation process, you should know exactly which SQL Server components you want to install. Figure 2-1 shows a partial list of all the components. This is a preview of the Feature Selection page of the installation wizard, which you will see again when you install SQL Server later in this chapter.
There are two groups of features on the Feature Selection page: instance features and shared features. Instance features are the components that are installed once for each instance, meaning you have multiple copies of them (one for each instance). Shared features are features that are common across all instances on a given machine. Each of these shared features is designed to be backward compatible with supported SQL Server versions that can be installed side by side. This section introduces only the instance features. For a description of the shared features, see Microsoft Docs.
The first item in the list of the instance features is Database Engine Services. The Database Engine is the relational database system of SQL Server. Parts II and III of this book describe different aspects of the Database Engine. The first instance feature under Database Engine Services, SQL Server Replication (see Figure 2-1), allows you to replicate data from one system to another. In other words, using data replication, you can achieve a distributed data environment. Detailed information on data replication can be found in Chapter 18.
The next instance feature is Machine Learning Services and Language Extensions. This feature lets you deploy R or Python packages to SQL Server, execute solutions in the context of SQL Server, and easily work with SQL Server data in R and/or Python. Detailed descriptions of R and Python integration in SQL Server can be found in Chapters 32 and 33, respectively.
The third instance feature under Database Engine Services is Full-Text and Semantic Extractions for Search (aka Full-Text Search). The Database Engine allows you to store structured data in columns of relational tables. By contrast, the unstructured data is primarily stored as text in file systems. For this reason, you will need different methods to retrieve information from unstructured data. Full-Text Search is a component of SQL Server that allows you to store and query unstructured data. (This component is described in Microsoft Docs.)
The next instance feature is Data Quality Services. These services are related to Data Quality Client. After that, the PolyBase Query Service is listed. PolyBase is a component of SQL Server that builds a gateway from SQL to Hadoop. This functionality has been available for several years in the Microsoft Analytics Platform System (APS) and is now an integrated part of SQL Server. (PolyBase will not be described in this book.)
The next category of instance features under Database Engine Services is Analysis Services (not shown in Figure 2-1), which is a component related to business intelligence (BI). Analysis Services is a group of services that is used to manage and query data that is stored in a data warehouse. (A data warehouse is a database that includes all corporate data that can be uniformly accessed by users.) Part IV of this book describes SQL Server and business intelligence in general, while Chapter 23 discusses Analysis Services in particular.
3. Where Will the Root Directory Be Stored?
The root directory is where the Setup program stores all program files and those files that do not change as you use the SQL Server system. By default, the installation process stores all program files in the subdirectory Microsoft SQL Server, although you can change this setting during the installation process. Using the default name is recommended because it uniquely determines the version of the system.
4. Should Multiple Instances of the Database Engine Be Used?
With the Database Engine, you can install and use several different instances. An instance is a database server that does not share its system and user databases with other instances (servers) running on the same computer.
There are two instance types:
- Default
- Named
An instance is either the default (unnamed) instance, or it is a named instance. When SQL Server is installed in the default instance, it does not require a client to specify the name of the instance to make a connection. The client only has to know the server name.
Any instance of the database server other than the default instance is called a named instance. To identify a named instance, you have to specify its name as well as the name of the computer on which the instance is running. On one computer, there can be several named instances (in addition to the default instance). Additionally, you can configure named instances on a computer that does not have the default instance.
Although all instances running on a computer do not share most system resources (SQL Server and SQL Server Agent services, system and user databases, and registry keys), there are some components that are shared among them:
- SQL Server program group
- Development libraries
The existence of only one SQL Server program group on a computer also means that only one copy of each utility exists, which is represented by an icon in the program group. Therefore, each utility works with all instances configured on a computer.
You should consider using multiple instances if both of the following are true:
- You have different types of databases on your computer.
- Your computer is powerful enough to manage multiple instances.
The main purpose of multiple instances is to divide databases that exist in your organization into different groups. For instance, if the system manages databases that are used by different users (production databases, test databases, and sample databases), you should divide them to run under different instances. That way you can encapsulate your production databases from databases that are used by casual or inexperienced users.
A single-processor machine will not be the right hardware platform to run multiple instances of the Database Engine, because of limited resources. For this reason, you should consider the use of multiple instances only with multiprocessor computers.
5. Which Authentication Mode for the Database Engine Should Be Used?
In relation to the Database Engine, there are two different authentication modes:
- Windows mode Specifies security exclusively at the operating system level—that is, it specifies the way in which users connect to the Windows operating system using their user accounts and group memberships.
- Mixed mode Allows users to connect to the Database Engine using Windows authentication or SQL Server authentication. This means that some user accounts can be set up to use the Windows security subsystem, while others can use the SQL Server security subsystem in addition to the Windows security subsystem.
Microsoft recommends the use of Windows mode. (For details, see Chapter 12.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.