VM Configuration

This guide is is intended for knowledgeable IT staff only.

1. Provision an Azure VM

2. SQL Server 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 443 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.

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 ShipperXL Desktop locally. This will automatically install fonts.

17. Create a Task Scheduler task to run F:\Config\offsite_to_b.cmd every day at 00:00

18. Install Job Agent (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. Run report deployment from Azure DevOps

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}

Certificate Renewal

1. Obtain updated SSL certificate.

2. Install via IIS Manager

3. Remove old certificate

4. Make sure root site shows updated cert (check remotely)

5. Use Report Server Config to update the certificate

6. Use the SQL Server Config Manager to update the cert (SQL Server Network Configuration \ Protocols for MSSQLSERVER \ Properties \ Certificate)

7. Use the Computer Certificates tool to set the security on the Private Keys to include "NT Service\MSSQLSERVER"

8. Restart SQL Server

9. Confirm everything is working AND shows the new cert.