I attended the SQL intersection conference in November 2014 in Las Vegas, Nevada.  I am including my notes from the sessions in this blog, primarily for my own reference later.  For anyone that comes across these notes, please take them with a grain of salt – I may very easily have misheard something or have written it down incorrectly.

Session Title:  Deploying SQL Server with Microsoft Azure Virtual Machine
Instructor: Bob Ward

Models

• • Azure portal (pay-as-you-go)
  • Bring own VM (use own license)
  • Management Studio (pay-as-you-go)
  • Azure VM Templates (capture Azure VM) (pay-as-you-go)
• Gallery images
  • Optimized for OLTP or DW
• VM Sizing
  • A series
    • Don’t use basic tier except for testing
    • A2+ for Standard Edition
    • A3+ for Enterprise Edition
    • A4, A7-A9 optimized
  • D series (faster CPU, SSD)
    • D4, D13-14 optimized
  • Can be changed later
• Cloud services for things like Availability Groups, Availability Sets, load balancing
• See MS decision tree for virtual networks
• Must create TCP endpoint for MSSQL port (1433)
• Takes about 10 minutes to provision
• Can provision via SSMS (Tasks, Deploy to Azure VM)
• Be sure to connect via RDP right after provisioning
• Storage decisions
  • 500 IOPS/drive limit, but can span across drives
  • Use Windows Server 2012+ to create storage pools
    • One for data, one for log
    • Span across multiple disks
  • Be sure not to use system drive ever (including for system databases)
  • Do not enable Azure options for caching on storage drives (disabled by default)
  • D drive – do not use on A series
    • On D series, use D drive (SSD) for tempdb or BPE
  • Enabled instant file initialization
  • Enabled locked pages
  • Turn off geo-replication (doesn’t guarantee write ordering, can cause transactional inconsistencies)
  • AZCOPY – like robocopy for Azure
  • Apply fix for tempdb eager writes (do this both on-premise and in Azure) for SQL2012
  • Disable SSAS, SSIS, SSRS if not used
  • Check that SQL authentication is enabled
  • Windows firewall – open up 1433 or SqlServer
  • Consider ACLs on TCP endpoints
  • Optimized images
    • 12 TB storage pool for data
    • 2 TB storage pool for log
    • Startup –E
    • TF 1117, 1118
    • 8 tempdb files
    • New DB appears to be on c: drive, but is on a mount point
    • Instant file initialization
    • Locked pages enabled
  • Disk transfers/sec on perf monitor to measure IOs
  • Configuration of Availability Groups
    • 3 primary options
      • All in Azure, same datacenter
      • Secondaries in different datacenter
      • Primary on-premise, replica in Azure
    • Requires DC in Azure
    • Coming soon: Always On template (about 5 images)
    • Add Replica Wizard in SSMS (requires hotfix)
    • Don’t try to upload AG VMs into Azure – won’t work and will have many problems
  • No failover clustering options (yet)
  • Can back up on-premise to Azure storage (2012+)
  • Can store data and log files on Azure storage (2014+)
  • Performance considerations
    • Batch transactions when possible
    • Autogrow considerations for log
  • Do not use autoscale for Azure
  • Future
    • G Series coming soon (more cores, RAM, larger SSDs)
    • Premium storage
    • Azure file storage (shared file system)