Things new in SQL Server 2016

In SQL Server 2016,

Configure multiple TempDB database files during Installation and set up.

https://hodentekmsss.blogspot.com/search?q=TempDB

The Query Store (new) stored texts, execution plans and performance metrics with the database. You have access to its dashboard related to query performance.

https://hodentekmsss.blogspot.com/search?q=query+store

[image]

Availability of Temporal Tables (history) which records all data changes.

https://hodentekmsss.blogspot.com/2016/07/temporal-tables-in-sql-server-2016-to.html

Built-in JSON Support(new). You can import/export, save and parse in JSON.

https://hodentekmsss.blogspot.com/2016/11/accessing-nested-json-formatted-text.html

Polybase(new) query engine integrated SQL Server with external data in Hadoop or Azure Blob storage. Import/export and executing queries all possible.

https://hodentekmsss.blogspot.com/search?q=polybase

Stretch Database(new) lets you dynamically, securely archive data from local SQL Server Database to an Azure cloud SQL database. querying is automatic both local and remote data by linked databases.

https://hodentekmsss.blogspot.com/2016/05/stretch-database-is-nice-feature-of-sql.html

In-memory OLTP:

Now supports FOREIGN KEY, UNIQUE and CHECK constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT, OUTER JOIN, and subqueries in SELECT.

Supports tables up to 2TB (up from 256GB).

Has column store index enhancements for sorting and Always On Availability Group support.

New security features:

Always Encrypted: When enabled, only the application that has the encryption key can access the encrypted sensitive data in the SQL Server 2016 database. The key is never passed to SQL Server.

Dynamic Data Masking: If specified in the table definition, masked data is hidden from most users, and only users with UNMASK permission can see the complete data.
https://hodentekmsss.blogspot.com/2018/05/data-masked-column-in-table.html

https://hodentekmsss.blogspot.com/2017/07/new-security-feature-in-sql-server-2016.html

Row Level Security: Data access can be restricted at the database engine level, so users see only what is relevant to them.

SQL Server PolyBase engines starts and stops in SQL Server 2016 - Part 2

Tried various suggestions from the SQL Server 2016 forum. However none of the checks / measures reveal the reason for this behavior.
According to documentation one can confirm whether Polybase is installed or, not by running the query:

SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled;

You should get 1 if it is installed, or 0 if it is not.


However, the problem arose when there was a third answer to the above query.


The above query runs and the response is as shown.

Immediately after this you get a screen to save the response. If you do indicate a location to save the response, the program saves it to a file with the extension .rpt.

The report file can be opened with Notepad which is just the message in the response to this query shown above.

While this problem was being examined on the machine there was an update to the OS on March 23, 2017.

This morning when I tried again to start the Polybase server, there was no problem. It started without any error messages.

However, after making sure the Polybase was up and running, I tried the 'IsPolybaseInstalled?' query and I still get the same third option.

SQL Server PolyBase engines starts and stops in SQL Server 2016

In SQL Server 2016 you can connect to external data sources that includes the Hadoop clusters and Azure Blob Storage but you should have Polybase installed. SQL Server 2016 SP1 has PolyBase installed.

You can read all my Polybase related posts here:
http://hodentekmsss.blogspot.com/search?q=polybase

According to documentation one can confirm whether Polybase is installed or, not by running the query:

SELECT SERVERPROPERTY ('IsPolybaseInstalled') AS IsPolybaseInstalled;

You should get 1 if it is installed, or 0 if it is not.

In the local installation of SQL Server 2016 SP1 on this machine neither of these results were obtained.

The statement was executed but a browser window opened looking for files of type .rpt.

There are 2 services with Polybase installation on SQL Server 2016 SP1.

SQL Server Polybase Engine (Instance Name)
SQL Server Polybase Data Movement(Instance name)

These are Windows services that you find in the Control Panel in the Services window. Both of them are present in the Control Panel.

Unless you start the engine the service will not be available as mentioned while describing the testing query.

Polybase_00

Click Start to start the engine.
I get a message like this.

Polybase_01


Try running the test for Polybase installation again. Looks like the query opens up a Save Results window and looking for .rpt files.

Polybase_02

Perhaps this a bug or perhaps by design.

Trying to start the Polybase engine comes back with the message:

Polybase_04

Updated the java version by downloading jdk-8u121-windows-x64-demos from Oracle site as correct Java SE version is one of the requirements.



You will find the answer from me when I find it.

Update your SQL Server Management Studio 2016 to work with SQL Server 2016

If you followed my recent post (http://hodentek.blogspot.com/2016/05/get-ssms-2016-and-install-to-work-with.html)you would have installed the SQL Server Management Studo 2016 Release Candidate which used the software released on April 2016.

SSMS2016_April2016

SQL Server 2016 Developer became available on June 1, and when the SSMS 2016 RC was launched, you would get a message that there is an update to SSMS 2016.

SSMSUpdate

Clicking on the Update button takes you to Microsoft web site to download the latest version whose details are shown below.

SSMS2016_June2016

Updating easy

Double click the downloaded   June 2016 version to display the

Click Install and takes a little while downloading packages and installing.

Click Close.

Here is the SQL Server 2016 Instance after making a connection to it:

SSMS_SQL2k16_00

Installing SQL Server 2016 Developer Edition

 Review the following before reading this post:

http://hodentekmsss.blogspot.com/2016/05/watch-out-sql-server-2016-general.html
http://hodentekmsss.blogspot.com/2016/05/download-sql-server-management-studio.html 
http://hodentekmsss.blogspot.com/2016/05/stretch-database-is-nice-feature-of-sql.html


Download the SQL Server 2012 Developer Edition from Microsoft Site.

Mount the .ISO file as described here.


Double click the executable file in the mounted drive.

Agree to updating computer information

SQL Server Installation center is displayed.

Install_00
Install_001

Click System Configuration checker.
Rules get checked as shown.

Install_002

Click OK

Click hardware and software requirements. You are taken to Microsoft site to review details.
Details here:
https://msdn.microsoft.com/en-us/library/ms143506(v=sql.130).aspx#top_principal
--------------
You can access a virtual machine on which SQL Server 2016 is already installed, if you have an Azure account for evaluation.

• Needs .NET Framework 4.6.1 and will be installed during SQL Server 2016 installation.

Hardware requirements as shown here:

Install_003

• You need 1GB except for Express which needs only 512MB. Recommended is 4GB

• x64 Processor, 1.4 GHZ recommended 2.0 GHZ or higher

• No x86 bit server

• No WOW support either

• Management tools OK on WOW

Two types of editions:

Principal Editions--Enterprise, Standard and Web
Breadth Editions--Developer and Express

For Windows 10, the type can be either Standard in Principal
or Developer and Express in Breadth editions

Click on Installation in SQL Server Installation Center to display Installation details

Install_004
Install_004_2

Click on the first option, New SQL Server stand-alone installation or add features to an existing installation.

The Product Key page is displayed. For developer edition no product key is required.

Install_005

Click Next to open the License Terms page as shown.

Install_006

Accept License terms and click Next.

Checks Global rules and takes you to product update page as shown
KB3164398---https://support.microsoft.com/en-us

Install_007

Click Next.

Install Setup Files page is displayed as shown.--this takes a while!

Install_008
Install_008_2

 

Install_008_3 

Click OK to Rules Check Result Message. Click Next

Features Selection page is displayed. Make selections

Install_009

After the 'Please Wait..' window the Instance Configuration window is displayed as shown.

Herein a named instance will be installed.

Installing a named instance called OHANA as shown

Install_010

Click Next.                         --after the 'Please Wait..' window


Polybase Configuration page is displayed as shown.

Install_011

Click Next. The Server Configuration page is displayed. The various service accounts needs to be set. In what follows, in this installation, the current computer user takes on all the accounts with his Windows password. This is just to keep it simple. In an enterprise, this way of handling must be avoided.

Install_012
Regarding initialization look here:
https://msdn.microsoft.com/library/ms175935(v=sql.130).aspx
---------------------------------
In the next couple of interactive windows the Service's Account Name will be changed to reflect the current user assuming them.

Click inside the Account Name window of the SQL Server Agent to bring up the drop-down menu.

Install_012_a

Click Browse...to display the Select User or Group window.

Install_012_b

Click Advanced... button.

Select User or Group (adv) page is displayed.

Install_012_c

Click Find Now button. The Search results pane gets populated with all relevant account information.

Install_012_d_1
Choose the Current User of computer by highlighting it and Click OK. The current user gets selected.

Install_012_e

Click OK. Current user account name is inserted for the SQL Server Agent's Account Name a shown.

Install_012_f

Do the same for the other services. Actually after doing for the SQL Server Agent you can copy the Account Name and paste in the other services. Enter password for each service, the same Windows password.

Install_013

Oops! there was a typo in the Reporting Services password field.

Install_013_1
--------------------------------------------------
Error in password for Reporting Services Corrected. Click Next.
Database Engine Configuration page is displayed.

Install_014

Click Add Current User on the Server Configuration tabbed pane as shown. The current user's user name gets added to the Specify SQL Server administrators'' pane

Install_014_a
Click Data Directories tab. In Data Directories accept default.

Install_014_b

Click TempDB tab. Note the file names and other details. Just accept the defaults.

Install_014_c

Click File Stream tab. Click all check boxes.

Install_014_d

Click Next.

Analysis Services Configuration mode is displayed
Default is Multidimensional and data mining mode. You can choose only a single server mode.
Accept default.

Install_015
Click Add Current User. Current User will be added as an administrator.

Click Data Directories tab.

Install_016

Make no changes and click Next.

Reporting Services Configuration page is displayed as shown.

Install_017

Accept default and click Next.

The Consent to install Microsoft R Open page is displayed.

Install_018                          --Please Wait window is displaeyd.
Click Next.
Feature configuration check is made and the Ready to Install page is displayed.

Install_019

Install_019_a

Install_019_b

Install_019_c

Install_019_d

Install_019_e

Install_019_f

Install_019_g
Click Install. The installation took slightly less than hour. This is the fastest ever for SQL Server in all these years.

The computer was not restarted but only the above message window clicked OK.

Install_020

That's all.

If you are just starting to learn Reporting Services, this is all that you may need:
https://hodentekmsss.blogspot.com/2017/05/installing-sql-server-2016-sp1-for.html