You are here from BigDataCloud.in >> Beginners >> 7 - SQL Services
Zakaas Inc. impressed with Databricks and HDInsight (HIVE) offering by Azure. It also understood we can store relational data in Hadoop Ecosystem. Then what is need of SQL DB?
SQL services offer more security and performance than Databricks and HIVE databases. Some of the features:
1) Row and Column level security
2) Azure Active Directory (AAD) integration
3) Various ways to authenticate user -- AAD, Windows AD, SQL Login
4) Data Masking
5) Threat detection
6) Vulnerability detection
7) Query analysis and optimization suggestions
8) High Availability
9) Various connectors
and much more....
Azure offers various databases on cloud -- SQL Database (known as Azure SQL), SQL Data warehouse, MySQL, PostgreSQL, Cosmos DB, MariaDB.
We will focus on SQL DB and SQL Data warehouse in this article. Creating SQL DB and SQL DW are simple ever. Since that is one time activity and mostly handled by infra team, let us focus on DB & DW features and offerings.
When SQL DB or DW created, we get one virtual server and one physical DB. Remember DW is also a DB but with different architecture and features. Virtual server means we only have server address (IP or name). We can not connect actual server rather we connect to DB. You will SQL DB or SQL DW using any supported client like SSMS. Once DB is created, for consumer it is same like as your on-prem DB. Whatever you do on on-prem DB will 100% work on cloud DB. DW is also a DB but 100% features of SQL DB are not supported.
SQL DW (now Synapse Analytics): When DW is created, it creates database service with storage. All data is stored on cloud storage, unlike page files for SQL DB. Similar to Hadoop ecosystem, for DW also has boss and workers. Boss is called Control node and workers are called Compute node. When we establish connection to SQL DW, we actually connect to control node. When query is fired, control node transfers data from storage to compute node (distributed data processing) and once compute is done, collects the results and send to client. Here compute node usage a engine called MPP (Massively Parallel Processing) for the purpose.
SQL DW is built for large data. It performs very high when you insert/query big data (in GBs/TBs) much more than SQL DB. There are limitations present in SQL DW over DB. There is a concept called Polybase which is associated with SQL DW. When data is queries from external storage (Blob or ADLS) thru DW, the result dataset is available within seconds. Mechanism to read data from external storage and making available to user is called Polybase.
SQL DW is costly than DB. There are various ways to control compute power and cost of SQL DB & DW. We will see those in advanced section. See the below video to know SQL DW.
We will see distribution patterns, sharding, Polybase (detailed) and architectures in advanced section on BigDataCloud.in
Migration: There are various ways to migrate our existing data or database to cloud. Azure helps us by providing tools like SSMA (SQL Server Migration Assistant), DMS (Azure Database Migration Service), lift and shift etc. We will see more on those in advanced section.
Go Back to BigDataCloud.in
Zakaas Inc. impressed with Databricks and HDInsight (HIVE) offering by Azure. It also understood we can store relational data in Hadoop Ecosystem. Then what is need of SQL DB?
SQL services offer more security and performance than Databricks and HIVE databases. Some of the features:
1) Row and Column level security
2) Azure Active Directory (AAD) integration
3) Various ways to authenticate user -- AAD, Windows AD, SQL Login
4) Data Masking
5) Threat detection
6) Vulnerability detection
7) Query analysis and optimization suggestions
8) High Availability
9) Various connectors
and much more....
Azure offers various databases on cloud -- SQL Database (known as Azure SQL), SQL Data warehouse, MySQL, PostgreSQL, Cosmos DB, MariaDB.
We will focus on SQL DB and SQL Data warehouse in this article. Creating SQL DB and SQL DW are simple ever. Since that is one time activity and mostly handled by infra team, let us focus on DB & DW features and offerings.
When SQL DB or DW created, we get one virtual server and one physical DB. Remember DW is also a DB but with different architecture and features. Virtual server means we only have server address (IP or name). We can not connect actual server rather we connect to DB. You will SQL DB or SQL DW using any supported client like SSMS. Once DB is created, for consumer it is same like as your on-prem DB. Whatever you do on on-prem DB will 100% work on cloud DB. DW is also a DB but 100% features of SQL DB are not supported.
There are some flavors in SQL DB. We will visit those in advanced section on BigDataCloud.in
SQL DW (now Synapse Analytics): When DW is created, it creates database service with storage. All data is stored on cloud storage, unlike page files for SQL DB. Similar to Hadoop ecosystem, for DW also has boss and workers. Boss is called Control node and workers are called Compute node. When we establish connection to SQL DW, we actually connect to control node. When query is fired, control node transfers data from storage to compute node (distributed data processing) and once compute is done, collects the results and send to client. Here compute node usage a engine called MPP (Massively Parallel Processing) for the purpose.
SQL DW is built for large data. It performs very high when you insert/query big data (in GBs/TBs) much more than SQL DB. There are limitations present in SQL DW over DB. There is a concept called Polybase which is associated with SQL DW. When data is queries from external storage (Blob or ADLS) thru DW, the result dataset is available within seconds. Mechanism to read data from external storage and making available to user is called Polybase.
SQL DW is costly than DB. There are various ways to control compute power and cost of SQL DB & DW. We will see those in advanced section. See the below video to know SQL DW.
We will see distribution patterns, sharding, Polybase (detailed) and architectures in advanced section on BigDataCloud.in
Migration: There are various ways to migrate our existing data or database to cloud. Azure helps us by providing tools like SSMA (SQL Server Migration Assistant), DMS (Azure Database Migration Service), lift and shift etc. We will see more on those in advanced section.
Go Back to BigDataCloud.in