Basic/Architecture SQL Server - Introduction

 

                    Introduction to SQL Server

What is SQL Server/RDBMS?

As you most likely know, SQL Server is primarily thought of as a Relational Database Management System(RDBMS). It is certainly that, but it is also much more.

SQL Server can be more accurately described as an Enterprise Data Platform. It offers many new features and even more enhanced or improved features from previous editions of the product. In addition to traditional RDBMS duty, SQL Server also provides rich reporting capabilities, powerful data analysis, and data mining, as well as features that support asynchronous data applications, data-driven event notification, and more.

Database Engine 

The Database Engine is the primary component of SQL Server . It is the Online Transaction Processing (OLTP) engine for SQL Server, and has been improved and enhanced tremendously in this version. The Database Engine is a high-performance component responsible for the efficient storage, retrieval, and manipulation of relational and Extensible Markup Language (XML) formatted data.

SQL Server Database Engine is highly optimized for transaction processing, but offers exceptional performance in complex data retrieval operations. The Database Engine is also responsible for the controlled access and modification of data through its security subsystem. SQL Server 2008’s Database Engine has many major improvements to support scalability, availability, and advanced (and secure) programming objects.

Analysis Services

Analysis Services delivers Online Analytical Processing (OLAP) and Data Mining functionality for business intelligence applications. As its name suggests, Analysis Services provides a very robust environment for the detailed analysis of data. It does this through user-created, multidimensional data structures that contain de-normalized and aggregated data from diverse data sources (such as relational databases, spreadsheets, flat files, and even other multidimensional sources).

Reporting Services

Reporting Services is a Web service–based solution for designing, deploying, and managing flexible, dynamic Web-based reports, as well as traditional paper reports. These reports can contain information from virtually any data source. Because Reporting Services is implemented as a Web service, it must be installed on a server with Internet Information Services (IIS). However, IIS does not have to be installed on a SQL Server. The Reporting Services databases are hosted on SQL Server 2008, but the Web service itself can be configured on a separate server.

 

Integration Services

SQL Server Integration Services (SSIS) is Microsoft’s new enterprise class data Extract, Transform, and Load (ETL) tool. SSIS is a completely new product built from the ashes of SQL Server 2000’s Data Transformation Services (DTS). SSIS offers a much richer feature set and the ability to create much more powerful and flexible data transformations than its predecessor. This huge improvement, however, is not without a cost. SSIS is a fairly complex tool and offers a completely different design paradigm than DTS. Database administrators adept at the former tool are very often intimidated and frustrated by the new SSIS. Their biggest mistake is in thinking that Integration Services would just be an upgrade of Data Transformation Services. 

Replication Services

SQL Server Replication Services provides the ability to automate and schedule the copying and distribution of data and database objects from one database or server to another, while ensuring data integrity and consistency. Replication has been enhanced in SQL Server 2008 onwards to include true Peer-to-Peer replication, replication over HTTP, the ability to replicate schema changes, and, very interestingly, the ability to configure an Oracle server as a replication publisher.

Multiple Instances

SQL Server  provides the capability of installing multiple instances of the database application on a single computer. Depending on the edition of SQL Server being installed, up to 50 instances can be installed. This feature allows for one high-performance server to host multiple instances of the SQL Server services, each with its own configuration and databases. Each instance can be managed and controlled separately with no dependency on each other.

Database Mail

In the past SQL Server relied on a Messaging Application Programming Interface (MAPI) mail client configured on the server to facilitate email and pager notification for administrative and programmatic purposes. What this essentially meant was that to fully utilize administrative notifications, the administrator needed to install Outlook or some other MAPI-compliant client on the server, and then create a mail profile for the service account to use.

Many organizations wanted to take advantage of the SQL Server Agent’s ability to send job and event notification via email but were unwilling to install unnecessary and potentially risky software on production server assets. The SQL Server Database Mail feature removes this requirement by supporting Simple Mail Transfer Protocol (SMTP) for all mail traffic. In addition, multiple mail profiles can be created in the database to support different database applications. 

SQL Server Services

SQL Server runs as a service. In fact, it runs as several services if all the different features of the product are installed. It is important to know what service is responsible for what part of the application so that each service can be configured correctly, and so that unneeded services can be disabled to reduce the overhead on the server and reduce the surface area of SQL Server.

MSSQLServer (SQL Server)

The MSSQLServer service is the database engine. To connect and transact against a SQL Server 2008 database, the MSSQLServer service must be running. Most of the functionality and storage features of the database engine are controlled by this service.

The MSSQLServer service can be configured to run as the local system or as a domain user. If installed on Windows Server , it can also be configured to run under the Network System account.

SQLServerAgent (SQL Server Agent)

This service is responsible for the execution of scheduled jobs such as scheduled backups, import/export jobs, and Integration Services packages. If any scheduled tasks require network or file system access, the SQLServerAgentservice’s credentials are typically used.

The SQLServerAgent service is dependent on the MSSQLServer service. During installation, the option is given to configure both services with the same credentials. Although this is by no means required, it is common practice. A frequent problem encountered by database administrators is that jobs that work perfectly when run manually fail when run by the agent. The reason for the failure is because the account that is used when testing the job manually is the logged-in administrator, but when the job is executed by the agent, the account the agent is running under does not have adequate permissions.

MSSQLServerOLAPService (SQL Server Analysis Services)

MSSQLServerOLAPService is the service that Analysis Services runs under. Analysis Services provides the services and functionality to support all of SQL Server OLAP needs, as well as the new data mining engine included with SQL Server .

SQLBrowser (SQL Server Browser)

The SQLBrowser service is used by SQL Server for named instance name resolution and server name enumeration over TCP/IP and VIA networks.

The default instance of SQL Server is assigned the TCP port 1433 by default to support client communication. However, because more than one application cannot share a port assignment, any named instances are given a random port number when the service is started. This random port assignment makes it difficult for clients to connect to it, because the client applications don’t know what port the server is listening on. To meet this need, the SQLBrowser service was created.

MSDTSServer (SQL Server Integration Services)

The MSDTSServer service provides management and storage support for SSIS. Although this service is not required to create, store, and execute SSIS packages, it does allow for the monitoring of SSIS package execution and displaying of a hierarchical view of SSIS packages and folders that are stored in different physical locations.

ReportServer (SQL Server Reporting Services)

The ReportServer service is the process in which Reporting Services runs. The service is accessible as a Web service and provides for report rendering, creation, management, and deploying. 

MSDTC (Distributed Transaction Coordinator)

The MSDTC service is used to manage transactions that span more than one instance of SQL Server or an instance of SQL Server and another transaction-based system. It utilizes a protocol known as Two-Phased Commit (2PC) to ensure that all transactions that span systems are committed on all participating systems.

SQL Server Database Objects

SQL Server 2008 database objects are defined and exist within a defined scope and hierarchy. This hierarchy enables more control over security permissions and organization of objects by similar function. SQL Server 2008 objects are defined at the Server, Database, and Schema levels.

Server 

The server scope encompasses all the objects that exist on the instance of SQL Server, regardless of their respective database or namespace. The database object resides within the server scope.

We can install multiple instances of the SQL Server 2008 Data Platform application on a single computer running a Windows operating system. 

Database

The database scope defines all the objects within a defined database catalog. Schemas exist in the database scope.

Schema

Each database can contain one or more schemas. A schema is a namespace for database objects. All data objects in a SQL Server 2008 database reside in a specific schema.

 

Object Names

Every object in a SQL Server 2008 database is identified by a four-part, fully qualified name. This fully qualified name takes the form of server.database.schema.object. However, when referring to objects, the fully qualified name can be abbreviated. By omitting the server name SQL Server will assume the instance the connection is currently connected to. Likewise, omitting the database name will cause SQL Server to assume the existing connection’s database context.

SQL Server  Databases

There are two types of databases in SQL Server: system databases and user databases. The system databases are used to store system-wide data and metadata. User databases are created by users who have the appropriate level of permissions to store application data.

System Databases

The system databases are comprised of Master, Model, MSDB, TempDB, and the hidden Resource database. If the server is configured to be a replication distributor, there will also be at least one system distribution database that is named during the replication configuration process.

The Master Database

The Master database is used to record all server-level objects in SQL Server 2008. This includes Server Logon accounts, Linked Server definitions, and EndPoints. The Master database also records information about all the other databases on the server (such as their file locations and names). Unlike its predecessors, SQL Server 2008 does not store system information in the Master database, but rather in the Resource database. However, system information is logically presented as the SYS schema in the Master database.

The Model Database

The Model database is a template database. Whenever a new database is created (including the system database TempDB), a copy of the Model database is created and renamed with the name of the database being created. The advantage of this behavior is that objects can be placed in the Model database prior to the creation of any new database and, when the database is created, the objects will appear in the new database. 

The MSDB Database

I mostly think of the MSDB database as the SQL Server Agent’s database. That’s because the SQL Server Agent uses the MSDB database extensively for the storage of automated job definitions, job schedules, operator definitions, and alert definitions. 

The TempDB Database

The TempDB database is used by SQL Server to store data temporarily. The TempDB database is used extensively during SQL Server operations, so careful planning and evaluation of its size and placement are critical to ensure efficient SQL Server database operations.

The TempDB database is used by the Database Engine to store temporary objects (such as temporary tables, views, cursors, and table-valued variables) that are explicitly created by database programmers. In addition, the TempDBdatabase is used by the SQL Server database engine to store work tables containing intermediate results of a query prior to a sort operation or other data manipulation.

The Resource Database

The last system database is the Resource database. The Resource database is a read-only database that contains all the system objects used by an instance of SQL Server. The Resource database is not accessible during normal database operations. It is logically presented as the SYS schema in every database. It contains no user data or metadata. Instead, it contains the structure and description of all system objects. This design enables the fast application of service packs by just replacing the existing Resource database with a new one. As an added bonus, to roll back a service pack installation, all you have to do is replace the new Resource database with the old one. This very elegant design replaces the older method of running many scripts that progressively dropped and added new system objects.

User Databases

User databases are simply that: databases created by users. They are created to store data used by data applications and are the primary purpose of having a database server. 

Distribution Databases

The distribution database stores metadata and transactional history to support all types of replication on a SQL Server. Typically, one distribution database is created when configuring a SQL Server as a replication Distributor. However, if needed, multiple distribution databases can be configured.

A model distribution database is installed by default and is used in the creation of a distribution database used in replication. It is installed in the same location as the rest of the system databases and is named distmdl.mdf.

You can read more about SQL Server Basics on below books:

Pro SQL Server Relational Database Design and Implementation