In this article, I will discuss the SQL Server graphics management tool and the T-SQL management command from the opposite aspect, and I will support my view through a clear example. After reading this article, you are welcome to email me your point of view. No matter what you support or oppose me, I will update this article according to your idea.
You may know the real database administrator (DBA) and system administrator in many places to use command management, only novice and end users use the Graphics Management Tool (GUI). Is this true, maybe, maybe not. I think this view is a bit related to the main interaction interface in this view and the UNIX world.
What is my favorite, Enterprise Manager or a T-SQL command? My answer is to perform Most management work through the Query Analyzer to execute the T-SQL command. I rely on the T-SQL command to complete my daily database management, because the T-SQL command can fully control my management work and very flexible.
When I play a command, I know what I am doing. When I press the "Complete" button of the Wizard dialog, it is not the case! In the Corporate Manager Wizard and dialog, it is a black box. You know that the wizard will complete your work, but you don't know how it is done.
Well, this means all database administrators know all T-SQL commands? no. But you can use your fingers to complete the command is also very beneficial! For example, commands that know the creation, modification, backup, restore, maintenance, and monitoring of the database is always available!
We know that all Microsoft products have friendly user interfaces, such as SQL Server's Enterprise Manager (generally called EM or SEM). Through the Enterprise Manager, any user can easily create and maintain the database, but these users are limited by the Enterprise Manager, some of which are also known as database administrators! Are they a real database administrator?
Can you still work if you leave the corporate manager? They can't! However, we have no one is born is a database administrator! We all start learning through the graphical interface, but with the in-depth learning, any curious database administrator will realize the limitations of the graphics tool, the command line is flexible.
For me, I have a lot of reasons don't like business manager. The main reason is that performance is limited! With the command line I can do a lot directly, but the enterprise manager uses a lot of memory to print basic pictures, and the SQL DMO library used by the Enterprise Manager is slow. There are many reasons to keep me away from the business manager.
So what is the limitations I talk about, what is the advantage? We will give a simple example! A novice is required to insert a new column in the first column in a table with millions of row of data!
He happily inserts a list using the Table Designer function of the Enterprise Manager. When he pressed the button, what happened?
After this, the Enterprise Manager creates a new table as needed, then copy the data in the old table to the new table, then delete the old table, rename the name of the new table is the name of the old table. Remember, this is a table with millions of row of data, it is obvious that this will take a lot of valuable time to complete this action. But after completion, you will find that the relevant information of all this table will be lost, just like this table is new (you can prove this with sp_depends command before and after modification)!
In this example, I think that an experienced database administrator will use the Alter Table command to add new columns, he will not care about this list in the table because the order of the column is irrelevant. The ALTER TABLE command can complete the same job in a short period of time. So where is the business manager? He made a lot of bad jobs in the background (you can use the event probe PROFILER to confirm), interrupt the connection between objects during this process. He can make you do some work on logically problems (such as adding a column in a specific location). There is also an example here. One day, a so-called database administrator tells me that the name of renaming a database is impossible. I ask him why? He said: There is no rename database in the Enterprise Manager. I opened the SQL Server Book, and referred to the files to see sp_renamedb, its face is very like :-). Where is the corporate manager wrong? He just did not provide all function functions.
We will give another example. If the database administrator does not fully understand the Backup and Restore commands, they can only use the Database Maintenance Plans to set up backup, so how to use the database maintenance plan to do database difference backup? Use the database maintenance planner, no one can complete the database difference backup!
There is also an example. Using Replication Wizards, you cannot subscribe or cancel a part of the item, you can only subscribe or cancel all items in replication or cancel all items. If you use the replicated stored procedure, there will be no such limit! Is there any problem with the business manager? It has only no full function command. Therefore, users who use mouse operations cannot take advantage of one command or product.
Some examples monitor the current activity in the Enterprise Manager? On a busy production server, this is very slow, it just shows some information about the process and lock.
How many times have you done, just because you forgot to refresh the node in the Enterprise Manager?
How many times do you have a white feet, because the business manager has an error, or an illegal access, or other internal errors.
How is the dbcc command? You can't run it in the Enterprise Manager.
Finally, how many times, because you upgrade some things (such as MDAC or MMC), interrupt your business manager because DLL input points are wrong.
Simply summarize the above content: Enterprise Manager provides some excess things, but you can't complete all function functions. Why is this? If you edit a user interface, you will understand: Complete a complete and flexible user interface is not very easy!
Therefore, some complex tasks cannot be implemented on the user interface. At the same time, most of the programmers of SQL Server are proficient in programming languages, like C, C , C #, but not SQL, which is why the Enterprise Manager will perform bloated T-SQL code in the background. I think Microsoft can better develop some SQL masters from the MVP team to promote the development of T-SQL.
So, do I use the enterprise manager? Of course, I use! Enterprise manager can do a lot of things, write code than yourself.
For example: Enterprise Manager is the best choice for creating a job (JOBS). Creating a job requires calling a lot of complex stored procedures in the MSDB database, I often use the Enterprise Manager to create a job, then generate scripts, upload it into Visual Source Safe (VSS), then according to different environments (for example, quality evaluation, Segmentation processing, situation changes) Configure the script to handle jobs. Also, the configuration replication is a very complex and needs to call countless stored procedures. Enterprise Manager can complete the process of configuring a replication, although the Copy Wizard does not provide some advanced options. Similarly, I use the Enterprise Manager to generate a copy script, save this script to the next time you want to configure similar replication!
A full-text search is an additional example. In my development environment, I often use the Enterprise Manager to create a full-text directory, then generate scripts, which take this as an engineering progress mark that is developed from the development to completion to implementation to maintain the entire environment.
DTS is also an example, which is very convenient and intuitive interface that creates DTS in the Enterprise Manager.
Now you can understand, the business manager has strong script acceptance, I try to use this feature. why? Because once I have a script to complete a one, I don't have to handle this job, I just need to run this script when I need it!
Here are some listings about the errors and problems of the enterprise manager. With these questions, you can be sure how you should use the Enterprise Manager in your future database management. (By the way, the following just want to tell you the question you may encounter when using the Enterprise Manager, the enterprise manager is still a useful tool, but Microsoft still needs
Take a little time to fix these problems! )
Q281347 BUG: Can't append columns to Tables with large number of columns in SEM TABLE DESIGNER
The error of this design table is that you can't add columns in a table with 299 columns.
Here are the issues of backup and recovery in the 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
Modifying a table without protecting the "NOT for rrplication" property in the design of the table function in the Enterprise Manager. Q240839 PRB: Pressing Esc WHEN MODIFYING SP in SEM ERASES CHANGES for PROMPTING
note! I strongly recommend not using the Enterprise Manager to edit the script, I think the SQL query analyzer is the best tool.
Q268505 PRB: Deletion of rows with the Same Values Behaves Differently in Sem and Query Analyzer
When you want to handle data in the database table, be sure to use the SQL query analyzer. This way, you must know at least the INSERT, DELETE, and UPDATE commands.
Q237398 PRB: SQL Enterprise Manager Returns "Cannot Start Transaction While In FireHose Mode" error Another Enterprise Manager to handle the problem of data in the table.
Q305711 BUG: DBO User Does Not Display in Enterprise Manager
Enterprise Manager has a problem in single user mode.
Q194014 PRB: Data Designer Does Not Support Case Statements in Views
This problem allows you to use advanced constructs when you define a view.
Q275618 FIX: Cannot Set SqlServergent 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 an interesting mistake I have encountered myself. When you use the Enterprise Manager to back up and restore the database, it pops up "There is no floppy disk in the floppy driver.". This window will not be displayed on the client, only displayed on the server, so you can't see it, unless you log in directly to the server. This window also prevents you from opening and closing the database services.
in conclusion? If you are a novice, just start learning SQL Server, using the Enterprise Manager is correct. However, if you are an engineer responsible for the product, or want to control your own work, then T-SQL commands and scripts make your choice. Just as I mentioned above, it is also appropriate to use the Enterprise Manager in some cases. But even in these cases, I still recommend that you open the SQL event probe to see what the business manager has done.
If you use the Enterprise Manager to generate a script, you must look at all scripts and remove those you don't want. I stated again that the business manager is not a very bad tool, but there are some problems in the inside, you have to be careful. If Microsoft spent enough time and energy on the corporate manager, it will be a very good management tool. Let us look forward to a better business manager in Yukon.
