Friday, December 5, 2008

Top 15 DBA Tasks SQL 2005/2008

Top 15 DBA Tasks SQL 2005/2008
• Adam Cogan / Justin King

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
• 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 (like red-gate)
• Idera
• Data Dude – Team System – AKA Microsoft Visual Studio 2008 Team Edition for Database Professionals
• Toad for SQL (an Oracle GUI)

• 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 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 by Chip Pearson - Free
• SQL Squirrel by NGS

3. Do you script everything?
• Manual + SQLCMD
• Powershell + SMO (was DMO)
• Data dude
• 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
• 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

1 comment:

  1. Comment by Erik Cox, on 18-DEC-2008 04:34 ( IP: , user id: )

    I can totally agree that Automation is our friend...but a lot of my colleagues agree with it out loud but most of them still don't use it enough... i mean a lot of people I know have a hard time using automatic solutions for the things the learned to do manually...seriously I do not see why that happens..but nevertheless it does...