Relationships - SQL Server 2008 tutorial by yaofenjin


									SQL Server 2008 – Administration,
 Maintenance and Job Automation

Agenda today – lots to cover
 Overview the concepts first
 Events, Alerts, Operators, and Jobs
 SQL Server Agent
 Management Tab
 Demo on a “Log is full” scenario
 Maintenance Plans plus Demo
Why Automate Admin tasks?
 Automation frees your time to perform other administrative
   As the tasks are routinely scheduled, they have no human
    element which reduces errors and improves consistency
   Microsoft SQL Server allows you to automate administrative
    tasks using SQL Server Agent which runs as a service
   To automate administration, you define predictable
    administrative tasks and then specify the conditions under
    which each task occurs
   The results of these tasks can be delivered to the operators
Events and Alerts
 Events are when something typically an error occurs on the
  SQL Server e.g. Database log fills up
 Alerts would be a message sent to an Operator that an event
  has occurred e.g. Hey operator, database log is full
 By recording specified events, SQL Server can help you
  troubleshoot performance, audit database activity and gather
  data for job related issues
More on Alerts
 Alerts are defined to provide event notification
 Alerts have to be user defined as there are none listed out of
    the box
   Alert can be raised on Error number or Severity Level
   Performance counters can also be used to define an alert
   With an alert, you execute a job and notify an operator
   Can be created using SQL Server Management Studio
    (SSMS) or T-SQL
   Demo on Alert “AW Log is full” later
 The Operator is the person or group notified about the status
  of a job
 The methods of notification include
   Email
   Pager
   Net Send
 Fail-Safe Operator is notified as a last resort
 Operator can be created using SQL Server Management
  Studio (SSMS) or T-SQL stored procedures
 Jobs contain one or more job steps. Each step contains its
  own task, for example, backing up a database
 Jobs can be created using
   SSMS
   Maintenance Plans
   T-SQL
   Jobs are owned by the creator
 Jobs Types can be TSQL, Active X, CmdExec, Replication,
  SSIS and PowerShell
 Jobs can be run manually but are often scheduled to run on
  their own
Bringing it all together
SQL Server Agent
 SQL Server Agent is a Windows service that executes
    scheduled administrative tasks, which are called jobs
   Agent uses either local system account or domain user
    account (preferred). Use configuration manager to set it up
   SQL Server Agent uses SQL Server to store job information
    in the system msdb database
   SQL Server Agent can run a job on a schedule, in response to
    a specific event/alert, or on demand, e.g. backup at midnight
   More info can be found by RMB (Right Mouse Button)
     Discuss General and Alert System tabs
SQL Server Agent Tab
 Jobs
 Alerts
 Operators
 Proxies
   An alternative security context that can run SQL Server Agent
    jobs instead of SQL Server Agent service account
 Error Logs (Agent Specific)
 Out of the box Reports
 Demo
Management Tab
 It has the following important items related to Management
   Maintenance Plans – Will cover it later
   SQL Server Logs
   Database Mail
   Legacy
   Demo
Demo on Log file is Full
 The scenario is: AdventureWorks2008 is our production
   We have created a job called “Increase AW Log” which creates
    a copy of Customer table and updates data every 5 minutes.
    This causes the transaction file to be full
   In order to fix the issue, we have created an Alert called “AW
    Log is full” based on Error Number 9002
   When SQL Server agent notices this alert, it takes necessary
    action which is to run Job “Backup AW Log”
   This can be monitored using Job Activity Monitor
Maintenance Plans
 Maintenance plans create a workflow of the tasks required to
  make sure that your database is optimized, is regularly
  backed up, and is free of inconsistencies.
 The Maintenance Plan Wizard also creates core maintenance
  plans, but creating plans manually gives you much more
 In SQL Server 2008 Database Engine, 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.
Maintenance Plans – Continued
 Wizard is easy to use and intuitive
 You can do all of the following tasks;
   Data optimization
   Database integrity check
   Backup databases and transaction logs
   Cleanup files and history information
 At the end it creates an SQL Server Integration Services
  (SSIS) package that can be edited using BIDS
 I use it as a starting point to get the TSQL I need
 Create a backup with master database if we have time

To top