SQL with Manoj

 
SQL Server 2016 was released as the Community Technology Preview (CTP) 2 just yesterday (27th May 2015). And in my [previous post] I discussed about it with the Registration and Direct Download link.

After downloading the bits I installed it on my machine and the setup process was very smooth with few changes, check the YouTube video or go through the post below:
 

1. After you execute the Setup.exe: you will be greeted with the similar Install window You just need to click on the Installation link on the left and then click on the first link shown below:

2. This will open up a new Setup window and takes you to the Setup Role page which this gives you 2 options, 1st Feature (custom) install and 2nd Default. I chose the first option to select only the features I want. Click Next.

3. Now on the Feature Selection page: you can select what exactly you want to work on. You can see here one more option PolyBase Query Service for External Data. Just select the features and click Next:

4. I got stuck at the the Feature Rule page: with this error Oracle JRE 7 Update 51 or higher is required, Failed. Just click on the Failed link and it will give you the URL from where you can download the JRE or Java Runtime Environment, download it from [here].

… the above Page also shows the limitation of Polybase that it will run only in one Instance per computer.

Re-run the rules and it will be Passed this time, click Next.

5. You will come to Instance Config page: If its is first SQL Server install on your PC then select as Default Instance, otherwise if you had installed SQL Server earlier you will have to choose a Named Instance and Click Next.

6. In the Database Engine Config page: you will see an extra option to set number of TempDB files. The label below mentions “The default value is 8 or the number of cores, whichever is lower. This value can be increased up to the number of cores”.

Update: Now with CTP 2.4 release the installer provides a separate tab for tempdb files configuration.

So, in my [C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2016\MSSQL\DATA\] folder I could see 8 data files, with 1 log file:

7. on Ready to Install page just click Install, and it will take some time to finish the installation.
 

What’s new in SSMS:

–> After finishing the installation I opened the SQL Server Management Studio 2016: and the object explorer looked like this:

You will see three Databases present by default:
1. DWConfiguration
2. DWDiagnostics
3. DWQueue

… some of these tables in these databases contains pdw prefix, these tables & databases are related to Polybase feature. I will blog about this feature in my coming posts as in when I get more information on this.
 

–> I created a new Database by name TestManDB, and I got this while expanding some Objects in Object Explorer:

– – On expanding Tables you can see 2 new table options:
1. System Tables
2. External Tables – for Polybase to query Non-Relational data (also relational)

– – There is a new category External Resources (for Polybase), on expanding you get:
1. Data Sources
2. File Formats

– – And as you expand Stored Procedures folder you can see a SP present by default with following name under pdw schema:
– pdw.instpdw, with one parameter @DatabaseName NVARCHAR(MAX).
 

–> There is one hidden feature in SSMS, i.e. Query Store, check this [blog post] on how to enable it fr a database.