SQL Server — Single instance vs Multiple instances

The Database Engine sits at the core of the SQL Server components which operate as a service on a machine and they are frequently referred to as an instance of SQL Server.

It is very important decision to choose single instance or multiple instance in database designing based on business requirement, environment set up, cost & budgeting, app/DB size etc. factors.

An instance is nothing but known as a collection (copy of the server files, databases and security credentials) of SQL Server databases run by a single SQL Server service which can have many databases on there and each instance can be started or stopped individually. .

If there are multiple server installations on single machine they are identified by instance name. Key facts to divide SQL Server into multiple instances are –

• Very well protected from one another
• They don’t interfere with each other
• Each instance has own databases, logins, security, etc.
• Multiples instances give us the ability to spread load over more than one TempDB.

Multiple instances of SQL Server on a single physical machine are a great way to have separate environments — both based on the versions (and editions) of SQL Server we might need to support…