I recently had an opportunity to write an article for mssqltips.com on managing registered servers under “local server groups”, and Greg Robidoux was kind enough to do a review for me and he got back to me saying my script not working under SQL Server 2014 SSMS, though it works under SQL Server 2012. Indeed, I tested the script only in SQL Server 2008/R2 and SQL Server 2012 because I have not installed SQL Server 2014 (expecting SP1 to be released soon).

So here is the background information, we can create registered servers / groups via PowerShell (i.e. SQLPS environment). What I need to do is to start SQL Server 2012 SSMS (abbr. as SSMS 2012 hereafter), connect to my local SQL instance and right-click an object, and from the pop-up window, click “Start PowerShell” as shown below

Fig-1

I then can enter into SQL Server 2012 SQLPS command window, where I can go into SQLServer:\SQLRegistration “folder” as shown below

Fig-2

Then I can use new-item cmdlet to create groups and registered servers.

For example, in the SQLPS 2012 window, if I run “new-item –path Group_A –type directory”

Fig-3

I will see in SSMS 2012, a group “Group_A” appear in the “Registered Servers” window:

Fig-4

However, if I do the same thing in SQLPS 2014, I will not see “Group_A” in SSMS 2014.

In my case, I prepared a script that will automatically create all the groups / registered servers based on my input, the generated groups / registered servers can be like the following:

Fig-5

I have tested my script in SQL Server 2008 / 2008 R2 / 2012 environments, no issue found, everything went well.

Since Greg told me it did not work in SSMS 2014, I started to install a SQL Server 2014 instance on my computer, on which I already have another two sql instances, one is SQL Server 2008 R2 and the other SQL 2012. After installation, I tested my script under SQL 2014 SQLPS window, and no doubt, it does not work, i.e. in SSMS 2014, I cannot see the registered servers / groups as shown in Fig-4. However, I noticed a weird thing that after I run my script in SQLPS 2014, I see all the registered servers / groups from SSMS 2012 though I do not see them in SSMS 2014, not in SSMS 2008R2 either (of course, I have cleaned everything in SSMS 2012 beforehand).  So this got me thinking there must be something wrong in SQL Server 2014.

So my first task in the debugging process is to figure out where these registered servers/groups information is saved. After some googling, I did find out and verified the answer from this link (http://stackoverflow.com/questions/3064289/where-are-registered-servers-stored). (Big thanks to both who asked and answered the question, you are my heroes.)

In my computer, the RegSrvr.xml for SSMS 2014 is located at the following folder (notice \120\, that is SQL Server 2014 version number)

Fig-6

Similarly the RegSvr.xml for SSMS 2012 is located at: (notice \110\, that is sql server 2012 version number)

Fig-7

With this information and the weird observation (i.e. registered servers/groups are seen from SSMS 2012 instead of SSMS 2014), I guess SQLPS 2014 may probably mis-manipulate the RegSrvr.xml in the wrong folder. So I cleaned up the registered servers/groups from SSMS 2012, and then mark down the [Date modified] value of RegSrvr.xml in \110\ folder, let’s say the time value is 10:00pm,  and I also marked the [Date modified] value for the RegSrvr.xml in \120\ folder, let’s say the value is 10:02pm. Now at 10:20pm, I run my script from SQLPS 2014 window to register all the servers/groups, and then I check back the [Date modified] value of RegSrvr.xml in both \110\ and \120\ folders, no surprise, the value of the file in \110\ folder changed from 10:00pm to 10:20pm, while that in \120\ folder remains at 10:02pm.

So this indeed proves SQLPS 2014 has a bug when it deals with registered servers/groups for “Local Server Groups” in SSMS 2014.

For fun purpose, I copied RegSrvr.xml from \110\ folder to \120\ folder (overwriting the existing one in \120\ folder), and then I started SSMS 2014 again. Just as expected, I find all the registered servers/groups appear in SSMS 2014 “Registered Servers” window, wow !