VM Configuration

This guide is is intended for knowledgeable IT staff only.

1. Provision an Azure VM

2. SQL Server Web Edition

3. Enable HTTP, HTTPS, and RDP Endpoints via networking

4. Add Web Server as a role

5. Install an SSL server certificate

6. See Certificates section at the bottom for configuration of SQL SSL Certificate

7. Create folders:

   • f:\config

   • f:\export

   • g:\backup\diff

   • g:\backup\full

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

9. Configure IIS for port 443

10. Point a virtual directory "export" at F:\Export

11. Create a default.html file and put it in c:\inetpub\wwwroot. The contents are:
    <meta http-equiv="refresh" content="0; url=https://shipperxl.com">

12. Edit the HOSTS file to name the server and point the server name and all DNS aliases to 127.0.0.1  (i.e. db02, db02.hurkin.com)

13. Install SQL Server Reporting Services (i.e. SSRS, separate download)

14. Set Reporting Services authentication in rsreportserver.config

• • <AuthenticationTypes>

  • <RSWindowsNegotiate/>

  • <RSWindowsNTLM/>

  • </AuthenticationTypes>

  • <RecycleTime>1440</RecycleTime>

15. Use the Reporting Services Configuration Manager to create a local database

16. Initialize SSRS endpoints for "/ReportServer" and "/Reports" (including SSL setup)

17. Using SSMS, connect to the Reporting Services instance, click Properties, and Advanced

    • Set SupportedHyperlinkSchemes to *

    • Set TileViewByDefault to False

18. Reboot

19. Check https://server.name.com/Reports from both local and remote locations

20. Create the following directories via the /Reports interface

    • ShipperXL/ShipperXL

    • Custom

    • Custom/{database} for all databases which have custom reports. These will auto-deploy when you deploy reports to it.

21. Install SQL Server Management Studio (i.e. SSMS, separate download)

22. Configure SQL Server

    • Setup Database Mail for support@hurkin.com using profile notifyops@hurkin.com. Port 587. SSL.

    • Create an Operator for notifyops@hurkin.com 

    • Configure SQL Server Agent, Alert System, Enable Mail Profile and set Fail-safe operator

    • Set Server Properties:

      • Remote Query Timeout = 0

      • Compress Backup (if available)

      • Advanced: Enable Contained Databases: True

      • Advanced: Boost SQL Server Priority: True

      • Advanced: Max Degree of Parallelism: <number of vCPUs>

      • Database Settings: Backup = G:\backup\

23. Add jobs for:

    • Archive (script). Yearly, first Saturday of every January

    • Backups (script). Every hour.

    • Maintenance (script). Every Saturday at 12:00 PM UTC

    • CreationQueue (script). Runs on DB03 only. Every 15 minutes.

24. Configure SSRS

    • Add ShipperXL/ShipperXL folders

    • Add custom/xxxxx folders

25. Install ShipperXL Desktop locally. 

26. Install Job Agent (will need an instance name and license key)

27. Run report deployment from Azure DevOps

Drive Configuration

F:\Config (installation bits, certificates, cmd files, etc.)
F:\Data    --> for SQL data files
F:\Export    --> IIS
G:\Log        --> for SQL log files
G:\Backup --> for all backup 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.