SQL Server 2012 Basics: The Tools

The SQL Server DB Engine

The DB Engine is the core app service in the SQL Server package for storing, processing, and securing data with SQL Server 2012. The SQL Server 2012 DB Engine is a Windows service that can be used to store and process data in a relational format, as XML documents, and new for 2012, as spatial data. The subsequent are the main responsibilities of the DB Engine:

Provide reliable storage for data
Provide a means to rapidly retrieve this data
Provide consistent access to the data
Control access to the data throughout security
Enforce data integrity rules to confirm that the data is reliable and consistent.
Each of these responsibilities is examined in greater detail in later chapters in this book.

Another key feature the DB Engine offers to confirm reliable storage is the transaction log. The transaction log makes a record of every change that is made to the DB. Another key feature the DB Engine offers to confirm reliable storage is the transaction log. The transaction log makes a record of every change that is made to the DB.

SQL Server Management Studio

SSMS is the central console from which most DB management tasks can be coordinated. SSMS offers a single interface from which all servers in a company can be managed.

Subsequent are some of the tasks you can execute with SSMS. Most of these tasks are discussed in detail later in the book:

Completely manage many servers in a convenient interface
Set server options and config values, such as the amount of memory and number of processors to use, default language, and default location of the data and log files
Manage logins, DB users, and DB roles
Create, edit, and schedule automated jobs throughout the SQL Server Agent
Back up and restore DBs and define maintenance plans
Create new DBs
Browse table contents
Create and manage DB objects, such as tables, indexes, and stored procedures
Generate DDL scripts for DBs and DB objects
Configure and manage replication
Create, edit, execute, and debug Transact-SQL (T-SQL) scripts
Define, implement, manage, and invoke SQL Server Policies
Enable and disable features of SQL Server
Manage and organize scripts into projects and save versions in source control systems such as Visual SourceSafe

SQL Server Config Manager

SQL Server Config Manager is a tool provided with SQL Server 2012 for managing the services associated with SQL Server and for configuring the network protocols used by SQL Server. Primarily SQL Server Config Manager is used to start, pause, resume, and stop SQL Server services and to view or change service properties.

SQL Server Agent

SQL Server Agent is a scheduling tool integrated into SSMS that allows convenient definition and execution of scheduled scripts and maintenance jobs. SQL Server Agent also handles automated alerts—as an example, if the DB runs out of space. SQL Server Agent is a Windows service that runs on the same machine as the SQL Server DB Engine. The SQL Server Agent service can be started and stopped throughout either SSMS, the SQL Server Config Manager, or the ordinary Windows Services Manager. In enterprise situations in which many SQL Server machines need to be managed together, the SQL Server Agent can be configured to distribute common jobs to multiple servers throughout the use of Multiserver Administration. This ability is most helpful in a wide architecture scenario, in which many SQL Servers are performing the same tasks with the DBs. Jobs are managed from a single SQL Server machine, which is responsible for maintaining the jobs and distributing the job scripts to each target server. The results of each job are maintained on the target servers but can be observed throughout a single interface. If you had 20 servers that all needed to run the same job, you could check the completion status of that job in moments instead of logging in to each machine and checking the status 20 times.The SQL Server Agent also handles event forwarding. Any system events recorded in the Windows system event log can be forwarded to a single machine. This gives a busy admin a single place to look for errors.

SQL Server Profiler

The SQL Server Profiler is a GUI interface to the SQL Trace feature of SQL Server that captures the queries and results flowing to and from the DB engine. It is analogous to a network sniffer, although it does not operate on quite that low a level. The Profiler can capture and save a complete record of all the T-SQL commandss passed to the server and the occurrence of SQL Server events such as deadlocks, logins, and errors. You can use a series of filters to pare down the results when you can want to drill down to a single connection or even a single query.

You can use the SQL Profiler to execute these helpful tasks:

You can capture the exact SQL statements sent to the server from an app for which source code is not available (as an example, third-party apps).
You can capture all the queries sent to SQL Server for later playback on a test server. This ability is extremely useful for performance testing with live query traffic.
If your server is encountering recurring access violations (AVs), you can use the Profiler to reconstruct what happened leading up to an AV.
The Profiler displays basic performance data about each query. When your users start hammering your server with queries that cause hundreds of table scans, the Profiler can simply identify the culprits.
For complex stored procedures, the Profiler can identify which portion of the procedure is causing the performance problem.
You can audit server activity in real-time.