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?
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 Managerto bind the named instance to whichever IP address on the server you desire. It's underSQL 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.