Microsoft SQL 2017 is the latest version for Microsoft’s flagship database server platform. This latest version of the SQL server software boasts some newer features which provide a wider range of performance, security and other options which should be explored with upgrades in mind. Let’s take a look at the installation process for SQL server 2017 and then some of the options which are available during installation as well as those that can be leveraged to gain further insights regarding improvements with the 2017 version.
1. Choose an Installation Path
There are three main installation paths which you can follow to begin making use of SQL 2017.
- A downloaded trial version can be used for 180 days before Microsoft requires that you purchase either a Standard or Enterprise license. For many people exploring the latest version of SQL server, this will be plenty of time to examine exactly what an upgrade to the 2017 version will require.
- However, there is a developer version which is available for free also including all of the options that a trial version offers. Using the developer version allows for an indefinite period of time to fully learn and appreciate all that is offered in the latest version of SQL server.
- Lastly, there is a fully free version, but it does not offer the entire scope of features so many high-end users will not find it valuable to test, develop or plan for an upgrade.
For the reasons already mentioned, the developer version may be the best choice for an initial installation path so that all of the improvements can be fully explored without worrying over the end of a trial-period. This particular version will also allow for workload testing as well as use of all business intelligence and programming features. The only drawback to the developer version is that it cannot be used in a production environment.
When installing SQL server 2017 it is important to understand that there are tools which are no longer available during the initial installation phase. As such, you may find that you will need additional time install more tools on top of the database engine and the various options chosen. For instance, SQL Reporting Services are installed separately as are the SQL Server Management Tools and SQL Server Data Tools (more about some of these are discussed further along in this post).
2. Configure Database Options
Once you begin an installation, there will be choices as to which initial licensing you will be using. For the sake of this article, let’s consider that the developer version is being used so that all of the available options can be considered. Beyond the licensing choice, there will be a number of options to be considered, among which are the following:
- Replication which can be used to emulate how the database will be managed including backup and mirroring. This is an important choice to consider but it is not specifically new to most database administrators.
- Machine learning services (In-Database)
- Installation of R and/or Python (both can now be installed on the same server instance) for programming enhancements, which also lends itself to this developer version.
- Which query options will be used for searches including full-text or semantic extractions.
- Data Quality Services which are important for standardizing and preventing duplication of data. This option is also important for implementing a data scale-out master with workers across several servers or instances.
- Polybase options are available at this point of the installation which can allow for NoSQL queries.
- Analysis services options for Business Intelligence integration are also in the mix during the installation.
3. Setup Additional Features & Reporting
Most of the main features have now been offered as choices during the installation, but there are additional options which can be considered based on needs. Advancing to the next steps in the insulation will provide choices for these options:
- Client tools are available for backward compatibility with earlier SQL versions in relationship to Data Quality Services. Again, these are services which can be important when using master and worker instances across several servers.
- SDK resources for developers are also available for installation which are additionally useful in the developer version in order to understand all the programmatic, security and performance improvement prior to using the 2017 version in production mode.
- Distributed Relay is a feature available that is very much like the SQL profiler, except it can be distributed over multiple servers. This is used in much the same way to capture traces for security and performance when gathering information for upgrades and testing. Additionally, this is where you want to make considerations regarding this tool since it allows you to simulate workloads to further advance understanding of development and upgrade issues.
- SQL client connectivity SDK is available for installation that provides OLEDB and ODBC connections using programming languages including .Net, Java, PHP and others.
- Master Data Services provides the means to organize important data into models while creating rules for access and control.
4. Configure Instances & Security
It is important to remember that multiple instances of SQL can be used on the same server which is critical for a variety of best practices development on the SQL Server 2017 platform. Among the considerations which can be simulated are workloads, replications versus mirroring, or the separation of instances while measuring performance impact.
In regard to security issues, the developer mode also includes the full options for SQL server authentication within Windows. Hammering out all security issues is important to achieve before making the shift to the newer SQL version and the developer version includes all of the authentication options. Windows account authentication in SQL is available to implement as is the mixed mode which is a feature allowing for the creation of logins and passwords within the SQL server environment.
5. Setup Data Directories & Filestream
While you are finalizing all of your decisions with your installation, be sure to check all of the data directories. It is here that you can choose the locations of your various data files and log files. A best practice is to place these various types of files on different drives in order to ensure top performance and implement good security as well as provide for effective disaster recovery planning.
There are a few other considerations to be made while installing SQL server 2017. FILESTREAM provides for storage of non-structured data within databases. If you are looking for ways to operate in data mining mode then you will be interested in using the Analysis Services configuration which provides for creation of fast queries and report results. When considering data mining modes, you must choose between tabular and multidimensional, the former being more memory intensive. If you have plenty of storage space then multidimensional mode may be the best choice since it has the least performance impact.
6. Install SQL Server Management Tools
After installing the main database server engine, you will need to go back to the setup window and choose to install the SQL Server Management Studio which can further assist with tuning an SQL instance. Additionally, you can consider installing SQL Server Data Tools which are important for using Business Intelligence tools, a major plus to the newer Microsoft SQL server platforms, especially the 2017 version.
Before installing SQL Server 2017 it is important to plan and understand what you need to accomplish with an initial installation of the newest version. The mentioned options and features in this article are meant to be considerations as a guideline for development and planning new SQL instances as well as migration of existing databases from previous versions. Making use of all the available tools for planning are as important as considering what new features can be implemented for database improvements.