2008-10-27

Management Studio 2005 registered servers

Unfortunaly it is not possible to share the registered servers in Management Studio 2005 while the file "RegSrvr.xml" can only be placed in the folder "%USERPROFILE%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell".
A solution could be to generate the registered servers file dynamic using MSXML and JScript.
This is a basic example on how to generate a registered servers file with one database instance:

var ProgId = "MSXML2.DOMDocument.3.0";
var XmlDoc = new ActiveXObject( ProgId );
XmlDoc.async = false;
XmlDoc.validateOnParse = false;
XmlDoc.resolveExternals = false;

var XmlNode= XmlDoc.createProcessingInstruction( "xml", "version='1.0' encoding='utf-8'" );
XmlDoc.appendChild( XmlNode );

XmlNode = XmlDoc.createElement( "RegisteredServers" );

var ServerType = XmlDoc.createElement( "ServerType" );
var ServerTypeId = XmlDoc.createAttribute( "id" );
ServerTypeId.value = "8c91a03d-f9b4-46c0-a305-b5dcc79ff907";
ServerType.setAttributeNode( ServerTypeId );
var ServerTypeName = XmlDoc.createAttribute( "name" );
ServerTypeName.value = "Database Engine";
ServerType.setAttributeNode( ServerTypeName );

var Server = XmlDoc.createElement( "Server" );
var ServerName = XmlDoc.createAttribute( "name" );
ServerName.value = "SANDBOX\\SSDB\_0";
Server.setAttributeNode( ServerName );
var ServerDescription = XmlDoc.createAttribute( "description" );
ServerDescription.value = "Primary sandbox database instance";
Server.setAttributeNode( ServerDescription );

var ConnectionInformation = XmlDoc.createElement( "ConnectionInformation" );

var SrvType = XmlDoc.createElement( "ServerType" );
SrvType.text = ServerTypeId.value;
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( SrvType );

var SrvName = XmlDoc.createElement( "ServerName" );
SrvName.text = "tcp:SANDBOX.SQLAdmin.dk,1521";
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( SrvName );

var AuthenticationType = XmlDoc.createElement( "AuthenticationType" );
AuthenticationType.text = "0";
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( AuthenticationType );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( XmlDoc.createElement( "UserName" ) );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( XmlDoc.createElement( "Password" ) );

var AdvancedOptions = XmlDoc.createElement( "AdvancedOptions" );

var PacketSize = XmlDoc.createElement( "PACKET_SIZE" );
PacketSize.text = "4096";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( PacketSize );

var ConnectionTimeout = XmlDoc.createElement( "CONNECTION_TIMEOUT" );
ConnectionTimeout.text = "15";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( ConnectionTimeout );

var ExecTimeout = XmlDoc.createElement( "EXEC_TIMEOUT" );
ExecTimeout.text = "0";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( ExecTimeout );

var EncryptConnection = XmlDoc.createElement( "ENCRYPT_CONNECTION" );
EncryptConnection.text = "False";
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t\t" ) );
AdvancedOptions.appendChild( EncryptConnection );
AdvancedOptions.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );

ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t\t" ) );
ConnectionInformation.appendChild( AdvancedOptions );
ConnectionInformation.appendChild( XmlDoc.createTextNode( "\n\t\t\t" ) );

Server.appendChild( XmlDoc.createTextNode( "\n\t\t\t" ) );
Server.appendChild( ConnectionInformation );
Server.appendChild( XmlDoc.createTextNode( "\n\t\t" ) );

ServerType.appendChild( XmlDoc.createTextNode( "\n\t\t" ) );
ServerType.appendChild( Server );
ServerType.appendChild( XmlDoc.createTextNode( "\n\t" ) );

XmlNode.appendChild( XmlDoc.createTextNode( "\n\t" ) );
XmlNode.appendChild( ServerType );
XmlNode.appendChild( XmlDoc.createTextNode( "\n" ) );
XmlDoc.appendChild( XmlNode );

XmlDoc.save( "RegSrvr.xml" );


Please notice that the server name to be displayed has extra "\" to get the right output. Also there are added some newline and tab code to get a readable result:

<?xml version="1.0" encoding="utf-8"?>
<registeredservers>
 <servertype id="8c91a03d-f9b4-46c0-a305-b5dcc79ff907" name="Database Engine">
  <server name="SANDBOX\SSDB_0" description="Primary sandbox database instance">
   <connectioninformation>
    <servertype>8c91a03d-f9b4-46c0-a305-b5dcc79ff907</servertype>
    <servername>tcp:SANDBOX.SQLAdmin.dk,1521</servername>
    <authenticationtype>0</authenticationtype>
    <username/>
    <password/>
    <advancedoptions>
     <PACKET_SIZE>4096</PACKET_SIZE>
     <CONNECTION_TIMEOUT>15</CONNECTION_TIMEOUT>
     <EXEC_TIMEOUT>0</EXEC_TIMEOUT>
     <ENCRYPT_CONNECTION>False</ENCRYPT_CONNECTION>
    </advancedoptions>
   </connectioninformation>
  </server>
 </servertype>
</registeredservers>


If you have a repository on you installed SQL Server services like Microsoft System Center Operations Manager (SCOM) you could use that as source in a extended version of the script above.
If you want to extend the script to generate registrations of instances of Integration Services, Analysis Services or Reporting Services, you can first register one instance of each manually to get the needed Server Types.

MSXML is documented by Microsoft at MSDN Library > Win32 and COM Development > Data Access and Storage > MSXML.

The script can only generate RegSrvr.xml for Management Studio 2005 while the format for Management Studio 2008 is rather different - but more on that another day...

No comments: