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.

Due to a hardware failure, we are migrating the software installed from one server to a new one. There are two pieces of software (vmware vcenter and symantec backup exec) that rely on separated SQLServer Express Instances.

The initial setup of this software (both of them) was done by an external company, and it seems that they followed the next-->...--->next-->finish approach to install the software, ending with each piece of software having their own SQLServer Express Instance as a result.

My question is, Is this approach the best option or would be better to keep all the databases on the same instance of SQLServer Express??

share|improve this question
    
Best in what sense? Best for what purpose? Having multiple instances of SQL Server is perfectly valid. What you don't ask or state is why you might think this is not a good practice. What are your concerns about having multiple SQL instances? –  joeqwerty Jan 16 '13 at 10:50
    
From my point of view, having two instances of SQLServer running on the same machine directly implies having two sqlservr.exe processes running and consuming resources. Is worth having this two processes consuming resources in terms of performance of the applications?? –  xoner Jan 16 '13 at 12:45
    
Well as I said, it's perfectly valid to have mulitple instances of SQL Server installed on one server and it's also quite common. As long as the server has sufficient resources I don't see a problem with it. –  joeqwerty Jan 16 '13 at 13:18

1 Answer 1

up vote 1 down vote accepted

The usual argument against multiple instances of SQL Server on the same computer is that additional resources are used for each instance. This means additional disk space for an entire copy of the SQL Server binaries, the system databases and other miscellaneous files. There is CPU, thread and RAM overhead associated with just running the server binary itself. The biggest blocks of resources may be the two separate data caches. Having two data caches which may prevent efficient use of RAM by one instances or the other, depending on database sizes, how the system is queried and how the system is configured. There will also be additional administration time for additional instances. For example, there will be two backup jobs to create and watch, two reindex jobs to create and watch, minimum & maximum RAM values to configure, etc. You may need to open additional firewall ports for each additional instance.

The most popular (and best, IMO) argument for multiple instances is that two different applications require as much isolation from each other as possible, security-wise. IIRC, the feature was originally intended to help PaaS vendors provide SQL Server to clients in the days before virtualization was ready for prime time and SQL Server (and Windows Server) had to be installed on "bare metal". Clearly, those clients would need to be totally isolated from one another and those security concerns are the same today as they were in the year 2000.

Some applications need specific collations for tempdb, which is shared by all applications running on an instance. This is solvable with multiple instances. Similarly, if your application requires a particular service pack or hot fix level, you can keep each instance at a different version.

Installing multiple instances is a possible solution if you have two different applications (or different versions (dev, test, production) of the same application) that must use the same database name.

(Another solution for a must-isolate requirement is to run two virtualized operating systems, each with it's own installation of SQL Server. This may provide better isolation, but it may increase costs for licensing and administration as well as creating more resource overhead on the computer. That said, I see more and more DBAs immediately going to virtualization. It may be that "multiple instances" is a feature whose time has come and gone, at least for big systems, but I haven't seen any indication that Microsoft will be dropping it.)

share|improve this answer

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.