Summary

This Knowledgebase article provides information about this specific release of the Neverfail for SQL Server Plug-in V201.5.13

What's new

Changes since previous version:

• Support for SQL Server 2017 editions
• Added an option to include the SQL Reporting Services private key corresponding to the service logon account in to the protected data set (EN-3486)

Supported Versions of Microsoft SQL Server

x64 / Intel 64 Platform (64-bit hardware)

• SQL Server 2017 Standard, Enterprise, and Express Editions on
  • Windows Server 2016 Standard and Datacenter
  • Windows Server 2012 R2 Standard and Datacenter
• SQL Server 2016 up to SP2 Standard, Enterprise, and Express Editions on
  • Windows Server 2016 Standard and Datacenter
  • Windows Server 2012 R2 Standard and Datacenter
  • Windows Server 2012 Standard and Datacenter
• SQL Server 2014 up to SP2 Standard, Enterprise, and Express Editions on
  • Windows Server 2012 R2 Standard and Datacenter
  • Windows Server 2012 Standard and Datacenter
  • Windows Server 2008 R2 SP1 Standard and Enterprise
• SQL Server 2012 up to SP4 Standard, Enterprise, and Express Editions on
  • Windows Server 2012 R2 Standard and Datacenter
  • Windows Server 2012 Standard and Datacenter
  • Windows Server 2008 R2 SP1 Standard and Enterprise
• SQL Server 2008 R2 up to SP3 Standard, Enterprise, and Express Editions on
  • Windows Server 2012 R2 Standard and Datacenter
  • Windows Server 2012 Standard and Datacenter
  • Windows Server 2008 R2 SP1 Standard and Enterprise
• SQL Server 2008 up to SP4 Standard, Enterprise and Express Editions on
  • Windows Server 2012 R2 Standard and Datacenter
  • Windows Server 2012 Standard and Datacenter
  • Windows Server 2008 R2 SP1 Standard and Enterprise 

Note: The IA-64 Itanium Platform is not supported 

Known Issues

• Reporting Services Web Service and Web Portal URLs fail to start after URL reconfiguration. RSPortal<TimeStamp>.log contains following error System.Net.HttpListenerException: Access is denied. This is a known SSRS 2016 and 2017 issue which can be solved as described in the following procedure. Workaround: In RSConfigTool go to Web Portal URL> Advanced or Web Service URL> Advanced and remove the existing URL reservation. Apply changes then open Advanced configuration again and re-add the URL configuration you had before and click Apply. More details about this issue can be found in this discussion thread. (EN-3600)
• Reporting Service (SSRS) service fails to start after switchover due to: The service did not start due to a logon failure. This happens when the Reporting Service is (re)configured with a new domain or local user logon account after Neverfail Engine deployment. Workaround: The password for the (re)configured SSRS logon account should be re-typed on the newly active server after the switchover. Recommendation: Reporting Service account reconfiguration should be performed either with Neverfail Engine configured for manual application maintenance (unprotect services) or with Neverfail Engine service stopped.
• SQL Server application health is falsely set to degraded when (re)configuring the SQL Server plugin. Health status is set to: "Failed 'Configure' after 3ms with status Plug-in task failed:Invalid user name or password. (System.Exception: Invalid user name or password {...}". The tasks and rules using the credentials are executing correctly.  App health is falsely set to degraded: "Failed 'Configure' after 3ms with status Plug-in task failed:Invalid user name or password. (System.Exception: Invalid user name or password {...}". Workaround: Execute action: Clear application health. (EN-3615)
• SQLServerConnections.xml is not deleted when Engine is uninstalled, hence not recreated by SQLServer plugin (as it exists) during Engine upgrade. Workaround: Delete SQLServerConnections.xml from%ProgramData% folder. SQLServer plugin File Filter Discovery task will recreate it next time it runs with the new Data Source. (EN-3438).

• SQL Server monitoring rules do not use the very same DB connection string as the one specified in the SqlServerConnections.xml (EN-672).
• Applies to SQL Server 2014 using In-Memory Optimized Tables: After a switchover is performed the user might see a warning message in the user-interface or logs stating that a certain Instance/Database is in a recovery state and the "Database Online Status" rule is unable to mark that instance/database as being ONLINE; Example "Rule Triggered: The following databases are not online: Instance/Database RECOVERING. File filters will be removed for these databases. They will be restored when the databases come back online: OFFLINE DBs detected" (Ref-EN-329). Explanation/Workaround: This is just a temporary/misreported state, which doesn't affect SQL Plugin functionality. If a database is using in-memory tables, when SQL Server 2014 is started, SQL Server will rebuild the database from the checkpoint files stored on the disk. Once the "In Recovery" state is cleared out, the user can open the Neverfail Advanced Management Client -> Applications tab -> Summary tab -> click the Clear button. 

• SQL Server 2012 File Tables are not supported. See Knowledgebase article #2495 - Neverfail for SQL Server Replication Stops in the Presence of SQL Server 2012 File Tables for more information (Ref-13103). 
• MSDTC.log file location changes are not replicated to the passive server(s) (Ref-13106).
  Workaround: Please see Knowledgebase article #2480  - Neverfail for SQL Server Plug-in Does Not Replicate MSDTC.Log File Location Changes to the Passive Servers.
• Filter discovery does not protect database snapshots created outside of current database directory (Ref-13236).
• \SQL Server Plug-in reverts to the default LocalSystem account if incomplete username credentials are provided for filter discovery (Ref-13245).
• When editing the User Account for Neverfail for SQL Server Plug-in, if the Local System account is used, it must be entered with initial caps ("Local System"). Using all lowercase characters will result in the plug-in failing to authenticate (Ref-13246).

• This version of SQL Server Plug-in does not support the following:
  • SQL Server 2016 Express Edition (LocalDB) version 
  • SQL Server 2014 Express Edition (LocalDB) version
  • SQL Server 2012 Express Edition (LocalDB) version
    • Attempting to install Neverfail for SQL Server with LocalDB will cause Neverfail SQL Server/File Filter Discovery to fail with the following message: The SqlServer/File Filer Discovery failed with the following warning: File Filter Discovery: Plug-in task failed:Errors encountered. Some data may not be protected. Please see logs. (System.ApplicationException: Errors encountered. Some data may not be protected. Please see logs. at SqlServerNFPlugin.x1426749251740f8a.OnUpdateFileFilters())
    • For more information about this issue, see Knowledgebase article #2496  - SQL Server Filter Discovery Fails With The Message: System.ApplicationException: Errors encountered. Some data may not be protected.

About Neverfail for SQL Server Plug-in 

The Neverfail for SQL Server Plug-in is an add-on to Neverfail Continuity Engine, which provides protection for Microsoft SQL Server. It configures Neverfail Continuity Engine 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.

Install/Uninstall the Neverfail for SQL Server Plug-in Via the Advanced Management Client.

To install the Neverfail for SQL Server Plug-in, follow the steps below:

1. Unzip the Neverfail for SQL Server Plug-in to the desired location. 
2. Launch the Advanced Management Client.
3. Navigate to Application -> Plugins.
4. Click on the Install button.
5. Click the Browse button and navigate to the location of the SqlServerNFPlugin.dll file.
6. Click OK to complete the installation.

Note: When editing the User Account for Neverfail for SQL Server Plug-in, if the Local System account is used, it must be entered with initial caps ("Local System"). Using all lowercase characters will result in the failure of the plug-in to authenticate.

To remove/uninstall the Neverfail for SQL Server Plug-in, follow the steps below:

1. Launch the Advanced Management Client.
2. Navigate to Application -> Plugins.
3. Select the desired plug-in to be removed/uninstalled.
4. Click on the Uninstall button in the Plugins pane.
5. When the plug-in is uninstalled, the files are not removed.  This is by design for future functionality and therefore the following workaround is provided:
6. Copy the contents of the r2\plugins\<sponsor>\<version> folder to a different location (e.g. C:\temp).
7. Delete the contents of the r2\plugins\<sponsor>\<version> folder.
8. To reinstall the plug-in, when prompted for the location of the plug-in files, navigate to the new folder location (e.g. C:\temp).

Note: As an alternative, reinstallation can be accomplished by using the download files from the original download location after removing the plug-in.

Login Credentials

SQL Server version 2008 R2 and prior 

The Neverfail for SQL Server Plug-in’s File Filter Discovery task and monitoring rules are configured, by default, to run as Local System for all SQL Server versions up to and including 2008 R2 SP1, the user is not required to configure login credentials. Please note SQL Authentication mode is not supported. 

SQL Server version 2012 or newer

The database security model is different starting from SQL Server 2012; for filter discovery to connect and query the database, it requires a Windows Domain User account with the sysadmin role.

• SQL Authentication mode is not supported.
• If there are multiple instances of SQL Server and at least once instance is SQL Server 2012 or newer, then each instance must be configured with the same Domain user account with the  sysadmin role.

To configure Neverfail for SQL Server Plug-in with a Domain User:

1. Using the Advanced Management Client, navigate to the Applications: Plug-ins tab.
2. Select the SqlServerNFPlugin.dll
3. Click Edit.
4. Enter the User domain, Username, and Password into the appropriate field.
5. Click OK.

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 Advanced 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 Advanced 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 has to 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.

To configure/modify these 'Rules', follow the steps below:

1. Using the Advanced Management Client, click on Application -> Rules.
2. Select the desired Rule.
3. Click on the Edit button at the top of the Rules pane.
4. Configure/modify the desired fields (listed below).
   • Condition: (the condition being evaluated) E.g. see the Note above.
   • Duration: (the length of time the condition exists)
   • First Failure: (action to take upon first failure) Note: The default is set to 'Alert'.
   • Second Failure: (action to take upon second failure) Note: The default is set to 'Alert'.
   • Third Failure: (action to take upon third failure) Note: The default is set to 'Alert'.

5. Click OK to save the changes.

Note: This dialog is also used to Enable/Disable the selected 'Rule'.

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 Engine Tasks tab or Advanced 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

To configure/modify these Tasks, follow the steps below:

1. Using the EMS Web UI ( or Advanced Management Client), click on Application -> Tasks.
2. Select the desired Task.
3. Click on the Edit button at the top of the Tasks pane.
4. Configure/modify the Interval field. 
   
   Note: Interval is the only field available for configuration/modification and is measured in seconds (secs).
5. Click OK to save the changes.

Note: This dialog is also used to Enable/Disable the selected 'Task'.

Database Protection and Monitoring Exclusion

Beginning with Neverfail for SQL Server Plug-in v201.5.7 users can specify databases to be excluded from protection and monitoring. This option allows for one or more databases to be excluded from the following database level rules:

• Database Availability
• DB Files Allocated Space
• DB Online Status rules

To disable a specific database from protection and monitoring:

1. Using the EMS Web UI (or Advanced Management Client), navigate to Data: File Filters.
2. Identify and select the applicable database file filter.
3. Click Edit.
4. Clear the Enabled check box.
5. Click OK.
6. Navigate to Applications: Plug-ins.
7. Select the Neverfail for SQL Server Plug-in.
8. Click Edit.
9. In the Databases to excluded from rule checks field, enter the instance/database. When entering multiple databases, separate each with a semicolon (";").
10. Click OK.

Beginning with Neverfail for SQL v201.5.9, users can specify offline databases to be protected. This option is selected post installation and allows one or more databases to be protected even though the database(s) is/are currently offline. By default, the option is not selected.

To protect an offline database:

1. Using the EMS Web UI (or Advanced Management Client), navigate to Applications: Plug-ins.
2. Select the Neverfail for SQL Server Plug-in.
3. Click Edit.
4. Select the option to Protect offline databases.
5. Click OK. 

Protected Service Discovery

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)
• SQL Server Integration Services CEIP service 14.0 (SSISTELEMETRY140) – SQL 2017
• SQL Server Analysis Services CEIP (MSSQLSERVER) - (SSASTELEMETRY)
• SQL Server CEIP service (MSSQLSERVER) – (SQLTELEMETRY)
• SQL Server PolyBase Engine (MSSQLSERVER) – (SQLPBENGINE)
• SQL Server PolyBase Data Movement (MSSQLSERVER) – (SQLPBDMS)
• SQL Server Launchpad (MSSQLSERVER) – (MSSQLLaunchpad)
• SQL Full-text Filter Daemon Launcher (MSSQLSERVER) – (MSSQLFDLauncher)
• SQL Server Integration Services 14.0 (MsDtsServer140) – SQL 2017
• SQL Server Integration Services Scale Out Master 14.0 (SSISScaleOutMaster140) - SQL 2017
• SQL Server Integration Services Scale Out Worker 14.0 (SSISScaleOutWorker140) - SQL 2017
• Microsoft Distributed Transaction Coordinator (MSDTC) 
• 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
• SQL Server Reporting Services (SQLServerReportingServices) - SQL 2017

Instance specific services

• MSOLAP$Instance - SQL Server Analysis Services (Instance)
• MSSQL$Instance - SQL Server (Instance) 
• MSSQLFDLAUNCHER$Instance - SQL Full-Text Filter Daemon Launcher (Instance)
• SQLAgent$Instance - SQL Server Agent (Instance) 
• SSASTELEMETRY$Instance - SQL Server Analysis Services CEIP (Instance)
• SQLTELEMETRY$Instance - SQL Server CEIP service (Instance)
• SQLPBDMS$Instance - SQL Server PolyBase Data Movement (Instance)
• SQLPBENGINE$Instance - SQL Server PolyBase Engine (Instance)
• MSSQLLaunchpad$Instance - SQL Server Launchpad (Instance)

Default Instance

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

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

Unprotected Services

These services should be left alone:

• SQL Server VSS Writer (SQLWriter)

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

Automatic File Filter Discovery

This plug-in provides a filter discovery task.

• The plug-in will automatically protect all newly created databases for SQL Server
• For detailed information about Automated Filter Discovery in Neverfail for SQL Server Plug-in, please refer to Knowledgebase article #398  - 'Automated filter discovery in Neverfail for SQL Server'.

Note: The information provided in the DSN SQL Server connection file must specify a user account with a minimum of, read access to the SQL Master database. The filter discovery task uses the DSN connection information to query SQL Server and create the file filters. 

Installing a New Instance of SQL Server (recommended)

Note: Starting with Neverfail Engine 8.5 this can be done also via the Upgrade Applications path, which briefly, means adding a new instance only on the Primary server followed by the Passive server(s) Recloning.  For older Neverfail Engine versions or in the scenarios where the Recloning method cannot be used, we recommend using the legacy procedure:

If you install a new instance of SQL Server after Neverfail for SQL Server has been installed, then this new instance must also be available on the Secondary machine. The safest method of ensuring that the installation and configuration are identical on both servers is to perform the following procedure:

1. Shutdown the Engine Service and leave the protected applications running.
2. Install the new instance of SQL Server on the Primary server.
3. Install an identical instance location/configuration on the Secondary server.
4. Restart the Engine Service.
5. Using the EMS Web UI (or Advanced Management Client), navigate to Application -> Tasks.
6. For SQL Server, select the Protected Service Discovery periodic task and click the Run Now button.
7. Check that the services of the new instance of SQL Server have been correctly added and marked as Running on the active server and Stopped on the passive server.

The SQL Server Plug-in will automatically detect the services and files for the new instance.

Unsupported Components or Features 

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

• The following SQL Server 2012/2014/2016/2017 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
• Full Text Catalogue protection is disabled by default (protection is not recommended because of the presence of sparse files. It can be enabled from the EMS Web UI (or Advanced Management Client).
  • Select the Application tab
  • Plug-ins tab.
  • Select the SqlServerNFPlugin.dll
  • Select the Edit button.
  • Enable the check box ‘Protect the index catalogues’ and press OK.
• If new SQL Server Instances are created post install please refer to Section Installing a New SQL Server Instance.
• Protecting the reports generated by the SQL Server Reporting Service, published via an IIS Web Server Virtual Directory, Neverfail for IIS is required.
• 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. 
• SQL Server VSS Writer (SQLWriter) is a demand start service that is run when required.
• The Mssqlserveradhelper service is not monitored or protected because it is a demand start service. 
• Jobs temporary directory. Note: If you wish to have the Jobs Output files replicated between servers, create a file filter for the Jobs Directory.

Applies To

Neverfail for SQL Server Plug-in