Depending on the GUI:
Part 1 - Worst Practices - Part 1 of a Very Long Series!
Part 2 - Worst Practices - Objects Not Owned by DBO
Part 3 - Worst Practices - Not Using Primary Keys and Clustered Indexes
Part 4 - Worst Practices - Making Databases Case Sensitive (Or Anything Else)
Part 5 - Worst Practices - Assigning Rights to Users
One of the strengths of SQL Server is its ease of management
and administration over other systems. Oracle, DB2, even early versions of SQL
Server required command line mastery to make many types of changes. I remember
one of the big selling points for SQL Server 6.5 was the tremendous amount of
work that could be performed using the GUI based Enterprise Manager.
This isn’t really particular to SQL Server, most of the
Microsoft products are based on using some type of GUI for many configuration
and administrative tasks. Theses GUIs are one of the reasons that so many
people find it easy to administer Microsoft products.
So why is this a “worst practice”? If the GUI makes the task
easier, then isn’t that a good thing? Don’t I always preach the KISS principle
(Keep It Simple Stupid)? Having a simple system is a good thing. Using a GUI to
cover up functionality that is complex is one of the keys to extending the
system to less-technical users. After all, one of the big knocks of Linux (and
other *nix’s) on the desktop is the need to be able to navigate a command
prompt fairly well. How many of us would ask out mothers to remember how to
fdisk a hard drive, or search for a file using grep?
None of us, but then again, we wouldn’t ask our mothers to
administer SQL Server either. There are a number of tasks and products that
require expertise to properly setup and maintain. I don’t mean to imply that
any of you are not qualified to work with SQL Server, but there are many
practices that are recognized as good, others that border on negligent, but
both types are still implemented by many “professionals” in the IT industry.
It’s not always a question of one’s incompetence, sometimes it is ignorance,
but the practices are still poor.
Am I implying that
using the GUI to perform tasks is a “worst practice”. In many cases, yes.
Let me give a few examples. Suppose that you need to add a
new table to your development server. You add the table to the server using the
GUI. An hour later the server crashes, someone deletes the table, or you
restore from last night’s backup for some reason. What happens to your table?
Will you remember every column and it’s defaults? The foreign keys?
Suppose that you are asked to setup a new database for a web
site, including a role and user and rights to various objects. You
painstakingly work your way through the Enterprise Manager rights dialog and
grant the rights. You find out the next day, that your boss wants the same
thing duplicated on another web server. Would you rather use the GUI again or
run a script?
Lastly, a third example from my week at work. I changed a
stored procedure to add a new parameter. I also added a new role for a limited
view user for a separate web site and created a lookup table that was seeded with
3 rows of data. To incorporate this new table into our schema, I altered an
existing table and established a Foreign Key between the two tables. That was
on development. Three days later, I had to repeat this process on our QA
server. Two days after that, it moved to the production server. Would you
suggest I complete these tasks in Enterprise Manager or using a script?
I realize that most of these examples are based on redoing
some action again. However, how many things do you do as a DBA only once? I pretty
much do everything three times, once on development, once on QA and once on
production. Actually, I’ll correct that, I do things on development and QA. I
then deliver a change package that someone else sends to production. Why? So I
have some responsibility to ensure that the package works. If I had to do it on
production, I’d be tempted at times to skip QA and just move it to the live
database. Change control is one of the most important processes to ensuring a
stable database system.
So do I ever use the GUI?
All the time. I check jobs, add jobs, even alter tables.
However, I always script out my changes. In the MMC Enterprise Manager, one of
the most useful features is the “script “ button in the table designer. I use
this to be sure that any alterations are scripted. These scripts are
immediately saved to text files and then loaded into our version control system
before I move on to any other tasks.
The worst practice is relying or depending on the GUI
completely. It dulls your skills and doesn’t provide an easy way to reliably
and assuredly repeat your actions.
As always I welcome the feedback and debate on this topic.
Please use the “Your Opinion” button below to post your comments.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also
rate this article.
Steve Jones
©dkRanch.net October 2001
