An instance is a SQL Server service. Each
instance has definite set of services with unique settings like Registry Structure,
Directory Structure and service names which resonates with the instance name
and ID created at the time of Set Up. Each
instance has several system databases and one or more user created databases
and runs independently. SQL Server database engine can have multiple instances
on the same machine. For instance (no pun intended!) your need is to have only
one instance, then its better to have the default. You can configure instance
through SQL Server Installation file, where you would have encountered 2 types
of instances
·
Default -as the name suggests, this is the default instance which takes only
the server name. There can be only one default instance in a server. MSSQLServer is the default
name.
· Named – You may name your instance as you’d like, pertaining to the below limitation
Ø
Less than 16 char and
not case sensitive
Ø
No special characters (underscore '_' is accepted in between but not at the start or end of the name) or keywords or the term ‘Default’
Ø Server_Name\Instance_Name (Eg: .\TEST ) is used in SSMS to connect to this instance of the database engine.
Few reasons for creating multiple
instances for database engine are
- If your databases have more than one language, you may create instances for each language
- If the compatibility of your databases demands specific version, you may create instances accordingly
- Create instances for different users based on their roles and requirement to view databases
- To configure a different server mode for Analysis Services
You can find the instances in your local
machine, by typing SQLServerManager13.msc for
[SQL Server 2016] in Search and hit enter. Below image shows the default instance
configured in my laptop.
No comments:
Post a Comment