For example, in Figure the registered servers are grouped by physical location of the servers—in other words, by continent, country, region, state, and so on. You can create registered servers for all server types. By default, the Registered Server window appears on the top-left corner of Management Studio. You can easily move, hide, or autohide the window to allocate more space to other components.
The Registered Servers window enables you to view and change the state of a server from the registered server. To view the status of a server, look at the icon to the left of to the registered server and match it to Table The state of the service is unknown. The context menu on a registered server see Figure provides the capability to change the state of a service, along with a variety of management options that are covered later in this chapter. Figure The Registered Servers menu provides a variety of management options.
It provides a hierarchical tree view of the objects on a server. The tree displays nodes in folders representing the server's logical structure. Each folder or a node in Object Explorer has a context menu with a variety of options to perform an action on the server.
Figure Object Explorer and its various sub-components. By default, the Object Explorer window appears on the left side of Management Studio, docked under the Registered Server window. You can move, hide, or autohide the window to allocate more space to other components.
There are several ways to quickly show the Object Explorer window. You can press F8 on the keyboard, select View, Object Explorer from the main menu, or click on the Object Explorer button on the main toolbar in Management Studio. Closing the Object Explorer window clicking on the [x] does not disconnect it from the server; it only hides it. To connect Object Explorer to a server, you can right-click on a registered server and choose the option Connect, Object Explorer.
You can also click on Connect in the toolbar of Object Explorer and select one of the server types. All these connection options, with the exception of the registered server, pop up the connection dialog, where you can enter the server name and other connection options. After you are connected, you can expand and collapse the nodes to navigate through the objects and use the context-menu to perform operations on the objects.
To disconnect, click on Disconnect in the toolbar or right-click on the server node and choose the option Disconnect. The context menu of the server node has an option to register the server. This is particularly useful for situations in which you have connected to a server and you want to quickly add it to your registered servers for subsequent connections.
You can use Object Explorer to connect to multiple servers in the same window. The servers could be of the same type or different types, installed locally or remotely.
This is a great productivity feature because you can manage all servers in your organization without launching a separate management tool or opening multiple instances of the same tool for each of the servers.
This is a useful option during upgrade and migration processes, where you can use Object Explorer to manage both SQL Server and SQL Server environments and remove the old tools altogether. In addition to managing objects, Object Explorer can assist you in programming and writing queries. You can optionally send the generated script to a new query window, a file, or the clipboard to subsequently paste into other editors.
In addition, you can drag an object from Object Explorer to a query window rather than typing it. This is extremely useful in a situation in which you have objects with long names or names that are too difficult to spell. You cannot select multiple objects in the Object Explorer window; use the Summary Window instead.
Object Explorer has several productivity features such as filtering. If you plan to work with a large number of objects, consider creating a filter. Filters enable you to view and work with only a subset of objects at a time. The filtering is applied just like a WHERE clause; it restricts the object list to only those objects that meet the filtering criteria.
Let's go through a filtering example and see how it works:. Figure shows a snapshot of tables in the AdventureWorks database prior to filtering.
Figure Tables in AdventureWorks database prior to filtering. Imagine that the objective is to create a filter to display only tables whose names contains the word "Transaction" and that belong to the "Production" schema.
To create the filter, right-click on the Tables folder in Object Explorer and select Filter, Filter Settings in the context menu. In the Object Explorer Filter Settings dialog, enter the filtering criteria to match the objective. For the Name property, pick the operator Contains and enter Transaction for its value. For Schema , pick the operator Equals and enter Production for the value.
The finished filter settings are shown in Figure Figure Object Explorer filter settings for AdventureWorks tables. After you click OK , the Object Explorer displays only two tables that matched the filtering criteria. Notice the word "filtered" is now appended to Tables , in parentheses, indicating that filtering is in effect see Figure To remove the filtering, you can right-click and select the option Filter, Remove Filter.
Figure Tables in AdventureWorks database after filtering. The Object Explorer filter settings are removed and not persisted after you disconnect Object Explorer from the server. Under the hood, Object Explorer uses a component called Enumerator to query the meta-data of objects on a server and displays them in a tree control.
Enumerator interprets the XPath-like query into a metadata query, which is understood by the particular server type. The enumerator fetches the query result from the server and passes it to Object Explorer in XML format.
Object Explorer transforms the XML into the graphic format and appends it to the appropriate node in the tree. The Summary window see Figure works in conjunction with Object Explorer to display additional information about the selected object in Object Explorer. In addition, the Summary window provides an alternative method to Object Explorer for navigating through the objects in a server.
You can find the title and relative location of the current object in the area just under the toolbar. The grid displays the down-level objects relative to the current object. You can double-click on the objects in the grid to further navigate their down-level objects.
To view the up-level node, click on the Up button in the Summary Window toolbar. Navigating through Object Explorer automatically changes the current node in the Summary window, but not vice versa. To change the current node in Object Explorer to the same node in the Summary window, you need to click on the Synchronize button on in the Summary window toolbar. Just as you can with Object Explorer, you can use filtering by clicking on the Filter button in the Summary window toolbar. Figure The Summary window provides an alternative method for viewing objects on the server with additional metadata.
The Summary window provides two views, List and Details , that can be selected from the List button on the toolbar. In Details mode, the Summary window grid displays additional metadata information for the down-level objects. You can sort the metadata information in the grid in ascending or descending order by clicking on the column headers. This metadata information is useful for the times when you want to find the creation date of an object quickly or you need to find objects with the same name in different schemas.
The Summary window performs better than Object Explorer for populations of nodes with many objects. The difference becomes more visible when populating nodes with 1, or more objects. It is recommended that you use the Summary window for navigation if the population of Object Explorer becomes a bottleneck.
Management Dialogs are components of SQL Server Management Studio that provide the functionality to manage objects or perform actions on a server through a graphical user interface. Some of these dialogs are very simple and some are complex. However, they all follow a consistent layout and flow to ensure a low learning curve for the user.
Figure displays a typical management dialog and its various components. You launch most of the management dialogs by selecting an entry on the Object Explorer context menu.
The Object Explorer context menu is carefully designed to provide a link to dialogs and actions that are relevant to the currently selected node. For example, from the Databases folder in Object Explorer you can launch the following management dialogs that are relevant to databases:.
However, a few innovations in SQL Server management dialogs could significantly improve your productivity. These innovations provide a new generation of dialogs that are resizable, non-modal, scriptable, and schedulable. One of the strong bits of feedback from SQL Server customers was related to the shortcomings of management dialogs in Enterprise Manager.
The dialogs were too small and not resizable. New in SQL Server , almost all management dialogs are resizable. You can discover the resizability of a dialog from the size-grip displayed on the bottom-right corner of the dialog. This was extremely frustrating in situations where the user was in the middle of creating an object and wanted to look up properties of another object.
To do this, the user had to cancel the first dialog, which meant losing all the data already entered. Next, the user had to open up the second dialog, look up the information, close the second dialog, and open the first dialog again and re-enter the information. This was a big hindrance to productivity. New in SQL Server , multiple instances of management dialogs can be opened without any stipulation to close the previous ones.
Most management dialogs provide several scripting options that can be accessed from the management dialog toolbar see Figure Figure The management dialog toolbar provides various scripting options.
The scripting options provide a means of generating a script for the action that the dialog is about to perform on the server. The language of the generated script depends on the type of server to which the management dialog is connected. Each new service pack contains all the fixes that are in previous service packs, together with any new fixes.
You do not have to install a previous service pack before you install the latest one. If components are clustered, you must follow the prompts to complete the installation. The SQL Server Express Edition updates are shown when you do an express scan and are available as an automatic update.
EXE also has the ability to reference environment variables which you can list by typing SET at the Command Prompt as long as they have not been defined using the previous two methods.
Through its switches, you can specify which type of server -t S, -t A, or -t C for SQL Server, Analysis Server, or SQL Server Mobile Edition, respectively , server name -S , and database -d you want to connect to, provide authentication information, or designate which queries, projects, or solutions to open -i filename.
EXE - operates as a command line version of Database Tuning Advisor, analyzing performance and providing recommendations regarding its improvements such as the addition or removal of indexes, indexed views, or partitions.
You have an option of running it on the same server you analyze or a separate one -R switch in order to offload the processing burden. Connection settings are specified using the same switches as the ones present in the previously described utilities.
Business Intelligence Development Studio Primarily a development utility, this program is intended for creating Business Intelligence solutions, integrating projects involving Analysis Services, Reporting Services, and Data Transformation Services.
Acceptable Use Policy. Advertiser Disclosure:. SQL Etc. Database Forum. Rss Feed. MS Access. Free Newsletters: Email subscribe. Advertiser Disclosure.
0コメント