You now have a couple of new installation options available when you install SQL Server 2016. One of those new features is being able to define multiple tempdb data files while installing SQL Server. Additionally the incorporation of PolyBase into the database engine means there is a new option for installing the new PolyBase Query Service. Greg Larsen walks you through the GUI installation process for SQL Server 2016 and explores these new installation options.
The installation process for SQL Server has changed slightly with the release of SQL Server 2016. You now have a couple of new installation options available when you install SQL Server 2016. One of those new features is being able to define multiple tempdb data files while installing SQL Server. Additionally the incorporation of PolyBase into the database engine means there is a new option for installing the new PolyBase Query Service. In this article I walk through the GUI installation process for SQL Server 2016 and explore these new installation options.
As with any installation I needed to set up a Windows machine that could support my SQL Server 2016 installation. To identify all the installation requirements I referred to this documentation: https://msdn.microsoft.com/en-us/library/ms143506.aspx.
I decided to install SQL Server 2106 on a VM machine that was setup to support 4 CPU. I chose to install SQL Server on the Windows 2012 R2 Data Center operation system.
Since my installation was going to install PolyBase my VM machine installation needed to meet these requirements, as well as the requirements for SQL Server 2016:
- 64-bit SQL Server Evaluation edition
- Microsoft .NET Framework 4.0.
- Oracle Java SE RunTime Environment (JRE) version 7.51 or higher.
- Minimum memory: 4GB
- Minimum hard disk space: 2GB
These and additional requirements for PolyBase can be found by reading the “Getting started with PolyBase” information found here: https://msdn.microsoft.com/en-us/library/mt163689.aspx
Note that PolyBase can only be installed on one instance of SQL Server on a machine. Lastly if you are not installing PolyBase, then there is no requirement to install the JRE from Oracle.
Installing SQL Server
I will be walking you through all the screen shots of my SQL Server 2016 installation. This may seem like overkill but I wanted you to be able to see each and every installation window that was associated with my installation.
The first step I take, once my virtual machine is set and running is to mount the ISO image of CTP2.4. I downloaded the ISO image from this location: https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016.
When I mount the ISO image and browse the image you see the following directory.
To launch the installation I just double clicked on the “setup” exe as shown. Upon doing this the following Planning pane for the SQL Server Installation Center window was displayed.
On this window I clicked on “Installation” to start my installation process. When I did this the Installation pane was displayed in the SQL Server Installation Center window.
On this installation pane, I clicked on the “New SQL Server stand-alone installation or add feature to an existing installation” option to start my install for my copy of SQL Server 2016. When I do this the “Product Key” page is displayed.
Product Key Page
Here I click on the “Next>” button to continue with the installation, since there isn’t any information for me to enter on this window, since the “Evaluation” edition is the only addition available. When I clicked on the “Next>” button the License Term window is displayed.
License Term Window
On this window I checked the “I accept the license terms.” checkbox. The “Turn on Telemetry and Error Reporting…” checkbox was already checked when the window first displays. I could have selected to uncheck these options, but instead I just used the default value. I just feel this is the correct choice in my case. I made this choice so if my installation identifies issues then those issues can be used to improve the installation process for others in the future. I then click on the “Next>” button to continue my installation. When I click “Next>” the following Microsoft Update window is displayed.
Microsoft Update Window
On this screen I’m am being asked if I want to use the “Microsoft Update” service to keep this installation up to date. Since my machine is only for testing I will leave this box unchecked. If this was a real installation after RTM, I would considering checking this box so my installation would install the latest updated image of SQL Server. When I click the “Next>” box the following Install Rules window is displayed.
Install Rules Window
On this screen it shows the status of some installation rules that were run. As you can see I’ve gotten a firewall rule warning. Since I’m running a stand-alone instance of SQL Server I’m not going to bother with fixing my firewall rules and it is only just a warning. On a real installation I would resolve this issue to make sure my firewall was configured based on the environment it was installed. To move on I just clicked on the “Next>” button. When I click on the “Next>” button I get the Setup Role window displayed.
Setup Role Window
On this screen I have the option to install individual features, or all the features with the defaults. For my installation I will only be installing the SQL Server database engine and PolyBase, therefore I selected the first radio button. I then clicked on the “Next>” button to move on to the next step. When the next button was clicked the “Feature Selection” window was displayed.
“Feature Selection” Window
On this window I selected the features I want to install. In my case I want the “Database Engine Services”, “PolyBase Query Service for External Data”, and the “Management Tools”. The PolyBase option is a new option available with SQL Server 2016. Since I wanted to install PolyBase I checked that option. On this screen I also have the option to identify the default directories that will be used for storing different SQL Server components. I’ll used the defaults directory settings for my installation. Once I’ve selected all the options I want, I then click on the “Next>” button. When I do a number of feature rules get checked. The results of those rules is displayed on the “Feature Rules” window.
“Feature Rules” Window
As you can see, I got a warning. This error was caused because I hadn’t installed the Oracle JRE that PolyBase requires. If I hadn’t selected to install PolyBase then this rule would not have run, and no error would have been reported. Since I am installing PolyBase I needed to resolve this prior to moving on. To resolve this problem I installed the latest JRE, version 8, from this location: http://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
Once the JRE was installed I clicked on the “Re-Run” button. This ran through the feature rule tests again. Since no errors where found this time, I was taken directly to the “Instance Configuration” window.
“Instance Configuration” Window
Here I decided to create a default instance, instead of a named instance. When I clicked on the “Next>” button the “Server Configuration” window is displayed
“Server Configuration” Window
Here I can define the accounts that will be used for each service, and additionally I can use the “Collation” tab to identify the collation I would like for my instance. When I look on the “Service Accounts” tab I noticed two new services: SQL Server PolyBase Engine, and SQL Server PolyBase Data Movement. These two services are shown here because I selected to install the “PolyBase” feature. If I hadn’t selected to install the new PolyBase feature then these two services would not have shown up on “Service Accounts” tab. From my test installation I just take the default values for the “Account Name”, “Password” and the “Startup Type”. When I click on the “Next>” button the “Database Engine Configuration” window is displayed.
“Database Engine Configuration” Window
On this window you can see a new tab called “TempDB”. I’ll get to that tab in just a bit, but first on the “Server Configuration” tab I just take the default “Windows Authentication” for my “Authentication Mode”. I also click on the “Add Current User” button account I’m using for the installation. When I do that the account I am logged in as will be placed in the “Specify SQL Server administrators” section of the screen below. In my case that will be my machines “Administrator” account. I then click on the “Data Directories” tab, which brings up a list of default directories where the installation process will store different SQL Server components.
“Data Directories” Tab
I just take the default directory settings. If I was doing a real installation I would not put anything on the C drive. Instead I would spread out these directories across one or more drives to optimize I/O contention. But on my test machine I only have a C drive. For the next step of my installation I clicked on the “TempDB” tab. This brings up the configuration options for TempDB.
This tab was a new addition that was added with the rollout of CTP 2.4. On this screen I can configure the number of tempdb data files I want. By default the installation my installation process set the default to the number of core on my machine, which is 4. The initial number of temdb files is determined by the number of cores, up to 8 that is. If your machine has more than 8 cores this initial number of tempdb will be set to 8. In my case I have 4 core, so that’s why 4 came up as a default. I could have changed this number to something less than 4. But I just left this item set to 4. Additionally this screen allows me to set the initial size and autogrowth setting for my tempdb data and log files. This is a great addition. No longer do you need to go to a post installation step to configure multiple tempdb data files and set appropriate sizing setting for all tempdb data files. When I click the “Next>” button the “Ready” to install window is displayed.
“Ready” to Install Window
Here I can use the scroll bar on the right side to review the installation setting I selected. If for some reason I wanted to change one of the settings because I incorrectly set it during the prior installation screens, I could then use the “<Back” button to go back and modify the installation options I selected.
After I’m satisfied with my installation settings I then clicked on the “Install” button to start the installation process. When I do this the following “Installation Progress” window is displayed.
“Installation Progress” Window
Once my installation is finished the “Complete” screen and the “Computer restart required” windows are displayed.
“Complete” Screen and the “Computer restart required” Windows
I closed the “Computer restart required” screen by clicking on the “Ok” button. After doing that I could use the scroll bar to review the status of each of the features I installed. Once I’m done reviewing my installation results I then clicked on the “Close” button and restarted my machine.
Once my machine was restarted I had my installation of SQL Server 2016 CTP 2.4 up and running.
Trace Flags 1117 and 1118 are no longer needed
In the old days when you had to configure multiple tempdb data files it was recommended that you also turn on trace flags 1118 and 1117. Those days are now gone with SQL Server 2016. There is no need to set Trace flag 1117 to make sure your tempdb files all grow at the same time. There have been improvements is how page extents verses mixed extents get allocated to tempdb file. With those improvements you no longer need to set trace flag 1118.
What I got when I installed PolyBase
When I install PolyBase, I found three new databases defined on my instance. Not only that, I also found two additional services were installed.
I looked under the “Database” node in SQL Server Management Studio (SSMS) and found the three databases used to support PolyBase:
“Database” Node in SQL Server Management Studio (SSMS)
These databases are: DWConfiguration, DWDiagnostics, and DWQueue.
Additionally if you use the services applet (services.msc) you can see there are two PolyBase services:
Two PolyBase Services
Based on the description, the “PolyBase Data Movement” services manages communication and data transfer between SQL Server and external data sources, whereas the “PolyBase Engine” creates, coordinates and executes the parallel query plan against external sources.
As with any new release of SQL Server there are many new features to explore. In this article I highlighted those new features you can configure as part of the installation of SQL Server. Keep in mind I only showed you CTP 2.4, which is just a preview of things to come. It is not the final baked version of SQL Server 2016. Therefore the final release of SQL Server 2016, or any future CTP might look slightly different then what was shown here. Download CTP 2.4 today and start exploring all the cool new features that are available in SQL Server 2016.
See all articles by Greg Larsen