PolyBase - Introduction
In one of previous articles, we went through the definitions and artifacts about the Azure SQL Data Warehouse and PolyBase. PolyBase is a new feature available in SQL Server 2016 and later versions. It serves to process T-SQL query to relational and non-relational databases (NoSQL). Certainly, using PolyBase you can run queries on external tables and files in Hadoop or in Azure Blob Storage as well import or export data to/from Hadoop.
If needed, then you can visit the post to get a brief introduction about the Azure SQL Data Warehouse and PolyBase .
PolyBase – Installation Prerequisites
PolyBase is a new feature and included in the SQL Server 2016 installer, here we will go through the installation of SQL Server 2016 and enablement the PolyBase feature. In this series, the following pre-requisites needed to proceed further –
- 64-bit SQL Server 2016 or later version or can go with a trial version of SQL Server - https://www.microsoft.com/evalcenter/evaluate-sql-server-2016
- Microsoft .NET Framework 4.5.
- Oracle Java Runtime Environment 7. JR7 or later is required, because PolyBase uses Java to connect to Hadoop. You can download JRE on the Oracle JRE page - https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
- Minimum memory – 4 GB and Space – 2 GB, though recommended minimum of 16 GB RAM.
PolyBase – Installation Mode
Before proceeding to install PolyBase on the SQL Server instance, must be aware about the installation mode i.e. –
- Single node (Standalone)
- PolyBase scale-out group
Since after the installation of PolyBase either standalone or in a scale-out group, you cannot change the configuration later. Even to update any setting later, you have to uninstall and reinstall the feature again.
Sometime a single node SQL Server instance with PolyBase create some performance bottleneck during dealing with massive data sets in Hadoop or Azure Blob Storage. In that scenario, it would be better to opt the scale-out group option. The PolyBase Group feature allows you to create a cluster of SQL Server instances to process large data sets from external data sources, such as Hadoop or Azure Blob Storage, in a scale-out fashion for better query performance. You can now scale your SQL Server compute to meet the performance demands of your workload. For a PolyBase scale-out group, make sure that:
- All the machines are on the same domain.
- You use the same service account and password during PolyBase installation.
- Your SQL Server instances can communicate with one another over the network.
- The SQL Server instances are all the same version of SQL Server.
STEP – 1: Launch SQL Installation Wizard
If you are moving with Evaluation edition, then download the setup file using the said URL - https://www.microsoft.com/evalcenter/evaluate-sql-server-2016.
Once downloaded, try to execute the setup file under administrator account that will launch the installation type wizard. In fact, it is essential to select the PolyBase features, so preferably go with Custom mode, and go ahead by clicking the Custom mode.
Sooner it will ask the target location where installation related files would be downloaded.
But, If you are moving ahead with the SQL Server 2016 license version like, Standard or any other edition, then go to the folder and run the SQL Server setup.exe under administrator account -
Sooner, the SQL Server Installation Center wizard will be launched, containing all relevant steps and features –
STEP – 2: Installation Process
On the left side of the wizard, select second option Installation, then selects New SQL Server stand-alone installation or add features to an existing installation from the numerous options available.
A new wizard with a default Product Key option will be launched by clicking the above said option. Here you have opted to provide the product key or go with free edition, select the appropriate and proceed by clicking the Next button.
Next, you will get license terms and conditions (License Terms), select the acceptance check box and click the Next button to proceed further.
In case of Evaluation edition, there will be not any license terms and conditions.
STEP – 3: Feature Selection
By accepting the Microsoft software license terms and conditions onwards, the system will proceed further activities meanwhile like, Global Rules, Product Updates, Install Setup Files and Install Rules. Next, time to select the instance feature from the Feature Selection page.
Here must select the PolyBase Query Service for External Data along with other features –
By clicking the Next button, setup will execute the rules and validate all pre-requisites to make installation succeed. During installing the selected features, you can get the failure message about Oracle JRE 7, as I got it –
If Oracle Java Runtime Environment 7 or later is not installer earlier, the setup will ask to install the same because PolyBase works on top of Java to connecting the Hadoop.
In some scenario, the failure would be a bit different such as listed below in case of Evaluation edition –
STEP – 4: Installing the JRE
In the meantime the JRE 7 or later version is mandatory to fulfil the PolyBase setup, so move to Oracle official download page and download 64 bit compatible Java version –
Move inside of the downloaded folder and execute the jre-8u201-windows-x64.exe file to install Java Runtime Environment. It will launch the Java Setup wizard to proceed further.
By clicking the Install button the installation will be started and sooner you will get the completion acknowledgement.
NOTE: Versions 7 (starting from 7.51) and 8 are supported as well JRE and Server JRE both work. JRE9 and JRE10 are not supported.
STEP – 5: Re-run Feature Selection
Post installation of JRE requisite, re-run the SQL Server 2016 setup’s rules under the Feature Rules section. Here you can get all rule validated successfully, go ahead and click the Next button.
STEP – 6: Instance & PolyBase Configuration
Post Feature Rules validation the setup will launch the Instance Configuration wizard. Here you can go with either default instance, or some precise named instance.
After Instance configuration, the setup will launch the PolyBase Configuration wizard. Since this is SQL Server 2016 Standard edition, so we have only one available option as scale-out group.
However, if you are re-installing the SQL Server and using an Evaluation edition, then it will ask a fresh installation or will go ahead with adding features to an existing one.
In case of SQL Server 2016 Standard edition, need to specify a port range with at least six ports; SQL Server setup allocates the available ports from the range automatically. Proceed with default port range for the PolyBase services and click the Next button.
However, in case of Evaluation edition, you can go with Standalone mode since it is a demo-based activity in the context of PolyBase exposure.
STEP – 7: Server Configuration
Once you configured the Instance and PolyBase settings, time to provide the Service Account. Here again, as per SQL Server edition, you will have to provide account like a common domain account or a standard service account etc.
For example, the wizard will be appearing a bit different from the Standard and Evaluation editions, like -
STEP – 8: Remaining Configuration
Post Server Configuration, required to set up remaining configurations like –
Analysis Service Configuration – Here it is essential to select the Server Mode and assign at least one admin-privileged account.
Reporting Service Configuration – By default, you can get only one option unless until you select the reporting feature earlier from the Feature Selection section.
Consent to install Microsoft R Open – Since I had selected the Microsoft R feature during selection so will have to accept the downloading and installation of Microsoft R Open.
STEP – 9: Complete the Installation
Post acceptance of the Microsoft R Open, by clicking the Next button setup will be proceeding further and will be Ready to Install with all configured settings as well selected services.
It will start the installation, the time you click the Install button. Setup will take some time to complete the installation and you will get the Complete page with success acknowledgement.
Congratulations, PolyBase installed!! 😊
Enable the PolyBase
You just completed the successful installation of the PolyBase, which must be enabled to access the features. Next, connect the SQL Server (SSMS) and execute the following T-SQL command –
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;
GO
EXEC sp_configure 'hadoop connectivity', 4;
GO
RECONFIGURE;
GO
Here show advanced option in 1 is used to enable an advanced option and hadoop connectivity equal to 4, is used to connect to Hadoop in Windows Servers.
Post execution of the T-SQL statements, you can see the succeed message –
Confirm the PolyBase Installation
Now PolyBase is ready and you can query the file and tables in Azure Blob or Hadoop using PolyBase. However, it is always good to validate the PolyBase installation to avoid any surprises later.
Execute the following command, if PolyBase is installed, the return is 1 otherwise 0 will be returned.
SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled;
Apart from this, PolyBase installs three user databases as listed below –
- DWConfiguration
- DWDiagnostics, and
- DWQueue
The above said databases should exist as well required for the PolyBase use, henceforth does not delete or alter them.
In a summarization, here you walked through the SQL Server 2016 installation along with PolyBase features. Now in further next articles we will do some hands on activities with PolyBase like, leveraging PolyBase to load data into Azure SQL Data Warehouse from an Azure Blob Storage.
Keep visiting the blog!
No comments:
Post a Comment