SQL Server offers several tools to efficiently manage databases. The Management Studio is a very useful tool for creating and maintaining the SQL server database objects. The SQL Server Business Intellegent Development Studio is used for creating and managing the BI solutions. There are several other tools provided by the SQL Server which include Database Engine Tuning Advisor and SQL Server Configuration Manager. These tools help the database administrators for server configuration and performance optimization.
5.1 SQL Server Management Studio
SQL Server Management Studio is the tool that is associated with the Server. It gives user an easy and integrated environment for creating and managing SQL Server database objects. It contains several components such as query editor, solution explorer and object explorer. The user can create and execute the queries. The user can view the execution plans in several formats.
The following image shows the SQL Server Management Studio.
The following table lists the main components of the SQL Server Management Studio.
It is a window that provides the ability to register, browse and manage servers. The explorer allows to configure the following components:
1) Security: It is used to create login users, and to assign permissions
2) Replication: It is used to create and manage publishers and subscribers
3) SQL Server Agent: It is used to automate administrative tasks by creating and managing jobs, alerts and operators
4) Management: It is used to configure the distributed transaction Coordinator, Database Mail Service, or SQL Server logs.
5) Server Objects: It is used to create and manage backups, endpoints, and triggers
Object Explorer Details
It is used to provide the detailed description of all the objects in the SQL Server
The window is used to display all the servers registered within the management studio. It also helps to record the
It is the window that provides the organized view of your projects and files
It is the window that provides the ability to execute the queries written in T- SQL.
It is used to provide the set of templates of SQL queries to perform the standard database operations
The window is available from the Help menu of the server.
5.2 SQL Server Business Intelligent Development Studio
SQL Server Business Intelligent Development Studio is provided by the Visual Studio. It consists of an environment for developing business intelligent solutions. They are created by the data generated in the organization. The data helps in making plans and decisions.
The steps for locating the SQL Server Business Development Studio are as follows:
1) Click ‘Start’, select ‘Programs’, Click ‘Microsoft SQL Server’.
2) Select ‘SQL Server Business Intelligent Development Studio’ from the list.
3) The studio window will open as shown in the following figure.
They following solutions are provided by the Studio:
1) Data Integration: The integration services helps to build solutions and integrate data from different sources and store them in the common warehouse.
The following snapshot shows the window for the business intelligent projects. 2) Data analysis: The analysis services helps to analyze the data present in the warehouse.
The following image shows the analysis service added in the SQL Server.
3) Data reporting: The reporting service allows the user to build reports in various formats.
The following snapshot demonstrates the data reporting in the application.
5.3 Database Engine Tuning Advisor
The Database Engine Tuning Advisor helps the administrator to analyze the data and modify the server performance. The administrator executes the T – SQL statements. The tool provides the suggestions to add, remove, or modify the database objects.
The image to demonstrate the tuning function is as shown below:
5.4 SQL Server Configuration Manager
The SQL Server Configuration Manager is used by the database administrator for managing the SQL Server services. These services can be SQL Server Agent, SQL Server Reporting services, SQL Server Integration Services, SQL Server Analysis Services. The administrator can perform the respective operations using the tool.
The tool helps user for managing network connectivity configuration in the client computer of the SQL Server.
The following image demonstrates the SQL Server Configuration Manager.
5.5 SQL Server Profiler
SQL Server profiler is useful for administrating events as connections, processing of the DML statements, stored procedures and checking the security permissions. The permissions are created in the database engine instance. The depth view of the query submission is provided. The output is generated after the objects are processed.
The database can be modified and the result is modifications on several database objects are chekced by the user.
The following snippet demonstrates the SQL Server Profiler.