Best VM Creation Solution

[questorfla]

I am thinking about creating a VM soluton for a small SQL Express server.  The system is small, runs a 3GB SQL-Express DB that accesses a 20GB data-pool.    A crop of recent issues with regard to access time and a few others have left me trying to find a way to watch every step to see where the problem is occurring.  Thinking it through, I thought of the possibility of using a VM as the primary engine to do the job to start with. 
I would need to be able to mirror the actual DB as often as possible to a physical disk for insurance against data loss but having never tried this on anything I thought i would see if anyone else had.
Our last email provider was using VM's to emulate each mail-server they handled but I have no idea what they were using to host everything on.

This is such a small DB on a system that does nothing else i just wondered if it would even be possible and if so what it would take to run it.  Current Host OS is Server 2008R2 X64 on a system with 16GB RAM Dual-core processor

[Shades]

You have several options for virtualization. If your needs are small and basic: VirtualBox/VMWare Player will do. You will need to re-install your server inside either of these solutions.
VMWare has software that could convert your current server into a virtual one, which can be used in VMWare only. If this software is able to convert your real server into a virtual one, make sure to test it first extensively before giving up the real server though.

Access times in a database can be affected by a lot of things. And what I have noticed with testing MS-SQL (2012) against Oracle (10g &11g & 12c) (each just using the most basic functionality) is that MS-SQL is slower. Sometimes significantly slower when you have created indexes that are filled by the DB software in a not so efficient way. To get that right, you need to have a deep insight in how MS-SQL does this and after you have fixed this yourself you'll need to be on the lookout for the DB not to mess it up again when it maintains these indexes. In this regard, Oracle is much more efficient and you see that back in improved access times. 

So, if your DB relies a lot on indexes, getting these filled and maintained properly requires you to spend time keeping them in shape. Reducing the amount of indexes might help, but that usually leads to database (re-)design. Good database design is harder than it looks. Finding the balance between storing data into the database and getting this data into your application as fast as possible can be tricky.

There is software included with the MS-SQL Server 2012 Express installer, that shows you where the time is spent when you make a query against the database. I must say that this software is more intuitive than the software that comes with Oracle databases.

You can install this software by downloading the 1GByte+ MS-SQL 2012 Express installer and follow the installation instructions. You'll need to mark first that you want to install the complete package and after a while you are presented with an option screen where you disable whatever functionality you don't need. I just mention this, because it might be confusing at first.

It could be that this functionality is already available in MS-SQL 2008 Express. The only experience I have is with the MS-SQL 2012 database.

Anyway, the overview provided by this functionality will give you a clear insight in how to improve access times in your database. If the sole purpose of this exercise is to improve access times, that would be the first place I would look, instead of spending time to virtualize your whole setup and hope that this will bring the improvements you seek.

[questorfla]

Sounds like a good plan to me Shades.  I wasn't looking forward to the excercise for sure.  Just tired of listeng to the gripes about slow access times. You are 100% correct on the DB design but I am stuck with it.  Very proprietayr and VERY profitable for the company that owns it.  Once you get in, you are practically "stuck"  trhe companies we have approached to covertto their product have all quoted more to convert the db to a standard foremat than the want for their software. 
They all say the one we have is so abnormalthere is no way to automate the process and it would take hand entry.  I am abotu ready to recommend that they do it anyway.  The company who owns what we have does almost nothing to it.  Nothing but collect license fees yearly for support which, when i can get it, want additional charges for doing anything.  Currently their backup is "broken" and after a week of getting the same .bak file with the same size and same date they acted like no big deal, he tried changing one thing, and then said  "wait another week to see  how it goes".
Not me. I installed my own backu program as their module is definitely worthless right now.  If we go two weeks without a backup and it was something I knew about , i would get the blame (and i should!  If I could do something about it and don't.. so I did!) .  This does not fix the original problem though and hey gavce me no insight into the WHY it wont run.  Windows error logs show a VSS error at the scheduled backup time every night now for the past 2 weeks.  Yet when i run a backup on the same DB with another software it works fine.  No errors.,
The DM was more of a wishful thinking thing.  As it is, until the existing problems are solved I sure cant run it on a VM either.

[Shades]

I would attempt to make a dump of the entire database. The software I mentioned before also allows you to do such a thing. Then I would create a new database, preferably on a different DB server and import this dump into it, again with the already mentioned software. A different DB on the same DB server is an option too.

With these actions you have checked that your data (including its structure) can be restored successfully. Don't underestimate the importance of this. Make the time for it, because you don't want to do this the moment the proverbial sh!t hits the fan. There is a bonus, you have now a second DB running and with that you can experiment to improve access times.

It may not be as easy as using 3rd party software to create database dumps, the software that accompanies the database is usually most reliable when generating and importing these dumps.

[f0dder]

questorfla: virtualization generally doesn't help you in determining bottlenecks or determine problems - it's more of a way to lower costs, or get high availability by sacrificing performance. It does sounds like it might be a good idea for you to explore, though, given your current isssues.

Stuff like "no way to automate the process and it would take hand entry", "additional charges for doing anything" and "the same .bak file with the same size and same date" makes me very suspicious about all companies involved. Converting from one database to another shouldn't require manual processing - of course if your data is junk, it might require massaging if you wanted to sanitize your entire system... but just moving from one DB backend to another? No.

Your first main concern will be getting the data from one database to another (if necessary) - if you can do that step, virtualization should be trivial in comparison. Performance troubleshooting tends to need a mix of watching database counters and application instrumentation.