Optimizing Dedicated SQL Pools in Azure Synapse Analytics
The course is part of this learning path
This course explains how to optimize dedicated SQL pools in Azure Synapse Analytics using different data distribution methods, partitioning, and query optimization. It also gives a quick overview of the security and disaster recovery features of dedicated SQL pools.
- Know when to use round-robin, hash-distributed, and replicated distributions
- Know when to partition a table
- Optimize queries using statistics, caching, and log investigation
- Understand the security and disaster recovery features of dedicated SQL pools
- Anyone who would like to learn advanced features of dedicated SQL pools in Azure Synapse Analytics
- Some knowledge of Azure Synapse Analytics (take Introduction to Azure Synapse Analytics if you’re not familiar with this Microsoft service)
- Experience with SQL (not mandatory)
Now, let’s move on to security. Synapse Analytics provides a variety of data protection features for dedicated SQL pools, including Data Discovery & Classification, Dynamic Data Masking, Vulnerability Assessment, Advanced Threat Protection, and Transparent Data Encryption.
Data Discovery & Classification scans your database looking for sensitive data, such as names, addresses, and credit card numbers. It then gives you a list of recommendations for how these columns should be classified, such as “Confidential” or “Highly Confidential.” If you accept the recommendations, then those columns will be labeled with those classifications. You can also label them manually if you want different classifications than the recommended ones. Once these columns are labeled, you can use the database auditing feature to monitor access to this sensitive data.
Dynamic Data Masking will obscure some of the information in a particular column when it is retrieved in a query. For example, if you apply data masking to a credit card column, then queries will return a credit card number with exes over everything except the last four digits.
The Vulnerability Assessment service scans your database looking for potential security issues, such as loose permissions and dangerous firewall settings. Then you can go through the list of issues and decide whether or not they truly are issues that need to be addressed. When you drill down into issues that you want to address, there will be a recommendation as to how you can remediate the issue, and, in many cases, there will even be a remediation script you can run.
Advanced Threat Protection looks for unusual attempts to access or exploit databases. For example, it triggers email alerts when it sees potential SQL injection attacks or brute force login attempts. Then you can drill down into each alert to get more details about a potential attack.
Transparent Data Encryption is used to encrypt the entire database, including the log files and the backups. So if a hacker gets access to a copy of the database, they won’t be able to read any of the data in it.
Another useful data protection feature is row-level security. This lets you restrict each user’s or group’s access only to the rows they should be able to see in a table. For example, you might give each department, such as Sales, Engineering, and Finance, access only to the rows that are pertinent to their department.
Similarly, you can use column-level security to restrict access to specific columns. For example, you could configure an employee database so that only people in the Human Resources and Accounting departments would have access to a column containing Social Security numbers.
And that’s it for security.
Guy launched his first training website in 1995 and he's been helping people learn IT technologies ever since. He has been a sysadmin, instructor, sales engineer, IT manager, and entrepreneur. In his most recent venture, he founded and led a cloud-based training infrastructure company that provided virtual labs for some of the largest software vendors in the world. Guy’s passion is making complex technology easy to understand. His activities outside of work have included riding an elephant and skydiving (although not at the same time).