Sign in to follow this  
Followers 0
ultimate99

sql on virtual machine?

23 posts in this topic

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

why would you do that and what VM you use?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

1 person likes this

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Moved to Virtualization & Cloud Services

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.