Neverfail for SQL Server Plug-in - Feature List

Follow

About Neverfail for SQL Server Plug-in

The Neverfail for SQL Server Plug-in is an add-on to Neverfail Heartbeat, which provides protection for Microsoft SQL Server. It configures Neverfail Heartbeat to protect the files and registry items used by SQL Server and also monitors aspects of the application and its environment that could affect availability.

Supported Versions of Microsoft SQL Server

For information about which versions of SQL Server and Service Packs are supported, please see Knowledgebase article #4 - 'Neverfail Products' or the SQL Server Plug-in Release Notes.

Monitoring

The monitoring of SQL Server and its system environment can be broadly categorized into the following three areas:

  • Availability Monitoring
  • Performance Monitoring
  • Database File Monitoring and Replication
  • Database Protection and Monitoring Exclusion

Availability Monitoring

Neverfail monitors the availability of a server by sending continuous heartbeat signals via the Neverfail Channel to the other server in the pair. The “Yes I am alive” response ensures that the other server is connected and responding. The heartbeat signal interval is configurable from the Neverfail Management Client.

Service monitoring ensures that the application services are available and servicing clients. The plug-in also provides a database availability test to ensure that the SQL Server instance is capable of responding to requests.

  • Database Availability
    • Executes a stored procedure.
    • Checks the SQL Server Instance is capable of responding within the designated period.

Note: The filter discovery task will also fail if an instance or database is unavailable because it needs to connect to SQL Server in order to determine the location of database and log files.

Performance Monitoring

Neverfail can monitor general system performance and does so via the Neverfail System Plug-in. For information on monitoring general system performance, please see Neverfail System Plug-in Online Help.

Each instance of SQL Server runs as a separate service. The default instance runs as MSSQLSERVER, and named instances run as MSSQL$<instance name>. Scheduling operations for SQL Server are controlled by the SQL server agent which can present itself as SQLSERVERAGENT for default instances and SQLAgent$<instance name> for named instances. Neverfail for SQL Server Plug-in monitors these services, and can react when they fail. The user may configure this reaction through the user interface of the Neverfail Heartbeat Management Client under the 'Application' screen.

Rules - The following Performance Monitoring 'Rules' are installed by Neverfail for SQL Server Plug-in:

  • Default Instance Buffer Cache Hit Ratio
  • Default Free Pages
  • Default Instance Free Pages
  • Named Instance Working Set
  • Named Instance Buffer Cache Hit Ratio
  • Named Instance Free Pages
  • Named Instance Total Server Memory
Notes:
  1. The default Buffer Cache Hit Ratio rule is triggered if the counter falls below 90% for 1 minute or more within a 30-minute period.  All other rules are disabled by default (please see Knowledgebase article #1401 - 'Some Plug-in Rule Parameter Values Must be Set from Established Base Lines').
  2. The following rules depend on the SQL Server instance name:
    • Buffer Cache Hit Ratio
    • Free Pages
    • Total Server Memory

    To have them running properly, the Condition field of the rules must be edited to show the correct SQL server instance name: SQLServer for the default instance or MSSQL$InstanceName for the named instance.
    Otherwise, the rule may become disabled with the error "Error creating counter SQLServer:[...]. Category does not exist".

Database File Monitoring and Replication

When the Neverfail for SQL Server Plug-in is installed, it will determine all current instances of SQL Server and their default locations for database files (Primary and Secondary data files and Transaction log files). It will also query the instance of SQL Server to determine any additional locations for database files, which may have been configured, for example, through the SQL Server Enterprise manager. These files will be protected, i.e. their contents will be synchronized, and subsequent updates replicated to the passive server. During operation, administrators may add additional data and log files to existing databases, or indeed add entirely new databases to new file locations.

Neverfail for SQL Server Plug-in will regularly (by default every two minutes) check for new database files or instances and add them to the set of protected files. This does not require any intervention by the user. The addition of new database files is likely to be an infrequent activity and thus the default time step that the Neverfail for SQL Server Plug-in checks for such changes is deliberately set at a reasonably low frequency of 2 minutes. However, it may be necessary to alter the frequency of this check (while the application is stopped). This is achieved through the Neverfail Heartbeat Management Client, by navigating to 'Application' screen and selecting the 'Tasks' tab. Expand the 'SQLServer' tree view, and selecting the 'File Filter Discovery' item and then clicking on the 'Edit' button in the 'Tasks' pane. The dialog that is invoked reveals the 'Interval' that the task will run in seconds. The default is 120 secs (2 minutes). This may be adjusted to suit, however it should not be decreased below about 30 secs, as this will create an unnecessary overhead, nor should it be increased greatly as this will increase the possibility of a failure occurring in the time window between new data files being added and Neverfail for SQL Server Plug-in detecting and protecting them.

  • All registry data and program files.
  • All installed SQL Server instances discovered at install time.
  • All the databases for the protected SQL Server instances.

Tasks - The following 'Tasks' are installed by Neverfail for SQL Server Plug-in:

  • Protected Services Discovery
  • File Filter Discovery
  • Registry Filter Discovery

Switchover and Database File Protection

During a switchover, all running services on the active server will be stopped. At that point, updates will have ceased for all SQL Server databases and there will be no transactions in progress. Once the queues of file-updates have been processed and the passive server has assumed the identity of the active server, all instances of SQL Server can be started.

Upon start up, the SQL Server engines will find that there were no transactions in progress, so they can continue without losing any transactions and without jeopardizing data integrity. Performing a switchover during the time period between the addition of new files to a SQL Server database, and their detection by the Neverfail for SQL Server Plug-in, should be avoided since the new files will not have been discovered and consequently not synchronized across to the passive server. However, once the new files have been detected, then they will be automatically synchronized to the passive server. As with the initial synchronization during the auto-check phase, during any synchronization process, the option to switchover will be temporarily disabled.

Failover and Database File Protection

SQL Server commits transactions by writing a record into a transaction log-file. As the log-files are part of the protected file-set, these commit records will be transferred to the passive server. However, this is not an instantaneous process. Some updates may still be queued on the active server; others may be queued on the passive server. After a failover, all updates that have not been transferred to the passive server will be lost. Transactions that were received on the passive server can be in two states: a commit record has been received, or no commit record has been received.

When the SQL server engines are started, they will check the log files. Any updates that are found for transactions that have not been committed (i.e. no commit record was found in the log file) will be rolled back. This way database integrity is maintained.

Note: Some transactions that were committed on the active server prior to the failure may not be present on the passive server following a failover. Under normal circumstances, this should only affect a very small number of transactions.

Note: If the active server fails during this time interval between the addition of new database files to SQL Server, and their detection by Neverfail for SQL Server Plug-in, then the passive server will not have synchronized the new data files.

Protected Services

This is a list of all services that can be protected by this plug-in at install time or as part of periodic service discovery. When the plug-in is installed, it scans all services to see if they are in the automatic or the started state. After the plug-in has been installed, the Service Discovery Task will protect services in the 'Automatic' state to protect a previously unprotected service, set it to 'Automatic'. Please note that where a plug-in supports multiple vendor versions, some services may not be present in a particular version.

  • Microsoft Distributed Transaction Coordinator (MSDTC)
  • Task Scheduler (Schedule)
  • Indexing Service (CiSvc) – SQL Server 2000
  • Microsoft Distributed Transaction Coordinator (MSDTC)
  • Search Service (MSSearch) - SQL Server 2000
  • SQL Server Integration Services (MsDtsServer)
  • SQL Server Browser (SQLBrowser)
  • SQL Server Distributed Replay Client (SQL Server Distributed Replay Client) – SQL Server 2012 onwards
  • SQL Server Distributed Replay Controller (SQL Server Distributed Replay
    Controller) – SQL Server 2012 onwards

Instance specific services

  • SQLAgent$InstanceName (SQLAgent$InstanceName) – SQL Server 2000
  • MSSQL$InstanceName (MSSQL$InstanceName) – SQL Server 2000
  • SQL Server (Instance) (MSSQL$Instance)
  • SQL Server Agent (Instance) (SQLAgent$Instance)
  • SQL Server Analysis Service (Instance) (MSOLAP$Instance)
  • Msftesql$Instance (Msftesql$Instance)

Default Instance

  • SQL Server (MSSQLSERVER)
  • SQL Server Agent (MSSQLSERVER)
  • SQL Server Analysis Service (MSSQLSERVER)
  • SQL Server Reporting Service (MSSQLSERVER)
  • Microsoft Full Text Search Service (Msftesql)

The Neverfail for SQL Server Plug-in will protect services and set them to manual.

The plug-in will determine at install time and at the configured discovery interval, the entire protected data set. Please note that exact filters will be based on the configuration of the application.

  • All registry data and program files
  • All installed SQL Server instances discovered at install time
  • All the databases for the protected SQL Server instances

Protected Data

This is a description of the protected data set that was determined at plug-in install time. Please note that exact filters will be based on the configuration of the application plug-in when it was installed.

  • All registry data and program files
  • All installed SQL Server instances discovered at install time
  • All the databases for the protected SQL Server instances
  • Support for SQL Server 2008 file streams used for remote BLOB storage
  • If configured, all offline databases

Automatic Filter Discovery

This plug-in provides a filter discovery component. For detailed information about the Automated Filter Discovery in Neverfail for SQL Server Plug-in, please refer to Knowledgebase article #398 - 'Automated filter discovery in Neverfail for SQL Server'.

  • The plug-in will automatically protect all newly created databases for SQL Server, independent of location (default or user defined).

Unsupported Components or Features

This plug-in does not protect the following application features or components:

  • The following SQL Server 2012/2014 features are not supported: SQL Server AlwaysOn Availability Groups/Failover Clusters, SQL Server File Tables, Semantic Search, SQL Server Express LocalDB feature, SQL Server Data Files in Windows Azure, Host a SQL Server Database in a Windows Azure Virtual Machine.

  • Although Full Text Catalogue database protection can be enabled/disabled from the Neverfail for SQL Server Plug-in, the default is disabled, as it is not recommended that these locations be protected because they contain unsupported features such as sparse files.
  • For protecting the reports generated by the SQL Server Reporting Service, which are published via an IIS Web Server Virtual Directory, Neverfail for IIS must be installed.
  • The Report and Analysis projects created with the Business Intelligence Development Studio are not protected by default. In order for them to be protected, a File Filter must be defined manually for the user location where they are saved.
  • Run-time filter discovery is not provided for the OLAP Services; the configuration will be determined at install time.
  • SQL Server VSS Writer (SQLWriter) as this service is started on demand.
  • The Mssqlserveradhelper service is not monitored or protected because this service is started on demand.
  • Jobs temporary directory. Note: If you wish to have the Job Output files replicated between servers, then create a file filter for the Jobs Directory.
  • Databases attached using SQL Server Management Studio are not protected by the filter discovery component. File filters have to be added manually for the specific locations.
  • Neverfail for SQL Server Plug-in does not replicate the 'Backups' directory.

Applies To

Neverfail for SQL Server Plug-in


Related Information

None

KBID-1397

0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.