Home > Ultimate vSphere Lab > Building the Ultimate vSphere Lab – Part 7: SQL Server

Building the Ultimate vSphere Lab – Part 7: SQL Server

Create a new linked clone based on the Windows2008R2_Base template just like you did with the Domain Controller.  Name the machine SQL.

Again, store it on SSD storage.  Run through the OS Setup like you did with the Domain Controller.

Change the Network Adapter to VMnet2 to it can reach the DC.

If all goes well, you should get a DHCP address.

image

Give it a valid IP address and point the DNS Server to the DC.

image

Rename the Guest OS and make it a member of the labo.local domain.

image

Create a useraccount SQLAdmin in Active Directory (set the password to never expire).  Add this account to the Local Administrators group on the SQL VM.

image

Log on with this account to the SQL VM.

Before starting the installation of SQL Server, we’ll add an additional HD to the VM first.  We’ll throw all SQL binaries on that disk.

Right-click the SQL VM and select Settings.  Click the Add… button and pick Hard Disk.

image

Create a new virtual hard disk of 40 GBStore the virtual disk as a single file.

image

Rename the VMDK to something like SQL_Data.vmdk.  This makes it easier to locate the correct vmdk if ever needed.

image

Get back into the VM and format the disk.

image

In order to reach the SQL binaries (which are stored on the Windows 7 desktop VM), i use the Shared Folders feature of VMware Workstation.  This allows you to share a single folder between the hosts and the guest VMs.

image

image

Start the Setup of SQL Server 2008 R2.  Select Installation and pick New installation or add features to an existing installation.

image

I will run SQL in Evaluation mode, but if you have valid licenses, feel free to add them here 🙂

image

Some Setup Support Files will be installed.

image

You will get two warnings: one because you cannot access the Internet and a second because the Windows Firewall is enabled.

Select a SQL Server Feature Installation.

image

Install the Database Engine Services and the Management Tools – Basic.  Change the directories to you SQL Data drive (in my case E:)

image

Install a Default Instance but again, change the drive to the SQL Data drive.

image

Set the useraccounts for the services to LABO\SQLAdmin and change all services to Automatic.

image

Set the authentication to Windows and add the Current User to the SQL Administrators.

image

Finish setup.

That’s the basic setup for SQL Server.

 

Building the Ultimate vSphere Lab – Part 1: The Story

Building the Ultimate vSphere Lab – Part 2: The Hardware

Building the Ultimate vSphere Lab – Part 3: VMware Workstation 8

Building the Ultimate vSphere Lab – Part 4: Base Template

Building the Ultimate vSphere Lab – Part 5: Prepare the Template

Building the Ultimate vSphere Lab – Part 6: Domain Controller

Building the Ultimate vSphere Lab – Part 7: SQL Server

Building the Ultimate vSphere Lab – Part 8: vCenter

Building the Ultimate vSphere Lab – Part 9: ESXi

Building the Ultimate vSphere Lab – Part 10: Storage

Building the Ultimate vSphere Lab – Part 11: vMotion & Fault Tolerance

Building the Ultimate vSphere Lab – Part 12: Finalizing the Lab

Categories: Ultimate vSphere Lab
  1. abyakta
    January 9, 2012 at 17:03

    Hi,

    when i added a new disk to the SQL vm it is showing in the storage management as offline .
    the disk is not visible in my computer.i am not able to format it to use.
    any suggestion?

    Thanks
    Abyakta

    • January 10, 2012 at 10:08

      You’ll have to Right-click the disk in Disk Management and Bring it online first (you might have to Initialize it as well after that).

      • perfecto
        March 27, 2013 at 01:58

        When i Right-click the disk in Disk Management and Bring it online and Initialize it as well after that , the disk is not visible in my computer.
        Any suggestion ???

      • April 14, 2013 at 19:45

        Did you create a Volume/Partition on it and assign a drive letter to it?

  2. Raj
    January 11, 2012 at 09:35

    i am currently setting up this on my laptop with i5 processor and 4 GB DDR3 ram, i am very limited on creating and running VMs simultaneously, so can i install SQL server on my DC VM ?
    Below is my setup in workstation 7, let me know if i am going the right way.
    1st vm with DC + SQL
    2nd vm with Vcentre + Storage
    3rd Esxi

    • January 11, 2012 at 09:43

      Raj,

      4 GB of RAM will definitely limit you to run 1 ESXi host only. To reduce the footprint of your infrastructure VMs even further, you could install VC on the DC with SQL Express (check this post in VMware Communities for more details http://communities.vmware.com/message/1351040).

      But then again, i would prefer your option first (install DC + SQL on 1st VM and vCenter and Storage on 2nd VM). It it really is underperforming, try combining all the roles on the DC.

      I hope you have an SSD to put the VMs on because on regular HDDs you will need to be very patient 🙂

  3. Raj
    January 11, 2012 at 15:37

    Hey Boerlowie, thanks for your reply, let me go ahead and buy a SSD.

  4. Tom
    March 6, 2012 at 17:38

    Just curious but why use full blown SQL instead of SQL Express since vSphere is such a small database?

    Thanks!

    • March 7, 2012 at 08:46

      Both will work.

      A full blown SQL will give you some extra things like log shipping (which is a supported way of protecting your vCenter databases).

      Besides that, you don’t have SQL Agent so your maintenance jobs/backup jobs won’t run with SQL Express…

  5. Derek
    March 24, 2012 at 18:36

    On DC and SQL IPV4 IP address you have 10.0.0.1 and 10.0.0.2. Is this local to your computer or should i copy the same? If i need to use my IP from my local network should i used my host IP?

    Thanks

    Derek

    • March 26, 2012 at 08:42

      You can use the exact same IPs. They are local to VMware Workstation as it is a private network and can’t be reached from your desktop pc.

      • Derek
        March 26, 2012 at 12:27

        Great! I thought so, but I wanted to make sure since i was getting errors.

      • Greg
        October 13, 2012 at 02:53

        Im using 10.0.0.1 on DC 10.0.0.2 on SQL ad DHCP assigned 10.0.0.100 to vCenter but I cant ping sql from vcenter nor can i ping vcenter to ping

        I think this is why my sql data base ODBC cant be reached

  6. Derek
    March 24, 2012 at 21:42

    I did the step “Create a useraccount SQLAdmin in Active Directory (set the password to never expire). Add this account to the Local Administrators group on the SQL VM.”
    1. I created the SQLAdmin user in AD in the DC VM
    2. I went into the SQL VM as the local user
    3. Added SQLAdmin as an administrator -It disappears and says it is alread an admin
    4. The process I am using to add the machine as an administrator is “admin tools”, “computer management”, “local users and groups”, and “groups”.

    HELP!!

    Thanks

    Derel

    • March 26, 2012 at 08:43

      Did you perform a proper sysprep on the 2008 VM/Template before deploying the SQL?

      I have seen this behaviour before and this occured when a clone was taken of a 2008 VM without sysprepping it first.

  7. Derek
    March 26, 2012 at 12:35

    Yes that is exactly what is was. I found out last night after i googled the error “S-1-5-XX-XXXXX-XXXXX-XXXXX-1104: No mapping between account names and security IDs was done.” I received this at the last leg of the SQL install when it asked for the LABO/SQLAdmin and password info. It irritated the crap out of me but I learned something new. HOpefully I saved someone else a future headache.

    Thanks

    Derek

    • August 29, 2012 at 01:32

      How did you fix it?

  8. long_lanh
    March 31, 2012 at 04:49

    Boerlowie,

    some how I can ping sql server 10.0.0.2/24 the DC, but from DC I cannot ping the SQL, same thing the vc 10.0.0.2/24 cannot ping sql and sql cannot ping vc other can ping no problem use exactly in your doc. advice please thanks

    • April 2, 2012 at 09:15

      Some things you can try:
      – verify if subnetting is set up correctly. The DC and SQL should be in the same subnet. A gateway is not needed
      – verify if they are connected to the same VMNet in VMware Workstation
      – temporarily disable Windows Firewall on both VMs and see if that improves things.

      That should be it a guess…

  9. long_lanh
    March 31, 2012 at 05:01

    Boerlowie,
    I find another thing is when create the link clone machine from base template on sql and vc server i found that the restart and shutdown is grey out, is this something ….and this 2 machine cannot ping each other or any machine cannot ping to this machine but if going to this 2 machine then i can ping another machine. 10.0.0.x/24 advice thanks

    • April 2, 2012 at 09:16

      You did run the sysprep part correctly?

      Also, the Restart/Shutdown Guest will be greyed out if VMware Tools are not installed (or not yet started). So verify that inside the VMs, worst case just reinstall VMware Tools inside the Linked Clone.

  10. radeon
    April 1, 2012 at 02:07

    I adding the second hard drive to the SQL server but this is not showing, am I missing a step?

    • April 2, 2012 at 09:10

      It doesn’t show up in Disk Management? Can you try to do a Rescan in Disk Management (or reboot the VM)?

  11. Tim
    April 3, 2012 at 21:20

    Hi, When you specified to create your SQL_Data.vmdk, its not clear to me where you physically created it (most importantly whether to HDD or SSD), or maybe it isn’t as relevent here as for the VMs themselves?

    Further, when installing SQL Server, and you specify that you install both Database Engine Services, Management Tools and Default Instance to your SQL Data Drive, is this the path to the default C Drive of the SQL virtual instance, or to the “SQL_Data” drive we just created and formatted before installing SQL Server?

    Thanks!

    • April 4, 2012 at 08:28

      it depends on the space you have on your SSD. I ran short on free space so i created the SQL_Data.vmdk on my HDD. But if you have the space (takes about 5 GB), it’s off course faster to put it on SSD.

      And i installed SQL Server on the Data drive, mainly again because my SSD is quite full. So if you have the space on the SSD, you can easily install it on the C drive as well and put your SQL DB Files on the SQL_Data.vmdk.

  12. April 25, 2012 at 23:33

    Hi, this is a totally super step by step guide…. Thanks so much!!

    Not sure if my post should be here or on the part 8 post dealing with vCenter.

    I installed SQL successfully and moved onto part 8 to install vCenter. When you say to: “Now, hop on to the SQL VM and open SQL Management Studio.

    Click Security – Logins – New Login.”, I dont see the screen image you post. When I start the SQL management studio, its blank on the left and wants me to specify a server type, server name and authentication.

    I’m not sure what to input there and I look forward to your promt response so I can get my lab up and running…..

    Thanks again!!!

    • May 2, 2012 at 08:44

      As the servername, just fill in ‘localhost’ or ‘SQL’ (the name of your SQL VM). Leave the authentication to Windows Authentication and you should be able to connect!

  13. Nr
    June 3, 2012 at 14:49

    On the VC machine when trying to connect (during configuration of the native SQL client) I keep getting login failed for user VCAdmin (ERROR 18456, STATE 28000) The firewall is turned off on the SQL machine, the TCP/IP in the SQL Config is enabled etc…. just doesn’t make sense…. I followed your directions to the t! lol….. help?

    • June 4, 2012 at 10:00

      That’s weird. You created a login for VCAdmin on the SQL machine?

  14. Rajiv
    June 29, 2012 at 16:34

    Hello,

    1 – I still didn’t understand what is the point of installing another hard disk. For other vms, we didn’t install any HDD.

    2 – Whats the purpose of a SQL server in this lab? Is it just a requirement of VC? Or we need SQL server for logs, query or searches? I mean in the old days, we didn’t need SQL for exchange server I think so how did it query without SQL? May be Im wrong on that.

    I don’t know nothing about SQl server. Lets say SQL server goes bad in production, who will fix it then?

    • June 30, 2012 at 20:33

      vCenter always needs a database. Either DB2, Oracle or SQL. And most people pick SQL.
      You can let the setup install SQL Express on the vCenter, but that’s difficult to manage.

      In production, i always pick a dedicated SQL if possible. Gives you good performance (if your DB is slow, vCenter will be slow) and is easy to manage (backups, …).

      Exchange has it’s own database format (EDB) and doesn’t rely on SQL… vCenter does so you better have that part properly set up!

      • Raj
        July 1, 2012 at 04:37

        My ocz 120 gb sees only 75 gb free. Why is that? Does someone has that problem too? Please let me know. I will reasearch on this too to see if i find something.

      • July 16, 2012 at 13:49

        Doesn’t sound normal too me… Any updates on this?

  15. Kumar
    July 3, 2012 at 21:08

    Hello,

    I have space on C drive and I am installing/saving everything for SQL server and in the end, i get the error saying something like – Wait on database engine recovery handled failed. What could it be?

    • July 16, 2012 at 12:11

      Kumar,

      when do you get this error? During SQL Server setup?

      Did you check the eventlogs of the SQL VM?

  16. JasonW
    July 4, 2012 at 13:15

    First, thanks for the series.

    I am having issues with the SQLAdmin account and I see you have a couple of other posts with similar behavior. I too get the error about the account not mapping and I’m fairly certain I followed the sysprep steps properly. (..No mapping between account names and security IDs was done..)

    Also, I found a couple of entries in the system log of the SQL server stating “…SQL1 and LAB have the same machine security identifier (SID). NT should be re-installed on either SQL1 or LAB.” (Event ID 5516). SQL1 is the name I gave the SQL server and LAB is my domain’s name.

    Does this indicate a failure of some kind during the sysprep steps, or is there a way to verify the process before I spend time trying to install SQL?

    Thanks in advance,
    JasonW

    • July 16, 2012 at 12:10

      Look like a sysprep error indeed.

      Did the cloned VMs ran the mini-setup? So where it asks you a computer name and all those other details?

  17. Kumar
    July 4, 2012 at 15:56

    Please help me out here –

    Im trying to install this SQL server but Im getting the ‘setup100.exe error’. And sometimes I get ‘Database engine failed error’. I deleted my SQL VM, cloned it again from the original but no luck. I removed the sqladmin account from AD, and local groups and readded but no luck. What am I doing wrong here? I even reinstalled the .netframework. Please assist.

    • July 16, 2012 at 12:09

      That’s weird… never really came across those problems.

      Some suggestions:
      – Try to start SQL setup into another VM to see if the sources are valid.
      – Try to move the VM to other storage to ensure you don’t have errors on that side.
      – Check eventlogs, VMware Workstation logs (vmware.log).

  18. Greg
    October 12, 2012 at 13:06

    i think I may have screwed up something because I installed SQL Stan instead of SQL R2. Im now getting “could not reach sql server named pipes” any ideas?

    • October 12, 2012 at 18:21

      Hop into the SQL Configuration Manager and enable TCP/IP or check those settings…. They are probably disabled or incorrect.

      • Greg
        October 12, 2012 at 18:43

        Thanks pal! I did that already. I made sure they were enabled and in the correct order (tcpip then named pipes) also checked the properties of each.

  19. Tim
    October 23, 2012 at 22:51

    I am not able to run SQL install (setup.exe) when logged in as SQLServer user. I get the error (Windows cannot access the specific device, path, or file. You may not have the appropriate permissions to access the item). However, when I log an as the regular (local) Administrator, the SQL setup.exe runs fine. Any ideas are appreciated. Thanks!

    • Tim
      October 24, 2012 at 21:14

      Please disregard my previous post. I believe there are something wrong with the original base VM I created and cloned. Once I started from scratch, the SQL install went fine. Thank you.

    • October 25, 2012 at 07:19

      Did you put the SQLAdmin user in the local administrators group on the SQL Server? It sounds like a security/permissions issue.

  20. rahul
    October 25, 2012 at 06:39

    Hi dude, first of ur lab is superb. hands down.. now i’m ready to do part 10, storage part, but in part 8, while doing the DSN settings, the connection test failed, is that normal? When i chose use existing sql database, the vcenter name pulled up from drop down, but I can’t put in the password, so I used setup a new sql instance and was finally able to install vcenter. Now am i doing something wrong? i can send u a screen shot of what i’m talking about.. please help! 🙂

    • October 25, 2012 at 07:15

      If the connection test failed, it means your vCenter was unable to communicate with the SQL. Since you were able to see the databases on the SQL from the ODBC, it was probably a security issue (vCenter account need dbo on the vCenter DB).

      If you used a new SQL Instance, you installed SQL Express locally on the vCenter server (rendering your seperate SQL Server useless).

      So double-check the permissions on the DB and go through the setup again.

  21. Ramy Mahmoud
    October 28, 2012 at 20:57

    I install SQL server 2008 R2 but in the feature selection I did not find management Basics so after the installation complete the management studio I not appear . kindly advice urgently

    • October 31, 2012 at 07:55

      That’s weird. Did you follow the guide? Can you send a screenshot at the point where you are stuck?

  22. Jason
    November 16, 2012 at 16:07

    Great Lab! It looks like some people are having issues connecting/pinging the different VM’s within this lab. I also encountered this and managed to resolve the connectivity issues by doing the following:

    – Open up Virtual Network Editor, chose option to “Restore Defaults”.
    – Recreated VMnet2 as per the instructions within this lab.
    – Edited the VM NIC settings to ensure that they were correct on each VM.
    – Within the Windows OS of each VM, Network Properties I disabled/enabled each NIC adapter.
    – Now I’m able to ping/connect to each VM within the Lab

  23. Cristian
    November 21, 2012 at 00:52

    [Error Message]
    The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.

    [Details]
    Microsoft.SqlServer.Configuration.Agent.InputValidationException: The credentials you provided for the SQL Server Agent service are invalid. To continue, provide a valid account and password for the SQL Server Agent service.

    I´m following the lab step by step

    What can i do??? heeeelp !!!!

    • November 27, 2012 at 21:17

      Never seen that error before… Do you get this only with the SQL Server Agent service?

      For this lab, you could do it without the SQL Server Agent, but as a result, your Maintenence plans in SQL won’t get executed. vCenter will throw a warning after a while that the Rollup jobs haven’t run.

      Gr

      • Cristian
        November 28, 2012 at 16:47

        The problem is solved!, i had not added the local user. 🙂 🙂

  24. Cristian
    November 29, 2012 at 03:04

    Hello again, i need some help, how can i install vCloud Director using the same lab???

  25. Tominator
    December 23, 2012 at 07:15

    abyakta :
    Hi,
    when i added a new disk to the SQL vm it is showing in the storage management as offline .
    the disk is not visible in my computer.i am not able to format it to use.
    any suggestion?
    Thanks
    Abyakta

  26. Tominator
    December 23, 2012 at 07:17

    Hi everyone,
    I have the same problem as abyakta above, I can see the new disk in the storage but its unallocated. I cannot format it or do anything to it. Any one have any suggestion.
    Thanks
    Happy Holidays to everyone!!

  27. Trikam
    December 23, 2012 at 18:07

    If the newaly added SQL vdmk is not online.
    here is the solution
    http://www.happysysadm.com/2010/11/disk-is-offline-because-of-policy-set.html

  28. Minhaj
    December 27, 2012 at 08:13

    Hi, Everyone,

    I am also in prep of this lab very well written. I came across the same issue with Creating SQLadmin account and adding to the local administrator group.

    After reading the above comments I was sure that it was something related to sysprep causing the problem. After looking every step the major one most of them are missing before starting sysprep is making a check mark to GENERALIZE tab.
    If we miss that check box SID is not created properly causing all type of issues.

    Thanks again

    I came to know why that should be checked by going over this website.
    http://jameskovacs.com/2008/10/15/how-to-sysprep-windows-server-2008/

  29. Minhaj
    December 27, 2012 at 20:24

    Need help!! I made simple mistake and could not delete my SQL database and login account. Never worked on SQL before so please let me know how to delete them. Tried by stopping the sql services but did not help..
    Any steps will be appreciated.

    Thanks

  30. GKeane
    July 3, 2013 at 07:55

    Just to say thank you very much for putting this together, The quality of this guide is outstanding – very well laid out, logical, great level of detail, plenty of screenshots. For example, I thought i knew VMWare workstation to a decent level but your details on why and how to create a base image was very very helpful to me. Appreciate the time you have spent putting this together – keep up the good work.

  31. Rahul
    July 31, 2013 at 09:32

    weird, having the same issue with pining. did the above steps (restore defaults, disable/ enable, double checked the NIC settings), but still can’t ping sql from DC vm, but able to ping DC from sql vm. Any ideas?

    • July 31, 2013 at 13:41

      Try disabling the firewall on both VMs to see if that makes a difference.

  32. Rahul
    August 1, 2013 at 03:56

    funny that worked. but then i enabled firewall and i was able to ping it again.. i’ll keep it off for now.. thanks.. much appreciated.

  33. JCG
    September 22, 2013 at 02:00

    ive been trying to build out this lab, but i cannot get the SQL vm and SQL to work, i keep getting install errors and errors when i try to open SQL mgnt studio

  34. JCG
    September 23, 2013 at 02:48

    Database engine install keeps failing.

  35. November 6, 2013 at 05:06

    Hello.

    I am not able to see any Features to Select in the “Select Features” portion of the SQL 2008 R2 Setup.

    Does anyone possibly have an easy fix for this?

  36. Jason
    December 5, 2013 at 00:55

    Hi boerlowie,

    Thanks for helping me thus far. I am at Lab 7 now where i just added a new virtual disk of 40 GB and selected iSCSC as the disk type. After that, i renamed the VMDK to SQL_Data.vmdk and stored it on my C:\VM which is my SSD.
    But i was not able to see the new Disk online. So i used diskpart to make it online, formatted it and gave it a volume name and after that i was able to view it in disk management as NTFS, active, primary partition.(New Volume (Z:)
    All go so far.

    So after i created a Shared Folder in my SSD C:\Shared, i am stuck at my next step which is starting the setup of SQL Server 2008 R2. Where do i get this ?

    Need your advice.

  37. Jason
    December 6, 2013 at 00:45

    Hi boerlowie,

    When creating a new Hard Disk, the Hardware wizard asks me to specify where i would like to store the disk file ‘SQL_Data.vmdk’ to ?

    Do i put this virtual disk file ‘SQL_Data.vmdk’ in the SQL VM folder on my SSD ?

    Since I have a lot of space on my SSD, i assuming i can just store this virtual disk file onto the SQL VM on my Win 8 desktop ?

    As for the setting up of SQL Server which i downloaded from Microsoft, do i store the installation file on the Shared Folder and run the setup from there or can i run it from my traditional HDD ?

  38. Jason
    December 9, 2013 at 02:07

    Hi Boerlowie,
    Could you answer to my question ?

  1. June 8, 2012 at 16:43

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: