Host Websites in Windows Environment

I am moving my websites from an external host provider to an in-house setup. Here is how to host the websites in a Windows environment, using Internet Information Services (IIS), SQL Server, MySQL Server, and SSL configuration.

Instead of using the latest versions, which may have undiscovered defects, I prefer solid, proven versions that are still actively supported. Therefore, I am setting up the hosting environment with Windows 10 Long-Term Servicing Channel (LTSC), SQL Server 2019, and MySQL 8.4 Long-Term Support (LTS).

Install Windows 10 in a Virtual Machine

As mentioned in https://www.dhirubhai.net/pulse/complete-guide-how-set-up-nas-ubuntu-server-gordon-data-rbv4c/, I have a NAS running 24/7, which supports virtualization, such as Oracle VirtualBox. I decided to run the Windows environment in a virtual machine.

1, In Oracle VM VirtualBox Manager, create a new virtual machine for Windows 10 (64-bit) with 12 GB memory and a 256 GB virtual hard disk (VDI, dynamically allocated).

2, In Settings, go to System > Processor and select 2 CPU cores. Then go to Storage, choose the optical disk that is Empty, and select the Windows 10 installation ISO. Finally, go to Network and attach Adapter 1 to Bridged Adapter.

3, Start the virtual machine, install Windows 10, and activate it.

Turn on IIS-Related Features

1, In Windows 10, search for Turn Windows features on or off and click to open it.

2, Expand .NET Framework 4.8 Advanced Services > WCF Services and check the HTTP Activation box (this will automatically tick other related options, including ASP.NET).

3, Check the Internet Information Services (IIS) box to enable its default features.

4, Now, navigate to https://localhost in a browser. We should see the Internet Information Services welcome page.

Install SQL Server 2019 in Windows 10

1, Start the Windows 10 virtual machine. In Oracle VM VirtualBox Manager, go to Devices > Optical Drives, and select the SQL Server installation ISO. In Windows 10, double-click setup.exe to begin the installation. During setup:

- Check only Database Engine Services under Instance Features.

- Change SQL Server Agent to Automatic startup.

- Select Mixed mode authentication.

2, (Optional) Configure firewall to make SQL Server accessible outside of the Windows 10.

2.1, In SQL Server Configuration Manager, go to SQL Server Network Configuration > Protocols for MSSQLSERVER, double-click TCP/IP, and set Enabled to Yes.

2.2, In Windows Defender Firewall with Advanced Security, go to Inbound Rules, create a New Rule for Port 1433, and allow the connection for the Public network. Then, double-click the rule, go to Scope, and restrict accessibility to certain IP address ranges (e.g., 192.168.1.0/24).

Migrate Data from External Host Provider SQL Server to In-House

1, Export Database Dump

Use a database tool (e.g., SQL Server Management Studio) or Plesk web interface to export a dump of our SQL Server databases from the external host provider.

2, Restore the Dump

On the in-house SQL Server, restore the dump using a database tool like SSMS.

Alternatively: If migrating data from a higher version of SQL Server, we may need to manually create databases and users in the in-house SQL Server and use the Import/Export Wizard to migrate the data.

CREATE DATABASE gordondata;

USE gordondata;

CREATE LOGIN reader WITH PASSWORD = '<password>';
CREATE USER reader FOR LOGIN reader;
EXEC sp_addrolemember N'db_datareader', N'reader';        

Install MySQL 8.4 in Windows 10

Instead of an installer, which might change system files, environment variables, or registries, I prefer a portable version that can, be extracted and, run directly without affecting the operating system.

1, Download the ZIP Archive of MySQL 8.4 and extract the contents to a preferred directory (e.g., C:\mysql).

2, Create a my.ini file in the root directory C:\ with necessary configurations. Initialize the MySQL service by running the bin\mysqld --initialize command. Note down the temporary password generated for root@localhost.

3, Open Task Scheduler and create a Basic Task. Trigger: set to "When the computer starts", Action: choose "Start a program" and select the bin\mysqld. Complete the task creation and hit Run to start the MySQL server.

4, Open Command Prompt, run mysql -u root -p to log in with the temporary password, and change the root password and create new users with appropriate permissions.

5, (Optional) Configure firewall to make MySQL Server accessible outside of the Windows 10: In Windows Defender Firewall with Advanced Security, go to Inbound Rules, create a New Rule for Port 3306, and allow the connection for the Public network. Then, double-click the rule, go to Scope, and restrict accessibility to certain IP address ranges (e.g., 192.168.1.0/24).

notepad C:\my.ini
  [mysqld]
  basedir=<path_to_mysql-8.4.2-winx64>
  datadir=<path_to_mysql-8.4.2-winx64/_data>

cd <path_to_mysql-8.4.2-winx64>
bin\mysqld --defaults-file=c:\my.ini --console --initialize

bin\mysqld

bin\mysql -u root -p
  ALTER USER `root`@`localhost` IDENTIFIED BY '<password>',`root`@`localhost` PASSWORD EXPIRE NEVER;
  CREATE USER 'gordon'@'%' IDENTIFIED BY '<password>'; GRANT ALL PRIVILEGES ON *.* TO 'gordon'@'%' WITH GRANT OPTION;
  CREATE USER 'reader'@'%' IDENTIFIED BY '<password>'; GRANT SELECT ON classicmodels.* TO 'reader'@'%';        

Migrate Data from External Host Provider MySQL Server to In-House

1, Export Database Dump

Use a database tool (e.g., MySQL Workbench), Plesk or cPanel web interface to export a dump of our MySQL databases from the external host provider.

2, Restore the Dump

On the in-house MySQL Server, run the dump as an SQL script using a database tool.

Set Up SSL Certificate

1, Download, install, and then launch Certify The Web.

2, Click on New Certificate, Add domains (e.g., gordondata.com, *.gordondata.com, gordondata.net, *.gordondata.net) to the certificate, and configure Domain Authorization (e.g., Challenge Type: dns-01, DNS Update Method: Cloudflare DNS API with API token for Credentials, choose DNS Zone Id, enter CNAME Delegation Rule: *.gordondata.com:*.gordondata.net).

3, Sign in to our modem/router, go to Advanced > NAT Forwarding > Virtual Servers, and add entries to allow external access to the Windows 10's port 80 (HTTP) and 443 (HTTPS).

4, Sign in to our DNS provider (e.g., cloudflare.com ), configure DNS record to map the hostname to our public IP address, which can found by visiting whatismyipaddress.com, and create a _acme-challenge CNAME pointing to, e.g., _acme-challenge.gordondata.net

5, In Certify The Web, click Request Certificate

6, Once completed successfully, open Manage computer certificates in Windows, the gordondata.com SSL certificate should be found in the Personal >Certificates folder.

Migrate Website from External Host Provider IIS Server to In-House

1, In the File Manager of the external host provider, archive our website files, including the error_docs folder, and download the archive to the Windows 10 computer.

2, Extract the archive to the Windows 10 directory C:\inetpub\wwwroot

3, Open Internet Information Services (IIS) Manager

3.1, Add Website, enter the Site name, select the Physical path (where our website files are stored), and configure the Binding (e.g., Type: https, Host name: gordondata.com, tick Require Server Name Indication, and select the SSL certificate). Click OK to start the website.

3.2, If necessary, select the website, then in the right pane, under Edit Site, click Binding to add more bindings (e.g., Type: https, Host name: gordondata.net, tick Require Server Name Indication, and select the SSL certificate).

3.3, Update the database connection strings in our website's configurations (e.g., web.config, or any other relevant places) to point from the external provider's database to the in-house database, for example:

<connectionStrings>
  <add name="WebDataTool" connectionString="Server=localhost;Database=gordondata;User Id=reader;Password=<password>;"/>
</connectionStrings>        

3.4, Navigate to our website ( e.g., gordondata.com ) to ensure it opens correctly. If errors occur, fix them as needed:

3.4.1, Error: Absolute physical path not allowed

Error: Absolute physical path "G:\PleskVhosts\gordondata.com\error_docs\unauthorized.html" is not allowed in system.webServer/httpErrors section in web.config file. Use relative path instead.

Fix: Open the web.config file and replace the path G:\PleskVhosts\gordondata.com\error_docs with the local path, e.g., C:\inetpub\wwwroot\GordonData\error_docs .

3.4.2, Error: HTTP Error 500.19 - Internal Server Error

Error: The requested page cannot be accessed because the related configuration data for the page is invalid.

Error Code?????????? 0x8007000d

Fix: Download and install the URL Rewriting Module for IIS.

3.4.3, Error: Invalid Win32 Application

Error: Request Error - ?The server encountered an error processing the request. The exception message is ' is not a valid Win32 application. (Exception from HRESULT: 0x800700C1)'.

Fix: In IIS, click Application Pools, select websites' app pool, click Advanced Settings and set Enable 32-Bit Applications to True.

3.4.4, Error: Access to the database file is not allowed

Error: The server encountered an error processing the request. The exception message is 'Access to the file is not allowed.'

Fix: Give IIS_IUSRS full access to APP_DATA folder.

3.5, Locate Notepad, run it as Administrator, and edit the file C:\Windows\System32\drivers\etc\hosts

127.0.0.1 GordonData.com
127.0.0.1 Chinese.GordonData.net        

3.6, Open Developer Tools in your browser, go to the Network tab, right click the Name column header, and tick Remote Address. Navigate to GordonData.com and ensure the remote address for all traffic is 127.0.0.1. This confirms the website is successfully and completely migrated locally.

3.7, In Notepad, edit C:\Windows\System32\drivers\etc\hosts again and comment out the added lines by adding # at the start of each line.

-- EOF --