A database instance is a copy of the SQL Server functioning as an operating system service, taking care of application requests to work with data from any of the databases administered by the instance. The instance serves as the target for the application’s connection request.
The following principles apply to all instance types:
If the instance and application are on different computers, the connection goes through a network connection.
If the application and instance are running on the same computer, the SQL Server connection can be either a network or an in-memory connection.
After a connection has been finished, an application sends Transact-SQL statements to the instance via the connection. The instance helps by converting the Transact-SQL statements into operations against the data and objects in the databases and then performs the work if the login credentials have the necessary permissions. Any retrieved data and any messages, such as errors, are returned to the application.
It is possible to run multiple instances of your Database Engine on one computer. This might be beneficial in very specific circumstances but is often incredibly complex.
For the most part, it is best to run only one instance. Running multiple instances on one system mapped to the same hardware can create problems. If one instance is under heavy load, it will affect the performance of other instances.
One possible benefit is the cost of licensing. You can create instances to your heart's content once you've paid for SQL Server and have it running on a specific machine. You'd need to buy more licenses if you wanted to add another machine to your system.
Cegal works daily with many database instances, as we have managed services on many of our clients instances and databases. They are mainly on Oracle and Microsoft technology, but also Open Source such as PostGreSQL.
We assist our customers in achieving the optimal setup for their databases, including how the configuration should be in terms of instances or not.
In addition, we also advise on licensing and governance, which can also be quite different, depending on how the database is configured.
If you want to quickly learn more about your database setup, which may have been adjusted by several external partners over the years, we can help you analyze your databases and give you an accurate picture of the state of health and e.g. the opportunities to optimize performance.