Top 15 DBA Tasks SQL 2005/2008

Top 15 DBA Tasks SQL 2005/2008
• Adam Cogan / Justin King
• www.SSW.com.au

Our tips (from the floor)
• scripting backups, index, reorgs
• SQL Agent jobs
• Using MSX
• Maintenance plans
• Emails for low disk space
• Polling for deadlocks
• Using SMO to generate scripts
• SCOM
• Send email on restart

Tools available (also from the floor)
• SQL Backup from Red-gate (works on all versions and not just Enterprise)
• Spotlight – alerts for email
• HybridX
• SQL Delta www.sqldelta.com (like red-gate)
• Idera
• Data Dude – Team System – AKA Microsoft Visual Studio 2008 Team Edition for Database Professionals
• Toad for SQL (an Oracle GUI)

Agenda
• Automating Alerts

Fear of the new century: no email
• people fear going without email

How do you do it?
• Automation is your friend
• You should automate all ordinary tasks
• This frees up time for you to perform “fun” tasks

1. Do you measure uptime?
• Measuring downtime to impress the boss
• Monitor Uptime
• Monitor Performance
• 2000 – How
o MOM
o 3rd parties
o Roll your own; generate Monthly report
• 2008
o Monitor servers – SCOM 2007 with SQL Server Management Pack
o Management Data Warehouse (MDW)

2. Are you up to date?
• Patching/Service packs
• SELECT @@VERSION (from Registered Servers folder)
• NetPing
• SQL Ping www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx by Chip Pearson - Free
• SQL Squirrel by NGS www.ngssoftware.cm/products/database-security/ngs-squirrel-sql.php

3. Do you script everything?
• Manual + SQLCMD
• Powershell + SMO (was DMO)
• Data dude
o GDR
• SSW SQL Deploy

4. Are you using a Domain Account?
• Configure to run as a domain account

5. Don’t run as an Administrator(?)
• Grant admin privs or lose functionality
• Missing BUILTIN\Administrators account
• ShellRunas by Marc Ruvonovich


7. Have you Turned on the Default Alerts?
• Severity Level 19 and above
• Other errors
o Developers want an email when a problem
o Network Admins don’t want junk in the Event Log
o SQL2000 – mail was unreliable

8. Have You Created Your Own Alerts?
• You can create application-specific alerts
• RAISERROR WITH LOG
• Use Database Mail in the proc – Async, Queued

9. Alerting Based on Perfmon Counters
• Configure through same UI as Transact-SQL…
• Monitor critical information not otherwise easy to get at: Memory Usage, Database Size, Tempdb usage
• Note that this is NOT an expensive operation…

A cool tool…
• TeraCopy
• O for awesome