Managing SQL Server via Management Studio vs TSQL Commands
By: Jeremy Kadlec | Updated: 2007-08-16 | Comments (4) | Related: More > Professional Development Skills Development
Over the last few months I have heard the terms 'typer' and 'clicker' related to how SQL Server DBAs manage SQL Server. Working with one specific customer, the customer came right out and asked me 'if I was a typer or a clicker'. I was surprised to hear that question, but I understood why they asked it as they observed me work. They noticed all of the T-SQL commands that I issued as opposed to using Enterprise Manager. They were also surprised to see how many SQL Server system tables that I accessed and the information that I gathered to troubleshoot the issue. This same distinction between DBA management preferences became crystal clear to me during a recent Northern Virginia SQL Server User Group session. As the group was having a discussion, I was surprised to see hear how how many people provided input based on clicks in the GUI (Management Studio or Enterprise Manager) versus issuing T-SQL commands. So is managing SQL Server via the GUI or T-SQL commands better than the other approach? Is there information that is only available in one technique and not the other?
So let's just clarify terms before we jump too far into this tip which could turn into a heated debate. From the experience I have had, a 'typer' is a reference to a DBA that issues T-SQL commands. Whereas the 'clicker' is a reference to a DBA that manages SQL Server with the GUI (Management Studio or Enterprise Manager). As such, let's take a look at a comparison matrix for both techniques.
|Advantages - GUI||Advantages - T-SQL|
|Disadvantages - GUI||Disadvantages - T-SQL|
So when I start thinking about SQL Server management tasks, what are some of the common tasks and can they be equally performed with either approach?
|1||Start SQL Server in single user mode||Use DOS commands with the needed switches to start the SQL Server services||Use the Services applet to add the specific switches and restart the SQL Server service|
|2||Perform backup and restore operations||T-SQL BACKUP Command||Enterprise Manager - Backup and Restore Interface
Management Studio - Backup and Restore Interface
|3||Review the SQL Server error logs||Use xp_cmdshell with the TYPE command to review the logs||Enterprise Manager - Error Logs
Management Studio - Log File Viewer
|4||Build and deploy Service Broker objects||Infrastructure Objects
||In SQL Server 2005 Management Studio, a Service Broker interface is not available with Management Studio to point and click and build the objects, but scripting out the objects is available.|
|5||Performance Tuning||A variety of commands, system stored procedures, scripts, system tables, dynamic management views, etc. are available which can be queried to troubleshoot the performance issue.||From a GUI perspective, the following native tools are available:|
Third Party Considerations
When it comes to third party products the considerations are a little different. Most of these tools have focused on a rich GUI that can be used across the enterprise to perform a specific task or two. These tools provide the ability to make quick configurations that impact all of the SQL Servers in the environment which can be beneficial in some circumstances. Many of these tools also offer a command line interface option which can address the need to automate or customize the interface as needed. For information about 200 tools in the SQL Server industry check out the SQL Server Product Listing.
So what's the bottom line?
As you can see, both of the techniques to interact with SQL Server offer much of the same functionality. The reality is that the devil is in the details in how the task is carried out. Independent of which technique you are using, you really need to understand what you are doing and how it is going to interact with SQL Server prior to executing the process. Here are some additional items to take into consideration as you make a judgment on the best way to interact with SQL Server:
- Starting off as a DBA - I think one of the reasons why SQL Server has gained in popularity with IT Professionals (DBAs, Developers, Network Admins, etc.) is the set of rich applications from Microsoft and third party vendors that are available at a reasonable price to manage and build applications. So why not use them starting off and then start to learn the underlying commands so you are well versed in what the applications have to offer and the flexibility of the underlying commands?
- Options - With SQL Server you have a variety of options to resolve a need. What might work one day might not work the next. What I mean by that is that environments change. When you are in a SQL Server environment with a few instances checking your SQL Server error logs or job status by clicking through the GUI is fine, but as you grow checking each log every morning does not scale well, so adding code to automate this process is essential.
- Comfort level - Figuring out your comfort level is probably going to be your best bet in the long term. I have heard and seen that many people have the impression that DBAs do not know what they are doing if the use the GUI. I think that is incorrect, although I use T-SQL commands more than the GUI, I think the information in this tip shows that using the GUI in a number of circumstances can save time.
I believe that the reality is that all DBAs should know both the T-SQL commands and the SQL Server GUI tools to manage the environment. The 'typers' can learn from the 'clickers' that the GUI does provide some short cuts to address quick needs. The 'clickers' can learn from the 'typers' the power of the underlying commands as they need to scale in their environment. So being a 'clicker' and a 'typer' at the right times make all of the sense in the world. It is just knowing what is right for the circumstance at hand.
- After you read this tip today, take note of your propensity towards being a 'typer' versus a 'clicker' to see if you are missing the boat in what the other technique has to offer.
- If you have always completed a task a particular way, it might make sense to seek out all of the options to complete the task and determine the right tool for the job.
- One appropriate time to conduct this checks and balances of sorts may be when you upgrade to the next version of SQL Server. The new platform may have new options and completing the task in the same manner as you have for previous versions may or may not be superior to how you are completing the task today. So have an open mind, research options and determine the right approach for you and your organization.
Last Updated: 2007-08-16
About the author
View all my tips