About Symantec Continuous Availability 2011 for SQL Server Plug-in
Symantec Continuous Availability 2011 for SQL Server Plug-in is an add-on to Continuous Availability, which provides protection for Microsoft SQL Server. It configures Continuous Availability 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.
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
Continuous Availability monitors the availability of a server by sending continuous heartbeat signals via the Symantec Channel to the other server in the pair. The “Yes I am alive” response ensures that not only is the other server powered-up, but also that it is available if needed. The heartbeat signal interval is configurable from the Continuous Availability 2011 Management Client.
Continuous Availability can monitor general system performance and does so via the Continuous Availability System Plug-in. For information on monitoring general system performance, please see Symantec Continuous Availability 2011 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. Symantec Continuous Availability 2011 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 Continuous Availability 2011 Management Client under the Applications page.
Rules - The following Performance Monitoring 'Rules' are installed by Symantec Continuous Availability 2011 for SQL Server Plug-in:
Database File Monitoring and Replication
When the Symantec Continuous Availability 2011 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, for example, their contents will be synchronized, and subsequent updates replicated to the passive server. During operations, administrators may add additional data and log files to existing databases, or indeed add entirely new databases to new file locations.
Symantec Continuous Availability 2011 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 Symantec Continuous Availability 2011 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 Continuous Availability 2011 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 create the possibility of a failure occurring in the time window between new data files being added and Symantec Continuous Availability 2011 for SQL Server Plug-in detecting and protecting them.
Tasks - The following Tasks are installed by Symantec Continuous Availability 2011 for SQL Server Plug-in:
- File Filter Discovery
- Protected Services 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 Symantec Continuous Availability 2011 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 (for example, 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 Symantec Continuous Availability 2011 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 protected by this plug-in. 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
- Search Service (MSSearch) - SQL Server 2000
- SQL Server Integration Services (MsDtsServer)
- SQL Server Browser (SQLBrowser)
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)
- SQL Server (MSSQLSERVER)
- SQL Server Agent (MSSQLSERVER)
- SQL Server Analysis Service (MSSQLSERVER)
- SQL Server Reporting Service (MSSQLSERVER)
- Microsoft Full Text Search Service (Msftesql)
When the Symantec Continuous Availability 2011 for SQL Server Plug-in is installed, all protected services will be set to manual unless otherwise stated.
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
Automatic Filter Discovery
This plug-in provides a filter discovery component. 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:
- Although Full Text Catalogue database protection can be enabled/disabled from the Symantec Continuous Availability 2011 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.
- 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. For more information on how to create File Filters from the Continuous Availability 2011 Management Client, please refer to the Symantec Continuous Availability 2011 Administrator's Guide.
- 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 must be added manually for the specific locations.
- Symantec Continuous Availability 2011 for SQL Server Plug-in does not replicate the 'Backups' directory.
Symantec Continuous Availability 2011