This article is part of an Essential Guide, our editor-selected collection of our best articles, videos and other content on this topic. Explore more in this guide:
3. - Discover the capabilities of SQL Server native tools: Read more in this section
- Evaluating SQL Server auditing tools: Here's the lowdown
- Six absolutely essential SQL Server management tools
- Ease DB projects with SQL Server Data Tools 2012
- Useful native tools for Microsoft SQL Server
- Exploring SQL Server Management Studio Table Designer capabilities
Explore other sections in this guide:
- 1. - Enhance SQL Server management with essential tools
- 2. - Improve security, high availability, BI with third-party tools
- 4. - Quiz: Native and third-party SQL Server tools
While we SQL Server users complain endlessly about the native SQL Server tools Microsoft provides us, the out-of-the-box tools are pretty impressive. Since the arrival of SQL Server 2012, Microsoft now ships three separate GUI-based tools for SQL Server administrators and developers. The first is SQL Server Management Studio, first introduced in SQL Server 2005. The second is SQL Server Business Intelligence Development Studio, also new to the 2005 release. The third, SQL Server Data Tools, is much newer -- it was included with the release of SQL Server 2012.
SQL Server Management Studio
SQL Server Management Studio (SSMS) was originally going to be called SQL Workbench, but another database vendor already had a management tool by that name -- this is why the file name in SQL Server 2005 was sqlwb.exe. SSMS can be used for database administration and database development. It allows for the administration of not just the SQL Server database engine, but also of the SQL Server Analysis Services engine, the SQL Server Reporting Services engine and the SQL Server Integration Services engine -- all from a single application. On the development side of the product, it can be used to write queries against the SQL Server database engine using Transact-SQL (T-SQL) as well as the SQL Server Analysis Services engine using MDX, DMX or XMLA, depending on the developer’s needs.
For more about Microsoft SQL Server tools
Get up to speed on SQL Server tools fast
Alleviate your documentation headaches with this SQL Server tool
Third-party SQL Server tools you may find useful
SSMS grew from two different tools available in SQL Server 2000 and older editions: Enterprise Manager and Query Analyzer. These older tools were separated into Enterprise Manager for database management and Query Analyzer for database development. Compared with today’s SSMS, these tools didn’t have much functionality. Enterprise Manager was a combination of the Object Explorer pane combined with the Object Explorer Details view of today’s SSMS. Query Analyzer had a very basic object explorer combined with a query window that allowed for running T-SQL statements.
SSMS is more than just a query tool, which is what many people use it for. With it you can run a query against a single server, and you can run queries against multiple servers by selecting a folder from the Registered Servers window and clicking New Query. The query will be executed against all the servers in that folder at once. You can also use the debugger feature -- don’t do this against production servers -- which allows you to step through code run against the server. This is so that variables can be inspected and code paths verified.
SSMS also includes IntelliSense, introduced in SQL Server 2008, which shows a drop-down menu when typing object names, making it easier to ensure that the names are typed correctly. While IntelliSense in SQL Server 2012 isn’t perfect, it’s a lot better than the versions in SQL Server 2008 and SQL Server 2008 R2. There are a variety of object types that aren’t included with IntelliSense such as SQL Service Broker. And it only works well with SELECT statements, and even then only with basic SELECT statements. As the statements grow more complex, IntelliSense can’t figure out all the objects and eventually stops prompting you with column names after typing a table alias.
Business Intelligence Development Studio
Business Intelligence Development Studio, or BIDS, is just what it sounds like; it is a development platform for business intelligence (BI) development. Extract, transform and load (ETL) packages for SQL Server Integration Services, reports for SQL Server Reporting Services or cubes for SQL Server Analysis Services -- BIDS is where all this development happens. It is simply a plug-in for the Visual Studio development environment, allowing for a feature-rich development platform for the BI developer to work in.
When working with SQL Server Integration Service (SSIS), for example, a toolbox is displayed that contains all the objects supported by SSIS. Simply dragging the object from the toolbox onto the design surface allows the developer to quickly and easily design and build powerful ETL processes that can use not just Microsoft SQL Server as the source and destination, but any ODBC-powered database as well as Microsoft Office files and text files.
Reporting Services reports can be built in BIDS just as easily, by dropping report objects onto the reports from the same toolbox and connecting those objects to a query result set.
SQL Server Data Tools
SQL Server Data Tools (SSDT) is the baby of the SQL Server native tools. SSDT, a separate download but available free of charge, is a database developer tool, designed to allow for quick and easy database development in the Visual Studio integrated development environment, which most application developers are used to working in. One of the major features is the table editor, which allows for editing in the visual editor, much like the table editor in SSMS, but it also shows the T-SQL statements that would create the same table. Those T-SQL statements can also be changed and the GUI editor will be updated in real time.
Another great feature of SSDT is the ability to target the SQL Server database engine as well as Windows Azure SQL Database, Microsoft’s cloud database service. The editor allows you to use only the features available in the version you are targeting. For example, if you were connected to an Azure instance, you wouldn’t be able to use the table partitioning syntax. Along with that is native IntelliSense support, which displays only the features for the correct version of the database engine and supports database engine versions back to SQL Server 2005, much further back that the SSMS IntelliSense supports.
Selecting the correct tool depends on the job that you are trying to get done -- SSMS is the tool for administration and either SSMS or SSDT for database development, while BIDS is for BI development. We are lucky that Microsoft has taken the time and spent the money to give us great tools like these. And they are all included in the cost of the product -- known as free.
ABOUT THE AUTHOR
Denny Cherry is an independent consultantwith more than a decade of experience working with SQL Server, Hyper-V, vSphere and storage systems. A Microsoft Certified Master and MVP, Cherry has written on SQL Server management and integration. Check out his blog, SQL Server with Mr. Denny.