SQL Server 2016 Community Technology Preview 2 (CTP2)

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Oct 8, 2015

SQL Server 2016 Community Technology Preview 2 (CTP2)


With PolyBase, we don’t have to install/configure any other tool to access data from different platforms like Hadoop and Azure on a single platform. The major benefit is that we don’t have to rely on third party tool. 


Introduction:

SQL Server 2016 CTP 2 (Community Technology Preview) was recently released with lots of advanced changes with security, hybrid cloud scenarios and rich visualizations on mobile devices. 

There are two major changes in MS SQL 2016 installation, now we have two additional options for installing the SQL Server database instances:

  • PolyBase
  • TempDB

With PolyBase, we don’t have to install/configure any other tool to access data from different platforms like Hadoop and Azure on a single platform. The major benefit is that we don’t have to rely on third party tool. Also there is no manual configuration of tempdb needs to be done post SQL Server 2016 installation. This blog covers both and will help in understanding the changes in the SQL Server 2016 installation. We thought this may be helpful as many of the clients are upgrading to meet their business requirements. 

  • PolyBase: 

PolyBase allows you to access data stored in Hadoop or Azure Blob Storage using T-SQL statements. PolyBase is basically optimized for ETL (Extract, Transform & Load - data warehousing workloads) and planned for SSAS (SQL Server Analysis Services - analytical query scenarios). It allows you to query semi-structured data and join the results with relational data stored in the SQL Server.

1) Installation requirement:

  • PolyBase can be installed on only one SQL Server instance per machine
  • 64-bit SQL Server Evaluation edition
  • Microsoft .NET Framework 4.0.
  • Oracle Java SE RunTime Environment (JRE) version 7.51 or higher. The installer will fail if JRE is not present
  • Minimum memory: 4GB
  • Minimum hard disk space: 2GB

2. PolyBase Feature Selection during installation: 

On the Feature Selection page select PolyBase Query Service for External Data and on the Server Configuration Page, configure the Polybase services i.e. PolyBase Engine Service and PolyBase Data Movement Service to run under the same account.

polybase
3) Verify PolyBase services in services.msc:


polybase2

4) Same as Reporting Services, Polybase also installs three user databases DWConfiguration, DWDiagnostics, and DWQueue.




polybase3

  • TempDB

    SQL 2016 Installation Setup now offers the ability to set the number of TempDB files based on the number of cores during installation at the Database Engine Configuration page. As per best practices, TempDB data files will be 8 or the number of cores whichever is less.

    polybase4

    Conclusion:                      

    Polybase is very powerful and will be widely used in database environments where data source is in different platform like Azure and Hadoop. Polybase provides the ability to query data outside of SQL Server from within the SQL Server. It can integrate data from two completely different file systems and provide the option to store the data in either place. With PolyBase one has the ability to retrieve valuable information from Hadoop and Azure using SQL.

    Tempdb configuration changes in SQL Server 2016 provide capability to Microsoft DBA’s to create multiple TempDB files based on the number of cores during installation while reducing the time for the DBA post installation. However, note that even though this creates multiple files during installation, the file settings (initial size and auto growth) should be adjusted from the default values of tempdb properties.  

    For any queries on the topic click below: 


    Book an Appointment with Kevin

     

    References:

    https://msdn.microsoft.com/en-us/library/mt163689.aspx

    https://www.mssqltips.com/sqlservertip/3670/sql-server-2016-installation-changes/