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.
Give it a valid IP address and point the DNS Server to the DC.
Rename the Guest OS and make it a member of the labo.local domain.
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.
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.
Create a new virtual hard disk of 40 GB. Store the virtual disk as a single file.
Rename the VMDK to something like SQL_Data.vmdk. This makes it easier to locate the correct vmdk if ever needed.
Get back into the VM and format the disk.
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.
Start the Setup of SQL Server 2008 R2. Select Installation and pick New installation or add features to an existing installation.
I will run SQL in Evaluation mode, but if you have valid licenses, feel free to add them here 🙂
Some Setup Support Files will be installed.
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.
Install the Database Engine Services and the Management Tools – Basic. Change the directories to you SQL Data drive (in my case E:)
Install a Default Instance but again, change the drive to the SQL Data drive.
Set the useraccounts for the services to LABO\SQLAdmin and change all services to Automatic.
Set the authentication to Windows and add the Current User to the SQL Administrators.
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
Boerlowie's Blog 
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
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).
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 ???
Did you create a Volume/Partition on it and assign a drive letter to it?
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
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 🙂
Hey Boerlowie, thanks for your reply, let me go ahead and buy a SSD.
Just curious but why use full blown SQL instead of SQL Express since vSphere is such a small database?
Thanks!
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…
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
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.
Great! I thought so, but I wanted to make sure since i was getting errors.
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
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
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.
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
How did you fix it?
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
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…
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
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.
I adding the second hard drive to the SQL server but this is not showing, am I missing a step?
It doesn’t show up in Disk Management? Can you try to do a Rescan in Disk Management (or reboot the VM)?
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!
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.
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!!!
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!
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?
That’s weird. You created a login for VCAdmin on the SQL machine?
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?
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!
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.
Doesn’t sound normal too me… Any updates on this?
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?
Kumar,
when do you get this error? During SQL Server setup?
Did you check the eventlogs of the SQL VM?
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
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?
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.
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).
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?
Hop into the SQL Configuration Manager and enable TCP/IP or check those settings…. They are probably disabled or incorrect.
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.
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!
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.
Did you put the SQLAdmin user in the local administrators group on the SQL Server? It sounds like a security/permissions issue.
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! 🙂
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.
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
That’s weird. Did you follow the guide? Can you send a screenshot at the point where you are stuck?
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
[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 !!!!
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
The problem is solved!, i had not added the local user. 🙂 🙂
Hello again, i need some help, how can i install vCloud Director using the same lab???
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!!
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
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/
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
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.
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?
Try disabling the firewall on both VMs to see if that makes a difference.
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.
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
Database engine install keeps failing.
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?
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.
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 ?
Hi Boerlowie,
Could you answer to my question ?