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:
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:
Hardware and Software Requirements for Installation
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.
Online Release Notes
There are two main sources for gaining information about all SQL Server features:
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.
System Configuration Checker
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.
SQL Server Installation on Windows
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.
SQL Server 2019 Installation
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:
The first one, Service Accounts (figure 15), allows you to determine the usernames and passwords related to all the components during the installation process.
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
Installing SQL Server Management Studio (SSMS)
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.
Adding SSAS To An Existing SQL Server Installation
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.