1 / 14

SQL Server 2008 – Administration, Maintenance and Job Automation

SQL Server 2008 – Administration, Maintenance and Job Automation. Learningcomputer.com. 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.

devon
Download Presentation

SQL Server 2008 – Administration, Maintenance and Job Automation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server 2008 – Administration, Maintenance and Job Automation Learningcomputer.com

  2. 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

  3. Why Automate Admin tasks? • Automation frees your time to perform other administrative functions • 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

  4. 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

  5. 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

  6. Operators • 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

  7. Jobs • 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

  8. Bringing it all together

  9. 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

  10. 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

  11. Management Tab • It has the following important items related to Management • Maintenance Plans – Will cover it later • SQL Server Logs • Database Mail • Legacy • Demo

  12. Demo on Log file is Full • The scenario is: AdventureWorks2008 is our production database • 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

  13. 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 flexibility • 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.

  14. 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

More Related