Secure, optimize, and deploy database solutions

Microsoft via Microsoft Learn

Go to class Write review

Learn how to implement data security and compliance features in SQL Server, Azure SQL, and SQL databases in Microsoft Fabric, including encryption, masking, Row-Level Security, and auditing.
By the end of this module, you'll be able to:
- Design and implement data encryption using Always Encrypted and column-level encryption
- Configure Dynamic Data Masking to protect sensitive data
- Implement Row-Level Security to filter data access based on user context
- Design object-level permissions and implement secure, passwordless database access using Microsoft Entra ID
- Configure auditing to track database activity
- Secure model endpoints and API endpoints for modern applications
Optimize Azure SQL Database performance by choosing the right service tier and managing concurrency with isolation levels. Analyze queries with execution plans and DMVs. Use Query Store for plan management and resolve blocking and deadlocks.
By the end of this module, you're able to:
- Evaluate and recommend database configurations including service tiers, compute tiers, and resource limits.
- Choose transaction isolation levels and concurrency controls that balance consistency with throughput.
- Analyze query performance using execution plans and dynamic management views.
- Monitor and tune queries with Query Store and Query Performance Insight.
- Identify and resolve blocking and deadlocks using DMVs and Extended Events.
Implement CI/CD for SQL Database Projects with source control, branching, schema drift detection, automated pipelines, and testing strategies using GitHub Actions and Azure DevOps.
By the end of this module, you're able to:
- Create, build, and validate database models by using SQL Database Projects, including SDK-style.
- Configure source control for SQL Database Projects and manage reference data with predeployment and post-deployment scripts.
- Manage branching, pull requests, and conflict resolution for database code.
- Detect schema drift by using schema comparison tools and SqlPackage.
- Implement CI/CD pipelines with GitHub Actions and Azure DevOps, including secrets management and deployment controls.
- Design and implement a testing strategy with unit tests and integration tests.
Learn how to use Data API Builder to create REST and GraphQL APIs for SQL databases, deploy to Azure, configure monitoring, and implement event-driven patterns for change handling.
By the end of this module, you'll be able to:
- Create configuration files for Data API Builder with database connections and runtime settings
- Define entities for REST and GraphQL with field mappings, caching, and relationships
- Configure REST and GraphQL endpoints with custom paths and operations
- Expose database views, stored procedures, and GraphQL relationships
- Explore deployment options for Data API Builder, including Azure Container Apps, App Service, and Static Web Apps
- Set up Azure Monitor configurations with Application Insights and Log Analytics
- Handle database changes using Change Data Capture, Azure Functions, and Change Event Streaming

Syllabus

Implement data security and compliance with SQL
- Introduction
- Protect data with encryption
- Configure dynamic data masking
- Implement row-level security
- Manage permissions and secure access
- Implement auditing
- Configure secure access to AI services
- Secure data API endpoints
- Exercise: Implement security features
- Module assessment
- Summary
Optimize database performance
- Introduction
- Recommend database configurations
- Preserve data integrity with transaction isolation levels and concurrency controls
- Evaluate query performance with execution plans and DMVs
- Monitor and tune queries with Query Store and Query Performance Insight
- Identify and resolve blocking and deadlocks
- Exercise: Optimize query performance
- Knowledge check
- Summary
Implement CI/CD by using SQL Database Projects
- Introduction
- Create, build, and validate SQL Database Projects
- Configure source control and manage reference data
- Manage branching, pull requests, and conflict resolution
- Detect and resolve schema drift
- Implement CI/CD pipelines
- Design and implement a testing strategy
- Exercise: Implement CI/CD by using SQL Database Projects
- Knowledge check
- Summary
Integrate SQL solutions with Azure services
- Introduction
- Create configuration files for Data API Builder
- Define entities for REST and GraphQL
- Expose database objects, stored procedures, and views
- Explore deployment options for Data API Builder
- Recommend Azure Monitor configurations
- Handle changes with event-driven patterns
- Exercise - Configure Data API Builder for a product catalog
- Module assessment
- Summary