Chapters



Aug
12
2013

SQL Server 2012 – Ch.02:D – Registered Servers and Central Management Servers (SSMS 2012)

Babu
Posted 1 years 40 days ago ago by Babu     
  • Currently 0.00/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5
  
 

Ch.02:D – Registered Servers and Central Management Servers (SSMS 2012)

Registered Servers explorer

By default the Registered Servers Explorer appears just above the Object Explorer as shown in the image below. If it’s not visible, you can show the Registered Servers explorer by selecting Registered Servers under the View menu, or by pressing Ctrl+Alt+G.

Registered Servers Explorer - iGnani.com

Registered Servers explorer lists servers which you manage frequently. You can add and remove servers from this list. This area details all SQL Server servers that have been registered on this SSMS installation. Currently only those SQL Server instances that are installed on the computer SSMS are listed. This explorer will also show registered services for Analysis Services and Reporting Services. In case your server does not appear, in Registered Servers list, right-click Database Engine, and then click Update Local Server Registration.

Types of Registered Servers

There are two kinds of registered servers:

Local Server Groups

Use local server groups to easily connect to servers that you frequently manage. Both local and non-local servers are registered into local server groups. Local server groups are unique to each user.

Central Management Servers

Central Management Servers store server registrations in the Central Management Server instead of on the file system. Central Management Servers and subordinate registered servers can be registered only by using Windows Authentication. After a Central Management Server has been registered, its associated registered servers will be automatically displayed.

What are the Benefits of Registered Servers?

Registered Servers provides a lot of benefits as provided in the list below:

  • By registering a server, the connection information is preserved so that it can be used for future connections.
  • Allows us to Determine whether a registered server is running or not without having to check out other tools.
  • Easily connect Query Editor and Object Explorer to a registered server.
  • Allows us to easily Edit or Delete a registered servers registration information.
  • Create, Edit or Delete groups of servers as a server group.
  • Allows us to give user-friendly names for registered servers by providing a value in the registered server name box that is different from the Server name list.
  • Provide detailed descriptions for registered servers and for registered server groups.
  • Export or Import registered server groups.
  • Move a registered server or server group.
  • Connect and Disconnect from a registered server.
  • View the SQL Server log files for online or offline instances of SQL Server.
  • Execute statements against multiple servers simultaneously.

Let us see how to make use of Registered Servers and reap the benefits mentioned above.

How-To Register a Server (SQL Server Management Studio 2012)

By registering a server, the connection information for servers can be saved, which can then be accessed as and when required. A server can be registered before connecting, or at the time of connecting from Object Explorer.

To automatically register a local server instance

In the Registered servers explorer, right-click any node in the tree and then click Update Local Server Registration.

To register a connected server

In the Object Explorer, right-click on a server to which you are already connected, and then click Register. Since the server is already connected, registration details will be prefilled. If you would want to change them or if they are not prefilled, then fill in the information.

  • Server name: Enter the name you want to use for the registered server. Registering a local or remote server using SQL Server Management Studio lets you store the server connection information for future connections. This field defaults to the server name entered when you were connecting to the server. You can retain this server name or enter another easy-to-use name for the server.
  • Server description: Enter an optional description for the server. The maximum number of characters allowed is 250.
  • Save: Click to save the information you have entered and create a registered server.

How-To Create a New Registered Server

New Servers Registration - iGnani.com
  1. In the Registered Servers explorer, right click on either the Local Server groups node or on then name of any Registered Servers Group node, and select New Server Registration. This will display the New Server Registration dialog. Fill in the following information
    • Server type: When a server is registered from Registered Servers explorer, the Server type box is read-only, with the selected item matching the type of server displayed in the Registered Servers pane. To register a different type of server, click Database Engine, Analysis Server, Reporting Services, or Integration Services on the Registered Servers toolbar before starting to register a new server.
    • Server name: Select the server instance to register in the format: <ServerName>[\<InstanceName>].
    • Authentication: As explained in the previous section of the chapter, SQL Server supports two types of Authentication: Windows Authentication and SQL Server Authentication depending on the configuration.
    • User name: Shows the current user name you are connecting with. This read-only option is only available if you have selected to connect using Windows Authentication. To change User names, log in to the computer as a different user.
    • Login: Enter the login to connect with. This option is available only if you have selected to connect using SQL Server Authentication.
    • Password: Enter the password for the login. This option can be edited only if you have selected to connect by using SQL Server Authentication.
    • Remember password: Select to have SQL Server encrypt and store the password you have entered. This option is displayed only if you have selected to connect using SQL Server Authentication.
    • Registered server name: The name you want to appear in Registered Servers. This name does not have to match the Server name box.
    • Registered server description: Enter an optional description of the server.
  2. Test: Click to test the connection to the server selected in Server name.
  3. Save: Click to save the registered server settings.

How-To Create a Server Group (SQL Server Management Studio 2012)

Registered server a group allows us to group registered servers to make the management of registered servers easier. I will explain on how to organize the servers in Registered Servers explorer in SQL Server 2012 by creating Server groups, and placing the servers in the server groups. Server groups can be created in Registered Servers explorer at any time.

To create a server group in Registered Servers, follow the steps below:

  1. In Registered Servers explorer, click the server type on the Registered Servers toolbar.
  2. Right-click a Server or a Server group, select New Server Group.
  3. In the New Server Group dialog box, in the Group name list box, give a name for the server group. The server group name must be unique for the current location in the Registered Servers tree.
  4. You can enter a description if you would like to provide on in the Group description list box.
  5. Click Ok to Save.

How-To Edit a Registered Server's Registration or Server Group

  1. In Registered Servers explorer tree, right-click a server or server group, and then click Properties.
  2. In the Edit Server Registration Properties or Edit Server Group Properties dialog box that shows up, modify the details and then click Save.

How to Remove a Registered Server or Server Group

  1. In Registered Servers explorer, right-click a Server or Server Group in the tree, and then click Delete.
  2. In the Delete Confirmation dialog box, click Yes.

How to move a Registered Server or Server Group

This topic describes how to organize the servers in Registered Servers by moving a registered server or server groups in SQL Server 2012. Server groups can contain registered servers, or other server groups. Both servers and server groups can be moved from one server group to another.

  1. In Registered Servers, right-click a server or server group, and then click Move To.
  2. In the Move Server Registration dialog box, expand the list of server groups, click the node where you want the server or server group to appear, and then click OK.

How to Connect & Disconnect from a Registered Server

  1. To connect to a Registered Server, in Registered Servers explorer, double click on a Server that you wish to connect.
  2. To disconnect from a Registered Server, in the Object Explorer, right-click a server and click Disconnect or in the Object Explorer toolbar click Disconnect.

How to Export & Import Registered Server Information (SSMS 2012)

Exporting and importing Registered Server files lets you easily configure several systems with the same servers in Registered Servers explorer. This is very useful for managing a large number of servers from computers in several locations, or to configure a less experienced user with basic connection settings.

Registered servers explorer allows us to save and export registered server information and distribute it to other employees or servers. Export feature in Registered Servers explorer provides a consistent user interface on multiple systems.

Server registrations that use SQL Server Authentication store passwords on a per-user basis. For such registrations, after importing the server registrations, users will have to enter the password for each server the first time they connect, which will store the passwords in their lists of registered servers. However, servers registered using Windows Authentication does not require this action.

Export Registered Servers - iGnani.com

To Export Registered Server information:

  1. In Registered Servers explorer, right-click on a individual server, a Server group or the Local Server Groups, and then click Export from the Tasks sub menu.
  2. This will display the Export Registered Servers dialog box. You need to provide a few details such as:
    • Server group: From the tree select an individual server, a Server group or the complete tree that has to be exported. Export functionality is recursive; for example, if server group A contains server group B, and server group B contains server groups C and D, exporting server group A exports all entries in A, B, C, and D.
    • Export file: Enter the name of the export file in the text box or use the Browse button (...) to locate an export file either on the client computer or on another computer on the network. The file will be using the .regsrvr extension. If you select an existing file as your export file, the contents of the file are overwritten with the server registration information.
    • Do not include user names and passwords in the export file: will exclude user names when exporting the file. By default it is checked.
  3. Click Ok to export the file.

To Import Registered Server information:

  1. In Registered Servers explorer, select the server type from the toolbar. Make sure the server type is same as the registered server export file type. For example, you must select Database Engine if you have exported Database engine registered server information.
  2. Right-click a Server group and select Tasks > Import.
  3. In the Import Registered Servers dialog box provide the details mentioned below:
    • Import file: Enter the name of the import file in the text box, or click the Browse button (...) to locate the import file. Registered server files have a .regsrvr extension.
    • Select the server group to import to: Select the root node or a particular server group to which the registered server entries in the file will be imported. The import functionality is recursive; for example, if server group A contains server group B, and server group B contains server groups C and D, importing server group A exports all entries in A, B, C, and D.
  4. Click OK when you are done to complete importing.

Server registrations that use SQL Server Authentication store passwords on a per-user basis. For such registrations, after importing the server registrations, users will have to enter the password for each server the first time they connect, which will store the passwords in their lists of registered servers. However, servers registered using Windows Authentication does not require this action.


Leave Your Comments