SQL Server 2005 Administrative Tools
Using SQL Sever Management Studio
A Database Administrator typically grapples with tasks relating to accessing, configuring and administrating the components of the SQL Server. To assist the Administrator in this task, SQL Server 2005 provides a graphical user interface called the SQL Server Management Studio. The SQL Server Management Studio combines graphical user interface with a rich script editor, so that administrators can script and administrate from a single window.
The SQL Server Management studio harnesses the power of the Enterprise manager and Query Analyzer of the earlier versions and also provides a single window to the other functionalities such as Analysis Services, Integration Services, Reporting Services and XQuery. The features of the Management Studio can be listed as under:
1. Supports administrative tasks for SQL Server 2005 and 2000.
2. Provides an integrated environment for Database Engine management and authoring
3. Provides access to SQL Server database engine, Analysis Services, Reporting Services, Notification Services, SQL Server Mobile and the script editor.
4. The dialog boxes are non modal and hence user is able to access multiple tools simultaneously.
5. The scheduling dialog box allows users schedule management tasks as per convenience.
6. Server registrations can be exported or imported from one Management Studio to another.
7. XML Showplan or Deadlock files generated by the SQL Server Profiler can be saved or printed for review.
8. The error informational message box facilitates user comments on error messages. Error messages can be copied to clipboard and emailed to the support team at Microsoft or within the organization.
9. The integrated web browser enables ease of access to MSDN and online help.
10. The Help system is also integrated with MyHelp and online communities.
11. Server tutorials help users hit the ground running when deploying the server for the first time!
12. The management studio contains a activity monitor and an automatic refresh which is useful.
13. It also has integrated database mail interfaces.
14. The code editor component of SQL Server Management Studio contains several integrated script editors that help in authoring T-SQL, MDX, DMX, XML/A and XML scripts. The rich set of templates also can be supplemented with custom templates. Queries can be written without a connection to the server and XML results can be viewed. Versions of scripts can be maintained and the whole is supported by IntelliSense.
Starting SQL Server Management Studio
When SQL Server 2005 is installed, Programs are added to the Start Menu. Click on Start> All Programs>Microsoft SQL Server CTP> SQL Server Management studio.
The first task that the user needs to undertake is connecting to the server. The Connect to Server dialog box appears and the users are prompted to select the server and the authentication mode. If windows authentication has been selected then, click connect to start the server, else select the authentication mode and the server and click connect to connect to the server.
As soon as the server connection is established the user is within the SQL Server Management Studio interface. Note that the window contains several parts. The tool bar and Menu Bar which give access to various options; the Registered servers window which gives details of registered servers on the system; an object explorer window and a summary window. We shall examine what each of these windows offer to the database administrator as we proceed with this tutorial series.
The Object Explorer
The Object Explorer is an integrated tool for viewing and managing objects in all server types. Objects can be viewed by their type or schema; they can be filtered by all or part of a name, schema or date; they can be asynchronously populated and filtered based on their metadata; SQL Server Agent on replication servers can be accessed and administered. Most of the objects in the object explorer are there for the use of the developer or the administrator. Some of these tools can be used with all server components while others can be used with specific components only.
The Object Explorer itself can be used to browse servers, create and locate objects, manage data sources and view logs. Specific editor windows connected to databases can be opened from the object explorer. Servers can be registered or registered servers can be managed for storing connection information. The Solution explorer stores and organizes scripts and related connection information called SQL Server Scripts. The SQL Server Templates can be used to create queries based on existing templates or create custom queries. Finally the Dynamic help displays a list of help topics when a component type or code is clicked.
The Object Explorer window is visible in the Management Studio interface by default. If it is not visible, click on the view menu to activate it.
A precondition to the use of the Object Explorer is that it must be connected to a server. To connect to a server click on connect in Object Explorer toolbar and choose the server from the drop down list.
Click on Database Engine… and the Connect to server dialog box opens. Select the server from the drop down list. Since there is only one registered server only one is displayed. However, the drop down list has an option ‘Browse for servers…’. On clicking the option another dialog box opens to display the servers available on the system. The server can be selected from the displayed list if required.
A number of optional settings are also available in the Object Explorer. If additional settings are specified while connecting to the server through the Connect to Server dialog box, the last used settings will be saved and any new connections made thereafter will use the settings as default. Optional connection settings can be specified as under:
* Click Connect on the Object Explorer toolbar, and click the type of server to connect to. The Connect to Server dialog box appears.
* In the Server Name box, type the name of your SQL Server instance.
* Click Options. The Connect to Server dialog box displays additional options.
* Click the Connection Properties tab to configure the additional settings. The settings that are available vary depending upon the server type. The following settings are available for the Database Engine.
The Connect to Database allows users choose from the available set of databases on the server. The user must have the permission to view the database, else it will not be displayed. The Network protocol can be selected from shared memory, TCP/IP or Named Pipes. The Network Packet size is configured in bytes. By default this is set at 4096. Connection timeout is configured in seconds and the default figure is 15 seconds. The Execution timeout is also configured in seconds and the default is 0. If Encrypt connection is checked the encryption is enforced.
Servers can be registered from the Object Explorer. Right click server name and then click Register.
The Register server dialog box opens. In the dialog box specify the server and indicate where it has to be located in the group tree. The server name can also be replaced with a more meaningful server name.
New server groups can be created by clicking on New Group… button in the Register server dialog box illustrated above. Another dialog box opens asking the user enter a name and description for the group.
The object explorer window displays a tree view menu. The items in the menu can be right clicked to perform common tasks. The objects can be double clicked to expand them and display their nodes. Clicking on the (-) will collapse the tree. The population of the nodes can be stopped by clicking on Stop. The list of nodes displayed can be filtered.
It is to be pointed out that the contents of the tree do not get automatically refreshed. To refresh a view right click the object and click on refresh.
By default the object browser can display 65,536 objects. When the number of objects exceeds the limit, the user will have to close some of the nodes or apply filtering to view the objects that lie beyond the default number of objects that can be displayed.
The filter feature of the object explorer is intended to help the user filter the number of objects on some predefined criteria.
1. Click on the folder to be filtered and then click the filter button to open the filter settings dialog box.
2. In the filter settings dialog box filters can be set by Name, creation date, and schema and additional filtering operators like ‘starts with’, ‘contains’, ‘Between’ can be set.
Normally, it is possible to select only one object at a time in Object Explorer. Multiple items can be selected if the user presses F7 and opens Summary Page.
A connected query editor can be opened from the object browser by right clicking the server name in the Object Explorer and clicking New Query. To open a query editor for a specific database, the database name has to be right clicked and New Query will have to be clicked. For Analysis Services query the user can select DMX, MDX or XMLA queries.
