Azure SQL Virtual Machine Configuration

This guide is is intended for knowledgeable IT staff only.

  1. Provision an Azure VM - Windows 2012 R2
  2. SQL 2014 Web, Standard, or Enterprise
  3. Enable MSSQL, HTTPS, HTTP, and RDP Endpoints
  4. Add Advanced Windows Firewall rules to allow inbound traffic for MSSQL, HTTPS, HTTP, and RDP.
  5. Add Web Server as a role
  6. Install an SSL server certificate using IIS manager (will be used later for the report server)
  7. Configure IIS for port 80 and point a virtual directory at F:\Export
  8. Configure SQL Server
    • Remote Query Timeout = 0
    • Compress Backup (if available)
    • Database Default Locations:
      • DATA = F:\Data
      • LOG = F:\Log
      • BACKUP = F:\Data\Backup
  9. Set Reporting Services authentication in rsreportserver.config
    1. <AuthenticationTypes>
    2. <RSWindowsNegotiate/>
    3. <RSWindowsNTLM/>
    4. </AuthenticationTypes>
    5. <Add Key="CleanupCycleMinutes" Value="60"/>
    6. <Add Key="MaxActiveReqForOneUser" Value="100"/>
    7. <RecycleTime>1440</RecycleTime>
  10. Use the Reporting Services Configuration Manager to create a local database
  11. Initialize SSRS endpoints for "/ReportServer" and "/Reports" (including SSL setup)
  12. Set up SQL Maintenance Plan or Managed Backup for the server. See Drive Config below for some details. Include
  13. Set up SQL Maintenance Plan for Shrink Database, Reorganize Indexes, Cleanup Backup Folder, Cleanup Backup History, etc.
  14. Edit the HOSTS file to name the server and point the server name and all DNS aliases to 127.0.0.1
  15. For SSRS and local reports, disable loopback checking (https://support.microsoft.com/en-us/kb/896861)
  16. Install the barcode fonts
  17. Create a Task Scheduler task to run F:\Config\offsite_to_b.cmd every day at 00:00
  18. Install Event Manager (will need an instance name and license key)
  19. Reboot
  20. Check https://server/Reports from both local and remote locations
  21. Add ShipperXL/ShipperXL folders
  22. Add custom/xxxxx folders
  23. Deploy reports from code folder (build solution first)

DRIVE CONFIGURATION

F:\Config (installation bits, certificates, cmd files, etc.)

F:\Data --> for SQL data files

F:\Data\Backup\Full --> for full backups daily @ 04:30

F:\Data\Backup\Log --> for transaction log backups every 3 hours

F:\Data\Backup\Diff --> for differential backups every 8 hours

F:\Data\Backup\Offsite --> for full offsite weekly backups Saturdays @ 23:00

F:\Export --> IIS

F:\Log --> for SQL log files

[copy F:\Config\offsite_to_b.cmd from another server]

B:\ --> net use b: \\hurkinbackup.file.core.windows.net\backup /u:hurkinbackup {key}

S:\ --> net use b: \\hurkinbackup.file.core.windows.net\logs/u:hurkinbackup {key}