SQL Server 2005 Import/Export Server Registrations

,

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.

Rate

3 (2)

Share

Share

Rate

3 (2)