SQL Server 2008 on Hyper-V Virtual Machine with Multiple instances

Question

Running SQL Server 2008 instance on a Virtual Machine (Hyper-V) on a Windows 2008 R2 OS. Currently has a few production DBs. The whole VM itself is on RAID 5 volume (so are the DB files etc).

We will be adding further development and production DBs onto this VM soon. The production DB's on average are 1.5Gig in size with the largest DB less than 20Gig.

My question is: would having multiple instances make much difference in my situation. i.e. Data separation (dev, prod) An instance fails the others still work (not sure if applicable in a shared disk situation)

Would multiple instances be worth the overhead? i.e. more services, memory used by services

Thanks.

PS. Our disk layout is on a single disk and I understand that is not the recommended but will leave as a separate issue as I want to nut down on the multiple instance question first.

• gbn: You have a single VM, on a single box, with a single disk?
• VDMT: The SQL server VM is sharing the box with other VMs - all the VMs are on a RAID 5 volume

Answer 1

There is an advantage to having separate SQL Server instances however do those advantages outweigh the cons? You'll have to look at how you would use those instances and decide for yourself.

Advantages

1. Allocate/limit resources that each instance has access to, specifically you can limit memory and CPU on the Dev instance so that the Live instance will not be 'overly' affected while you make mistakes in Dev...

2. Data separation: by having separate instances if you want to share data between Live and Dev you would need to explicitly set up a Linked Server, if they aren't separated you can just refer to the live db (assuming you've given the same user permission)

3. Ease of development: you can have db's named the same thing on difference instances, ie: dev can be a structural replica of Live (which seriously reduces the development complexities)

Disadvantages

1. Heavy resource usage: As others (and yourself) have pointed out, having two instances of the same services will consume more of the resources, which will reduce the amount of resources that can be used by SQL Server to actually do the work the DB is for....

Note: The benefits of a separate instance would probably be better if you instead created a separate VM for your Dev DB environment. This would have the added benefit that when/if you can get another physical server you can move your VM without having to change any connection settings...

• Andrew Bickerton: @VDMT Glad to help, prod & dev sharing the same server is not ideal, but if that's all you can afford so be it... Good luck :-)

Answer 2

For your hardware setup you should go with a single instance of SQL Server. Configure resource governer to identify dev and prod logins and set limits accordingly.

Answer 3

If I understand, you have a single VM running under Win 2008 Host OS on a single box?

If so, there is no advantage is having multiple SQL Server instances.

Data separation of dev vs prod doesn't matter because you'd only end up using resources for dev that should be on prod. And with a single server you have a single point of failures (no matter how you configure disks).

If you have do it on the cheap, one big instance. If you can buy more kit, get a proper physical production server.

• VDMT: When you say: "with a single server you have a single point of failure" are you referring to having a SAN or clustered setup instead? - we are limited to having 1 SQL VM
• Andrew Bickerton: -1 I strongly disagree that there is no advantage in having having multiple server instances, +0.25 as your points about server resources are 100% correct