Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
I completely understand what you're saying - in an ideal world where the impact of production failing was higher than it is, I'd separate the hell out of them. However, this is a low-business-impact site, so I'm fairly comfortable with the trade-off of sharing hosts. I'm also more comforted by the fact these are db boxes, so dev code running on them is minimal (for this application). However, I'm not so comfortable with just having a single server with different databases for prod and dev (access rights etc.), hence the question about separate instances. –  growse Apr 26 '11 at 21:56
    
@growse See my edit –  squillman Apr 26 '11 at 22:44
add comment

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.

share|improve this answer
    
I appreciate what you're saying, although I would prefer more segregation around logins and access rights than what I get from two different databases on the same server. –  growse Apr 26 '11 at 21:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.