DataGate® for SQL Server Multi-member File Support

---

Prior to 14.0, DataGate® for SQL Server only supported the creation of Physical and Logical files with zero to one member; with 14.0 DSS gained the ability to migrate Physical and Logical files with any number of members. Join files are still excluded. DSS handles these two file types differently, although with similar ease.

Physical Files

Single Member Physical Files

Single member files include those with a maximum member value of zero or one; both are handled the same way:

The underlying SQL Server objects of Single Member Files are optimized to use a table that shares the name of the file. The member, if present, will already have the same name as the file.

Single Member Files are forced to have exactly one member, and the member's assumed name is the same name of the file.

Multi-member Physical Files

Multi-member files (any file with a maximum member value greater than one) are handled with a table that represents the file, and which shares the name of the file.
An additional table is created for each member using the naming convention FileName#MemberName. Each table contains the data from one member.

Changing the name of the File-table will change the FileName portion of the names of eaxh associated Member-table. Members can be freely added, deleted, and renamed (so long as they retain the FileName suffix.

This applies to both migrated files and files created through DataGate.

Logical Files

Single Member Logical Files

Single member files include those with a maximum member value of zero or one; both are handled the same way:

The underlying SQL Server objects of Single Member Files are optimized to use a table that shares the name of the file. The member, if present, will already have the same name as the file.

Single Member Files are forced to have exactly one member, and the member's assumed name is the same name of the file.

Multi-member Logical Files

Multi-member files (any file with a Maximum Member value greater than one) are handled by creating a view that represents the file, and which shares the name of the file.
An additional view is created for each member using the naming convention FileName#MemberName. Each view contains the data from one member.

Changing the name of the File-view will change the FileName portion of the names of eaxh associated Member-view. Members can be freely added, deleted, and renamed (so long as they retain the FileName suffix.

Installation Configuration

Previously, any file with a MaxMember value other than "1" would throw an error. With multimember support active SQL instead treats each member as a separate file.

The implementation of this facility could break compatibility for older programs that set a MaxMember value greater than one while creating a new physical or logical file.
ADBFM Classic users are particularly at risk.

In order to preserve compatibility with these older programs, the support for files with more than one member is configurable.

Each DSS Server Installation can be configured to support multi-member files or be restricted to only support single member files.

Whenever a file is created on a restricted installation, the maximum number of members is forced to be one (1).

The ability to restrict an installation to force single members is included as an option in the DSS ASNA Stored Procedures Wizard (which runs immediately following the DSS Installation Wizard) to control the new behavior.

The configuration selection to support files with multiple members is stored in a row of the configuration table 'Master.dbo.ASNA_LibL' with a LibType of 'M' and a LiblName value of SUPORTSMULTIMEMBER as shown below: