Installing Microsoft SQL Server 2019 Integration Services
You are going to install a new instance of SQL Server on your workstation; if you prefer, you can also upgrade an existing SQL Server instance, as noted in the recipe. The installation will include all the features necessary to design SSIS solutions generally, not just to perform the work presented in this book.
The Client Tools Software Development Kit (SDK), an otherwise optional SQL Server component, must be installed on the workstation used in SSIS development. It contains the .NET managed assemblies with design-time access to the SSIS runtime, without which SSIS development simply cannot take place.
The installation of the SQL Client Connectivity SDK, another optional component, is recommended, though not necessary. It contains SQL Server Native Client connectivity resources that you might need in database application development.
Important note
None of the SDKs mentioned would ever need to be installed on a server used for hosting data, as such a server would normally not be used for SSIS development.
Getting ready
You need access to the SQL Server 2019 installation media. Since this is going to be a typical development workstation installation, you can use the free, specialized SQL Server 2019 Developer Edition, available for download at https://www.microsoft.com/en-us/sql-server/sql-server-downloads. Alternatively, you can also use a free trial edition of SQL Server 2019 Standard, or the Data Center editions, but please note that their use is limited to 180 days.
Important note
The Developer edition of SQL Server 2019 is a full-featured free edition, licensed for use as a development and test database in a non-production environment.
Either download the installation media from the website or use the installation media provided by the administrator in your organization. If you decide to use the Developer edition of SQL Server 2019, download the executable from the website, and save it to the local drive on the workstation that you will be using for the recipes in this book. If you prefer to use the Evaluation edition of SQL Server 2019, you will have to sign up on the website by providing some of your personal information before being able to access the installation media.
How to do it…
Start your workstation and log in with an account with administrative operating system privileges. Make sure the workstation has access to the SQL Server 2019 installation media – for instance, make sure that the Developer edition executable file is available on the local drive:
- Locate the installation media, and double-click the installation executable (for instance, SQL2019-SSEI-Dev.exe for SQL Server 2019 Developer Edition) to start the installation.
When prompted by the operating system, which will ask whether you want to allow the application to make changes to your device, as shown in the following screenshot, click Yes to continue:
Figure 1.1 – The SQL Server installation User Account Control dialog
- At the beginning of the installation, you are asked whether you would like to use the basic settings, customize them, or simply download the rest of the installation media.
Tip
Generally, you will only download the installation media if you plan to install SQL Server on additional devices. On the other hand, a very good reason for having the media available locally would be in case you need to repair the installation later.
Select Custom to continue.
- You now have to select the location of the installation files. Use the Default folder unless you prefer to use an alternative location.
When ready, click Install to continue. Depending on your internet connection, it should take just a few minutes to download and extract the files.
- After the installation files have been prepared, the SQL Server Installation Center window will open, as shown in the following screenshot:
Figure 1.2 – SQL Server Installation Center
On the Installation page, select the New SQL Server stand-alone installation or add features to an existing installation option to start the setup wizard.
- On the Product Key page, either specify your free edition (Developer or Evaluation) or provide the product key of a licensed edition that you are allowed to install.
Important note
Do not install the Express edition of SQL Server 2019, as it does not include SSIS, nor does it come with many of the other features that you will need for the recipes in this cookbook.
- Specify the appropriate edition, as shown in the following screenshot:
Figure 1.3 – Specifying the SQL Server edition
Click Next to continue.
- On the License Terms page, read and accept the license terms, and then click Next to continue.
- On the Microsoft Update page, shown in the following screenshot, check Use Microsoft Update to check for updates to include the latest updates for the product, unless you prefer to install them later:
Figure 1.4 – Adding available updates to the installation
Click Next to continue.
The setup program should now perform a few checks of your system to verify whether the installation can proceed.
- On the Install Rules page, you should see the system validation results.
If there are errors, click on the link in the Status column to access each error message. Depending on the error, the installation might not proceed until you correct the cause or might even have to be aborted and restarted after the problem has been resolved.
If there are warnings, the installation should allow you to continue; however, you should inspect the warning messages anyway, as shown in the following screenshot, as additional activities might be needed during or after the installation in order for the SQL Server instance or one or more of the shared features to work as expected:
Figure 1.5 – Install Rules
Tip
If SQL Server has not been installed on this workstation before or has not yet been configured for external access, you might see the Windows Firewall warning, as shown in the screenshot. In this particular case, the warning points to information on configuring the Windows firewall in order to allow external access to the SQL Server instance.
You do not have to make any changes to your workstation's firewall at this time.
When ready, click Next to continue.
If the installation detects an existing SQL Server instance, an additional step is added, shown in the following screenshot, asking you to either create a new SQL Server instance or add features to an existing one:
Figure 1.6 – Installation Type
This cookbook assumes that you will be using a workstation where SQL Server has not been installed before, but you can also use an existing SQL Server instance if you prefer.
- On the Feature Selection page, select the following features to be installed:
a) Database Engine Services
b) PolyBase Query Service for External Data
c) Java connector for HDFS data sources
d) Client Tools Connectivity
e) Integration Services
f) Client Tools SDK
g) SQL Client Connectivity SDK
You can see part of the selection in the following screenshot:
Figure 1.7 – SQL Server 2019 Feature Selection
When ready, click Next to continue.
- If you are installing this SQL Server instance on a workstation where SQL Server has not been installed before, you can create either a new default instance or a named instance. Otherwise, your options will depend on what parts of SQL Server are already installed on the system:
Figure 1.8 – SQL Server 2019 Instance Configuration
If possible, use the default instance, as the preceding screenshot shows; however, if you decide on using a named instance instead, we recommend that you use MSSQL2019 as its name. Throughout this book, we will refer to this SQL Server instance either as localhost (the default instance) or MSSQL2019 – in both cases, this will mean the same SQL Server instance.
When ready, click Next to continue.
- On the PolyBase Configuration page, leave the default settings unchanged, as shown in the following screenshot, and click Next to continue:
Figure 1.9 – PolyBase Configuration
- On the Java Install Location page, leave the default option selected to install the version of the Java runtime environment included in the installation, as shown in the following screenshot, unless you are installing to an environment where a different version is required by your administrators:
Figure 1.10 – Java Install Location
If you have to install a different version, select the second option, and use Browse to locate the installation files.
When ready, click Next to continue.
- On the Server Configuration page, on the Service Accounts tab, change the Startup Type setting of the SQL Server Agent feature to Automatic.
Leave all other settings on this tab unchanged, as displayed in the screenshot that follows:
Figure 1.11 – SQL Server service accounts
- On the Collation tab of the same page, you can leave the default collation for the database engine; however, it is recommended to use a Windows collation instead.
To change the collation, click Customize next to the Database Engine Collation setting, and then in the Customize the SQL Server 2019 Database Engine Collation dialog, select the Windows collation designator and sort order option with the following settings:
a) Collation designator: Latin1_General_100
b) Accent-sensitive: Checked
c) Char/Varchar Storage Options: UTF-8
The recommended settings are shown in the following screenshot:
Figure 1.12 – Setting the default collation for the SQL Server instance
Click OK to confirm the settings.
Review the settings on the Collation tab; the full name of the collation should be Latin1_General_100_CI_AS_SC_UTF8, as shown in the following screenshot:
Figure 1.13 – The default SQL Server collation
When ready, click Next to continue.
- On the Database Engine Configuration page, on the Server Configuration tab, shown in the following screenshot, leave Windows authentication mode selected for Authentication Mode:
Figure 1.14 – Database Engine Configuration
Add the account you are currently using to the SQL Server administrators role by clicking the Add Current User button.
After a few moments, your username should appear in the SQL Server administrators list box.
You do not have to make any changes to the rest of the settings on this page unless you want to change the location of the database files, which you can do on the Data Directories tab.
Important note
For the purposes of this cookbook, you can use the default locations on the C: drive for the database files; however, in a real-life environment – even if solely for testing purposes – it is recommended to host the database files on a different drive, not the one used for the operating system and other installed features files.
When ready, click Next to continue.
- On the Ready to Install page, review the installation options listed in Steps 5 through 16.
When you are sure that everything has been configured in accordance with the instructions in this recipe, click Install to start the automated part of the installation. On modern hardware, the installation should complete within a few minutes.
- Once the installation has completed, inspect the installation status of each installed feature. If any errors are encountered during the installation, they will be marked with a Failed installation status. When you select each feature reported as failed, additional information about the failure should be displayed in the Details section:
Figure 1.15 – The final step of the SQL Server 2019 installation
Inspect any error messages and address the cause of each problem based on the information provided. You can repeat the installation after you have resolved the problems.
Click Close to complete the installation.
One more task awaits you, and then the installation can be considered finished (for the time being, at least). In order to allow the PolyBase services access to the SQL Server instance, you must allow connections to the instance through the TCP/IP protocol.
- In the Windows Start menu, in the Microsoft SQL Server 2019 folder, locate SQL Server 2019 Configuration Manager and start it:
Figure 1.16 – SQL Server Configuration Manager
In Configuration Manager, shown in the preceding screenshot, under SQL Server Services, you should see that the SQL Server instance that you just installed is running; however, both SQL Server PolyBase services will either be Stopped or in the Change pending… state.
The reason for this is that the services are unable to connect to the SQL Server instance. They use TCP/IP to connect, and on a newly installed SQL Server instance, this protocol is not enabled.
- Under SQL Server Network Configuration | Protocols for MSSQLSERVER, shown in the following screenshot, right-click the disabled TCP/IP protocol, and select Enable from the shortcut menu:
Figure 1.17 – Enabling the TCP/IP protocol
A warning is displayed, telling you that in order for the changes to take effect, the affected service must be restarted. Click OK to close the warning.
- Back under SQL Server Services, right-click the SQL Server (MSSQLSERVER) service, and select Restart from the shortcut menu:
Figure 1.18 – Restarting the SQL Server instance
- Wait for the service to restart. When prompted to stop or restart any other dependent service, confirm those as well. After the SQL Server instance has restarted, the PolyBase services should start as well. However, if they stay in the Change Pending… state for an extended period of time, try starting them manually. If even that fails, you might have to restart the workstation.
Close SQL Server 2019 Configuration Manager.
How it works…
You have just successfully installed a new SQL Server instance. You will use it to complete the rest of the recipes in this cookbook; of course, it can also be used for other development work involving the SQL Server platform. If you later determine that you need additional features, you can add them by starting the installation again from SQL Server Installation Center.
Important note
If you have installed the Developer edition of the product, please remember that it has not been licensed for production use. You are only allowed to use it for application development and testing.
If you have installed an Evaluation edition of the product, remember that the license expires 180 days after the installation.