Home / server / Questions / 263673
Accepted
growse
Asked: 2011-04-27 13:16:30 +0800 CST

MSSQL: separate dev and prod databases as separate instances on shared hardware?

  • 772

I've got a small virtualised environment which hosts a small internal website with database backend. I've currently separated my webservers and database servers on separate VMs running on the same hardware.

However, I'm wondering if a better strategy might be to consolidate the dev/prod database servers onto the same VM(s) but using separate instances? I'm using SQL mirroring for redundancy, so I'd theoretically go from 4 SQL VMs down to 2. Advantages would be fewer VMs to manage, less complex resource allocation and a guarantee that both dev and prod are on the same patch/service pack level.

What are the downsides to this approach? If I install a default MSSQL instance on a Windows server, can I just run the installer again to create a second (named) instance and use that for the other environment? Finally, would it be possible to put different instances behind separate IP addresses if I allocated both to the same VM?

virtualization sql-server consolidation

3 Answers

  1. You can certainly set up multiple MSSQL instances and bind them to separate IP's. However I'd recommend against it if it's for the purpose of splitting production and development environments.

    I prefer to keep dev and production on completely different physical servers / VM's so that no dev resources are consuming production resources. It's not uncommon to have runaway processes / code on a dev machine that consume everything and bring the box to its knees. This is obviously not good for production. Downtime for a development environment should not affect production.

    Obviously you still have a common denominator in your host machine so anything wrong there will still have the potential of taking down everything You inherently have shared resources at the VM level, but you at least won't have contention within a VM.

    There is also the [fairly typical] case where production OS security is completely different than development security and they are mutually exclusive. In this case you are back to different servers.

    Edit:
    I understand the low impact, that's something you'll have to weigh out for yourself. Keep in mind, though, that a poor performing query can also sap the life out of a server. If that's not an issue to your production environment then you'll be ok with multiple instances. You'll probably want to set max memory limits on your SQL instances, that'll help alleviate that.

    • 1
  2. Best Answer

    Having fewer VMs to manage is an excellent incentive to reduce server sprawl! Using instances of MSSQL to separate development from production environments is quite easy. It is generally just as easy as you've described it -- run the installer again to create a second (named) instance.

    You can then use the SQL Server Configuration Manager to bind the named instance to whichever IP address on the server you desire. It's under SQL Server Network Configuration >> Protocols for <INSTANCE NAME>.

    Downsides of doing this include the standard performance issues, although if these are low traffic you almost certainly won't run into any problems.

    • 1

  3. An even easier approach would be using two databases in the same instance of SQL Server; this is what I would do, unless I had compelling reasons to need two different instances.

    Anyway, yes, you can have two instances on the same (physical/virtual) server quite easily; you just have to run the setup again and specify you want to install a named instance.

    • 0