SQL Server works in client-server architecture, hence it supports two types of components − (a) Workstation and (b) Server.
- Workstation components are installed in every device/SQL Server operator’s machine. These are just interfaces to interact with Server components. Example: SSMS, SSCM, Profiler, BIDS, SQLEM etc.
- Server components are installed in centralized server. These are services. Example: SQL Server, SQL Server Agent, SSIS, SSAS, SSRS, SQL browser, SQL Server full text search etc.
- An instance is an installation of SQL Server.
- An instance is an exact copy of the same software.
- If we install ‘n’ times, then ‘n’ instances will be created.
- There are two types of instances in SQL Server a) Default b) Named.
- Only one default instance will be supported in one Server.
- Multiple named instances will be supported in one Server.
- Default instance will take the server name as Instance name.
- Default instance service name is MSSQLSERVER.
- 16 instances will be supported in 2000 version.
- 50 instances will supported in 2005 and later versions.
- To install different versions in one machine.
- To reduce cost.
- To maintain production, development, and test environments separately.
- To reduce temporary database problems.
- To separate security privileges.
- To maintain standby server.