Friday, August 02, 2013

Managing Multiple Servers Thanks to Registered Server Lists & the Benefits

Since I changed organisations in January this year, I asked one of my colleagues to export his SQL Server Management Studio Registered Server list. Ever since March I have been updating our list on nearly a daily basis, since I believe this is very important to managing multiple SQL Servers. For DBA teams, I believe one should make the registered server list available for the rest of the team via a network share.
Note that you can open REGSRVR file with a text editor to search through the XML data, such as to figure out within which group a specific server resides.

Multiple people cannot update this list unfortunately (unless manually) thus I do my best to keep it up to date. Maybe, at a later date, we`ll be able to have this managed under a Central Management Server (CMS), but that`ll have to wait until we begin to manage through Policies from a single CMS server.

If you have not used Registered Servers in SQL Server Management Studio before, simply click View, Registered Servers. You will also notice the option Types, which groups by Database Engine, Analysis Services, Reporting Services, SQL Server Compact, and Integration Services. Our list has very few updated for anything other than the Database Engine Type, but that will change over time!
Remember to re-import it frequently if this is the case in your organisation: to do so, right click on top of the Registered Server list, Local Server Groups, and import from the a shared folder.

One of the nice features to take advantage of with a Registered Server list is querying across multiple servers.  By right clicking on any group, even the entire Local Group, you can select New Query to view, for example, Server Infrastructure details quickly.

he first method to going essential server properties, as mentioned on Technet, is to execute the following parametr details on any SQL Server installation (I tested back to SQL 2000), by executing the following command.
xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount", "ProcessorType"
-- result set is a table, with a row for each parameter
-- thus if you execute
xp_msver, you will have twenty rows

The second, and my preference as best practice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):
select serverproperty('MachineName') MachineName
,serverproperty('ServerName') ServerInstanceName
,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled
,serverproperty('productVersion') ProductBuildLevel
,serverproperty('productLevel') SPLevel
,serverproperty('Collation') Collation_Type
,serverproperty('IsClustered') [IsClustered?]
,convert(varchar,getdate(),102) QueryDate
serverproperty('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName],
when exists (select * from msdb.dbo.backupset where name like '%backup%') then 'SQLBackupUsed'
else 'Local Copy_Only'
-- where you would replace the
-- strings with your respective third party or native backup solution

More examples? Sure:
I was asked the other day to find out how many physical sockets (not logical) that are used across servers, so one would write this:

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM    sys.dm_os_sys_info ;

How about all cluster node details across a group of clustered servers:

SELECT * FROM sys.dm_os_cluster_nodes

--All non-background processes and their wait times across servers:
select * from sysprocesses where status <>'background'order by waittime desc
-- Read Error logs across servers
exec master.dbo.xp_readerrorlog

Thanks to SQL Server Central forums for the physical socket query, no wonder I enjoy blogging there also Grin
...and thanks to Microsoft Premier Field Engineers for the last three during a recent Clustered Server resolution analysis issue.

Managed Service Accounts - The Saviour for the Domain-based SQL Server Service Account?

For those Database Administrators seeking to lock-down security related to a Service  Account(s), there is an option starting with Windows Server 2008 R2:
a Managed Service Account.
This type of account is tied to a machine, and cannot be locked out, and seems to be a saviour for vigilant DBAs wanting to achieve a higher level of SQL Server Instance isolation: 

Would you agree? Or have a proposal for an even better solution? 

This is a short post (originally posted June 21st), since as you can see below, it's summer and getting some wave skiing in is essential to maintaining sanity :)

Managing Critical Server Lists with Remote Desktop Connection Manager

You don't climb with out the right tools,
so use RDCMan to get higher
Most Server Administrators, whether Database Administrators or not, are quite familiar with the Remote Desktop Connection (a.k.a. RDP) tool, which is formerly known as the Microsoft Terminal Services Connector accessible from the command mstsc in Run, or from a Command Prompt.  What most do not realise, however, is that there is a free tool that manages a list RDP connections, titled Remote Desktop Connection Manager (sometimes referred to as RDCMan). Since free is the best price self-evidently, this little add-on to Remote Desktop, requiring no different security settings than RDP itself, is a welcoming time saver to those who are responsible for a long list of servers - in our case hundreds of servers to potentially manage.  And when I say little, Microsoft certainly has made this 789KB download the best value for under one megabyte I have ever seen (very sorry VNC, I much prefer a RDP connection).

 Why use RDCMan?

If you enjoy optimised productivity, and maintaining a current server list, then you will want this tool.
▪ Saves administrative burden, since you can save connection preferences and stores your current password in an encrypted format.
RDCMan is a simple add-on to Remote Desktop; it only stores a list of RDP connections with respective profile, and has the same options you see with an individual RDP saved connection.
▪ Options can be set for the group (such as connecting to local resources, etc.), and then can be changed per individual server
▪ Importing and adding servers to the list is easy, through simple text file import. Duplicates are automagically eliminated.

If you are on a team, as are most of us, I am pretty sure your DBA colleagues, would appreciate an updated RDG be provided to them, which is the Remote Desktop Group saved configuration, and list SQL Server Hosts. 
Prerequisites:  For the firewall conscious people out there, RDP uses Port TCP 3389, and on the server itself, Remote Desktop Connections must be enabled.

Nota bene: As I mentioned here on Simple Talk, as the first commenter - do not click on Connect Group, since you will find yourself, all of a sudden, making an RDP connection to every single server in your list!
References: Here's the official literature from Microsoft about RDCMan  
PS: Online versions of RDCMan would be which I use to manage and aid my family with remote support. There are free or professional versions, and is simply another flavor of Join.Me (which is the very same company).