What's best for DBAs? GUI or T-SQL commands?
Last updated: August 26th '02 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

See also:   Database coding conventions SQL Server interview questions Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools SQL Server security best practices SQL Server administration best practices

Related books:

What's best for DBAs? GUI or T-SQL commands?

In this article, I will discuss the pros and cons of using the SQL Server graphical administration tools versus the T-SQL administrative commands and I will support my views with specific examples. After reading this article, if you agree or disagree with specific points, feel free to email me, and I will update this article with your thoughts, if relevant.

You must have read in many places that real Database Administrators (DBA) and system administrators use command line and only novices and end users use the graphical user interface (GUI) tools. Is that true? Well, may be, or may be not. I believe, that statement has something to do with the fact that command line being the dominant interface in the UNIX world.

What do I prefer? Enterprise Manager or T-SQL commands? I prefer to do most of the administrative work using T-SQL commands from Query Analyzer. I rely mostly on T-SQL for my day-to-day DBA activities, because these T-SQL commands give me complete control and flexibility over things I do. When I type a command, I know what exactly I am doing, which is not the case, when I press the "Finish" button of a wizard. A Wizard or a dialog box in Enterprise Manager is like a black box to me. You know what the Wizard is going to do, but you don't know how exactly it's going to achieve, what you asked it to achieve.

So, does that mean, real DBAs must know the syntax of all the T-SQL commands? No, not necessarily! But it sure is advantageous to have the syntax of certain commands on your fingertips. For example, knowing the syntax of commands that deal with creation and modification of databases, backup, restore activities, database maintenance and monitoring etc. is always beneficial.

As we all know, all Microsoft products ship with user-friendly graphical administration tools. In case of SQL Server, it is Enterprise Manager (popularly known as EM or SEM). Using Enterprise Manager, any user can easily create and manage databases. But those users are limited by the capabilities of Enterprise Manager. Some of these users claim to be DBAs. But are they really DBAs? Can they survive a day's work without using Enterprise Manager? They can't. But hey, none of us are born DBAs. All of us relied on the graphical tools in the beginning. But over time, any inquisitive DBA will realize the limitations of graphical tools, and the flexibility of commands.

Personally, I don't like Enterprise Manager for a lot of reasons. Primary reason being, it's capabilities are limited. There's a lot more that I can do, by using the commands directly. Enterprise Manager has a large memory footprint, and the SQL DMO library used by Enterprise Manager is a bit slow. There are a lot of other limitations that make me stay away from using Enterprise Manager.

So what exactly are those limitations? What are those disadvantages I am talking about? Let's take a simple example. A novice DBA is asked to add a new column at the beginning of an existing table, which has a few million rows. He happily uses Enterprise Manager's Table Designer to insert a new column at the beginning of the table. When he clicks on the 'save' button, guess what exactly happens? Behind the scenes, Enterprise Manager creates a new table with the required structure, copies all the data from the original table into the new table. Drops the original table, and renames the new table as the original table. Remember, this table has a few million rows, so this obviously takes its own sweet time to complete, while blocking the users. And when it finishes, you'll realize that all the dependency information on this table is lost, as it is recreated (You can verify this by running sp_depends, before and after the modification). In this scenario, I would think, an experienced DBA would use ALTER TABLE command to add the new column, and he wouldn't care about the position of the column in the table, as the ordinal position of a column is not relevant. ALTER TABLE command will finish the same job in seconds. So, what's wrong with Enterprise Manager here? It is doing an awful lot of work behind the scenes (can be verified by using Profiler), and breaking things in that process. It's also letting you do things (like adding columns at specific positions), that are logically incorrect.

Here's another example. The other day, a so-called DBA was telling me, it is not possible to rename a database. I asked him, why? He said, there's no option to rename a database in Enterprise Manager. It was more of an RTFM case :-) I pointed him to the sp_renamedb documentation in SQL Server Books Online. What's wrong with Enterprise Manager here? It's simply not providing the complete functionality.

Let's take one more example. DBAs without complete understanding of BACKUP and RESTORE, tend to use database Maintenance Plans in Enterprise Manager to setup backups. So, how about making differential database backups using Maintenance plans? Using Maintenance Planner, one can not perform differential backup of a database.

Yet another example: Using the replication wizards, one cannot subscribe/unsubscribe to a specific article. You either subscribe/unsubscribe to all the articles in the publication. This is not a limitation if you use replication stored procedures. What's wrong with Enterprise Manager here? It's not exposing the complete functionality of commands. So, a point-and-click user cannot take advantage of all the features of a command or product.

Want few more examples?

How about monitoring current activity from Enterprise Manager? It is so slow and takes forever to display any information about processes and locks, on a busy production server.

How many times have you taken a wrong decision, just because you forgot to refresh a node in Enterprise Manager?

How many times have you lost your work, because Enterprise Manager died on you, because of an access violation or other internal error?

How about some of the DBCC commands? You just can't run them from Enterprise Manager.

Last but not the least, how many times your Enterprise Manager broke with a DLL entry point error, just because you upgraded something like MDAC or MMC?

To quickly summarize the above paragraphs, Enterprise Manager is performing some stuff incorrectly and not exposing the complete functionality. Why is it so? If you ever programmed a user interface, you will understand this: It is not always easy to implement a completely flexible user interface. So, some of the complex tasks are not implemented in the user interface. Also, most of the programmers in the SQL Server development team are good at programming in languages like C, C++, C#, but not SQL. That's the reason why Enterprise Manager executes sloppy T-SQL code behinds the scenes. I think Microsoft is better off outsourcing the T-SQL development to some of the SQL gurus from the MVP community ;-)

So! Do I ever use Enterprise Manager? Yes, of course! There are things that are better done with Enterprise Manager, instead of trying to code on your own.

For example, Enterprise Manager is the best choice for creating jobs. Creating a job involves complex calls to multiple stored procedures in msdb. So, I always use Enterprise Manager to create jobs. Then using Enterprise Manager, I generate scripts for my jobs and check them into Visual Source Safe (VSS) and use that script for deploying those jobs to different environments like, QA, staging, live etc.

Another example is, 'setting up replication'. It is a very complex process and requires calling numerous stored procedures. Enterprise Manager does a pretty good job with setting up replication, though some of the advanced options are not exposed in the replication wizards. Again, I use Enterprise Manager to generate replication scripts, and save those scripts for recreating the same replication topology at a later date.

Full-Text search is yet another example. I often use Enterprise Manager to create Full-Text catalogs in my development environment. Then I generate scripts for those catalogs, and use them as the project progresses from development to integration to operations to staging to live environments.

DTS is another example. Enterprise Manager is the most comfortable and intuitive interface for creating DTS packages.

As you may have realized by now, Enterprise Manager has great scripting capabilities, and I take complete advantage of those capabilities. Why? Because, once I have a script to perform a certain operation, I don't have to redo the complete operation manually. I just need to run the script whenever I need to perform that operation.

Here is a partial list of the bugs and problems associated with Enterprise Manager. Go through them and decide for yourself, to what degree you want to depend on Enterprise Manager for your day-to-day DBA tasks. (Btw, the following list is only intended to give you an idea of the kind of problems you might run into, using Enterprise Manager. Enterprise Manager is still a very useful tool, it's just that Microsoft should spend some more time on it and fix up all these problems.).

Q281347 BUG: Can't Append Columns to Tables with Large Number of Columns in SEM Table Designer
This bug in the table designer, will prevent you from adding columns to a table with more than 299 columns.

The following are some problems related to backup/restore in Enterprise Manager:
Q260235 BUG: Point-in-Time Recovery Adds Incorrect Seconds Value to Recovery Time Selected in SEM
Q319697 FIX: SQL Enterprise Manager Restore to Point in Time Does Not Stop at Requested Time and the Database is Left in a Loading State

Q239667 BUG: Design Table in SEM Does Not Preserve NFR Property for IDENTITY
Using Design Table in SQL Server Enterprise Manager to modify a table does not preserve the NOT FOR REPLICATION property.

Q240839 PRB: Pressing ESC When Modifying SP in SEM Erases Changes Without Prompting
Dangerous! I strongly discourage the use of Enterprise Manager for editing scripts. Query Analyzer is the best tool in my opinion.

Q268505 PRB: Deletion of Rows with the Same Values Behaves Differently in SEM and Query Analyzer
A problem with the way Enterprise Manager deals with table data. Always use Query Analyzer, if you need to edit your table data. Come on, you gotta know at least your INSERT, DELETE and UPDATE commands ;-)

Q237398 PRB: SQL Enterprise Manager Returns "Cannot Start Transaction While in Firehose Mode" Error
Another problem with the way Enterprise Manager deals with table data.

Q305711 BUG: DBO User Does Not Display in Enterprise Manager
Enterprise Manager gets confused with orphan users!

Q194014 PRB: Data Designer Does Not Support CASE Statements in Views
This problem prevents you from using advanced constructs in your view definition.

Q275618 FIX: Cannot Set SQLServerAgent Password to More Than 16 Characters in Enterprise Manager

Q285952 FIX: NO_TRUNCATE May Be Added to Backup Log Statement when You Perform a Log Backup from SEM

Q296769 BUG: Can't Use SQL Enterprise Manager to Create Stored Procedures Containing Linked Server Objects

Q262607 BUG: SQL Server Enterprise Manager Does Not Create New Table in Default Filegroup

Q259551 FIX: Database Remains in Single User Mode with the Database Maintenance Plan Option "Repair Any Minor Problems"

Q319246 FIX: Error Dialog Box During SQL Server Database Backup
This is a funny bug that I myself ran into. When you use Enterprise Manager to backup/restore a database, it pops up a dialog box saying 'there is no floppy disk in the floppy drive'. This dialog box pops up on the server console and not on the client machine, so you'll never get to see it, unless you log onto the server directly. This dialog box could prevent you from stopping and starting the SQL server service.

Conclusion? If you are a beginner, and learning SQL Server, it is okay to play around with Enterprise Manager. But if you are responsible for real production systems, and want complete control over the stuff you are doing, T-SQL commands/scripting is the way to go. As I mentioned above, there are situations where use of Enterprise Manager is more appropriate. But even in those situations, I would suggest you use Profiler to verify what exactly Enterprise Manager is doing. If you are using Enterprise Manager for generating scripts, be sure to scan those scripts and get rid of unwanted commands in them, if any. Once again I would like to emphasize, Enterprise Manager is not a very bad tool, it's just that it has some problems associated with it, which you should be well aware of. If Microsoft spends enough time and effort on Enterprise Manager, it will make a real good administration tool. Let's hope for a better Enterprise Manager in Yukon!