How to Recycle a Virtual SQL Server 2005 Machine

How to Recycle a Virtual SQL Server 2005 Machine

One of my clients in Los Angeles has a project that uses VMware images extensively. The complicated server configurations take a long time to install. Using virtual servers gives us flexibility with many applications that are based on SQL Server 2005 (i.e., Microsoft Office SharePoint Server 2007, Dynamics CRM, Dynamics Great Plains—GP, and Dynamics Business Portal). The client's production environment has a mixture of physical servers and virtual servers. My organization deployed Active Directory (AD) domain controllers (DCs), file servers, and Exchange Server 2003 servers as physical servers, and mission-critical servers as virtual servers for production use.

A complicated configuration such as this one needs a development environment, and we have several on-site developers. One day I received a call from the developers, requesting a new virtual server to test a project release update. They needed a new virtual SQL Server 2005 machine that would also host Dynamics GP, and they needed it within 24 hours. The client's development environment is based on a virtual Small Business Server (SBS) Premium 2003 server, so adding a virtual member server to the AD forest wouldn't be a problem.

Because I work in San Diego, I'd typically hop in my car and make the drive—but as I said, I was pressed for time. Instead, I made a VPN connection from my office to the client site, searched the network, and found a copy of the virtual SQL Server 2005 machine used for Dynamics CRM in production. This discovery would save me time because I could use the Windows and SQL Server installations on the copy of the virtual machine (VM) and not need to obtain installation CD-ROMs or keys from someone onsite.

If I started up the copy of the production VM while the actual production VM was running, I would create a problem with a duplicate IP address on the network. So, before I started the virtual server, I changed the properties of the virtual NIC to host mode only instead of bridged mode.

I started the copy of the production virtual SQL Server machine and logged on with domain administrator rights. (This profile was cached, so I was able to log on.) Because I had to join the machine to another forest, I made sure I had an account with local administrator rights so I could control the server image when I removed it from the domain. Next, I changed the IP address and the DNS IP address. I used the free tool NewSID 4.0 (newsid.exe) to generate a random SID and change the name of the server. Then, I restarted the machine and launched SQL Server 2005. I noted that the server was set to SQL Security. Perfect! I had named the new server DEVGPSQL2005 and joined it to a workgroup. After that, I logged on to the SBS 2003 machine and defined the server account for the new server. I then powered off the virtual SQL Server machine that I was recycling and set the virtual NIC to bridged and restarted the VM. Now I had no conflict with the production virtual SQL Server machine.

Next, I joined the virtual SQL Server machine to the SBS 2003 domain. Yet I worried about how all these changes would affect the SQL Server machine. Typically, the default SQL Server instance assumes the name of the server. Only one SQL Server instance was installed, so I started SQL Server Management Studio (SSMS), opened a query window, and ran the following statement to see what the renamed virtual SQL Server machine thought its name was:

select * from sys.servers

Figure 1 shows the results of running this command. You can see from these results that the name of the instance didn't change with the server name change. The name of the default instance remained the same.

Figure 1: Results for determining the virtual SQL Server machine's name

Therefore a new instance name was necessary also. I used the SQL Server 2005 sp_dropserver and sp_addserver stored procedures as follows to fix this problem:

sp_dropserver computercs 
  sp_addserver DEVGPSQL2005, LOCAL

Then, I again ran

select * from sys.servers 

Figure 2 shows the new results from this command.

Figure 2: Results for determining the virtual SQL Server machine's name after changing the instance name

With the SQL Server "brain transplant" completed, I then installed Dynamics GP 9.0 and created a test company called TWO (The World Online). (Note: The TWO database appears when you install Dynamics GP on a SQL Server machine. It's displayed in the application as the company name "Fabrikam," which is used as a model for creating production company databases.) I also created another database so I could import a backup from the production Dynamics GP server. Next, I cleaned up some SQL Server logins and deleted domain accounts that existed in the old AD forest. Finally, the foundation was ready for installing two additional products in the now two-server development environment. The developers then installed Microsoft BizTalk Server 2006 and Microsoft eConnect in the test environment.

The total solution took only six hours. By using an existing virtual SQL Server machine and "recycling" it, I saved four hours in driving time alone. Although I'd hesitate to implement this solution in a production environment, it worked great in a pinch. I guess you could say the "Dynamics" SQL Server machine became just a little bit more "dynamic."
—Curt Spanburgh, Consultant/System Engineer

See Associated Figure

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.