Chapter 1

Planning to Install SQL Server 2019 on Windows

Chapter 2

SQL Server 2019 Installation

Chapter 3

SQL Server Management Studio Installation

Installing SQL Server is one of the simplest works you need to do as an administrator.

Install SQL Server 2019 using these steps.

To start installing SQL Server,

• Mount the ISO file from the SQL Server software
• Then double click on the “Setup.exe” file.

Figure 1: SQL Server 2019 Installation File

.NET Framework must be installed before installing SQL Server. If you do not have it on your system, download it from the Microsoft website and install it.

• A dialog box like figure 2 appears.

Figure 2: Starting SQL Server Installation

• After a few seconds, figure 4 disappears from the screen and the “SQL Server Installation Center” window appears (figure 5). This tool supports you in three phases:

• Planning
• Installation
• Maintenance

The “Installation Center” window is first shown in the “Planning for the Installation” step. As its name implies, this part is responsible for some early planning before starting SQL Server installation included checking hardware and software requirements for installation, the latest security and training documents, checking all provisions for a successful installation, etc.

The Installation Center window consists of two parts. The first option in the left column is Planning (figure 3).

Figure 3: SQL Server Installation Center Window (Planning Section)

As shown in figure 3, when you click on “Planning”, some options appear on the right whose works are as follow:

Microsoft recommends that you run SQL Server on NTFS or ReFS systems.

Installing SQL Server on a computer with the FAT32 file system is supported by Microsoft but is not recommended because it is less secure than the other two file systems.
The minimum main memory (RAM) required for installation is 1MB (the Express edition only requires 512KB). However, such a configuration will not have maximum performance.

As a general guide, you will need the main memory of at least 2 GB or more. Although SQL Server also requires at least 6 GB of hard disk space, the disk space you require will vary depending on the SQL Server components you install.

When you click “Security Documentation” on the Planning page, you will go to the Microsoft page in which general Security Considerations discuss.

Separation of services is one of the most significant security measures. For this, use separate Windows accounts for different SQL Server services.

 There are two main sources for gaining information about all SQL Server features:

• Microsoft Dos
• Online Release Notes.

Microsoft Dos (documentations) is a series of official online documents provided by Microsoft, while online release notes are just the latest information about the release that does not necessarily find in Microsoft documents.

One of the critical tasks in planning is to check whether all conditions for a successful database installation are provided or not.

When you click “System Configuration Checker”, a component called Setup Global Rules starts automatically. Setup Global Rules identifies problems that may occur while installing SQL Server support files.

After the completion, the system displays how many operations are checked and how many are faulty. You must correct all defects before proceeding with the installation process.

Note: It is recommended to execute this option before starting the SQL Server installation. By executing this option, installation conditions will be checked and if there is a problem, the system alerts.

In the previous part, it is mentioned that the “SQL Server Installation Center” page supports three steps.

The first was “Planning”, which has already been briefly discussed. Now let us examine the second step (Installation). The main steps of the SQL Server installation start over here.

Just click on the “Installation” option in the left column to install. By doing this, installation options (SQL Server installation and its management tools like SSMS) appear on the right side.
Given that since SQL Server 2016 onwards, the SQL Server Management Studio has been separated from the SQL Server Installation file, we divide our teaching into two parts:

• SQL Server 2019 Installation,
• SQL Server Management Studio Installation.

If you have ever installed a sophisticated software product, you probably experience uncertainty feelings along with starting installation.

This feeling comes from the complexity of the product and different questions that may arise during the installation process.

This section helps you to have a better and easier installation by answering the questions you may face.

Note: Depending on whether you are doing a new installation or an installation on an already installed SQL Server, the steps are slightly different. For example, the navigation menu on the left side of the Installation Center window is not the same for both.

The Installation Center shows you several options for installing a database system and its components.

After checking the system and preparing installation conditions (via System Configuration Checker in Planning):

Step 1: Installation > New SQL Server stand-alone installation …

• click the “Installation” on the left column
• and then “New SQL Server stand-alone installation or add features to an existing installation” (figure 6).

Figure 6: SQL Server installation Menu

What appears next depends on whether you are installing SQL Server 2019 for the first time or a version of it have already existed on your computer.

In the former (first installation), the “Product Key” and “Terms License” pages appear before the “Global Rules” page.

In the latter (reinstallation), first the “Global Rules” page and then the “Product Key” and “Terms License” pages appear.

     If this is the first time you are installing the product on this system, the “Product Key” and “Terms License” will appear here. This step determines your software version.

If you do not have the product key, select the first option (SQL Server Free Edition); otherwise, select the second option, enter the product key, and then click “Next” (figure 7).

Note: Remember that do not install a free version in the operational environment because the SQL Server expiration date will expire after 180 days.

Step 2: Product Key > Enter the 25-character key

On the “Product Key” page, enter the 25-character key from the product packaging, then click “Next”.

another option is to specify free edition, SQL Server Express.

Figure 7: Product Key Installation Page

Step 3: License Terms > I Accept the License Terms

In the next window, tick the “I Accept the License Terms” on the “License Terms” page and click “Next”.

Figure 8: Accepting Microsoft License’s Terms

The “Global Rules” page is similar to what you saw in the Planning step (see the previous section).

As mentioned before, at this stage of the installation, “Setup Global Rules” check installation errors preventing the accurate installation of SQL Server and correct them before installation.

Step 4: Global Rules > Next

If there is no error, click “Next” (figure 9).

Figure 9: Global Rules Page

After clicking “Next”, you enter to the “Microsoft Updates” page.

On this page, you can decide whether you want to use the “Microsoft Update” service to keep the installation up-to-date or not. (I strongly recommend that enable the “Microsoft Update” service.)

Step 5: Microsoft Update > Use Microsoft Update to Check for Updates > Next

If you want to receive the product update, tick the “Use Microsoft Update to Check for Updates (Recommended)” option.

Figure 10: Product Updates Page

Step 6: Install Setup Files > Next

After clicking Next, the “Install Setup Files” screen appears and installs all the files needed to boot (figure 11).

Figure 11: Install Setup Files Page

Step 7: Install Rules > Next 

By clicking “Next”, the “Install Rules” page appears (figure 12). Click Next to continue.

This page checks some installation rules to see whether the installation process can go on or not.

For proper running and continuing the installation, the word “Passed” must be displayed in front of each rule in the “Status” column.

You can skip warnings displayed at this phase because they are just information for users who are installing the product. For example, the Firewall option may be shown in Warning status where there is no problem for continuing. 

Figure 12: Install Rules Page

Step 8: Feature Selection > Select The Components That You Want To Install > Next

Select the components that you want to install on the Feature Selection page (figure 13). Then click Next to continue.

These tools are installed on drive C by default (of course, you can use the desired installation directory). When you select a component from the Features section, its related description is displayed on the right of the page (Feature Description).

At the bottom of this page, the Disk Space Requirements section shows the amount of space required to install ticked items. You can also specify a directory for storing shared components at the bottom of the page.

Here, we just choose the “Database Engine Services” option and go to the next step. Note that after installing SQL Server, whenever you want, you can easily add or remove features selected at this stage using the Setup file.

Click “Select All” to choose all tools. It is recommended that avoid selecting all as much as possible to not install additional services on your system. (To better understand, refer to the “Which SQL Server component should be installed?” part discussed in this chapter.) Then click Next for going on.

Figure 13: Feature Selection Page

Key Note: PolyBase installation requires Oracle Java SE Runtime Environment (JRE) version 7.51 or higher. In other words, if we select this component on the Feature Selection page and JRE has not been installed, the installation process will encounter an error.

Note: In this version (2019), by making changes to PolyBase, Microsoft allows us to connect to the other databases’ tables in an External Table format and work with them using T-SQL statements. We can also connect to a cluster of Spark servers using the new features provided in SQL Server 2019.

Note: PolyBase technology goes beyond Linked Server in SQL Server and its power and speed are not comparable to Linked Server.

Step 9: Instance Configuration > Select Either Default Instance or Named Instance

On the Instance Configuration page, select either Default Instance or Named Instance (figure 14).

(Refer to “should multiple instances of database engine be used?” part.) Click the Default option to install the Default Instance. If the Default Instance has already been installed on your system and you select the Default instance, the Setup program will update it and allows you to install additional components. Therefore, there will be another opportunity to install the components skipped in the previous steps.

Figure 14: Instance Configuration Page

To install a new Named instance, click Named Instance and type the new name in the specified box. A list of instances already installed on your system is displayed at the bottom of the page. MSSQLSERVER is the default named instance for the database engine. Click Next for going on.

Note: If none of the SQL Server editions is already installed under the Default Instance on the Windows operating system, select the Default Instance and click Next. Otherwise, select the Named Instance and type your desired name in the box to install SQL Server 2019 (see figure 14). It should be noted that since 2000, each computer could have more than one instance. Using different instances increases security and performance.

Step 10: Server Configuration > Service Accounts: Determine The Usernames and Passwords | Collation: Choose Your Desired Language > Next

The next page, Server Configuration, consists of two tabs:

• Service Accounts

The first one, Service Accounts (figure 15), allows you to determine the usernames and passwords related to all the components during the installation process.

• and Collation.

In the Collation tab, you can choose your desired language. To choose the desired language, click Customize in the Database Engine, then select the desired language in the opened window (e.g., Persian), and click OK.

Although you can set an account for all services, this is not recommended for security reasons. In fact, this tab defines that which user can access which SQL Server service. As illustrated in figure 15, this tab contains four parts:

•  Service: Specifies the name of the service you are going to use. For example, we can determine a specific user for the Database Engine service and another user for the Analysis Services service.
• Account Name: Specifies each user’s name who has access permission to each service. You can change the user name by clicking on each cell.
• Password: Specifies the user password.
• Startup Type: Specifies how to run the selected service.

Figure 15: Server Configuration Page

If you are installing other services (e.g., Analysis Services), change the related Collation in the same way, and finally, click Next. This prevents any problems while storing and regulating the data of that particular language.
In this step, be sure to tick the option “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service”. It is better to change the startup type for the agent service to automatic.

Step 11: Database Engine Configuration > Server Configuration: Choose the authentication mode (Windows or Mixed authentication modes) > Data Directories: Specify locations for storing files

After this step, you will enter the “Database Engine Configuration” page in which there are several different tabs. Server Configuration, the first tab on this page, is used for security and authentication and allows you to choose the authentication mode for your Database Engine. As we saw earlier in this chapter, the Database Engine supports two Windows and Mixed authentication modes.

The Windows authentication mode allows every user who logs in through Windows to access SQL Server too; this means that Windows verifies the user’s identity and SQL Server does not ask for the password and does not perform the identity verification. In the box below, we specify which Windows user will be the SQL Server administrator. By using the Add Current User option, select the current user as the SQL Server administrator.
To connect to the Database Engine using both Windows and SQL Server authentication, select Mixed Mode in the Authentication Mode window. Enter the password in the Enter Password field and re-enter the password in the Confirm Password field. Then click the Add Current User to select the currently logged-in username, or you can select the desired username via the Add option. Finally, click Next.

Figure 16: Database Engine Configuration Page

The second tab on the page, Data Directories, allows you to specify locations for storing files related to the Database Engine. In other words, it lists the default folders in which steps of installing various SQL Server components during the installation process will be stored; you can change them.

Figure 17: Database Engine Configuration Page

What appears next depends on whether you have selected Analysis Services during the installation process or not.

If you select this service, a separate page will appear.

After clicking the Next, you will be taken to the Analysis Services Configuration page.

On this page, like the previous step, click on the Add Current User option to select the current user as the Analysis Services administrator,

and then click Next to continue.

Note: As mentioned before, Analysis Services is an option for Business Intelligence subjects in SQL Server. If you are looking for a reference roadmap to becoming a SQL Server Analysis Services specialist, be sure to read the following article:

SQL Server Analysis Services: The Definitive Guide (2021)

If you want to learn how to install SQL Server Analysis Services (SSAS), follow the link below:

Figure 18: Analysis Services Configuration Page

Step 12: Ready to Install > See a Summary of All SQL Server Components > Install 

The last page before starting the installation process is the “Ready to Install” page. This page allows you to see a summary of all SQL Server components that are going to be installed during the installation process.

Click Install to begin the installation process.

After clicking, the installation process begins and the installation time depends on the power of your computer hardware (figure 19).

Figure 19: Ready to Install Page

Step 13: Installation Progress > Next

The Installation Progress page displays the progress of the installation process.

Figure 20: Installation Progress Page

Step 14: Install Completion > Close

As illustrated in figure 21, after the completion of the software installation, a window appears indicating the “successful” installation.

This is the final window of the installation process, after which a message appears which shows the complete and successful installation.

If the service has not been installed properly, then it will be displayed with a red icon.

Click the Close button to complete the installation process.

After installing SQL Server, you must restart your computer.

Figure 21: Install Completion Page

 Up to now, the SQL Server 2019 database engine has been installed by doing previous steps.

As mentioned earlier, from SQL Server 2016 edition onwards, the SSMS management tool must be installed separately. Therefore, you must download it separately and then install it on the SQL Server database engine to manage the database and its objects via Wizard or Coding.
Downloading and installing this tool is very simple.

Just select the “Install SQL Server Management Tools” option from the SQL Server Installation homepage (SQL Server Installation Center window),

and download the SSMS Setup.exe on the page opened from the Microsoft website.

Then, double-click on the downloaded file and install it by following a few steps.

Figure 22: SQL Server Installation Center Page for Installing Management Tools

Figure 23: Download SSMS Management Tool

After receiving and executing the file, the following window will open. In this step, you can change the SSMS installation path if necessary and then click on the Install option for installation. The installation steps are easy and short and are illustrated in the below pictures, respectively.

Figure 24. SQL Server Management Tools Installation Window

Figure 25. SQL Server Management Tools Installation Progress Window

Figure 26. SQL Server Management Tools Installation Completed Window

After executing the above steps, the SSMS management tool will be installed.

For running this tool, just search SSMS in the Windows search bar then click on it.

On the opened page, enter your username and password according to the type of authentication you selected during the SQL Server installation process and click on Login.

In the next chapter, we will examine how to work with this tool through the Wizard.

To install and activate SQL Server Analysis Services (SSAS), you must follow the steps below when installing SQL Server on the Feature Selection page (Step 7 Installing SQL Server):
1. Select the Analysis Services option similar to the one below:
2. On the next page (Analysis Services Configuration page), select one of the modes. By default, Tabular mode is selected.

If you want to know more about Analysis Services (SSAS 2022) and how it works, I suggest reading the SSAS Tutorial article.