sql on virtual machine?

[ultimate99]

would it be best to install sql server 2012 on a virtual machine or on local for practice and training purposes?

[adrynalyne]

That is what I do, I install it to a VM.

[ultimate99]

why would you do that and what VM you use?

[adrynalyne]

why would you do that and what VM you use?

Well, mainly because:

 

I run OS X as my main OS, but also because SQL Server installs so much crap that I didn't want to clutter Windows with it.

 

So even when I was running Windows as my main OS, I used it in a VM. 

 

 

The VM OS is Windows Server 2012 R2. I used to use VMWare, but I've recently switched to Parallels.

[LambdaLambdaLambdaFn]

would it be best to install sql server 2012 on a virtual machine or on local for practice and training purposes?

 

I'd do it on a cloud server, personally:  AWS or Azure.

[Jarrichvdv]

I use Windows Azure to run all my databases. It's convenient.

[The_Decryptor Veteran]

I'd do it on a cloud server, personally:  AWS or Azure.

That's overkill for local practise, no need for a DB cluster if it's one guy using a console.

[Dav-id]

If you install into a VM you don't need to worry about what happens in your system, when you aren't using it you can file it away and turn it off. If you install it locally it is much more convenient (assuming you have it set to automatically start with windows). However what you could also do is install the server itself in a VM and install the management tools locally and happy connect when the VM is up.

 

If you go the VM route you should try and install on an OS that will as closely match your intended production environment as that will help reduce funkiness you might experience when you push it live - although to be fair once SQL Server is up running it is pretty damn reliable and easy to work with.

 

Personally I run multiple versions of SQL Server locally for development since it is more convenient for me and I don't have to worry about performance.

[Chicane-UK Veteran]

We run over a hundred different instances of SQL Server, in production, on VMware virtual machines. Even for big applications such as data warehousing with databases that are approaching a couple hundred gigabytes in size. 

 

SQL Server virtualises just fine.

[Kalint]

That's overkill for local practise, no need for a DB cluster if it's one guy using a console.

Without a question, absurd for practice.  If he is just talking about queries and what not the VM route is too much as well.

[Seahorsepip Veteran]

would it be best to install sql server 2012 on a virtual machine or on local for practice and training purposes?

Training? I would Install a localhost devserver which includes phpmyadmin and mysql for learning sql. This way creating databases in easy for those sql queries.

 

Also maybe a bit easier method would be installing microsoft access...     Sometimes things might be a little bit different from mysql etc but for simple sql training purposes it will be more then adequate.

[Roger H. Veteran]

Moved to Virtualization & Cloud Services

[fusi0n]

VM.. Vmware Workstation, or if you want to get crazy and have an extra box around.. You can install the free version of vSphere Hypervisor..

http://www.vmware.com/products/vsphere-hypervisor/

[PGHammer]

would it be best to install sql server 2012 on a virtual machine or on local for practice and training purposes?

Depressingly simple - simply install the OS that it needs in a VM, then install the software.  Not only are VMware and Oracle VirtualBox options, so is Hyper-V - in fact, that is one of the biggest uses FOR Hyper-V in testing-lab situations - it can be the basis for an entire virtual network - given enough CPU, you can literally simulate an entire soup-to-nuts AD-based domain on a single physical PC.  (Given the level of skill of the administrator, there are three Hyper-V-based hosting options for the VMs - Microsoft Hyper-V Server, Windows 8+, or Server 2012R2.  CPU required - Core i5 or better (any generation), with i7 preferred.  RAM required - 16 GB (32 GB or more preferred).  Local storage - 2 TB (4 TB or greater preferred).  (Those are the hosting server requirements - administrator-box requirements are, naturally, a lot less.

 

The CPU requirement - This is so the VMs themselves don't bog down.  SQL is compute-intensive (that is any version, and from anybody); it's why I refuse to suggest anything less than a quad-core for any PC hosting SQL.  Further, Microsoft SQL Server 2012 and later explicitly supports HTT (a feature of Core i7) - if you can afford it, apply it.

 

RAM requirements - Virtualization eats memory like M&Ms - therefore, fill a virtualization box up if possible.

[The_Decryptor Veteran]

Without a question, absurd for practice.  If he is just talking about queries and what not the VM route is too much as well.

It's like a sandbox on steroids, which depending on how it's setup might be worth it (I used to use difference images for VMs for classwork, could run 6 VMs side by side with less than 1GB of on-disk footprint)

[+John Teacake MVC]

SQL server works well on virtual machines. There is a 20% performance penalty but it is not visible unless you have millions of data.

 

Where did that figure come from? 

[shastasheen]

From Microsoft. http://blogs.technet.com/b/andrew/archive/2010/11/23/sql-server-performance-in-a-virtual-world.aspx

No virtual machine will ever work as fast as a physical so your question is a bit pointless.

Erm, that's not what the article says. 80% is an assumption and the author states 95% is more likely. In addition, the article was written in 2010, and is assuming 2008R2 as your hypervisor. Like everything else in life, performance of virtualized SQL depends on a slew of factors. For example, I run SCCM 2007 with a dedicated SQL VM. The SQL VM is running on an external SSD attached via USB 3.0. My customers dedicated SQL box is running on a DL580 rack server with 16 cores, 32GB of RAM, partitioned indexes, RAID 1+0, etc. I can run a simple query for all Windows updates on both servers. To emulate my customer, I have the same categories and classifications selected. On my laptop VM, the query completes in 6 seconds to return about 80000 rows. On the production server, it takes about 30 seconds for the same result set. Disk I/O is clearly the bottleneck in this scenario. For CPU intensive tasks, the server would beat my little VM for sure.

 

On a side note, if you want to make setting up a virtual lab easy, check out the hydration kit by Johan Arwidmark. His Deployment Research blog is fantastic.

[shastasheen]

Dude I work for a private cloud hosting company and we set up SQL on a daily basis. 8k rows is nothing for SQL. If you want the performance then you go for physical. Anyway this is a different conversation.

I said 80000, not 8K. The OP's original question was about the viability of virtualized SQL for training and practice. My point is that it's entirely feasible. There are several configuration items that can be tweaked to tease out more performance, but for a lab environment none of those are really necessary.

[bronislav84]

It is better to install SQl 2012 directly to the installed server together with SQL Managment studio to manage the server databases.

[+Kyle Subscriber¹]

For database intensive servers you need to ask yourself if it is production or development. 

 

If it's production, it is recommended you don't run it off of a VM unless the drive you are running it on is dedicated purely to that database. So if you have multiple users read/writing data at the same time, you don't need to worry about another server operation slowing down database read/writes. (this is of course for big applications that have a lot of database interaction).

 

If you are just learning/developing, then by all means use a VM. It's quick, easy, and you won't notice any real usability concerns.