Setting Up Virtual SQL Server Using Virtual Box and SQL Server

One of my clients relies on an intranet web-based application for their company’s daily operation. The web-based was coded in ASP sometime in 2011-2013. Essentially, the application has been used almost 10 years and would only run in older generation of server. The old server was HP Proliant which was manufactured around 2008-2010 (just my guess, I don’t remember the exact build). It ran on Microsoft Server 2003 and SQL Server 2000. When the old server could not be used anymore, the economical way to replace or upgrade the system is by hosting the web application in newer server system on virtual machine.

I have been using VirtualBox for years. So, naturally, it is the one I used to host the web application. The new system runs in Windows Server 2016. What I did to setup was, I installed Windows Server 2003 and Microsoft SQL Server 2000 in the newly created VirtualBox virtual machine (VM) which I now call the guest machine. All the web files and database were installed in the guest machine while the database backups need to be stored in the storage of the new system running Windows Server 2019 (I call it the host machine).

CONFIGURE NETWORK SETTING FOR HOST AND GUEST MACHINE

Next thing needed to be done was setting up the network communication between the host and guest server. For this, I chose ‘Bridged Adapter’ setting for the VM as it needed to be able to be accessed by other computers within the company’s network. What needed to be configured when setting up the network were:

For the guest machine (VM):

  1. Add protocol exceptions in Windows Firewall. As it was an intranet system with only 6-7 connections, I switched it off.
  2. Set a fixed IP with similar network subnet. If other system in the network is using 192.168.0.xxx, then, the guest machine’s adapter need to bet set a unique number, as example, 192.168.0.123
  3. Set a system name for this machine; I would call it DBSERVER

For the host machine:

  1. Add protocol exceptions in Windows Firewall, or, in my case, I switched it off.
  2. Set another unique fixed IP within the network subnet for the network adapter. For this post purpose, I would call it 192.168.0.211
  3. Also, set a system name. Let’s call it HMSERVER

To test if it works, we could use ping <ip> command, or access the web system on the guest machine from the host machine using the designated IP address (http://192.168.0.123 or something like that).

SAVE DATABASE BACKUP FILES IN DIFFERENT MACHINE ON NETWORK

Now, whenever SQL Server on guest machine performs database backup, the files need to be stored in the host machine storage. Straight forward way of doing this is by using Shared Folder setting in VirtualBox (I am going to write a different post on this as, I, too would fiddle a bit on how set this up at times due to lack of practice). However, the backup process would stop halfway and produce error due to different files formats (this, also depends on which virtual drive configuration that was initially set when creating the VM). Mine was created on dynamic storage, so this option did not work.

What I did was use network sharing protocol as is normally done to share folders from a computer to another within the same network. To do this:

On HMSERVER (the host server):

  1. From Control Panel, under Programs, go to Turn Windows Features On Or Off
  2. Enable SMB 1.0 feature on Windows Server 2019. This is necessary for the guest server, which runs on Windows Server 2003, to access the file sharing protocol on the network from host server. Apparently, newer server systems do not use the feature anymore. Tick all the options SMB 1.0/CIFS File Sharing Support, SMB 1.0/CIFS Client and SMB 1.0/CIFS Server.
  3. To be safe, I also turned On Network Load Balancing
  4. Configure the intended backup storage folder’s file sharing settings. Enable access to a specific user or Everyone or Administrator, depending on your security architecture.
  5. Check Windows Firewall as well to add exceptions on TCP/IP connection.

On DBSERVER (guest server):

  1. Find the shared folder from network. Map the network folder as a drive. For this instance, I set the shared folder as drive Y:.
  2. From SQL Server 2000, run the command, xp_cmdshell 'net use Y: \\192.168.0.211\SharedFolderName /user:HMSERVER\User "password"'
  3. Then only the drive Y: would be visible as drive directory in the SQL Server Enterprise Manager.
  4. Set backup destination as usual.

Just to add, if the changes did not happen after reconfiguration, please restart your system or VM so that the changes could take place. I hope this article helps in configuring your in-house information system.

CATEGORIES