book

Professional Azure SQL Database Administration

by Ahmad Osama

July 2018

Intermediate to advanced

398 pages

7h 43m

English

Packt Publishing

Read now

Unlock full access

Why Subscribe?
About the Author
What This Book Covers
HardwareSoftware
Conventions

Sign up for a Free Azure AccountInstall SQL Server Management StudioInstalling Microsoft PowerShell 5.1Installing Microsoft Azure PowerShellInstalling Microsoft RML UtilitiesInstalling Visual Studio Community EditionGet in touchReviews
Azure SQL Database ArchitectureAzure SQL Database Request FlowProvisioning an Azure SQL DatabaseConnecting and Querying the SQL Database from the Azure PortalConnecting and Querying the SQL Database from SQL Server Management StudioDeleting ResourcesDifferences between Azure SQL Database and SQL ServerBackup and RestoreRecovery ModelSQL Server AgentChange Data CaptureAuditingMirroringTable PartitioningReplicationMulti-Part NamesUnsupported FeaturesSQL Browser ServiceFile StreamCommon Language Runtime (SQL CLR)Resource GovernorGlobal Temporary TablesLog ShippingSQL Trace and ProfilerTrace FlagsSystem Stored ProceduresUSE StatementActivity: Provisioning Azure SQL Server and SQL Database using PowerShellProvisioning Azure SQL DatabaseExecuting the PowerShell Script
Finding the Migration Benefits
Basic Service TierStandard Service TierPremium Service TierPremium RS Service TierDatabase Transaction UnitsScaling up the Azure SQL Database Service TierChanging a Service Tier
Azure SQL Database DTU CalculatorFinding an Initial Service Tier for the Database to be Migrated using the DTU Calculator
Data Migration AssistantSQL Server Data Tools for Visual StudioSQL Server Management StudioSQLPackage.exeSQL Azure Migration Wizard
Migrating the On-Premises SQL Server Database to Azure SQL DatabaseActivity: Using Data Migration AssistantActivity: Performing Transactional Replication
Automatic BackupsBackup StorageBackup Retention PeriodConfiguring Long-Term Backup Retention for Azure SQL Database
Backing up the Azure SQL Database using SSMSDACPAC and BACPACManual versus Automated BackupsActivity: Perform Back-ups
Restore TypesPoint-In-Time RestoreLong-Term Database RestoreRestoring Deleted DatabasesGeo-Restore DatabaseImporting a DatabaseActivity: Perform PITRActivity: Perform Geo-Restore
Access ControlFirewall RulesManaging the Server-Level Firewall Rules using the Azure PortalManaging the Server-Level Firewall Rules using Transact-SQLManaging Database-Level Firewall Rules using Transact-SQLAuthenticationSQL AuthenticationAzure Active Directory AuthenticationAzure Active DirectoryActive Directory – PasswordAzure Directory – IntegratedActive Directory – Universal with MFA SupportUsing Active Directory – Password to Authenticate to an Azure SQL DatabaseAzure SQL Database Authentication StructureAzure SQL Database Authentication ConsiderationsAuthorizationServer-Level Administrative RolesDatabase CreatorsNon-Administrative UsersCreating Contained Database Users for Azure AD AuthenticationGroups and RolesRow-Level SecurityDynamic Data Masking
AuditingConfigure Auditing for Azure SQL ServerThreat DetectionConfigure Threat Detection for Azure SQL ServerActivity: Implement Row-Level SecurityActivity: Implement Dynamic Data Masking
Vertical ScalingScale Up or Scale Down Service TiersUsing T-SQL to Change the Service TierVertical Partitioning
Shard Map ManagerData Dependent RoutingMulti-Tenant Data ModelsSingle Tenant (database-per-tenant)Shared Database – ShardedActivity: Creating AlertsActivity: Creating ShardsActivity: Split Data between ShardActivity: Using Elastic Queries
Introducing Elastic PoolsWhen Should You Consider Elastic Pools?Sizing an Elastic PoolComparing the PricingSizing Best PracticesCreate an Elastic Pool and Add Toystore Store Shards to the Elastic Pool
Use CasesArchitectureElastic Job WorkflowCreate an Elastic Job using the Azure PortalActivity: Exploring Elastic PoolsExecuting the PowerShell Script
High Availability
Standard Geo-ReplicationDatabase FailoverActive Geo-ReplicationDatabase FailoverSynchronous ReplicationAuto-Failover GroupsAuto-Failover Group TermsConfiguring Active Geo-Replication and Performing a Manual FailoverConfiguring the Auto-Failover GroupActivity: Configure Active Geo-ReplicationExecuting the PowerShell ScriptActivity: Configure Auto-Failover GroupsExecuting the PowerShell Script
Monitoring an Azure SQL Database using the Azure PortalMonitoring Database MetricsAlert Rules, Database Size, and Diagnostic SettingsAlert RulesDatabase SizeDiagnostic SettingsQuery Performance InsightMonitor Queries using the Query Performance Insight BladeMonitoring an Azure SQL Database using DMVsMonitoring Database Metricssys.resource_statssys.dm_db_resource_statsMonitoring ConnectionsMonitoring Query Performancesys.dm_exec_query_statsMonitoring BlockingExtended EventsExamining Queries
Automatic TuningCreate IndexDrop IndexesForce Last Good PlanIn-Memory TechnologiesIn-Memory OLTPMemory-Optimized TablesNatively Compiled ProceduresColumnstore IndexesClustered Columnstore IndexNon-Clustered Columnstore IndexActivity: Explore the In-Memory OLTP Feature

Overview

Learn everything you need to manage Azure SQL Database with 'Professional Azure SQL Database Administration'. This book covers critical tasks such as migration, performance optimization, security, and disaster recovery. Perfect for those transitioning to the cloud, it equips you with skills to ensure your database runs smoothly and efficiently.

What this Book will help me do

Effectively migrate on-premise SQL Server databases to Azure.
Master backup, restore, and security operations with Azure SQL Database.
Optimize performance and scalability using monitoring and tuning techniques.
Implement high availability and disaster recovery strategies.
Simplify database management through automation and advanced techniques.

Author(s)

Ahmad Osama is a seasoned database admin and Azure expert with extensive experience in SQL Server and cloud database management. As a consultant and trainer, he has guided numerous organizations through cloud transitions. Ahmad's teaching philosophy blends practical insights with clear instruction.

Who is it for?

This book is intended for database administrators and developers looking to transition their skills to Azure SQL Database. If you have some experience with on-premise SQL Server and are familiar with PowerShell, you'll find this guide invaluable. Ideal for those wanting to develop, migrate, or manage Azure SQL solutions.