By: Rajendra Gupta | Comments (2) | Related: > SQL Server 2017
Problem
Microsoft SQL Server 2017 has many new features. In this previous tip, we installed a database engine with the SQL Server 2017 installer. In this tip, we will explore installing SQL Server 2017 along with other features such as R services, Python, SSIS, etc.
Solution
SQL Server 2017 comes in the editions listed below which are available for both Windows and Linux operating systems.
- Enterprise: Comprehensive, high-end capacities for commanding database and business intelligence essentials.
- Standard: Core data management and business intelligence abilities for non-critical workloads with minimal IT support.
- Express: This is a free version with limited SQL Server features.
- Developer: This is a free version to build, test, and demo apps in non-production environments. All Enterprise Edition features are included.
- Web: SQL Server Web edition is a low-cost version for Web hosting companies and Web VAPs to provide scalability, affordability, and manageability capabilities to their customers.
Below is the comparison for some of the important feature in a different edition of SQL Server 2017. For a full comparison list please refer to this link.
| Features | Enterprise | Standard | Web | Express |
|---|---|---|---|---|
| Maximum Relational DB Size | 524 PB | 524 PB | 524 PB | 10 GB |
| Log shipping | Yes | Yes | Yes | No |
| Database mirroring | Yes | Yes, Full safety only | Witness only | Witness only |
| Backup compression | Yes | Yes | No | No |
| Always On Availability Groups | Yes | No | No | No |
| Resumable online index rebuilds | Yes | No | No | No |
| Columnstore | Yes | Yes | Yes | Yes |
| Resource Governor | Yes | No | No | No |
| Automatic Tuning | Yes | No | No | No |
| Batch Mode Adaptive Joins | Yes | No | No | No |
| Batch Mode Memory Grant Feedback | Yes | No | No | No |
| Interleaved Execution for Multi-Statement Table-Valued Functions | Yes | Yes | Yes | Yes |
| Encryption for backups | Yes | Yes | No | No |
| SQL Profiler | Yes | Yes | No | No |
| SQL Server Agent | Yes | Yes | Yes | No |
| Database Tuning Advisor (DTA) | Yes | Yes | Yes | No |
| Advanced R integration | Yes | No | No | No |
| Machine Learning Server (Standalone) | Yes | No | No | No |
| Query Store | Yes | Yes | Yes | Yes |
| Database mail | Yes | Yes | Yes | No |
Install SQL Server 2017 Developer Edition
SQL Server 2017 Developer is a full-featured free edition, licensed to use as a development and test database in a non-production environment. You can download from this link.
Once SQL Server 2017 Developer edition is downloaded, double click on it to launch.
This gives us the option to select an installation type:
- Basic: Select Basic installation type to install the SQL Server database engine feature with default configurations.
- Custom: Select Custom installation type to step through SQL Server installation wizard and choose the features, configurations while doing the installation.
- Download Media: If we want to download the setup files only and perform the installation later you can use this option.
In this tip, we are going to perform the installation of features other than the database engine. Therefore, click on the Custom type.
Specify the Media Location where we want to save the SQL Server media. The drive should have at minimum free space of 9240 MB in order to extract the media.
While the media is being downloaded, we get different messages with some useful links.
In the below example, it shows these links:
- Link for Microsoft GitHub repository containing code samples for SQL Server, development samples for using different features like In-Memory OLTP, R-Services etc.
- Link to get Azure SQL Database samples and reference Implementation Repository to get different reference implementation samples for C#, Java, node.js, PHP, Python.
Below we can see more links for:
- Getting started with SQL Server
- Post your questions on Twitter using #sqlhelp hashtag and tagging @SQLServer
- SQL Server Documentation for specific Transact-SQL commands
- SQL Server Documentation
Once the SQL Server 2017 set up is downloaded, it extracts the files into the folder specified above.
Once extraction is complete, the SQL Server Installation Center is launched. Click on Installation on the left side.
Then click on New SQL Server stand-alone installation and you will get this message.
In the next screen, the SQL Server setup checks for Global Rules. Global Rules identify problems that might occur while installing SQL Server setup support files. If there is any failure, it must be corrected before proceeding further.
If all the Global Rules are passed, go to the next screen. In the next screen, it checks for Microsoft Update. Microsoft Update offers security and other important updates for Windows and SQL Server. We can skip this step, by unchecking the "Use Microsoft Update to check for updates."
In the next step, it installs the setup files. If files are already present, it skips the step.
Since we are doing a new installation of SQL Server 2017, select the option "Perform a new installation of SQL Server 2017". This is the default option.
If we select "Add features to an existing instance of SQL Server 2017", we need to select the instance from the drop-down list.
In Product Key, we can specify a license or select a free edition. In this example we are using Developer for the free Developer Edition.
In the next step, accept the License Terms. If we want to read the license agreement, we can copy it or print it to read further.
In the Feature Selection, we can select the required features. Here are some of the options.
- Database Engine Services it is the database server to install, configure, and perform administration of the databases.
- Machine Learning Services (In-Database) we can install R or Python languages for Machine learning. Read more about it using in this article Machine Learning Services.
- Full-Text and Semantic Extractions for Search is used for full-text queries.
- Data Quality Services is used to enrich, standardize and avoid duplicate data. Read more about it in this tip Data Quality Services.
- PolyBase query Service for External Data is useful to query NoSQL Data.
- Integration Services we can choose to install a scale out master or scale out worker. Read more about it using the SSIS Scale Out Feature.
- Distributor Replay Controller is useful to replay complex workloads. Read more about how to configure the SQL Server distributed replay feature.
- SQL Client Connectivity SDK will install SQL Server OLEBD and ODBC connectors. These connectors are used to connect SQL Server using.NET, Java, PHP, etc.
Note: You cannot find SQL Server Reporting Services and SQL Server Management Studio in the feature installation. This is because you need to download and install these components separately.
Refer to the below links to install Reporting Services and Management Studio:
- SQL Server 2016 rc0 installation and configuration changes
- Download SQL Server Management Studio
- Installing SQL Server Reporting Services 2017
Select the desired features and click on Next.
In the Instance configuration, we have the choice to use a default instance or a named instance. In a named instance, provide the instance name. Note: If a default instance is already present on the server, we have to use a new named instance.
In the next screen, we can specify SQL Server service accounts and collation. The account names are created automatically for each service or we can specify the accounts we want to use. We can also select the service startup type. For most of the services, it is Automatic by default.
By default, "Grant Volume Maintenance Task privilege to SQL Server Database Engine Service" access is provided. Read more about this in SQL Server 2016 rc0 installation and configuration changes.
We can select the desired collation from the Customize button.
In the Database Engine Configuration, we can select the authentication mode as Windows or Mixed mode (SQL Server authentication and Windows Authentication).
We can also specify the SQL Server administrators. If we want to add the current user as an administrator, click on "Add Current User".
To add other users, click on Add and search the user or group.
In the Data Directories tab, specify the directory for the data files, log files, and backups.
In the TempDB tab, we can specify the number of TempDB files, initial size, auto growth, and data directories. Read more about TempDB configuration best practices in SQL Server.
The Integration Scale-Out Configuration – Master Node is used to define the port that the master node uses to communicate between the master and worker nodes. A default self-signed certificate is created if we choose to create a new SSL certificate. If we have a certificate, we can use an existing certificate as well.
In the Integration Services Scale-Out Configuration – Worker Node, we need to specify the endpoint of the Master Node. Its format should be https://[MasterNodeMachineName]:[Port]). We can configure the SSL certificate of the master node to trust this machine.
In the next screen, we need to provide Consent to install Microsoft R Open. By clicking Accept, we can choose to download Microsoft R Open and agree to accept patches and updates as per the SQL Server update preference.
Click on Accept and wait for the installation to finish. Once done, it enables the next option.
Click on the Next button.
In the next screen, Consent to Install Python we can accept to install Python which is a powerful language for machine learning.
Wait for the Python install to complete.
If there is any issue in connecting to the download server, the setup gives the option to download the Microsoft Machine Learning Server installation files.
Download the files from the links and place them in a folder.
Provide the location of the folder to complete the offline installation of the Microsoft Machine Learning Server Components.
Review the configuration settings. If everything is OK, press Install.
This starts the SQL Server 2017 installation with the selected components.
Once set up is completed, it shows the installation status of each component, we can also review the summary log file from the link provided below.
Next Steps
- Install SQL Server Management Studio 17.x to connect to the database server.
- Download and install SQL Server Data Tools to build SQL Server relational databases, Azure SQL databases, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports.
- Go through these other SQL Server 2017 tips.
About the author
Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
