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 HTTPS and RDP Endpoints

4. Add Advanced Windows Firewall rules to allow inbound traffic for MSSQL (1433), HTTPS (443), and RDP (3389).

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. Edit the HOSTS file to name the server and point the server name and all DNS aliases to 127.0.0.1

9. Configure SQL Server

   • Start the SQL Server Agent

   • Setup Database Mail for support@hurkin.com

   • Remote Query Timeout = 0

   • Compress Backup (if available)

   • Database Default Locations:

     • DATA = F:\Data

     • LOG = F:\Log

     • BACKUP = F:\Data\Backup

     • Enable Contained Databases = True

     • Boost SQL Server Priority = True

     • Max Degree of Parellelism = 2 (number of virtual processors)

10. Set Reporting Services authentication in rsreportserver.config

• 1. <AuthenticationTypes>

  2. <RSWindowsNegotiate/>

  3. <RSWindowsNTLM/>

  4. </AuthenticationTypes>

  5. <RecycleTime>1440</RecycleTime>

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

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

13. Install ShipperXL Desktop locally. 

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

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

16. Add jobs for (TODO):

    • full backups (04:30)

    • transaction log backups (every 3 hours)

    • differential backups (every 8 hours)

    • (db03-only) database creation

17. Reboot

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

19. Add ShipperXL/ShipperXL folders

20. Add custom/xxxxx folders

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