SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Worst Practices - Depending on the GUI

By Steve Jones,

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


Total article views: 6217 | Views in the last 30 days: 1
Related Articles

Worst Practices - Assigning Users Rights

Continuing with Andy Warren's series on Worst Practices for a DBA, Steve Jones joins in this week wi...


Worst Practices - Making On-The-Fly Changes

Continuing with our worst practices series, Steve Jones looks at another administrative no-no. Makin...


Worst Practices - Encrypting Data

Continuing with the Worst Practices Series: Steve Jones examines why encryption in the database is a...


Worst Practices - Blank Passwords

Not even worth talking about, right? Probably, but they still exist. Read about this worst practice ...


Worst Practice - Not Qualifying Objects With The Owner

Regular columnist Chris Hedgate follow up on Andy's recent article about Worst Practices with one of...