SQLServerCentral Article

Worst Practices - Depending on the GUI


Depending on the GUI:

This article continues the series on WP.

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

Return to Steve Jones Home



5 (1)

You rated this post out of 5. Change rating




5 (1)

You rated this post out of 5. Change rating