The SQL Server Databases object in SQL Server provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities. Monitor transactions and the transaction log to determine how much user activity is occurring in
the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication.
-Backup Devices: During a backup operation on a SQL Server database, the backed up data (the backup) is written to a physical backup device. This physical backup device is initialized when the first backup in a media set is written to it. The backups on a set of one or more backup devices compose a single media set. [Please see the below for more details]
-Endpoints: An endpoint is simply a means of connecting to SQL Server. The term was introduced in SQL Server 2005 because this was the first version that gave us a lot of control over how connections could be made other than setting the protocols that were accepted.
.Endpoints are essentially web services that expose database access over HTTP. Aside from architectural design decisions, these are useful is that your application development team does not have to have management studio access to SQL Server in order to begin development. The WSDL generated shows the parameters required for the endpoint. [To View Endpoints: SELECT * FROM sys.endpoints]
-There are several different kinds of end points which can be created; four to be specific. Two are system specific, the SERVICE_BROKER and DATABASE_MIRRORING endpoints can only be used for the SQL Service Broker and Database Mirroring respectively. The other two are for general use. They are the SOAP and TSQL endpoints.
-Linked Servers: The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a
linked server is quite straight forward in SSMS, all you need is details of the remote server, and the database that you need to query.
.Distributed Queries: Once you have configured linked server, you will be able to run queries against it. When you run a query against a linked server, it is referred to as a distributed query. When you execute a distributed query against a linked server, you must include a fully qualified, four-part table name for each data source to query. This four-part name should be in form linked_server_name.catalog.schema.object_name.
-Triggers: Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL triggers appear in the Database Triggers folder. This folder is located under the Programmability folder of the corresponding database. DDL triggers can fire in response to a Transact-SQL event processed in the current database, or on the current server. The scope of the trigger depends on the event. For example, a DDL trigger created to fire in response to a CREATE_TABLE event can do so whenever a CREATE_TABLE event occurs in the database, or on the server instance. A DDL trigger created to fire in response to a CREATE_LOGIN event can do so only when a CREATE_LOGIN event occurs in the server instance.
-Data Collection: A Data Collection container contains a definition for a set of statistical data you would like to collect, when to collect them and how long to keep them. This definition uses the data collector process to gather and store this information in the MDW. The data collector uses SSIS and SQL Agent jobs to accomplish this.
•The three most important components of any computer and server are the CPU, Memory, and Hard disk specification. Management Data Collection generates reports for the three said components by default. Configuring Data Collection is a easy task and can be done very quickly.
•Configuring the Management Data Warehouse (MDW): The data collected by Data Collections needs to be stored somewhere. That
somewhere is the MDW. The MDW is just a database located on a local SQL Server 2008 instance. In fact, before you can define and use a
Data Collection on a given SQL Server 2008 R2 instance you must have a configured MDW that those Data Collections can point to.
•System Data Collection Sets: When you go through the MDW wizard to set up an instance to start using data collections, three different
system data collections are created. They are “Disk Usage”, “Query Statistics” and “Server Activity”. The Disk Usage data collection collects
disk space usage information related to each database. Another system data collection is the “Query Statistics” data collection. This data
collection gathers information about the different queries that are run against your server. Note that not all queries are captured. Books
Online documents the criteria for when T-SQL queries are captured by this data collection. You can use the query statistics to help identify
queries that use large amounts of resources. The last system data collection is “Server Activity”. This data collection collects system related
information like CPU, memory and other system related performance counters. You can use the information this data collection gathers to
produce trend reports that show resources usage information over time. This information will help identify server resource bottlenecks. To
view the reports, right click on the Data Collection Reports Management Data Warehouse 3 reports available, click any.
-Resource Governor: SQL Server 2008 provides Resource Governor, a feature than you can use to manage SQL Server workload and system
resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application
requests can use. Resource Governor is configurable in SQL Server Management Studio by using T-SQL statements or by using Object Explorer.
-The Resource Governor is designed around the concepts of resource pools (named units that allocate CPU and memory) and workload groups
(named units that include user sessions). As a user session is initiated it is assigned to a workload group. Workload groups are connected to a
resource pool. Resource Governor is not on by default. It can be enabled in Object Explorer using the [server name] ð Management ð Resource
Governor enable or disable command context menu. [Document Author: SQLHelper.IN ]
•Resource pools: Two resource pools (internal and default) are created when SQL Server 2008 is installed. Additional User-defined resource
pools can be created for more granular configuration of resources. For each resource pool, the min% and max% CPU and memory can be
configured. The total of all the resource pools’ min% settings must be equal to or less than 100%. The granularity of the min% and max%
setting is 5%, which fits perfectly with 20 as the maximum number of resource pools (including the internal and default pool, leaving 18
possible user-defined resource pools).
•Workload groups (WG): Two WG (internal & default) are created & mapped to their corresponding resource pools when SQL 2008 installed.
•Classifier: There are internal rules that classify incoming requests and route them to a workload group.
-Maintenance Plans: Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly
backed up, and free of inconsistencies. Maintenance plans create an Integration Services package, which is run by a SQL Server Agent job.
These maintenance tasks can be run manually or automatically at scheduled intervals.
.Database maintenance plans include the routine tasks for: Checking database integrity, Performing index maintenance, Updating database
statistics, Performing database backups, Shrinking the database, Checking file sizes and free disk space.
.These maintenance tasks can be automated and scheduled using the SQL Server Agent service. The maintenance tasks are referred to as SQL
Server Agent jobs. There are two ways to create a maintenance plan:
•Maintenance Plan Wizard: Used to quickly create a basic maintenance plan, this wizard enables you to choose typical predefined maintenance tasks such as performing database backup, rebuilding indexes, updating statistics, checking data integrity, and shrinking the
database. It does not allow you to add any custom tasks.
•Maintenance Plan Design Surface: Used for designing maintenance plans with more flexibility, this option enables you to create a workflow of maintenance tasks and custom maintenance tasks using T-SQL scripts. It allows extended logging, useful for troubleshooting purposes.
.Logging the Maintenance Progress: Many of maintenance tasks that you automate will execute during off hours, when you’re unlikely to be around to monitor system. Fortunately, you can set maintenance tasks to log & report actions so that you don’t need to watch every moment.
-SQL Server Logs: Microsoft includes a robust error and information logging system for SQL Server. SQL Server also records various events in the Windows Event Logs. The primary log you’ll use for SQL Server is the Application log, although the System log is also important, since SQL Server depends on many Windows functions to operate. The Error Logs in SQL Server are actually text files. They are normally found at: Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n (Replace the “N” with the version of SQL Server and the error log number. But most folks use graphical tools to read them.)
.Open SSMS and connect to a SQL Server Instance. One you have connected to a server, open the Management | SQL Server Logs item from the Object Explorer pane and click on the current Error Log. This view is much better for the administrator than the earlier versions. Everything is in one place, including not only the SQL Server Agent logs, but Database Mail and even the Windows Event logs. You can check each box for the logs you want to see, and the entries they contain pile into the right-hand view, ordered by date and time by default. This is a great way to correlate events by looking at the system as a whole, rather than having to peruse each log separately.
-Database Mail: Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.
.Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure stored procedure, or by using the Surface Area Configuration facet of Policy-Based Management.
-Distributed Transaction Coordinator (DTC): The DTC service coordinates transactions that update two or more transaction-protected resources, such as databases, message queues, files systems, and so on. These transaction-protected resources may be on a single computer
or distributed across many networked computers.
-Legacy: Although SQL Server 2000 Data Transformation Services (DTS) is deprecated, SQL Server 2008 provides optional 32-bit management, run-time, and design-time support for packages that were created by using the DTS tools and object model.