SQL Server 2012 : SQL Server Management and Development Tools - Registered Servers

9/28/2013 7:34:46 PM

Registered Servers is an optional built-in feature; if you manage only one or a few SQL Servers, Registered Servers offers little benefit. If, however, you are responsible for many SQL Servers, or if you simply want an organized way to manage your registered instances, this is the right place to take control.

Using Registered Servers, you can maintain connection information for connections to the Database Engine, Analysis Services, Reporting Services, SQL Server Mobile Edition Databases, and Integration Services. The toolbar at the top of Registered Services enables selection among the types of services.

Managing Servers

Servers are easily registered using the context menu and supplying the Properties page with the server name, authentication, and maybe the connection information. One key benefit of registering a server is that it can be given an alias, or Registered Server Name, in the Properties page, which is great if you're a DBA managing dozens of servers or instances with cryptic server names.

After a server is registered, you can easily select a server, and using the context menu, connect to it with Object Explorer or Query Editor. Although this is a good thing, the server aliases don't propagate to the Object Explorer, which can lead to confusion. The workaround is to keep the Object Explorer free from all other connections except those currently in use.

You can also use the Server context menu, as shown in Figure 1, to connect to the server with Object Explorer or Query Editor, or apply Policies. Other tasks include starting and stopping the service, and opening the registration's Properties page.

To share your registered server list, or move from one SSMS installation to another, export and import the server configurations using the Context menu ? Tasks ? Import/Export.

Figure 1 Registered Servers is the tool used to manage multiple servers. Here, we see the context menu showing how to manage service control remotely on your servers.


Server Groups

Within the Registered Servers tree, servers may be organized by server groups. This not only organizes the servers, but enables new group actions as well.

  • Local Server Groups: Stores the connection information in the local file system. Think of these as Management Studio groups. The tree in Registered Servers flows from the service (i.e. Database Engine), to Local Server Groups, to Server Group (if you've created any), and finally to the registered server itself. Registering servers locally allows you to register servers using both Windows and SQL Server authentication.
  • Central Management Server: Central Management Server functions just like Local Server Group registrations except that the connection information for registered servers is stored in the msdb database of the designated Central Management Server. The other notable difference is that server registrations under a Central Management Server can only use Windows authentication to register.

The server group (local or Central Management Servers) context menu includes the same Object Explorer, Query Editor, and Policy commands as registered servers. When a query/command/policy is executed at the root level of the registered servers, they apply to all servers and groups below it. However, when these commands are executed from a specific group, they apply only to all servers in the group or groups nested below the selected group.

  • Object Explorer: Opens Object Explorer and connects to every server in the Server Group or groups nested below it.
  • New Query: Opens a new query window with a connection to the group instead of a connection to the server. T-SQL commands can then be submitted to every server simultaneously. The Query Editor merges (or “unions”) the results from every server and adds two columns, server name and login, to indicate which server returned each row as seen in Figure 2. The columns and whether results are merged or returned in separate result sets can be configured in Tools ? Options ? Query Results ? SQL Server ? MultiServer Results, or Query Editor context menu ? Results ? MultiServer. Messages now include the server name and login with each message.
  • Policy-Based Management Policies: May be applied to every server in the group or groups.

Figure 2 Multi-server results in Management Studio.

  •  SQL Server 2012 : SQL Server Management and Development Tools - Organizing the Interface
  •  SQL Server 2012 : SQL Server Private Cloud - Upgrading SQL Server
  •  SQL Server 2012 : SQL Server Private Cloud - Discovering SQL Server Sprawl
  •  SQL Server 2012 : Storage Systems (part 7) - Measuring Performance - Storage Performance Testing
  •  SQL Server 2012 : Storage Systems (part 6) - Measuring Performance - Sequential Disk Access, File Layout, Flash Storage
  •  SQL Server 2012 : Storage Systems (part 5) - Measuring Performance - Storage Performance Counters, Disk Drive Performance
  •  SQL Server 2012 : Storage Systems (part 4) - Storage Technology - Remote Data Replication, Windows Failover Clustering, SQL Server AlwaysOn Availability Groups
  •  SQL Server 2012 : Storage Systems (part 3) - Storage Technology - Storage Tiering, Data Replication
  •  SQL Server 2012 : Storage Systems (part 2) - Storage Technology - SQL Server and the Windows I/O Subsystem
  •  SQL Server 2012 : Storage Systems (part 1) - Storage Technology
    Most View
    Spring Is Here (Part 2)
    Is 802.11ac Worth Adopting?
    BlackBerry Z10 - A Touchscreen-Based Smartphone (Part 1)
    LG Intuition Review - Skirts The Line Between Smartphone And Tablet (Part 5)
    Fujifilm X-E1 - A Retro Camera That Inspires (Part 4)
    My SQL : Replication for High Availability - Procedures (part 6) - Slave Promotion - A revised method for promoting a slave
    10 Contenders For The 'Ultimate Protector' Crown (Part 3) : Eset Smart Security 6, Kaspersky Internet Security 2013, Zonealarm Internet Security 2013
    HTC Desire C - Does It Have Anything Good?
    Windows Phone 7 : Understanding Matrix Transformations (part 2) - Applying Multiple Transformations
    How To Lock Windows By Image Password
    - First look: Apple Watch

    - 10 Amazing Tools You Should Be Using with Dropbox
    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

    - How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
    Top 10
    OPEL MERIVA : Making a grand entrance
    FORD MONDEO 2.0 ECOBOOST : Modern Mondeo
    BMW 650i COUPE : Sexy retooling of BMW's 6-series
    BMW 120d; M135i - Finely tuned
    PHP Tutorials : Storing Images in MySQL with PHP (part 2) - Creating the HTML, Inserting the Image into MySQL
    PHP Tutorials : Storing Images in MySQL with PHP (part 1) - Why store binary files in MySQL using PHP?
    Java Tutorials : Nested For Loop (part 2) - Program to create a Two-Dimensional Array
    Java Tutorials : Nested For Loop (part 1)
    C# Tutorial: Reading and Writing XML Files (part 2) - Reading XML Files
    C# Tutorial: Reading and Writing XML Files (part 1) - Writing XML Files