SQLintersection session – Deploying SQL Server with Microsoft Azure Virtual Machine

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)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.