Guide to SQL Server data management and data quality
A comprehensive collection of articles, videos and more, hand-picked by our editors
Management tools play a vital role in enterprise database management. This is because the well-integrated tools extend the administrator's capabilities, whereas a random collection of tools can lead to confusion, operational mistakes, high training costs and poor database administrator (DBA) productivity.
To keep up with these new demands of complex enterprise database management solutions, Microsoft ships SQL Server software with a broad group of several graphical management tools that help DBAs and developers efficiently create, manage and maintain SQL Server solutions, and allow them to quickly resolve complex performance and configuration problems. In this article, we will discuss an overview of the key features of these graphical SQL Server 2012 management tools.
SQL Server Management Studio
SQL Server Management Studio, or simply SSMS, is an integrated environment for most administrative tasks that support registering multiple SQL Servers into a single SSMS console, which will help you centrally manage multiple SQL Servers in an IT shop. For example, you can use SSMS to manage SQL Server Services such as Database Engine, SQL Server Integration Services, SQL Server Reporting Services and SQL Server Analysis Services, as well as databases on multiple servers. SSMS has several wizards that guide database administrators and developers through the process to perform various administrative tasks, such as DDL and DML operations, security server configuration management, and backup and maintenance. SSMS also provides a rich editing environment for authoring and editing Transact-SQL, MDX, DMX and XML/A scripts. In addition, you can also generate scripts based on the actions that you want to perform. SSMS also has Template Explorer, which provides a rich set of templates with the ability to create custom templates. Furthermore, SSMS also includes support for sqlcmd scripts, support for viewing XML results, and support for writing and editing scripts or queries without requiring a SQL Server connection. Versions of SSMS from 2008 onward also include the Transact-SQL debugger, IntelliSense support, and integrated source control for solution and script projects, storing and maintaining copies of scripts that have evolved over time.
SSMS also provides you access to SQL Server Surface Area Configuration and Activity Monitor. You can use SQL Server Surface Area Configuration to enable and disable SQL Server database engine features, whereas you can use Activity Monitor to view the information about current processes and discover which SQL Server resources are currently being used.
Key windows of SSMS include: Object Explorer, Object Explorer Details, Object Search, Solution Explorer and Database Engine Query.
SQL Server Configuration Manager
SQL Server Configuration Manager is a tool that you can use to manage the services associated with SQL Server, including configuring and managing installed client and server communication protocols used by SQL Server and Client computers. You can use SQL Server Configuration Manager to modify the dump directory (the location where SQL Server creates memory dumps if an error occurs), SQL Services start-up parameters, master database files and the ErrorLog location. SQL Server Configuration is a Microsoft Management Console (MMC) snap-in that can be launched using Start menu. For example, you can launch SQL Server 2012 Configuration as follows: Click Start, choose All Programs , navigate to Microsoft SQL Server 2012 and then select SQL Server Configuration Manager, under Configuration Tools.
Alternatively, you can launch it from any other MMC display. Microsoft Management Console (mmc.exe) uses the SQLServerManager11.msc file in the Windows System32 folder to open the SQL Server Configuration Manager.
SQL Server Profiler
More information on SQL Server 2012 management
Some risky SQL Server management assumptions
SQL Server backup and restore management tutorial
Microsoft SQL Server Profiler is a graphical user interface that captures SQL Server or Analysis Services activities depending on the selected events. SQL Server Profiler captures the events as a trace data, which can be saved to a local file or network file, or inside a SQL Server table. SQL Server Profiler includes a number of predefined templates that meet most common trace-capture scenarios. You use the data captured by SQL Profiler for analysis, testing and troubleshooting purposes. For example, you can replay and examine the trace files to troubleshoot a problem or, alternatively, you can compare the trace files against a Windows Performance log file to determine the database events that were occurring during a peak in resource use. You can also create an audit trail for table activity.
Database Engine Tuning Advisor Wizard
Database Engine Tuning Advisor, or simply SQL Server DTA, is a tool that lets you determine the optimal database indexes, indexed view and partitions without requiring the in-depth knowledge of the database structure or internals of SQL Server. You can create Transact-SQL scripts as workloads with the Query Editor in SSMS and trace file and table workloads using Tuning Templates in SQL Server Profiler. You can then load and analyze specially created trace files to make recommendations about indexes and other changes that might help to improve query performance. You can also use SQL Server DTA to evaluate individual queries. You can start the SQL Server DTA from within a query window in SSMS to determine the impact of ad hoc queries.
Sqlcmd command-line tool
The sqlcmd tool is a command-line utility used to execute Transact-SQL statements, stored procedures and script files. The sqlcmd utility establishes an Open Database Connectivity (ODBC) connection to the database to execute Transact-SQL batches. The results of the Transact-SQL commands are displayed in the command prompt window. You can use sqlcmd utility to point to a script file that contains multiple Transact-SQL scripts or statements.
SQL Server PowerShell
SQL Server PowerShell provider is a powerful scripting shell that allows DBAs, administrators and developers to automate server management and deployment. PowerShell language supports more complex logic than Transact-SQL scripts, giving DBAs the ability to create more robust administration scripts.