Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server 2005 Import/Export Server Registrations

By Vinod Devasia,

Introducing SQL Server 2005 Import/Export Registered Server files.

By Vinod Devasia
MCDBA, Mcp+IIS, Mcsd, Mcse, Ocp
WebAdministrator, www.HumNRI.com

In earlier versions of SQL server, it was very tedious trying to register each and every server that you have on the network into the SQL enterprise manager. You would have to do register them one at a time manually. And the process need to be repeated for each and every machine where the Enterprise need be installed and the server accessed.

In short, if you had 50 servers, and 10 DBAs, you will have to register the 50 servers 10 times for each DBAs… that’s is 50x10 times. Thats a lot of work.

With SQL 2005, a new feature is introduced called the Exporting & Importing Registered Server file.

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

In this session, we will learn how to do this;

First we will create a Server Group

On machine one, open the SQL Server Management Studio.

Right click on Database Engine, and select New, and then Server Groups



Type in the Server Group name as Developers, and click save



You should see this group now in the Registered Servers window



Start registering each server into the group as follows;

  1. Right-click on the Register Server name, in our case “Developer”, point to New, and then click Server Registration. The New Server Registration dialog box opens.



  2. In the Server name text box, type the name of your SQL Server instance. In the Registered server name box, type the name of the server name



You will notice the name displayed in the registered servers under the developer server group..



Repeat the same step to register all the servers under this group…



Now we are ready to export this into a registration file…

Right click the server group and select “Export”



Select the SQL server group and type in the name of the file where you want this to be exported, and click ok



Note : I suggest ensuring that the check box “Do not include username and password in the export file” is selected. Although the export files are encrypted, if user names and SQL Server Authentication passwords are included in the file, access to the file should be carefully controlled. User names and passwords are therefore excluded from the export file by default.

Click ok



check the directory, to find the file



Now lets import this to our new group…

For our test, first we will just delete the existing server group, by right clicking the group name, and selecting delete.



click yes, to confirm delete.



Right click on “Database Engine” and select “Import’



select the filename, as shown below;



click “ok”

and voila!!



To register these servers on any other machine, all we need to do is to import this file.

Total article views: 8820 | Views in the last 30 days: 4
 
Related Articles
FORUM

SSMS 2008 Export/Import of Aliases and Registered Servers can fail

SSMS 2008 Export/Import of Aliases and Registered Servers, which can fail

BLOG

Bug in SQL Server 2014 SQLPS For Registered Servers/Groups

I recently had an opportunity to write an article for mssqltips.com on managing registered servers u...

FORUM

Export option (right click in the database - tasks - export data...)

Export option (right click in the database - tasks - export data...)

SCRIPT

Import/Export SQL Server 2000 Enterprise Manager Registered Servers

Exports and imports SQL Server 2000 Enterprise Manager groups and server registrations using Powersh...

BLOG

SSMS Tips & Tricks – Registered Server Groups

Here are a couple more SSMS tricks I’ve stumbled upon recently.  Both involve the functionality of r...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones