Summary

This article describes how Neverfail Heartbeat for Microsoft SQL Server discovers new information during normal operation.

More information

Database structure in Microsoft SQL Server can be divided into two areas:

• Logical structure - the arrangement of the data into tables, the relationships between data in different tables, and the queries, views and other structures that allow the data to be manipulated, profiled and presented to the end-user.
• Physical structure - the physical arrangement of data files and transaction logs on the host server(s).
  
  Neverfail for SQL Server protects the PHYSICAL structure of a SQL Server database. In so doing, it automatically protects the logical structure as well.

Physical database structure

SQL Server uses two types of files to store data:

• data files ( .MDF , .NDF ), which act as a repository for all SQL Server data
• transaction logs ( .LDF ), which are used to ensure data integrity is maintained at the level of individual transactions

Neverfail for SQL Server protects and replicates data files and transaction logs from the active to the passive server.

SQL Server Instances

An INSTANCE of SQL server is a complete, independent installation of the entire SQL Server database engine. SQL Server instances are essentially independent of one another. A physical server may support up to 16 SQL Server instances. One of these instances is termed the DEFAULT instance; the other 15 are NAMED instances.

For each SQL Server instance, Neverfail for SQL Server will detect and monitor two services:

For the default instance:

• MSSQLSERVER
• SQLServerAgent

For any named instances:

• MSSQL$
• SQLAgent$

In addition, the Microsoft Search Service (MSSEARCH) is also detected and monitored. If any of these services stops unexpectedly, Neverfail for SQL Server will detect this and perform one or more user-defined recovery operations on that service.

Each SQL Server INSTANCE can support up to 32,767 databases. Each individual database can in turn contain up to 32,767 data files divided across a maximum of 256 filegroups. In practice, the number of databases, and the number of files and filegroups per database, will usually be far smaller.

Default Data File Locations

Every INSTANCE of SQL Server has a default directory where all the data files and transaction logs for that instance are stored, unless the user chooses a different directory. The location of this default directory is stored in the Windows registry.

Neverfail for SQL Server protects this location with filters terminating in a recursive wildcard ( \** ), meaning that all files and folders under the protected directory are replicated from the active to the passive server. If SQL Server is installed in its default location, the filter will look like this.

For a DEFAULT instance:

C:\Program Files\Microsoft SQL Server\MSSQL\Data\**

For a NAMED instance this will be:

C:\Program Files\Microsoft SQL Server\MSSQL$\Data\**

Neverfail for SQL Server obtains these locations by querying the Windows registry.

User-Specified Data File Locations

In addition, SQL Server data files and transaction logs may be placed in any user-specified location. This may be done for performance reasons - it may, for example, be desirable to place data files and transaction logs on different physical disks.

Neverfail for SQL Server protects such files on a per-file basis, using specific filters which name ONLY the protected file. Such filters might look like this:

C:\SQLServerData\MyDatabase\MyDataFile.MDF
D:\MoreData\MyOtherDataFile.LDF
E:\SQL Server\Transaction Logs\MyLog.LDF

Neverfail for SQL Server determines these locations by querying the master database for each SQL Server instance.

Other Protected Locations

Neverfail for SQL Server creates filters for some other default SQL Server directories, which are not directly related to data file or transaction log storage.

SQL Server File Extensions

Manually created files must have a file extension to be replicated with Neverfail for SQL Server Plug-in. Manually created files without a file extension will not be replicated to the passive server(s).

Applies To

Neverfail for SQL Server

Additional Information

None

KBID-398