In database programming, the scome technique is a mechanism used to collect RDBMS system data from remote linked servers via a central SQL Server. This data can be used to monitor the status of managed database servers and their associated databases. The method comprises of two T-SQL stored procedures that together are used to iterate over records in SQL Server system tables and Dynamic Management Views. The stored procedures are typically executed from a central master SQL Server.
The central monitoring SQL Server communicates with the remote RDBMS through a linked server object. A collecting stored procedure (known from here on as the collector) is created on the monitoring SQL Server. The remote linked server name is passed to the collector in the form of a stored procedure parameter.
CREATE STORED PROC Collect_system_data @Server_name N'VARCHAR(256) AS ... SELECT [system data] FROM @Server_name.master.dbo.[system table] (Insert results into corresponding table)
The iterating stored procedure (known from here on as the iterator) retrieves the name of all linked servers from a table through the means of a cursor or memory table. The iterator executes the collector for each linked server, which in turn inserts the desired data into a corresponding table.
CREATE STORED PROC Iterate_Collect_system_data @Server_name N'VARCHAR(256) AS ... (For each linked server name retrieved from the server name table) EXEC Collect_system_data @Server_name
This results in a table with the desired data for all monitored linked RDBMS'. This data is then accessed directly through the RDBMS or via an interfacing platform such as one created using the .NET Framework.
Advantages of the scome technique
An advantage of using the scome technique is that a complete custom designed monitoring solution can be developed with only T-SQL skills, giving the database administrator flexibility to write monitoring reports based on any meta data available within system tables and views. This can be achieved out-of-the-box with the utilties provided by SQL Server. Monitored servers are not limited to being SQL Servers. Any RDBMS, such as Oracle, can be monitored using scome provided there is an OLE-DB provider from Microsoft to allow the linked server to be created for that RDBMS.
Disadvantages of the scome technique
Data is collected from often undocumented RDBMS system tables that may change from version to version, thus breaking the mechanism.
The term scome became prevalent after appearing on the SQL Server community website, SQLServerCentral. It is thought that the term scome originally stood for Servers Coming Outta My Ears.
- Dynamic Management Views and Functions, Microsoft Technet.
- Linking Servers, Microsoft Developer Network.
- Using DMVs with SCOME, Monitoring SQL Server Centrally.