Do multiple SQL Server instances increase security?

Context

Let's assume you have a server which exposes a web server and one or more web services to store and manage sensible information about physical persons (assume, for this example, complete medical history but also phone numbers, e-mails and other private information).

Access is authenticated and you did in your code everything you need to reach a reasonable level of security.

Web server and web services are running on a Windows Server with IIS + ASP.NET and databases are in a single SQL Server instance. Assume system is always up-to-date, logs are carefully evaluated and system is properly configured and an attacker has no physical access to machine itself.
Current Architecture

• SQL Server is installed on a separate - firewalled - machine (What is the best practice for placing DataBase servers in secure network topologies and How do you explain to experts that a database server should not reside in the DMZ?).

• Obviously every user input is validated and sanitized (if/when required), also information sent by client (even when not directly entered by user) are re-validated and inconsistencies trigger alerts).

Even if not directly related to DB also remember that:

• Proper password managing (store hashes with a good - and slow - hashing algorithm, also described How to securely hash passwords?) and security rules (passphrases are encouraged over short/complicated password and password changes are required only after many failed login attempts, see also How does changing your password every 90 days increase security?).

• Handling of parallel attacking (incremental delays for each failed login - both from same IP and for same user name - and black lists). Related: How to uniquely identify users with the same external IP address?.

• Sessions have timeout (user activity reset short one, long one is fixed). Also client side there is a weak protection that automatically disconnect user (same described in Google Chrome restores session cookies after a crash,

In general multiple instances do not increase security, only complexity. There are reasons to use multiple instances but I don't think they fit your situation.

• You have different groups of users that need administrative (sysadmin, securityadmin etc) access.

• One of your databases needs to be on a different side of the firewall than the rest. I'm honestly not sure why this might be necessary but I've seen it done.

• DR/HA

• Wildly different use cases. For example

• Reporting data vs OLTP (basically splitting a load)

• One database is high transaction and you want optimize for ad hoc turned off while the rest you want it turned on. (requires different server level settings for best performance)

You will note that only one of these has anything to do with security and it's going to be pretty unusual. Generally those types of system privileges go to one team. Sometimes however you need to isolate an instance that is for a vendor package and you have no choice but to grant the application sysadmin.

As far as performance for multiple instances goes, well, there is one fairly obvious consideration. Each instance has it's own overhead. The amount of memory/system resources required for multiple instances will always be higher than for a single instance. If you are willing to pay the price for the additional hardware then that shouldn't be a big issue. The other performance issue comes into play when you have data on multiple instances that needs to work together. For example writing a query that ties your log information to your login information. If the data is on two different databases then you have no unusual performance issues. If on the other hand you are using two separate instances you have to either use a linked server (performance + security issues) or load all of the data into your application and use the application to sort it out (can work for small amounts of data but anything past that is going to give you a massive headache).

StackExchange Database Administrators Q#106752, answer score: 2