• 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
• SQL Delta www.sqldelta.com (like red-gate)
• 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
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)
• 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
• 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…
• O for awesome
Other related posts:
GeekPost Weekly Newsletter Volume 4 Issue 7
MSDN Unplugged Roadshow Wellington
Right Sizing Agile Development - MSDN Magazine Webinar
Comment by Erik Cox, on 18-Dec-2008 04:34
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...
Add a comment
Please note: comments that are inappropriate or promotional in nature will be deleted.
E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.
Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.