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.
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.
Comments
Post a Comment