Overview of Microsoft SQL Server 2012

Database Engine

The Database Engine sits at the core of the SQL Server components. The engine operates as a service on a machine, which is often referred to as an instance of SQL Server. You can run multiple instances of SQL Server on a given server. When you connect to SQL Server, the instance is the target of the connection. Once an application is connected, it sends Transact-SQL (T-SQL) statements to the instance. The instance in return sends data back to the client. Within the connection is a security layer that validates access to the data as specified by the database administrators (DBAs). The Database Engine enables you to leverage the full capabilities of all of the other components, such as accessing, storing, and securing the data.

The storage component of the Database Engine determines how the data is stored on disk. When designing your databases, you will specify various aspects that will dictate how your tables, indexes, and, in some cases, views are physically organized on your disk subsystem. You will examine the concepts of tables, indexes, and views in detail in later chapters. In SQL Server 2012, you can physically distribute data across disks by partitioning it, or dividing the data into distinct, independent parts. Partitioning not only improves query performance, but it also simplifies the process of managing and maintaining your data. With the release of SQL Server 2012, Microsoft increased the number of supported partitions to 15,000 per table.

Within the Database Engine itself, the storage engine is the primary component. Surrounding it are several additional components that depend on the engine. These components include the following:

• T-SQL programming interface (Microsoft’s implementations of the SQL ANSI standard language)
• Security subsystem
• Replication
• SQL Server Agent
• High availability and disaster recovery tools
• SQL Server Integration Services
• SQL Server Management tools

The following sections provide a brief explanation of each component.