SQL Server GUI in Action
Recently I was replacing a maintenance task a client had put together for SQL Server database backups. I haven’t been doing this kind of work for some time, and it was a raw reminder of a number of things.
- I was reminded how the GUI tools for performing maintenance tasks in SQL Server provide non-DBA individuals with a lot of power, with little understanding required
- Microsoft uses proprietary system procedures they add to enable their GUI driven tasks which are often un-documented and unsupported
- When you have a lot of configuration to do at one time using the DB Maintenance tool (a small subset of SSIS) or the SQL Agent Scheduled tasks, the GUI tools are more of a pain than a help
For my first point, I think it is a good thing to provide a user friendly way allowing non-technical people to perform basic tasks, such as backups, without requiring a deep education. It definitely extends the market base for any product.
A couple popular examples for my second point are sp_foreachdb and my recently discovered xp_delete_file. With sp_foreachdb you may execute an SQL statement against each database. With xp_delete_file you can delete files from your disk and provide a lot of different parameters regarding how you want to delete the files.
I’m not so sure it’s a good thing to have these procedures exposed. My primary reason is the fact that MS doesn’t support them. They may change or delete these procedures at any time without regard to you the consumer.
Regarding my third point, I had to setup backups for a number of databases. I useda SQL Server Agent job to perform the backups instead of the Management Wizard SSIS defined job. I went this route because the maintenance wizard task was swallowing errors, and I didn’t have time to figure out how to fix it, or if it could be fixed.
I used a separate step for each backup. After the third step I became frustrated with the GUI for creating steps in an SQL Agent task, because it was too cumbersom. Instead, I scripted the job I had already configured with three steps, and used a text editor to complete the job. It was incredibly painful to jump back and forth between the Advanced and General view in the step editor. The mouse became my worst enemy instead of my ever helpful friend. Scripting a couple steps out was all I needed to give me a template to complete my work in a significantly shorter time.
That being said, I do appreciate the GUI. I appreciate even more the fact that Microsoft has added the scripting capability for their GUI tools for those of us who need to go to the next level without research into the syntax required.
Do you feel like sharing a comment about my rant, or anything else software related that is on your mind? If so, send your thoughts to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
SSWUG TV
With Stephen Wynkoop
Did you catch the bloopers our producer Josh Harrison found with Steve?
Watch the Show
Featured White Paper(s)
SQL Server 2008 on Stratus ftServer Systems
Written by Stratus Technologies
Microsoft SQL Server 2008 on a Stratus fault… (read more)