|
behrman
Yak Posting Veteran
76 Posts |
 Posted - 2009-12-24 : 10:22:51
|
| 10 things you should know about SQL Server 2005 Management StudioTakeaway: SQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. Check out the improvements in SQL Server Management Studio, which makes it easier to locate errors in your code and allows you to keep tabs on your reports.By Arthur Fuller and Stephen GilesMicrosoft SQL Server 2005 contains a number of major new features, but the feature that we like the most is the SQL Server Management Studio. This tool is leaps and bounds beyond what was available in earlier versions of SQL Server. Here is a list of the 10 things that we find most useful about SQL Server Management Studio.1. Combines the best features of many toolsIn earlier versions of SQL Server, you had two main tools: a graphical administration tool (Enterprise Manager) and a Transact SQL Editor (Query Analyzer). The problem with this split is that we do development and administration on SQL Server (sometimes at the same time) and often have to flip back and forth between the two. In SQL Server Management Studio, the Enterprise Manager and Query Analyzer are combined into one common interface, allowing you to manage your servers graphically and to write Transact SQL.SQL Server Management Studio also allows you to access an Object Browser for all registered servers, which combines the features of the Object Browser from Query Analyzer with the Server tree view from Enterprise Manager. In addition, it provides a workspace similar to Query Analyzer, with the expected tools like the Language Parser and the Graphical Show plan. Now you can write queries and scripts and manipulate objects with Wizards and Property sheets in the same tool at the same time.SQL Server Management Studio's interface has a separate Registered Servers view that allows you to work with multiple servers at the same time. You can do this in Enterprise Manager; however, SQL Server Management Studio allows you to register server instances as well as all Analysis Services, Reporting Services, SQL Server Integration Services, and Mobile SQL instances. Thus, you can obtain an enterprise view or concentrate on the particular instances and objects of interest.2. Work with projects and solutionsIf you have worked with Visual Studio, then you are familiar with the concept of projects and solutions. In a nutshell, projects allow you to group files together and access them as a unit. A solution is a series of projects, enabling you to drill down to projects just as OLAP users can drill down to the data dimension of interest.A project can contain .sql, .mdx, .xmla, and .dmx scripts. You can also add other files (such as XML or CSV files) to a project. Therefore, the project itself is a drill-down object.To create a new project, follow these steps:1. Click File | New | Project. 2. Choose the type of project you want to create (SQL Server Scripts, Analysis Services Scripts, or SQL Mobile Scripts). 3. Give your project and solution a name. 4. Select the path where you want to store the files. 5. Click OK.Now you can define various data sources (if your project touches more than one database) and add files effortlessly (simply right-click the Scripts folder in the Solution Explorer and select the items to add). You can also import scripts into a project if you have done some work already.(If you don't see the Solution Explorer in your SQL Server Management Studio, select View | Solution Explorer or hit [Ctrl][Alt]L.)3. The tool is a data analyst's best friendThanks to the integration of the OLAP tools, SQL Server Management Studio is a great tool for working with your cubes. The object browser allows you to access Analysis Services objects to graphically manage your cubes. It also lets you write and execute MDX and DMX and XMLA statements from within the editor window, allowing you to run both OLTP and OLAP queries from the same tool and even from within the same project.4. You can display line numbersHow many times within Query Analyzer have you had to walk down the lines to count up to the line flagged with an error? With SQL Server Management Studio, you can display line numbers in the code editor, which is extremely helpful when you're parsing code to locate the problem line and inevitable typos.This feature is not turned on by default. Here's how to enable line numbers:1. Select Tool | Options. 2. Expand Text Editor in the Options Tree and select All Languages. 3. In the property pane on the right, select the Line Numbers check box under the Display heading. There is one "feature" with line numbering. If you have batch statements in your script (Begin...End or Go statements), the results page will recalculate line numbering within the block (i.e., it will start counting from line 1).5. It's easier to find errorsSQL Server Management Studio has retained one of our favorite features of Query Analysis: linking to errors in the body of your script from the error message in the Message pane. Note that the line number referenced in the error message may not correspond to the line numbering if a script contains multiple batches. You can, however, find the line causing the error simply by double-clicking the error (the red text) in the Message pane. This action will highlight the offending line in the body of the script. (You may want to use the pre-parse function with this feature to clean up all syntax errors before running a script.)6. Get started faster with an expanded Template ExplorerTransact SQL is the language of SQL Server, and (as with other versions of SQL Server) you can perform all tasks from queries to object creation through Transact SQL. We like scripting objects primarily because it allows us to have absolute control over what we create, and we can save scripts to document objects and move them easily from a test to a production environment.However, new features mean new syntax, and thus much more to remember. To make life easier, the SQL Server Management Studio includes an updated Template Explorer (View | Template Explorer or [Ctrl][Alt]T) that lays out the structure of more than 100 objects and tasks in Transact SQL, including administrative tasks like backing up and restoring databases.Due to the integration of formerly disparate tools, the Template Explorer now includes templates for both Analysis Services and SQL Mobile commands. This means that you can drive DMX, MDX, and XMLA expressions through script templates the same way you could create objects in earlier versions of Query Analyzer.7. It (sort of) plays well with previous versionsSQL Server Management Studio can run through the SQL Server Distributed Management Objects (DMO) as well as the SQL Management Objects (SMO), which are the preferred management objects for SQL Server 2005. This means that you can administer SQL Server 2000 and MSDE databases using the SQL Server Management Studio. This allows you to keep your databases in previous versions while administering them from SQL Server 2005. One caveat is that SQL Server Management Studio's version of DMO will not allow you to administer SQL Server 7.0 servers. You should consider this yet another good reason to upgrade.8. Name that registered serverWhen registering our databases (both in Enterprise Manager and SQL Server Management Studio), we like to use an IP address rather than a server name; this approach facilitates remote connections across a VPN, and name resolution can sometimes be a problem. In Enterprise Manager, we had to remember the IP address for each particular server for which we work. For this purpose, we kept a text file listing all the connection parameters.In SQL Server Management Studio, you can register by IP address but still give the computer a more recognizable name and even add a description of the server. The name and description will show up on the Registered Servers pane (View | Registered Servers or press [Ctrl][Alt]G), so you always know which server you are working on.9. Manage your SQL Server Integration Services pagesMicrosoft intended for SQL Server Management Studio to enable you to manage all services in one consistent UI, which is the case with the SQL Server Integration Services (SSIS). SSIS is the replacement for DTS in earlier versions of SQL Server and is also utilized by the new Maintenance Plan Wizard.In SQL Server Management Studio, you can view all packages on a server and see which packages are currently running. You can also Import and Export packages using this tool (which is something that was not easy to do in earlier versions of SQL Server), and run packages from within the SQL Server Management Studio. To access SSIS through SQL Server Management Studio, follow these steps:1. Register a server through the Integration Services tab in Registered Servers. 2. Right-click the Server and select Connect | Object Explorer. 3. Manage your packages through the tree that appears in the Object Explorer. 10. Keep tabs on your reportsAs with Analysis Services and SSIS, you can use SQL Server Management Studio to manage your Reporting Services. In the SQL Server 2000 version of Reporting Services, all administration was carried out through a Web-based administrator that was installed as part of Reporting Services. If you managed several report servers, you had to manage each one through separate admin sites.In SQL Server Management Studio, you can register all of your Reporting Services and administer them through the Object Explorer. You can also perform all tasks that were available on the Reporting Services admin site through the Object Explorer.To access Reporting Services through SQL Server Management Studio, follow these steps:1. Register a server through the Reporting Services tab in Registered Servers. 2. Right-click the Server and select Connect | Object Explorer. 3. Manage your packages through the tree that appears in the Object Explorer.ConclusionSQL Server 2005 Management Studio is a phenomenal advance over the tools provided with SQL Server 2000. We have gone so far as to remove the SQL Server 2000 tools, and we now do everything in SQL Server 2005. This version of SQL Server provides great leaps forward plus backward compatibility, which in our book is a winning combination.RAQ Report: Web-based Excel-like Java reporting tool |
|
10 things about SQL Server 2005 Management Studio