RSS

Running a SQL Server Environment at Home Using Native Boot Virtual Hard-Drives (VHD)

17 Sep
Running a SQL Server Environment at Home Using Native Boot Virtual Hard-Drives (VHD)

I’ve run into a road block. As a Microsoft Certified Trainer, I have all the software I need to get hands on with SQL Server. I even have Virtual Hard Drives (VHDs) that I can download directly from Microsoft; there are even publicly available ones HERE. My problem is on the hardware end. Since I’m not a millionaire that can throw money around and buy an entirely new server to put Windows Server on with SQL Server, I had to find another way… and I did 🙂

First let me give you some more details about my problem.

I recently downloaded many of the Virtual Machines (VMs) available to me as a MCT so I can get some more hands-on experience with the course material I have to help me succeed as an instructor. Unfortunately, I don’t have a separate machine with Windows Server on it. There is no way to run Hyper-V (Microsoft’s Virtualization product) on Windows 7. There are many posts out there saying that you just need to download the Admin Pack for Windows 7, however the Hyper-V Management Tool that comes with it does not allow for creating VMs. If I’m wrong please help me out and post in the comments. Windows 8 will have Hyper-V with it by the way.

I have been using Virtual Box to create virtual machines to host my own Windows Server OS; it is free software for creating VMs. There are limitations for this however. Performance on my desktop takes a pretty big hit since I’m running a Virtual environment on top of my regular Operating System. I would also like to play around with Windows and SQL Clustering and would like to work with Hyper-V as well. I have not found a way to use Hyper-V through Virtual Box and I have read many posts talking about using VMWare. Switching to VMWare isn’t going to solve my performance problem and it will be very difficult to run multiple VMs on my host operating system. Plus, the only VMWare that is free is free on a trial basis unlike Virtual Box which is open source. Adding more RAM or buying a better CPU might not be an option for many people as is the case with me.

Another route to go is to just create a dual boot between Windows 7 and Windows Server. Not everyone is comfortable with re-partitioning their hard drive or playing around with the Windows Boot Log. There is a solution that might actually help the performance issues and allow you to dual booth into Windows Server without having to mess around with your Boot Log too much.

The solution is: Native Boot!

It is this neat feature with Windows 7 that allows you to use a VHD to boot up a completely different operating system. Before I forget, let me write this in red to indicate that it is very important. YOU CANNOT USE DYNAMIC DISKS FOR NATIVE BOOT WITH VHD. I spent hours trying to figure out what I was doing wrong, so hopefully this will save you a lot of time. Plus I didn’t read the pop-up that I got when making a RAID 1 array out of my hard drives.

Hold you horses, you need the VHD file first! There are a couple of ways to do it.

First method.
Second method: A lazier way is to download an already existing VHD HERE.
Third Method: A great way to do it if you can’t create a VHD using Hyper-V (because you have Windows 7 like I do!) then you can use Virtual Box to do it. It’s simple, just make sure you select VHD for the hard-drive file type when you are creating your hard drive. Once you install the OS using virtual box you can attach that VHD file and use it with native boot.

***Side note for MCTs and those with access to Microsoft’s courseware on VHDs: You can just download them and skip the steps for creating them 🙂

Make sure you run command prompt as Administrator. Do this by going to the start menu type in cmd, but don’t press enter press CRTL+SHIFT+ENTER. Press yes at the pop-up dialog. Another way to do this is by right clicking the icon and selecting “Run as Administrator.”

Commands that you need to run under command prompt are in this blue color.

Step 1 – Backup! Obviously.You can name the backup whatever you want and store it wherever you want.

bcdedit /Export c:\bcd_backup

Step 2 – basically makes a copy of your default profile which we will edit later. You can name the profile whatever you like, I chose to name mine Test Addition.

bcdedit /copy {default} /d “Test Addition”

Step 3 – take a note of the GUID that is created! It should look something crazy like this: {123f8871-1993-19e1-4e64-bgg8e7aff7b9}. You can copy it from the command prompt window by right clicking, selecting mark, selecting the text and then right clicking again.

Step 4 – edit the entry for the new record you created using the GUID and keep the curly braces { } around it. After the vhd= put the path that you have the VHD stored at. Make sure you have the proper drive letter between the square brackets [ ]. I used my local path in the example below.

bcdedit /set {YOUR GUID HERE} device vhd=[S:]\MCTVHD\Base10A-WS08R2-HV.vhd

then run

bcdedit /set {YOUR GUID HERE} osdevice vhd=[S:]\MCTVHD\Base10A-WS08R2-HV.vhd

As a side note, if you use directories with spaces in the name to store the VHD you need to put the path in quotes; see example below.

EXAMPLE: 

bcdedit /set {762f8074-1393-11e1-8e64-bdd8e7cdf7b9} osdevice vhd=[S:]”\MCT Classes\Microsoft Learning\623XB\Drives\623XB-MIA-DC\Virtual Hard Disks\Base10A-WS08R2-HV.vhd”

Step 5 – Verify that the new boot record is there and that the path is correct.

bcdedit

If you mess anything up, you can always run the following command to remove the record you added:

bcdedit /delete {Your GUID}

Now all you have to do is restart your computer and select your OS from the boot-up menu (it will have the name you gave it). Next step, install SQL Server on your new OS and have fun! Or, you can start creating some VMs using Hyper-V and put SQL Server on there.

—————————Special Instructions for MCTs looking to use existing VHDs—————————

If you are a MCT and have access to the course ware VHDs then you don’t need to create anything. Use the BASE images (for example Base10A-WS08R2-HV.vhd) as your boot-up device for native boot. That is what I did in my example above. Then make a second copy of the base VHD somewhere else. Load up your Windows 2008R2 OS. The next steps are easy, just follow the instructions for creating the VMs using Hyper-V; it is a pdf file named something like 6231B_hvs.pdf.

Once you have the OS loaded, you can copy the additional course ware VMs on to the local C drive of your new OS (which is actually your VHD file that you booted from). So essentially your native boot OS will be using the Base VHD and then you will use a copy of it to start up your lab. That way when you follow the instructions to setup the VMs you have all files where they need to be for things to work.

Please note, your Base VHD may have a different name. I’m using the SQL Server Course 6231 Base VHD as an example.

Advertisements
 

Tags: , , , , , , , ,

13 responses to “Running a SQL Server Environment at Home Using Native Boot Virtual Hard-Drives (VHD)

  1. Marc Jellinek

    September 17, 2012 at 9:37 AM

    Have you considered downloading Microsoft HyperV Server 2012? It’s free, hypervisor based virtualization. http://www.microsoft.com/en-us/server-cloud/hyper-v-server/

     
  2. Ayman El-Ghazali

    September 17, 2012 at 10:03 AM

    This work around saved me the trouble of having to install another OS. I get the VHDs for free from Microsoft so everything is ready to go and once I boot my PC I have the option to login to Windows 7 or my VHD. I could always scrap the VHD and put a new one easily without having to do much work. I could even have multiple VHDs setup for booting. Windows 8 would help, and so would Server 2012 but that isn’t going to meet my immediate needs right now. I will definitely be playing around with Server 2012 in the near future.

     
  3. learnersstreak

    September 23, 2012 at 9:05 PM

    How many Virtual machines are you running at a time & have you faced/solved any performance problems related to storage?

     
    • Ayman El-Ghazali

      September 24, 2012 at 7:10 AM

      When I load using Native Boot I’m running 3 VMs on top of that using Hyper-V. One is a domain controller, and the other two are SQL Server boxes. I only have 6GB of RAM, so I use about 4GB for the VMs and then reserve 2GB for the Host OS. They run quite smoothly but obviously I’m not running huge loads on them. For my own testing/training purposes they work fine.
      I have read that there are performance issues related to running off of Native Boot. However, I don’t think I have pushed my machine hard enough to see anything. All of my VMs and my VHD for native boot are on a 7200RPM 1TB SATA 2 drive; nothing too impressive but things run well.

      I hope that answers your question.

       
      • learnersstreak

        September 24, 2012 at 8:13 AM

        This is all a home setup configuration wise mine has a wider waist line, with 32 GB @ 1600 mhz RAM is never a problem where I’m feeling low steam is when it touches the HDD. I’m scared of investment VS RIP time for SSD’s so yes I’m stilll struck with HDD. At a time there could be DC, Exg+SS, N(1-4) kind of 7-8 machines running on my physical box.

        What I’v done to speed them up is use multiple differential disks (spindle 1) rooted to a single parent (spindle 2). Still at times I can see HDD queue going 40+ the mammoth is not a sloth neither a horse. Basic steps to improve NTFS performance have been done.

        From some where I’ve heard that Fat does better / faster reads than NTFS, any ideas on this ?
        And for performance wave offs have you tried hosting VM’s on REFS or exFAT ?

         
      • Ayman El-Ghazali

        September 24, 2012 at 8:41 AM

        Not sure which is faster regarding FAT or NTFS but I’m sure you’ll find many arguments for both sides all over the internet. I have not tried hosting VMs on REFS or exFAT. My reason for running Native Boot with my VMs is because I have VHDs that come with the course lab and content for Microsoft Classes I would like to teach. I need to explore the setup and practice the labs, etc, so I can be prepared when I teach my students. The VMs are already prepared but require Hyper-V to work because they use multiple files that build upon each other; it is strange how it works and I don’t fully understand it yet.

        You could try investing in a SATA-3 (6MB/s) Drive. They’re quite good and I’m sure you can get a good price on a 7200-10K RPM Drive. I had a SSD that failed on me earlier this year, so I understand your concern with purchasing one.

        I’m taking an educated guess on what I’m about to say and I hope that it helps you. When you create your VMs, allocate all the HD space you think you will use for the OS drive. That could help keep the VMs hard drive file in one location on your physical drive’s platters and reduce fragmentation. If you use and expanding or Dynamically allocated disk, when it expands it may cause fragmentation. This might improve your performance slightly.

         
  4. learnersstreak

    September 25, 2012 at 9:33 AM

    There lies the bitter end, differential disks are all dynamic.

     
    • Ayman El-Ghazali

      September 25, 2012 at 10:08 AM

      Could you elaborate more on this? I’m not sure I understand what you mean exactly.

       
  5. Pavel

    November 16, 2015 at 9:07 AM

    Do any one know where i can find re virtual environment the MIA-DC and MIA-SQL??

     
    • Pavel

      November 16, 2015 at 9:10 AM

      An update i’m looking for 20462c-MIA-DC and 20462C-MIA-SQL.
      thanks in advance

       
    • Ayman El-Ghazali

      November 16, 2015 at 10:01 AM

      Those are for MCTs I believe. Microsoft certified trainers

       
      • Pavel

        November 17, 2015 at 3:12 AM

        I want to create same lab at home for practicing, and the question is where i can find the images.
        Thanks

         
      • Ayman El-Ghazali

        November 17, 2015 at 6:30 AM

        Create your own images. Check out my link at the top for creating your own virtual lab.

         

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: