Monitor Servers

The procedure Monitor Servers creates an overview of all servers that are defined as Linked Servers. This list is compared to the list that was retrieved the last time this procedure ran. Servers that are unavailable are reported as well as newly found servers. Any server that is not found is marked Unavailable and will be skipped by the other procedures. If a server that was previously unavailable is found again, the mark is removed and the other procedures will inventory the server once more.

The following properties are in the report:

  • LinkedServer
  • SQLVersion
  • MachineName
  • InstanceName
  • Edition
  • DataSource
  • DateStart
  • DateStop
  • Active
  • Available

How to use it

The procedure usp_Report_Servers will crawl all Linked Servers that are present on the server where your Sequenchel database resides. Information for these servers is stored in the Sequenchel database in the table tbl_Servers.

Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @Recipient varchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)

SET @Recipient = ‘yourname@yourdomain.com’
SET @ExceptionList = ‘ServerNotToProcess’
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Report_Servers] 
   @Recipient
  ,@ExceptionList
  ,@MailStats
  ,@SqlVersion
  ,@Separator
;

  • The recipient is the emailaddress where to send the results to.
  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • Mailstats = 1 means that you will recieve an email if there is no useful information te report, telling you exactly this. If you set this to 0 then no email is sent unless there is something to report.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated ExceptionList. You can choose any single character for this.

We recommend that you schedule this procedure on a daily basis.

Collected Information

Collected Information

  • ServerID: The number assigned to this server when it was first found.
  • LinkedServer: The name of the Linked Server that points to this server.
  • LocalServerName: The @@ServerName property value.
  • DataSource: The complete connectionstring.
  • PromotionOn: If Promote Distributed Transactions is enabled for the Linked Server.
  • MachineName: The Serverproperty machineName.
  • Domain: The domain for the SQL Server.
  • InstanceName: The complete Instance name.
  • Edition: The SQL Server Edition.
  • SQLServerComponents: The Components installed. this information is not automatically retrieved.
  • SQLVersion: The short SQL Server version.
  • SQLVersionLong: The complete SQL Server version.
  • SQLVersionText: The SQL Server version in Text.
  • SQLServerLicenseType: This information is not automatically retrieved.
  • SPLevel: The Service pack level.
  • Collation: The SQL Server collation used for the server.
  • IsClustered: If this is a clustered SQL Server installation.
  • WindowsAuthentication: Windows only (1) or Windows and SQL Server authentication (0).
  • FileStreamEnabled: The Filestream level of the server.
  • IP_Address: The local IP address on which this server was connected.
  • Port: The portnumber on which this server was connected. This should be the same as the portnumber in the Linked Server connection.
  • Logical_CPU_Count: The number of Logical CPU’s in the instance.
  • Hyperthread_Ratio: Hyperthread ratio is the multiplier between physical and logical cpu’s.
  • Physical_CPU_Count: The number of Physical CPU’s in the instance.
  • Physical_Memory_MB: The maximum amount of memory in the SQL Server.
  • Owner: A free field, this information is not automatically retrieved.
  • Application: A free field, this information is not automatically retrieved.
  • Location: A free field, this information is not automatically retrieved.
  • Remarks: A free field, this information is not automatically retrieved.
  • MonitorServer: If the server is set to be monitored by Sequenchel.
  • MonitorContent: If the contect of this server is set to be monitored by Sequenchel.
  • AutoAdded: If this server was automatically added to the database. This happens if a Linked Server is created and the rest goes automatically.
  • Active: If the server is supposed to be operational. This setting is not automatically altered.
  • Available: If the server was found during the last monitoring.
  • DateStart: The first time the server was found.
  • DateStop: The first time the Linked Server does not exist anymore. This is not an indication whether or not the SQL Server does still exist. If the Linked Server is re-created, a new record will be created as well.
  • LastFound: The date the SQL server was last found and succesfully connected to.