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

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. 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)

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

13. Set Reporting Services authentication in rsreportserver.config

• • <AuthenticationTypes>

  • <RSWindowsNegotiate/>

  • <RSWindowsNTLM/>

  • </AuthenticationTypes>

  • <RecycleTime>1440</RecycleTime>

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

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

16. Reboot

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

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

19. Configure SQL Server

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

    • Create an Operator for notifyops@hurkin.com 

    • 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\

20. 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.

21. Configure SSRS

    • Add ShipperXL/ShipperXL folders

    • Add custom/xxxxx folders

22. Install ShipperXL Desktop locally. 

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

24. 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.