Technical Article

Add Enterprise Manager registrations quickly

,

Use this Perl script to add Enterprise Manager registrations from a text file. The text file should contain three fields seperated by spaces. The first two fields are the server groups you want to create, and the last is the server name. ex...

Production Accounting ACTPRSQL01
QA Accounting ACTQASQL01

The script also goes out and registers all SQL Servers broadcasting on the network under a category called UNKNOWN.

I have hard-coded the script using integrated security to register the servers, but you can always easily modify this in the RegisterServer subroutine.

If you don't want to lose your current Enterprise Manager config, follow these steps...

1. Open Enterprise Manager

2. Go to Tools > Options.

3. Note the Read/Store user independent option - this is very important!

4. If the box is checked, uncheck it and exit EM. Open regedit (not regedt32, we're not changing registry permissions!). Delete all the subkeys (folders) under ...

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X

5. If the box is NOT checked, check it and exit EM. Open regedit (not regedt32, we're not changing registry permissions!). Delete all the subkeys (folders) under ...

HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\80\Tools\SQLEW\Registered Servers X

6. Create your text file and run the script.

perl registerservers.pl -fTextFileName.txt

Your EM should now contain a bunch of SQL Servers you have come to love and enjoy, along with a bunch of other ones you had no idea existed. If you want to flip back to your old config, just toggle the Read/Store user independent option back to where it was.

By the way, in case you were asking, you can't simply copy the registry keys from one machine to another. The SQL Server info is encrypted.

Future upgrades: (Ok, known bugs, gee whiz!)

1. Error handling
2. RegisterServer subroutine to not add existing registered servers.
3

use strict;
use Getopt::Std; 
use Win32::OLE;  
use Win32::OLE::Const("Microsoft SQLDMO");

my (%args, $args, $serverfile, $groupname, $appname, $servername, $AppGroup, $cnt);
my $err = 0;
my $SQLServer = new Win32::OLE 'SQLDMO.SQLServer';
my $Application = $SQLServer->{Application};


getopts('f:', \%args);
$serverfile = $args{f};
$cnt = 0;
open SERVERFILE, $serverfile or die "Cannot open file: $serverfile\n";
while (<SERVERFILE>)
{
print "Processing $_\n";
s/#.*//;         # remove comments, ignore blank lines
if (!$_) {next;}
tr/\t /  /s;
$cnt++;
($groupname, $appname, $servername) = split;
$AppGroup = new Win32::OLE 'SQLDMO.ServerGroup';
$AppGroup = CreateGroup($groupname, $appname);
RegisterServer($AppGroup, $servername);
$AppGroup = undef;
$servername = undef;
}
close SERVERFILE;
RegisterUnknown();
exit $err;


sub CreateGroup
{
my $ServerGroup = new Win32::OLE 'SQLDMO.ServerGroup';
$ServerGroup->{Name} = $groupname;
$Application->ServerGroups->Add($ServerGroup);
$ServerGroup = undef;
$ServerGroup = $Application->ServerGroups->Item($groupname);

$AppGroup->{Name} = $appname;
$ServerGroup->ServerGroups->Add($AppGroup);
$AppGroup = undef;
$AppGroup = $ServerGroup->ServerGroups->Item($appname);
$ServerGroup = undef;
return $AppGroup;
}

sub RegisterServer
{
my $RegisteredServer = new Win32::OLE 'SQLDMO.RegisteredServer';
$RegisteredServer->{Name} = $servername;
$RegisteredServer->{UseTrustedConnection} = 1;
$AppGroup->RegisteredServers->Add($RegisteredServer);
$RegisteredServer = undef;
}

sub RegisterUnknown
{
$AppGroup = new Win32::OLE 'SQLDMO.ServerGroup';
my $undefname = "UNKNOWN";
$AppGroup->{Name} = $undefname;
$Application->ServerGroups->Add($AppGroup);
$AppGroup = undef;
$AppGroup = $Application->ServerGroups->Item($undefname);
my $namelist = $Application->ListAvailableSQLServers();
my $i = 2; #start at item 2 to skip over locally installed SQL Server.
for (($i) .. ($namelist->{Count}))
{
$servername = $namelist->Item($i);
RegisterServer($AppGroup, $servername);
$i++;
}
$AppGroup = undef;
}

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating