0

Should all of my databases for various apps (Sharepoint, CRM, Commerce Server) be on one VM/server or should I separate them out?

I am trying to mimic the server environment and what this question hinges on is whether database tuning involves just the database or working on the server's settings to? I am developing a custom ASP.NET web app and one of the strategies I am following is to have a read and write database which are duplicates in content/schema but tuned differently for read/write ops (does this involve any server tuning?).

Bear in mind that I have staging environments, so an issue is if I spread out the servers on my workstation, I will have quite a few servers and Windows Server OSs installed, taking up a fair amount of space (on my 2tb HD).

SQL I/O will also be another issue (partly governed by my network card I believe).

Thanks

Tom H
  • 46,766
  • 14
  • 87
  • 128
GurdeepS
  • 65,107
  • 109
  • 251
  • 387

2 Answers2

2

Database optimization benefits the most from having the data and transaction logs on a separate, RAID 5 (or 10) arrays (one array for data, another array for logs) from the OS. Beyond that, the most RAM you can throw at it.

The only benefit to having it on a VM is backup & deployment in my experience with VMWare products.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
0

When multiple databases run on the same machine (or VM image), is better to consolidate them on a single instance. The only reason that prevents this is security, if for some application is more sensitive and requires a subset of administrators (sysadmin), then it must be separated on a different instance.

Having multiple instances of SQL Server on a single machine is suboptimal, for reasons I have detailed here: When can I host IIS and SQL Server on the same machine?.

As for the single VM vs. multiple VMs., things are a bit different. VMs offer some isolations between SQL instances, offer resource governance by controlling the available resources at VM level, and most importantly can be easily ported to new physical hardware by moving the VM image. Given that you're decided to run VM anyway, I think is more flexible to have separate VMs, although less performant.

With separate VMs you will waste some resources: OS kernel memory, disk space, I/O will be replicated by the OS inside each VM, so your physical resources will be stretched thinner. You will also have to duplicate some human effort (configuration, management). You would have to consider these factors when planning the capacity of your deployment.

But the fact that each VM can be ported to a new physical machine with simple copy operations, as it becomes more used and more resource intensive, is a huge advantage.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569