Pr.Pg Next Pg

MS SQL Server 2012 Architecture

MS SQL Server 2012 architecture consist of the following common things

1)     Services

2)     Instances

3)     Tools



  • Services are process in Windows which are started without user interaction. In MS SQL Server there is multiples number of services, which are set up when you install MS SQL Server in your windows machine.

  • Typically this services are started when computer startup’s.

  • Some of the MS SQL Server services are SQL Reporting Services, SQL Server Analysis Services and SQL Integration Services.

  • Other component of MS SQL Server are Database engine, which is use for executing our query and SQL Server Agent which is use for executing scheduled jobs, monitoring the system for defined alert conditions and other administrative tasks.



  • An Instance is a basic unit of program execution in MS SQL Server it is a copy of the SQLSERVER.EXE executable program which runs as a Windows services. Logically instances represent the programs and resource allocations that support a single copy of SQL Server running on a Windows machine.

  • SQL Server instances define the container for all operations you perform within SQL Server. Each instance contains its own set of databases, security credentials, configuration settings, Windows services, and other SQL Server objects.

  • SQL Server 2012 supports the installation of up to 50 instances on SQL Server on a single machine. You can install one instance as the default instance along with up to 49 additional named instances, or you can install 50 named instances with no default.

  • When you connect to a default instance of SQL Server, you use the name of the machine to which the instance is installed.

  • One of the instance may be setup as the default instance which means you will access it using only the name of the host computer and remaining instances on the same machine must be named instances which you will access using a combination of the host name and the instance name

  • When connecting to a named instance, you use the combination of the machine name and instance name, such as <machinename>\<instancename>.


Main reasons for installing more than one instance of SQL Server on a single machine are:

·         You need instances for quality assurance testing or development.

·         You need to support multiple service pack or patch levels.

·         You have different groups of administrators who are allowed to access only a subset of databases within your organization.

·         You need to support multiple sets of language colliation

  • NOTE Only SQL Server 2012 Enterprise supports the installation of multiple instances on a single machine.



  • MS SQL Server have many number of tools for developing, managing and querying Database you will find shortcuts to many tools on the windows start menu in the subfolder of Microsoft SQL Server 2012. The tool which w use for creating and querying database is called SQL Server Management Studio (SSMS) SSMS is an integrated management tool.

  • Some other MS SQL Server tools are as follow

  • SQLCMD: A command-line client that allows you to submit T-SQL commands as an alternative to SSMS

  • SQL Server Configuration Manager: This tool is use for administrators and also useful for managing SQL Server software installed on client machines

  • SQL Server Installation Center: It provides the ability to add, remove and modify SQL Server program features if you have permission to do so.



Pr.Pg border                                              Next Pg