automating common dba tasks n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Automating Common DBA Tasks PowerPoint Presentation
Download Presentation
Automating Common DBA Tasks

Loading in 2 Seconds...

play fullscreen
1 / 19

Automating Common DBA Tasks - PowerPoint PPT Presentation


  • 233 Views
  • Uploaded on

Automating Common DBA Tasks. Jonathan Kehayias MCITP Database Administrator SQL Server MVP http://sqlblog.com/blogs/jonathan_kehayias jmkehayias@sqlclr.net http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA. Agenda. Why Automate? What can we Automate?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Automating Common DBA Tasks' - urielle-horton


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
automating common dba tasks

Automating Common DBA Tasks

Jonathan Kehayias

MCITP Database Administrator

SQL Server MVP

http://sqlblog.com/blogs/jonathan_kehayias

jmkehayias@sqlclr.net

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AutomatedDBA

agenda
Agenda
  • Why Automate?
  • What can we Automate?
  • How can we Automate?
  • Putting it all together (Demonstrations)
  • Monitoring your Monitoring?
  • Additional Tools Available
  • Questions
who am i
Who am I?

SQL Database Administrator OSI Restaurant Partners

SQL Server MVP

MSDN Forums Moderator

Founder SQLCLR.net

Member Tampa SQL Users Group

PASS Member

why automate
Why Automate?
  • Predictable Results
  • Reduce workload - do more in less time
  • Demonstration of Value
what can we automate
What can we Automate?
  • Installation and Configuration SQL Server Software
  • Backups and Database Maintenance
  • Log Checking
    • SQL Server Error Log
    • Server Event Logs
  • Check Drive/Database Free Space
  • Check Job Success/Failure History
  • Monitor Performance
  • Multi-step operations
how can we automate
How can we Automate?
  • SQL Server Agent
    • DTS/SSIS packages
    • Stored Procedures
    • VBscript/ActiveX
    • CmdExec Operating System processes
  • Windows Task Scheduler
    • VBScripts
    • .NET Applications
    • Powershell
  • Dos Batch Files
    • Multistep processes
sql server agent
SQL Server Agent
  • Pros
    • Advanced Scheduling
    • Built in Alerting
    • Built in Logging
    • Detailed Execution History
    • Easily scripted against
  • Cons
    • Dependency on SQL Services
    • Non-SQL Admins require SQL Access to view schedules
    • Tasks Execute under the Service Account unless setup with a proxy
windows task scheduler
Windows Task Scheduler
  • Pros
    • Does not depend on SQL Services
    • Non-SQL Admins can see schedules
    • Reduced Security through use of Run-As
  • Cons
    • Lacks Detailed History of SQL Agent
    • Logging must be done by operation being run
    • Requires additional steps to connect with SQL
    • Not easily scriptable
what to automate backups and maintenance
What to Automate (Backups and Maintenance)
  • Database Maintenance Plans
  • Custom Scripts
  • Custom Schedules

DEMO BACKUPS AND MAINTENANCE AUTOMATION

what to automate sql server and windows logs
What to Automate (SQL Server and Windows Logs)
  • Log Rollover and Retention
  • Exception based Alerting
  • Aggregation of Events (Multi-Server)

DEMO SQL ERROR LOG AUTOMATION

what to automate drive free space
What to Automate (Drive Free Space)
  • DTS/SSIS Package
  • WMI with VBScript
  • COM with TSQL

DEMO DRIVE FREE SPACE AUTOMATION

what to automate database free space
What to Automate (Database Free Space)
  • TSQL
  • VBScript with WMI or DMO
  • SMO with PowerShell

DEMO DATABASE FREE SPACE AUTOMATION

what to automate sql agent job history
What to Automate (SQL Agent Job History)
  • TSQL
    • Adhoc Queries
    • Trigger on sysjobhistory
  • VBScript with WMI or DMO
  • SMO with PowerShell

DEMO SQL AGENT JOB HISTORY AUTOMATION

what to automate multi step operations
What to Automate (Multi-Step Operations)
  • TSQL
  • VBScript with WMI or DMO or TSQL
  • SMO with PowerShell/.NET
  • DOS Batch with osql and sqlcmd

DEMO MULTI-STEP OPERATIONS AUTOMATION

what to automate monitoring performance
What to Automate (Monitoring Performance)
  • SQL Agent Alerts
  • WMI Alerts
  • SQLH2 Performance Collector
  • TSQL DMV Queries

DEMO DATABASE FREE SPACE AUTOMATION

monitoring your monitoring
Monitoring your Monitoring?
  • How do you know your monitoring is working?
  • Can you trust your monitoring?
additional tools
Additional Tools
  • Free Tools Available
    • SQL Server PowerShell Extensions
    • SQL Server Health & History (SQLH2)
      • Recently Released Open Source
    • SqlMonitoring Tool
  • Non-Free Tools
    • Quest
    • Red-Gate
    • Idera
    • SQL Server 2008
resources
Resources
  • The DBA Checklist (Buck Woody)
  • Microsoft Internal Database Operation Team Scripts
  • Automating DBA Processes (TechEd 2008)
  • Automating Common SQL Server Tasks using DMO