This content is part of the Essential Guide: Guide to SQL Server data management and data quality
Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Six SQL Server management tools you can't live without

Expert Ashish Kumar Mehta lists six important SQL Server management tools, including BIDS, SSMS and SSDT.

Microsoft SQL Server comes with easy-to-use tools to make the daily life of a database administrator and developer easier. This article will give you an overview of some of the most important SQL Server management tools that someone can use while working with SQL Server.

Business Intelligence Development Studio (BIDS)

Microsoft introduced Business Intelligence Development Studio (BIDS) in SQL Server 2005. BIDS is a SQL Server management tool built to help developers who are using SQL Server Integration Services, Reporting Services and Analysis Services. BIDS includes project templates to create cubes, reports and Integration Services packages. In BIDS, developers can create projects that include facets from Integration Services, Analysis Services and Reporting Services. BIDS can deploy the objects to test servers and then deploy the output of the projects to production servers. BIDS is nothing but Microsoft Visual Studio along with project templates specific to SQL Server Business Intelligence.

SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) is a replacement for SQL Server BIDS, which was available in the previous versions of SQL Server prior to the release of SQL Server 2012. This SQL Server management tool has all the features of BIDS and has certain new enhancements such as:

  • The Data Compare feature, which allows you to compare and synchronize data between two databases.
  • Support for SQL Server Unit Testing, which allows one to generate unit tests for SQL Server functions, triggers and stored procedures.
  • Object Explorer, which can create, edit, delete, can rename tables, functions, triggers and stored procedures and can even perform certain level of database administration tasks.

SQL Server Management Studio

Microsoft first introduced SQL Server Management Studio (SSMS) in SQL Server 2005. Database developers use this SQL Server management tool to develop T-SQL queries; create objects such as tables, indexes, constraints, stored procedures, functions and triggers; and debug T-SQL code. At the same time, database administrators use SSMS to perform maintenance tasks, such as index rebuild, index recognize, backup and restore, and security management. You can even create various scripts for analysis services and manage SQL Server Database Engine, SQL Server Integration Services and Reporting Services.

Reporting Services Configuration Manager

Reporting Services Configuration Manager can create and change settings for the Report Server and Report Manager. If you have installed SQL Server Reporting Services in install-only mode, then after the installation you will have to use Reporting Services Configuration Manager to configure the report server for native mode. If you have installed Report Server using the install-and-configure option, then you can use this SQL Server management tool to verify and modify the existing settings. The tool can configure a local or remote report server instance, and can configure the service account in which the report server service should be run. You can configure Web Service and Report Manger URLs, as well as create, configure and manage Report Server Databases such as ReportServer and ReportServerTempDB databases. Other features include:

  • Configuring email settings on the report server to send out reports as an email attachment.
  • Configuring the unattended execution account so that it can be used for remote connections during scheduled operations during scenarios when user credentials are not available.
  • Backup and restore or replace the symmetric key that is used to encrypt connection stings and credentials.

SQL Server Configuration Manager

SQL Server Configuration Manager can be used to manage all SQL Server services. This SQL Server tool can network protocols such as shared memory, named pipes and TCP/IP. Configuration Manager can also manage network connectivity configuration from SQL Server client machines. It is always advised to start, stop, pause and resume all SQL Services using this tool. As a best practice, one should always change the service account or passwords using the SQL Server Configuration tool.

SQL Server Installation Center

Once you install SQL Server on your machine, you end up seeing this tool under Configuration Tools. This is a very handy SQL Server tool for DBAs and developers, as you can see important SQL Server resources. It also mentions different installation options which are available in SQL Server, as well as options for installation.

Next Steps

Free SQL Server tools you need now

Dig Deeper on Microsoft SQL Server Tools and Utilities