New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. is against this unjust law - help us

A Space for All Things .NET Related

Top 15 DBA Tasks SQL 2005/2008

By James Hippolite, in , posted: 6-Dec-2008 18:32

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

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.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
New Zealand

Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".