Server Registration Feature not available in SQL 2005

  • Article

I am not sure how many of you are aware of this feature in SQL 2000, so will start with a little background.

IN SQL 2000

In SQL Server 2000 Enterprise Manager (EM), you can register another SQL Server 2000 box by going through the "New SQL Server Registration Wizard", which pops up when you right click on "SQL Server Groups".

However, this calls for you to register each server in your network manually. But what if you have 50+ servers in your network and you want each of your DBAs to register them in EM? Plus, if new servers get added or old servers get deleted, DBAs will have to manually update their registration.

To allow ease of use, SQL 2000 Enterprise Manager provides an alternate method.

Imagine you have 50+ servers in your network. All you have to do is register them ALL one by one manually by using the "New SQL Server Registration Wizard" on one server, say Server A.

This server A will then become your source server from where every DBA can read server registration information without having to register each server manually.

Each DBA can log into their personal machine and go to Enterprise Manager. In EM, they need to click on "Microsoft SQL Servers" and go to TOOLS--> OPTIONS.

Here you will see "Server Registration Information" section. Under this section, you have 2 options:

a) Read/Store Locally (by default) This is checked when you manually register your servers.

Under this, there is another option "Read/Store User independent", which when checked, will show ONLY registered servers in EM to the user who registered them. If another user logs in, he will not see these servers.

b) Read from Remote. If you check this option, you can provide the network name of the Server A above and your EM will get pre-populated with ALL servers registered on Server A's EM.

ADVANTAGES and DISADVANTAGES

The advantage of this method of registering servers is ease of use for DBAs. If a new server is added in the network, all you have to do is register that server on Server A's Enterprise Manager. This information will automatically get populated in every DBA's Enterprise Manager that is configured to use "read from remote". Similarly, if a server is removed, it needs to be deregistered from Server A, and it will be removed from all DBA's EM.

However, it has some serious downsides.

First, if you choose "Read from Remote", ALL SQL servers will pop-up in the EM, irrespective of who is logged into the box. So if a Windows guy logs into the PC of the DBA, and goes to EM, he too will see ALL servers registered and could potentially do harm (specially if he is unhappy with the company or just got fired). Enterprise Manager doesn't check the user id of the logged in user and simply reads from the remote server irrespective of who is logged into the computer.

However, if you decide NOT to read from the Remote Server, then you should check the "Read/Store user independent". This ensures that only the person who registered these servers can see them, and if somebody else logs in the same machine, he will NOT be able to view other person's servers.

This is a cool feature but should be used with caution. Use of this feature is documented in SQL 2000 Books Online under heading "Accessing Server Registration Options".

In SQL Server 2005

Although this feature is really cool, but it has been removed from SQL 2005 as it is clearly a security breach.

In SQL 2005, you can register servers either through Object Explorer or through Registered Servers views. This is similar to SQL 2000's "New SQL Server Registration Wizard".

Also, when you register a server manually, it creates a file RegSrvr.xml at <installation directory>:\Documents and Settings\userid\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\. Note that it is placed under the folder creatd by the name "userid". If you try to copy this file to ALL USERS folder, this won't work.

However, as a replacement for "Read from Remote", SQL 2005 provides a new method called Import/Export. Under VIEW, go to REGISTERED SERVERS and right click to choose IMPORT/EXPORT. 

If you are the DBA who has all the servers registered, then you can EXPORT the list. This will create an XML file with extenstion .regsrvr. Pass this file to another DBA and he too will be able to import this file and ALL your servers will pop-up in his Management Studio as well.

For registered servers that use SQL Server Authentication, you can select Do not include user names and passwords in the export file. This will ensure that although the server list is given to another DBA, he will need to have his own login in those remote SQL Servers to log in.

Hope this helps those customers who were using this feature in SQL 2000.