SQL 2012 Management Studio Remote Servers Service Status Slow/Stops Working

This is a huge annoyance.  I manage about 50 SQL instances of SQL Server versions 2005-2012 and I noticed that it takes 1-2 seconds to load the service status (green arrow) on each individual server.  If you then connect to a server, you have to wait for all the statuses to be loaded first in the Registered Servers list before it loads the statuses for servers in the Object Explorer.  This happens whether the servers are registered in Local Server Groups or Central Management Servers, and whether I have access to all servers (as windows local administrator and sa), or not.  It also happens regardless of whether I run as Administrator or normally on my instance of SSMS (which is SQL 2012 Development on Windows Server 2008 R2).

These servers are also registered in alphabetical order by group and server name, which means that if I have "DEV" groups, they will be registered before "PROD", unless I rename accordingly.  It sometimes takes a few minutes, if I'm lucky, to get a server I connect to to load, which means I can't use SSMS to really manage any remote servers and start/stop/pause/resume/restart services.

Worse, sometimes due to a server becoming unavailable (due to updates/patches/failovers), SSMS is unable to load the service statuses anymore for any new server connections made to object explorer, resulting in the loss of ability to view/change service statuses.  In this case, there is no way to recover (Refresh of Central Management Servers does nothing), except to close SSMS and open it again, and then wait again for all the statuses to load.

I have lived with this since first installing SQL, and regardless of the Windows Updates and SQL patches applied, the problem has never gone away.  This also happens if I load SSMS in Windows Server 2012.  Its starting to become a huge issue now that I have to patch several servers each month.

I have already checked WMI permissions, GPO, firewall, etc. as per all the suggestion on the internet, including this post http://www.sqlservercentral.com/Forums/Topic825246-146-1.aspx.  None of these suggestions have made a difference, as my real problem is one of connectivity and performance, and not security, which most of these suggestions apply to.  Of note, all servers are in the same domain, firewall is disabled across the board, correct permissions for local administrators exist for remote registry settings, WMI, GPO, etc, and all service accounts and windows accounts that are used are local admins and sysadmins.

My work-around has been to delete the Central Management Server and any local server group registered servers, close and re-launch SSMS, and then connect to a server I'm interested in via Object Explorer.  Otherwise, close and re-launch will take forever.  My other alternative is to RDP into the remote server and manage services via SQL Configuration Manager locally.

If anyone has any ideas on how to fix this, or make it go faster, I'd really appreciate it.

January 30th, 2015 2:17am

Have you considered the lightweight approach of using batch files and SQLCMD to accomplish your tasks? You will find many solutions to remotely restart services using tools such as sc, powershell, etc. After the patch installation, you can run a simple batch file that uses SQLCMD to loop through a list of servers (that you can input in a text file) and run a health check script file and redirect the output to a text file.  
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2015 11:15pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics