Home Page Icon
Home Page
Table of Contents for
Table of Contents
Close
Table of Contents
by Ahmad Osama
Professional Azure SQL Database Administration
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
Comparing the Pricing
Sizing Best Practices
Create an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
Elastic Jobs
Use Cases
Architecture
Elastic Job Workflow
Create an Elastic Job using the Azure Portal
Activity: Exploring Elastic Pools
Executing the PowerShell Script
Summary
8. High Availability and Disaster Recovery
High Availability
Disaster Recovery
Standard Geo-Replication
Database Failover
Active Geo-Replication
Database Failover
Synchronous Replication
Auto-Failover Groups
Auto-Failover Group Terms
Configuring Active Geo-Replication and Performing a Manual Failover
Configuring the Auto-Failover Group
Activity: Configure Active Geo-Replication
Executing the PowerShell Script
Activity: Configure Auto-Failover Groups
Executing the PowerShell Script
Summary
9. Monitoring and Tuning Azure SQL Database
Monitoring an Azure SQL Database using the Azure Portal
Monitoring Database Metrics
Alert Rules, Database Size, and Diagnostic Settings
Alert Rules
Database Size
Diagnostic Settings
Query Performance Insight
Monitor Queries using the Query Performance Insight Blade
Monitoring an Azure SQL Database using DMVs
Monitoring Database Metrics
sys.resource_stats
sys.dm_db_resource_stats
Monitoring Connections
Monitoring Query Performance
sys.dm_exec_query_stats
Monitoring Blocking
Extended Events
Examining Queries
Tuning an Azure SQL Database
Automatic Tuning
Create Index
Drop Indexes
Force Last Good Plan
In-Memory Technologies
In-Memory OLTP
Memory-Optimized Tables
Natively Compiled Procedures
Columnstore Indexes
Clustered Columnstore Index
Non-Clustered Columnstore Index
Activity: Explore the In-Memory OLTP Feature
Summary
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
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
Comparing the Pricing
Sizing Best Practices
Create an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
Elastic Jobs
Use Cases
Architecture
Elastic Job Workflow
Create an Elastic Job using the Azure Portal
Activity: Exploring Elastic Pools
Executing the PowerShell Script
Summary
8. High Availability and Disaster Recovery
High Availability
Disaster Recovery
Standard Geo-Replication
Database Failover
Active Geo-Replication
Database Failover
Synchronous Replication
Auto-Failover Groups
Auto-Failover Group Terms
Configuring Active Geo-Replication and Performing a Manual Failover
Configuring the Auto-Failover Group
Activity: Configure Active Geo-Replication
Executing the PowerShell Script
Activity: Configure Auto-Failover Groups
Executing the PowerShell Script
Summary
9. Monitoring and Tuning Azure SQL Database
Monitoring an Azure SQL Database using the Azure Portal
Monitoring Database Metrics
Alert Rules, Database Size, and Diagnostic Settings
Alert Rules
Database Size
Diagnostic Settings
Query Performance Insight
Monitor Queries using the Query Performance Insight Blade
Monitoring an Azure SQL Database using DMVs
Monitoring Database Metrics
sys.resource_stats
sys.dm_db_resource_stats
Monitoring Connections
Monitoring Query Performance
sys.dm_exec_query_stats
Monitoring Blocking
Extended Events
Examining Queries
Tuning an Azure SQL Database
Automatic Tuning
Create Index
Drop Indexes
Force Last Good Plan
In-Memory Technologies
In-Memory OLTP
Memory-Optimized Tables
Natively Compiled Procedures
Columnstore Indexes
Clustered Columnstore Index
Non-Clustered Columnstore Index
Activity: Explore the In-Memory OLTP Feature
Summary
Index
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset