Skip to main content

Polybase : Polybase Scale-Out Group

In the last blog, we discussed the Introduction of the Polybase and the Implementation process of Polybase in SQL Server. PolyBase Scale-out Group consists of multiple virtual machines, each having its own SQL server instances which help in parallel processing and distribution of data. Data loading and query performance can increase in the direct proportion of the number of SQL server instances on each virtual machine.

Polybase scale-out group is a master-slave architecture. The scale-out group works together to supply the necessary computation resources. The Polybase scale-out group helps in data virtualization and scale-out reads with its readers and writers.

Polybase scale-out group is a master-slave architecture that contains one Head node and one or more compute nodes.

1) Master node:

The head node contains the SQL Server instance to which Polybase queries are submitted. Each PolyBase group can have only one head node. The master node distributes work to slave or compute nodes using data movement service on the compute nodes for execution.

2) Compute-Slave node:

A compute node contains the SQL Server instance that assists with scale-out query processing on external data. A PolyBase group can have multiple compute nodes.

The part of the query that refers to external tables is handed-off to the PolyBase engine. It parses the query on external data, generates the query plan and data movement service distributes work to compute nodes. The final result is brought back to the Head node.

Steps for Polybase Scale-out group implementation:

1) Install the same version of SQL Server 2016 onwards with PolyBase on N machines in the same Domain.

In our example, we are installing Polybase on SQLCONTROL, SQLCOMPUTE2 virtual machines. Please refer Polybase Installation blog which has stepwise instructions on how to install polybase.

 2) Configure scale-out group with SQLCONTROL as Master and SQLCOMPUTE2 as a slave:

Run the stored procedure sp_polybase_join_group in SQLCONTROL SQL Server in SQLCONTROL  virtual machine.

EXEC sp_polybase_join_group 'SQLCONTROL', 16450, 'MSSQLSERVER';

Then run the same stored procedure sp_polybase_join_group in SQLCOMPUTE2 SQL Server in SQLCOMPUTE2  virtual machine.

EXEC sp_polybase_join_group 'SQLCOMPUTE2', 16450, 'MSSQLSERVER';

Once it is successful then restart the Polybase engine and Polybase data movement service.

3) Run the below dynamic view to check if Polybase scale-out group is created.  

Select * from sys.dm_exec_compute_nodes; 

 

Fig 1: Polybase scale-out group

4)  Remove a compute node from using sp_polybase_leave_group.

If you want to remove the compute node from the polybase scale-out group then you can do it by using the sp_polybase_leave_group procedure.

Notes:

Each machine hosting SQL Server must in the same network domain.

Each SQL Server instance must be running the same version of SQL Server with a version greater than or equal to 2016.

We can only install PolyBase once per physical or virtual machine.

 

About Amlgo Labs : Amlgo Labs is an advanced data analytics and decision sciences company based out in Gurgaon and Bangalore, India. We help our clients in different areas of data solutions includes design/development of end to end solutions (Cloud, Big Data, UI/UX, Data Engineering, Advanced Analytics and Data Sciences) with a focus on improving businesses and providing insights to make intelligent data-driven decisions across verticals. We have another vertical of business that we call - Financial Regulatory Reporting for (MAS, APRA, HKMA, EBA, FED, RBI etc) all major regulators in the world and our team is specialized in commonly used regulatory tools across the globe (AxiomSL Controller View, OneSumX Development, Moody’s Risk, IBM Open Pages etc).We build innovative concepts and then solutions to give an extra edge to the business outcomes and help to visualize and execute effective decision strategies. We are among top 10 Data Analytics Start-ups in India, 2019 and 2020.

Please feel free to comment or share your views and thoughts. You can always reach out to us by sending an email at info@amlgolabs.com or filling a contact form at the end of the page.

 


Labels:

Comments

Post a Comment

More Popular Posts

Amlgo Blog - Experience The Experiments

Amlgo Labs Blog  is a step towards our vision to share knowledge and experiences, Amlgoites accept every challenge very enthusiastically. We do experiments, we fail but we learn and build complex solutions to help our clients solve their problems in Data, Analytics, Prediction, Forecasting, Reporting, Designing and Development area. During this process we enjoy immense learning everyday and we have decided to share our thoughts, learnings, experiments and experiences so that we don't work in silos and contribute the best of our knowledge towards community and learn more by views and reviews. This website is maintained and brough to you by  Amlgo Labs Professionals .   Our Strong Basics -  1)   KISS (Keep It Simple and Straightforward) :  We believe most of the problems can be solved by keeping things simple and straight. This is the learning we had in past, sometimes we try to solve technical problems using high end algorithms and complex codes but this results into complications.
Post a Comment
Read more

Polybase Blog - Introduction

Overview: This Polybase blog series is all about the use of Polybase Technology in today’s era to be able to take advantage of the Data(Relational and Non-Relational) by using T-SQL only. Data whether Big or not is the lifeline to many different sectors to cope up with Production, Maintenance, Predictions, Taking Precautionary Measures, Customer Satisfaction, Customer Retention, Sales, Revenue Generation and many more.
1 comment
Read more

Financial Regulatory Reporting

This blog is an introduction to the Regulatory Reporting. Regulatory reporting is mandatory activity banks have to perform with the coordination of Treasury, Group Finance, IT, and business lines. Regulators across the globe depend on accurate and timely submission of various Risk and non-risk reports by banks to measure the overall health of the banking sector.
1 comment
Read more

Polybase Installation for Scale-Out process

This part is the continuation of the previous blog about the introduction of  Polybase Scale-Out Group . As we have discussed in our earlier blog PolyBase enables your SQL Server instance to process Transact-SQL queries that read data from external data sources. SQL Server 2016 and higher can access external data in Hadoop and Azure Blob Storage. Starting in SQL Server 2019, PolyBase can be used to access external data in SQL Server, Oracle, Teradata, and MongoDB.
1 comment
Read more

Stages of New Regulatory Reporting Implementation

In the last blog, we discussed what is  Financial Regulatory Reporting  and its importance in the banking industry. Regulatory Reporting comprises the submission of business numbers as required by regulators to evaluate and track the financial and operational status of financial institutions and their compliance with required regulatory provisions.
2 comments
Read more