SQLServer Express Single or Multiple Instance Installation

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??

Solution 1:

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.)