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