Skip to content

Latest commit

 

History

History
107 lines (94 loc) · 13.1 KB

SQL-Server-Azure-VM.md

File metadata and controls

107 lines (94 loc) · 13.1 KB

SQL Server Azure VM (IaaS)

Management

SQL Resource Provider and SQL IaaS Extension

  • Recommend you register any virtual machines which are running SQL Server workloads with the SQL Resource Provider and Install the SQL Server IaaS Extension.
  • Registering with the resource provider creates the SQL virtual machine resource within your subscription, which is a separate resource from the virtual machine resource.
  • Registering your SQL Server VM with the resource provider requires installing the SQL Server IaaS Extension which unlocks automated patching, automated backup, and monitoring and manageability capabilities. It also unlocks licensing and edition flexibility. Previously, these features were available only to SQL Server VM images from Azure Marketplace.
  • SQL IaaS extension has three management modes Lightweight (license type/edition management only), Full , No Agent (for SQL2008/SQL2008R2 on W2008).
  • Steps to install the SQL Resource Provider. Note: This may restart the SQL Server service.
  • Steps to install SQL Server IaaS Agent Extension in Full mode. Note: This may restart the SQL Server service.
  • Registering a VM with the SQL Resource provider automatically installs the SQL Server IaaS extension. But the reverse is not true. So, installing the SQL Server IaaS extension alone does not automatically register the VM with the SQL Resource Provider.
  • Bulk Register multiple SQL virtual machines in Azure with the SQL VM resource provider.
    • The Register-SqlVMs cmdlet can be used to register all virtual machines in a given list of subscriptions, resource groups, or a list of specific virtual machines. The cmdlet will register the virtual machines in lightweight management mode, and then generate both a report and a log file.

Backup

1. Manual

  • Custom backup such as SQL Agent/PowerShell backup scripts or 3rd party SQL Server backup solution.
  • VLDB Backup options:

2. SQL IaaS Extension’s Automated Backup

  • SQL IaaS Extension’s Automated Backup feature. This feature must be managed and monitored at the individual VM level.

3. Azure Backup

  • Allows you to manage multiple servers within one dashboard.
  • This short video gives a great overview of what this solution looks like
  • Zero-infrastructure backup: You do not have to manage backup servers or storage locations.
  • Scale: Protect many SQL VMs and thousands of databases.
  • Central management and monitoring: Centrally manage all of your backups, including other workloads that Azure Backup supports, from a single dashboard in Azure. Use the portal to recover databases to a specific point in time without having to manually restore multiple full, differential, and log backups.
  • Support for SQL Always On: Detect and protect a SQL Server Always On configuration and honor the backup Availability Group backup preference
  • Policy driven backup and retention: Create standard backup policies for regular backups. Establish retention policies to maintain backups for years.
  • 15-minute Recovery Point Objective (RPO): Configure SQL transaction log backups up to every 15 minutes.
  • Consolidated email alerts for failures: Configure consolidated email notifications for any failures.
  • Azure Backup Considerations
    • Once you specify the SQL Server VM that you want to protect and query for the databases in it, Azure Backup service will install a workload backup extension on the VM by the name AzureBackupWindowsWorkload extension.
    • To be able to discover databases on a VM, Azure Backup creates the account NT SERVICE\AzureWLBackupPluginSvc. This account is used for backup and restore and requires SQL sysadmin permissions
    • Recommended: Please review the supported operating systems and SQL Server versions as well as the feature considerations and limitations listed here: Support matrix for SQL Server Backup in Azure VMs
  • Back up behavior for Always On Availability Groups
  • Walkthrough: How to Configure Azure Backup for SQL Server databases in Azure VMs
  • Tutorial - Back up a SQL Server database in an Azure VM
  • How to Restore Azure Backup Protected Databases
  • Retention
    • Azure Backup has a limit of 9999 recovery points, also known as backup copies or snapshots, per protected instance.
    • Your backup policy configuration determines how quickly you consume the recovery points. For example, if you create a recovery point each day, then you can retain recovery points for 27 years before you run out. If you take a monthly recovery point, you can retain recovery points for 833 years before you run out.
  • Manage and monitor backed up SQL Server databases
  • PowerShell
  • If you are having trouble with Azure Backup detecting your SQL Instances, please review these articles which walk through VM permission and connectivity requirements:

Monitoring

1. Manual

  • Custom monitoring such as SQL Agent/PowerShell scripts and 3rd party monitoring tools

2. Guest VM Azure Metrics (Currently in Preview)

  • Azure Diagnostics extension is an agent in Azure Monitor that collects monitoring data from the guest operating system of Azure compute resources including virtual machines.
  • Collects guest metrics (typically time series/performance counter) into Azure Monitor Metrics.
  • Data which can be collected
  • It can send data to Azure Storage, Azure Monitor Metrics (Windows VM only) and Event Hubs.

3. Azure Monitor Logs (aka Azure Log Analytics)

4. Security Monitoring

Reference: Key SQL Server Performance Counters

  • I/O: IOPS. Disk Reads/sec, Disk Writes/sec
  • I/O: Throughput. Disk Read Bytes/Sec, Disk Write Bytes/Sec, Disk Bytes/Sec
  • I/O: Latency. Avg. Disk sec/Read, Avg. Disk sec/Write
  • CPU. Processor. % Processor Time
  • Memory.
    • Memory. Available Megabytes
    • SQL Server: Memory Manager. Total Server Memory (KB), SQLServer:Memory Manager\Target Server Memory (KB), SQL Server Buffer Manager: Page Life Expectancy
  • Waits. SQL Server: Wait Statistics. Lock waits, Log buffer waits, Log write waits, Memory grant queue waits, Network IO waits, Non-Page latch waits, Page IO latch waits, Page latch waits, Thread-safe memory objects waits, Transaction ownership waits, Wait for the worker, Workspace synchronization waits
  • Query/Usage Patterns.
    • SQLServer:Workload Group Stats. Max request memory grant (KB), Active requests, Blocked requests, Max request CPU time (ms), Query optimizations/sec
    • SQLServer:SQL Statistics. Batch Requests/sec, SQL Attention rate, SQL Compilations/sec, SQL Re-Compilations/sec
    • SQLServer:Access Methods. Full Scans/sec, Index Searches/sec, Page Splits/sec
    • SQLServer:Batch Resp Statistics. Batches >=050000ms & <100000ms , Batches >=100000ms
    • SQLServer:General Statistics. User Connections, Transactions
  • T-Log. SQLServer:Databases. Log Bytes Flushed/sec, Percent Log Used, Transactions/sec
  • Tempdb Activity:
    • SQLServer:Access Methods. Workfiles Created/sec, Worktables Created/sec
  • Always On Availability Groups:
    • SQL Server: Database Replica. Transaction Delay, Mirrored Write Transaction/sec, Log Send Queue
  • Errors.
    • SQLServer:SQL Errors. DB Offline Errors, Kill Connection Errors
    • SQLServer:General Statistics. Processes blocked
  • Monitor Resource Usage (System Monitor)
  • SQL Server Performance Object reference
  • Monitor SQL Server Resource usage
  • Monitor performance for Always On availability groups