Ok all you DBAs out there. Here is an article that will stir up a debate. Narayana discussed the pro and cons between these two options. What do you think is better for DBAs? 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! | DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More SQL Server Articles
More By Narayana Vyas Kondreddi  developerWorks - FREE Tools!
|
Effective governance for lean development isn’t about command and control. Instead, the focus is on enabling the right behaviors and practices through collaborative and supportive techniques. Hear from Scott Ambler on how it is far more effective to motivate people to do the right thing than it is to force them to do so. Learn how to form a lightweight, collaboration-based framework that reflects the realities of modern IT organizations.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement.
FREE! Go There Now!
| | | |
Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server.
FREE! Go There Now!
| | | |
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
| | | |
Rational Modeling Extension for Microsoft .NET enhances usability for code generation supporting a more intelligent refactoring. The latest enhancements enable organizations with Java and .NET systems and software development maintain architectural integrity across heterogeneous platforms.
FREE! Go There Now!
| | | |
Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
FREE! Go There Now!
| | | |
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
| | | |
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
FREE! Go There Now!
| | | |
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
| | | |
IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
/ 25 
