SQL Server 2016 Public Preview (CTP2) – Let’s see how it looks


The Public Preview of SQL Server 2016 is available for download on the Microsoft downloads site. The feature highlights are available in this blog post. I thought I will give blog about what’s different in the setup of the Database Engine.

SQL Server 2016 CTP2 Setup

The basic screens remain the same. The screen to select the edition is the same and the there is no change in the “License Terms”, “Global Rules”, “Product Update”, “Install Setup Files”, “Install Rules” and “Setup Role” pages.

The first major change that you will notice is in the “Feature Selection” page. This is where you will notice a change. Namely, the “PolyBase Query Service for External Data“. This installs two services:

1. SQL Server Polybase Engine Service which creates, coordinates and executes the parallel query plan against external data sources.

2. SQL Server Polybase Data Movement Service which manages communication and data transfer between SQL Server and the external data sources which will be used by the instance.

Note that these services are not instance aware and like Integration Services, there can only be one service per Windows host.

SQL Server 2016 CTP2 Setup

The next screen where you will notice a change is the Feature Rules page which checks if Oracle JRE 7 Update 51 is available or not. This is required for the PolyBase service since I had selected that in the previous screen.

SQL Server 2016 CTP2 Setup

If you don’t have the requisite Oracle JRE version running, then you will be prompted with the following message:
Rule “Oracle JRE 7 Update 51 (64-bit) or higher is required” failed.

This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

The forwarding link will take you to a webpage where you can download the latest JRE update. Once that is done, you will be able to continue with the installation. Another interesting part was the check for KB2919355 which is the Windows 8.1 Update which contains a cumulative set of security updates, critical updates and updates. So the setup has an OS related check as well.

The Server Configuration page allows you to choose the service accounts for the two PolyBase services.

SQL Server 2016 CTP2 Setup

The Database Engine Configuration page comes with a nifty tempdb file configuration which has a bit of learning to do but provides the opportunity to create additional tempdb files during the installation itself.

SQL Server 2016 CTP2 Setup

Once the setup is complete, you can connect to the database engine using Management Studio and you will see that your version number is 13.0.200. Also, you will notice that there are three user databases: DWConfiguration, DWDiagnostics and DWQueue which is related to the PolyBase service. More on that maybe in a future post.

So that was all about the Database Engine setup for the new SQL Server 2016 Public Preview release.

SQL Server 2016 CTP2 SSMS Disclaimer: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Running SQL Nexus using Command Line Parameters


SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data. One of the least commonly known facts is that the SQL Nexus tool also allows you to run it’s reports using command line arguments or even import diagnostic data automatically. The command line help reference is shown below:

sqlnexus.exe [ [ /S [ /E | /Uuser /Ppwd ] [/D"database"] ] | [/C"connstr"] ] [/Iinputpath] [/Ooutputpath] [/Rreport] [/X]

/Sserver        Specifies a SQL Server name to connect to.
/D"database"    Database to connect to
/E              Log in to SQL using Windows/integrated security
/Uuser          Specifies a SQL (non-Windows) login name
/Ppassword      Specifies the password for a SQL (non-Windows) login
"/Cconnstring"  Specifies the SqlClient ConnectionString to use (can be used instead of /S /E).
"/Ipath"        Import SQL diagnostic data from this path
"/Rreport"      Specifies a report file name to run.
"/Opath"        Specifies an export path for reports executed via /R
/X              Exit after importing (/I) or exporting (/O) the specified report (/R)
/Vparam=value   Specify the value of a form parameter

Automating Report Execution

The first scenario that I want to outline is the need to automate a report execution especially when you need to execute the reports repeatedly after activities like performance testing or during some other iterative activity which requires analysis of various sets of diagnostic data.

This can be achieved using the command line parameters shown below:

sqlnexus.exe /S"." /X /D"sqlnexus" /R"\Summary.rdl" /O""

As you can see from the screenshot below, you can see that I have a standard report i.e. “Bottleneck Analysis_C.xls” that was exported and a non-standard report which I created Summary.xls in the same folder. This also gives additional opportunity to create your own performance reports and schedule them using the SQL Nexus executable.

Report Output

After you have the exported reports, you could also write additional automation to email the reports as required.

Automating Data Import

SQL Nexus also allows you to perform data imports automatically using the command line parameters. The command line shown below performs data import from the specified folder into a SQL Server database called sqlnexustest on the default SQL server instance installed on the machine.

sqlnexus.exe /S"." /X /D"sqlnexustest" /I"C:\temp\output"

If the database does not exist on the SQL Server instance, then it will be created. The settings for SQL Nexus utility will be used during the data import which were saved when the last time the UI was used. So if you have disabled the profiler trace import from the UI and saved your settings, then the command line execution will not import the profiler traces.