![]()
Professional Azure SQL Database Administration
![]()
Table of Contents
Professional Azure SQL Database Administration
Why Subscribe?
PacktPub.com
Contributors
About the Author
About the Reviewer
Packt is Searching for Authors Like You
Preface
What This Book Covers
What You Need For This Book
Hardware
Software
Who This Book is for
Conventions
Download the example code files
Installation and Setup
Sign up for a Free Azure Account
Install SQL Server Management Studio
Installing Microsoft PowerShell 5.1
Installing Microsoft Azure PowerShell
Installing Microsoft RML Utilities
Installing Visual Studio Community Edition
Get in touch
Reviews
1. Microsoft Azure SQL Database Primer
Azure SQL Database Architecture
Azure SQL Database Request Flow
Provisioning an Azure SQL Database
Connecting and Querying the SQL Database from the Azure Portal
Connecting and Querying the SQL Database from SQL Server Management Studio
Deleting Resources
Differences between Azure SQL Database and SQL Server
Backup and Restore
Recovery Model
SQL Server Agent
Change Data Capture
Auditing
Mirroring
Table Partitioning
Replication
Multi-Part Names
Unsupported Features
SQL Browser Service
File Stream
Common Language Runtime (SQL CLR)
Resource Governor
Global Temporary Tables
Log Shipping
SQL Trace and Profiler
Trace Flags
System Stored Procedures
USE Statement
Activity: Provisioning Azure SQL Server and SQL Database using PowerShell
Provisioning Azure SQL Database
Executing the PowerShell Script
Summary
2. Migrating a SQL Server Database to an Azure SQL Database
Finding the Migration Benefits
Finding the Blockers
Selecting a Service Model
Selecting a Service Tier
Selecting the Main and Disaster Recovery Region
Selecting a Migration Tool
Azure SQL Database Service Tiers
Basic Service Tier
Standard Service Tier
Premium Service Tier
Premium RS Service Tier
Database Transaction Units
Scaling up the Azure SQL Database Service Tier
Changing a Service Tier
Determining an Appropriate Service Tier
Azure SQL Database DTU Calculator
Finding an Initial Service Tier for the Database to be Migrated using the DTU Calculator
Determining Compatibility Issues
Data Migration Assistant
SQL Server Data Tools for Visual Studio
SQL Server Management Studio
SQLPackage.exe
SQL Azure Migration Wizard
Determining the Migration Method
Migrating the On-Premises SQL Server Database to Azure SQL Database
Activity: Using Data Migration Assistant
Activity: Performing Transactional Replication
Summary
3. Backing Up the Azure SQL Database
Automatic Backups
Backup Storage
Backup Retention Period
Configuring Long-Term Backup Retention for Azure SQL Database
Manual Backups
Backing up the Azure SQL Database using SSMS
DACPAC and BACPAC
Manual versus Automated Backups
Activity: Perform Back-ups
Summary
4. Restoring an Azure SQL Database
Restore Types
Point-In-Time Restore
Long-Term Database Restore
Restoring Deleted Databases
Geo-Restore Database
Importing a Database
Activity: Perform PITR
Activity: Perform Geo-Restore
Summary
5. Securing an Azure SQL Database
Access Control
Firewall Rules
Managing the Server-Level Firewall Rules using the Azure Portal
Managing the Server-Level Firewall Rules using Transact-SQL
Managing Database-Level Firewall Rules using Transact-SQL
Authentication
SQL Authentication
Azure Active Directory Authentication
Azure Active Directory
Active Directory – Password
Azure Directory – Integrated
Active Directory – Universal with MFA Support
Using Active Directory – Password to Authenticate to an Azure SQL Database
Azure SQL Database Authentication Structure
Azure SQL Database Authentication Considerations
Authorization
Server-Level Administrative Roles
Database Creators
Non-Administrative Users
Creating Contained Database Users for Azure AD Authentication
Groups and Roles
Row-Level Security
Dynamic Data Masking
Proactive Security
Auditing
Configure Auditing for Azure SQL Server
Threat Detection
Configure Threat Detection for Azure SQL Server
Activity: Implement Row-Level Security
Activity: Implement Dynamic Data Masking
Summary
6. Scaling Out Azure SQL Database
Vertical Scaling
Scale Up or Scale Down Service Tiers
Using T-SQL to Change the Service Tier
Vertical Partitioning
Horizontal Scaling
Shard Map Manager
Data Dependent Routing
Multi-Tenant Data Models
Single Tenant (database-per-tenant)
Shared Database – Sharded
Activity: Creating Alerts
Activity: Creating Shards
Activity: Split Data between Shard
Activity: Using Elastic Queries
Summary
7. Elastic Pools
Introducing Elastic Pools
When Should You Consider Elastic Pools?
Sizing an Elastic Pool ...
