Configuring a Database System for Deployment

In a SQL-NS deployment, the configuration of the SQL Server, and the instance and application databases, can have a dramatic impact on performance and reliability. Making the right choices about the databases at deployment time can also make administration tasks, such as backing up and restoring SQL-NS data, much easier later on. This section provides some overall guidance on setting up a database system and describes the SQL-NS settings that control the physical characteristics of the instance and application databases.

The choices you have to make for the database system in a SQL-NS deployment fall into three categories:

• Hardware configuration of the machine hosting SQL Server

• SQL-NS settings that control how the instance and application database objects are mapped to physical files

• Other database settings that affect runtime behavior

This section provides general recommendations in all three categories, but the specific choices appropriate for your SQL-NS instance depend on its particular characteristics and the constraints of your deployment environment. The choices you make will determine the database settings needed in the ICF and ADFs before you deploy your SQL-NS instance.

Disks and Physical Database Files

Every SQL Server database is mapped to a set of physical files located in the filesystem of the SQL Server machine. SQL Server manages these files for you, reading from and writing to them when it executes queries. Through configuration settings, you can control how many files are created for a particular database, where those files are located, and how their sizes are maintained.

By carefully managing the physical storage of data, you can leverage the hardware on your SQL Server machine to achieve optimal performance. This section covers some basic principles of database physical storage and provides some recommendations for the configuration of your SQL Server machine. The next section describes the ICF and ADF settings used to manage the physical storage of the SQL-NS instance and application database objects.

Managing File Sizes

When SQL Server creates a database file, it preallocates a certain amount of disk space for it. This preallocated space is consumed as records are written to the database. If the database grows large enough, it will eventually require additional storage space, beyond the initial capacity of the file. When this occurs, SQL Server expands the file by allocating additional disk space for it.

When creating a database, you can optionally specify an initial size for its physical files, a growth increment (the amount by which the file should be expanded when SQL Server needs more space), and a maximum file size. If you don't specify these values, SQL Server defaults are used.

The process of expanding a data file to provide more capacity is usually CPU and disk intensive. In some cases, expanding a file may involve copying significant parts of the data from one area of the disk to another. Therefore, expanding files too frequently can reduce the performance of a database. For optimal performance, your database files should be configured so that they won't have to be expanded often. To achieve this, you can specify an initial file size that matches the amount of data you expect to store in the database and provide a growth increment that allows the file to expand proportionately to expected increases in data volume. In the section "Defining the Physical Storage of SQL-NS Database Objects" (p. 459), later in this chapter, we look at how these attributes can be specified for the SQL-NS databases files.

Separation of Data and Log

In the physical storage of a SQL database, one file is used to store the transaction log, and other files are used to store the actual data. A database can have several data files, but always has exactly one log file.

Almost every SQL transaction requires access to both the data and the log files: SQL Server reads from or writes to the data files to perform the actual data operation, and writes to the log file to record the transaction. If both the data and the log files are on the same physical disk, the disk heads may have to move between different areas of the disk surface for every transaction. Physically moving a hard disk head can be slow (relative to the speed of data transfer), so unnecessary movement is likely to reduce the rate at which transactions can be committed.

Separating the data and log files onto different physical disks can significantly improve performance. With the files on separate disks, contention is reduced, and the files can be accessed concurrently without having to constantly move the disk heads across the physical medium. Later sections in this chapter describe the configuration settings used to place the data and log files for the SQL-NS instance and application databases onto separate physical disks.

Note

The same principle applies to parts of the data that are often accessed concurrently. SQL Server allows you to create several data files for a single database and spread these across separate physical disks. You can then assign tables and indexes to specific files (or specific groups of files, as described in the "Using Filegroups" section, p. 459, later in this chapter). By doing this, you can eliminate disk contention for the most commonly accessed parts of the data.

Separating and Sizing tempdb

Every SQL Server has a temporary database called tempdb. This database is used to store temporary tables, hold intermediate results of join queries, and materialize the results of cursors. The tempdb is frequently accessed when running SQL-NS applications because the internal SQL-NS stored procedures use several temporary tables, joins, and cursors.

For the same reasons outlined in the previous section, it's recommended that you store the tempdb data files on a dedicated physical disk, separate from other database files associated with the SQL-NS instance. Also, it's a good idea to increase the size of the tempdb data file from its default to prevent unnecessary and costly file expansion.

The tempdb configuration is usually treated as a general SQL Server issue, not necessarily specific to SQL-NS. Therefore SQL-NS does not offer any settings that affect tempdb sizing. For more information on how to properly configure tempdb, see the "Optimizing tempdb Performance" topic in the SQL Server Books Online.

Hardware Considerations for the Database Machine

To implement the storage recommendations given in this section, the database machine in your SQL-NS deployment needs at least three physical disks. The first disk will store the instance and application database data files, the second will store the tempdb data files, and the third will store the log files. The capacity of these disks needs to be sufficient to store the expected volumes of data and logging information in your applications.

You may need additional disks if you plan to separate various parts of your data as well. This is usually necessary only in the largest of SQL-NS applications. In the next section, we look at how to assign the various SQL-NS application database objects (such as the event tables, subscription tables, and notification tables) to specific physical files.

Carefully plan the disk layout of your database machine and decide on the locations of the physical files in advance. Before deploying your SQL-NS instance, you may need to specify these file locations in your ICF and ADFs, as described in the next section.

Defining the Physical Storage of SQL-NS Database Objects

When a SQL-NS instance is compiled, the instance and application database objects are created in one or more databases. As you've seen in previous examples, the ICF and ADFs can specify the database and schema in which the objects are created, by means of the <Database> element. In addition to the database and schema, you can also specify how the individual objects are to be stored in physical database files. By organizing the physical storage appropriately, you can eliminate or reduce performance bottlenecks due to disk contention or file growth operations. This section describes the ICF and ADF elements used to specify physical storage for SQL-NS instances and applications.

Using Filegroups

When a database is created, its physical storage is specified in terms of filegroups. A filegroup is literally a group of files that are managed and administered together. A filegroup can consist of a single file, or several files, potentially on different physical disks. Each filegroup has a name that can be referred to in subsequent operations.

Individual database objects, such as tables, stored procedures, and triggers, are created on specific filegroups. The physical contents of a database object created on a particular filegroup are spread across the files in that filegroup. One filegroup in a database can be designated as the default filegroup; when creating an object in that database, the default filegroup is used unless another filegroup is explicitly specified.

When defining a SQL-NS instance or application, you can specify the filegroups on which the SQL-NS compiler creates individual objects. For example, you can designate a particular filegroup to be used for an event class; the compiler will then create all the objects associated with that event class (including the events table, the event batches table, and associated indexes) on the specified filegroup.

The following section, "Creating Filegroups" (p. 459), explains how filegroups are created. The section "Specifying Filegroups for SQL-NS Database Objects" (p. 464) describes the ADF elements used to assign various parts of a SQL-NS application to specific filegroups.

Creating Filegroups

When a SQL-NS instance is first compiled, the resulting database objects can be placed in existing databases, or new databases can be created for them. The choice is controlled by the values of the <DatabaseName> elements in the ICF and ADFs.

If the database specified in the <DatabaseName> element in an ICF or ADF does not exist at compilation time, the SQL-NS compiler creates it. Within the <Database> element, several subelements can be used to define the database's filegroups (and other physical storage attributes). These are shown in Listing 13.1. When the SQL-NS compiler creates a database, it uses the values specified in these elements to set up its physical storage.

Listing 13.1. The <Database> Element in the ICF Used to Define Filegroups

 <NotificationServicesInstance>   ...   <Database>     <DatabaseName>InstanceDB</DatabaseName>     <SchemaName>NSInstance</SchemaName>     <NamedFileGroup>       <FileGroupName>Primary</FileGroupName>       <FileSpec>         <LogicalName>Primary1</LogicalName>         <FileName>C:\DataFiles\Primary1.mdf</FileName>         <Size>2MB</Size>         <MaxSize>20MB</MaxSize>         <GrowthIncrement>20%</GrowthIncrement>       </FileSpec>       <FileSpec>         <LogicalName>Primary2</LogicalName>         <FileName>C:\DataFiles\Primary2.mdf</FileName>         <Size>2MB</Size>         <MaxSize>20MB</MaxSize>         <GrowthIncrement>20%</GrowthIncrement>       </FileSpec>     </NamedFileGroup>     <NamedFileGroup>       <FileGroupName>SecondaryGroup</FileGroupName>       <FileSpec>         <LogicalName>Secondary1</LogicalName>         <FileName>C:\DataFiles\Secondary1.mdf</FileName>         <Size>2MB</Size>         <MaxSize>20MB</MaxSize>         <GrowthIncrement>20%</GrowthIncrement>       </FileSpec>       <FileSpec>         <LogicalName>Secondary2</LogicalName>         <FileName>C:\DataFiles\Secondary2.mdf</FileName>         <Size>2MB</Size>         <MaxSize>20MB</MaxSize>         <GrowthIncrement>20%</GrowthIncrement>       </FileSpec>     </NamedFileGroup>    <LogFile>       <LogicalName>Log</LogicalName>       <FileName>E:\LogFiles\Log.ldf</FileName>       <Size>10MB</Size>       <MaxSize>60MB</MaxSize>       <GrowthIncrement>20%</GrowthIncrement>     </LogFile>     <DefaultFileGroup>SecondaryGroup</DefaultFileGroup>     <CollationName>SQL_Latin1_General_CP1_CI_AS</CollationName>   </Database>   ... </NotificationServicesInstance> 

As used in Listing 13.1, the <Database> element defines a new database to be created by the SQL-NS compiler. It does not simply specify an existing database, as other examples (including the music store sample) have done. When used this way, the <Database> element may contain one or more <NamedFileGroup> elements that declare filegroups to use for the physical storage of the data. The example shown in Listing 13.1 declares two filegroups, one named Primary and another named SecondaryGroup. SQL Server mandates that every database must have one filegroup named Primary. Databases can optionally have other filegroups, and those can be given any name.

Each <NamedFileGroup> declaration consists of a name for the filegroup and a series of <FileSpec> elements that define the individual files in it. The example in Listing 13.1 uses two files for each filegroup. Each <FileSpec> element provides a logical name for the file, a physical filename (including a drive letter and path), and the file size attributes.

The <LogFile> element specifies the same attributes for the database's log file. Notice that the log file isn't part of any filegroup. Filegroups are used only for data files. The log always consists of a single file, declared separately.

The <DefaultFileGroup> element specifies which filegroup should be used by default when creating objects in the database. All SQL-NS instance objects (such as the subscribers and subscriber devices tables) are created on the default filegroup of the instance database. However, in the ADF, you can override the default and choose other filegroups for particular application database objects, as described in the following section.

Note

The <Database> element is also used to set the database collation, which determines the character set and sort order used for the data. The <CollationName> element specifies the name of the database collation to use. See the SQL-NS Books Online for more information on this element.

Notice that in Listing 13.1, the filenames for the data files and the log file specify different drive letters. Assuming these drive letters correspond to different physical disks, this ensures the separation of the data from the log. When configuring a SQL-NS instance for deployment, the specified filenames and paths must correspond to the disk layout of the deployment database server.

Tip

You can use parameters to specify the drive letters and paths in the ICF and ADFs. This allows you to change them without having to edit the code. For more information on parameters, see the "Parameters" section (p. 104) in Chapter 4.

This book's music store sample instance (and all its other samples) used the same database to store both the instance and the application database objects. In other words, the <Database> element in both the ICF and ADF specified the same value for <DatabaseName>. However, application database objects can also be stored separately from the instance database objects, in a different database, by specifying a different <DatabaseName> value in the ADF than the ICF. In fact, every application within an instance can store its database objects in a different database.

The same elements shown in Listing 13.1 can be used within a <Database> element in an ADF (the syntax is exactly the same as the ICF example) to specify how a new database should be created for the application's database objects. But, this makes sense only if the <DatabaseName> element in the ADF specifies a database name different from the associated ICF and the database does not already exist at compile time.

Caution

If you specify the same database for both the instance and the application database objects and the database does not exist at compile time, you should specify the physical storage attributes only in the ICF. In the ADF, specify the database and schema name as though the database already exists. In fact, when the application's database objects are created, the database will exist, because the compiler creates it while compiling the ICF first. Physical storage attributes specified in the ICF or ADF are ignored if the database already exists.

The elements shown in Listing 13.1 are relevant only if the database specified in the <DatabaseName> element does not exist at compilation time. If the database does exist, only the <DatabaseName> and <SchemaName> elements should be presentall other elements should be omitted. If the <NamedFileGroup>, <LogFile>, <DefaultFileGroup>, or <CollationName> elements are specified when the database already exists, they are ignored.

When a SQL-NS application's database objects are deployed into an existing database, you cannot create new filegroups via ADF elements, but you can still assign parts of the application to existing filegroups, as described in the following section. These filegroups must have been previously created in the database. See the sidebar "Creating Filegroups With T-SQL" (p. 463) for an example of the T-SQL syntax used to specify filegroups when creating databases outside of the SQL-NS compiler.

Creating Filegroups With T-SQL

Generally, the filegroups that make up the physical storage of a database are specified when the database is created. If you're creating databases outside of the SQL-NS compiler, you can define filegroups in a CREATE DATABASE statement. For example, the following CREATE DATABASE statement creates the music store database with two filegroups:

    CREATE DATABASE [MusicStore]    ON PRIMARY    ( NAME = Primary1,        FILENAME = 'C:\DataFiles\Primary1.mdf',        SIZE = 2MB,        MAXSIZE = 20MB,        FILEGROWTH = 20% ),    ( NAME = Primary2,        FILENAME = 'C:\DataFiles\Primary2.mdf',        SIZE = 2MB,        MAXSIZE = 20MB,        FILEGROWTH = 20% ),    FILEGROUP SecondaryGroup DEFAULT    ( NAME = Secondary1,        FILENAME = 'C:\DataFiles\Secondary1.mdf',        SIZE = 2MB,        MAXSIZE = 20MB,        FILEGROWTH = 20% ),    ( NAME = Secondary2,        FILENAME = 'C:\DataFiles\Secondary2.mdf',        SIZE = 2MB,        MAXSIZE = 20MB,        FILEGROWTH = 20% )    LOG ON    ( NAME = 'Log',        FILENAME = 'E:\LogFiles\Log.ldf',        SIZE = 10MB,        MAXSIZE = 60MB,        FILEGROWTH = 20% ) 

The music store database used in this book's sample is in fact created with a statement much like the one shown here. You'll find this statement in the file C:\SQL-NS\Samples\MusicStore\CreateMusicStore.sql on your system. The exact file paths used in the script differ from the ones shown here (the ones in the script are customized for the install location you chose), but the number, names, and definitions of the filegroups are the same.

Like the example in Listing 13.1, the statement defines two filegroups. The first is called PRIMARY (as required by SQL Server) and encompasses two files. The second filegroup in this example is called SecondaryGroup, and, like the PRIMARY filegroup, also encompasses two data files.

The SecondaryGroup filegroup is designated as the default filegroup for the database by means of the DEFAULT keyword after its name. All objects subsequently created in the database are created on the default filegroup unless another filegroup name is explicitly specified.

The filegroup definitions each consist of two file specifications. Each file specification provides a logical name, a physical filename, the initial size, the maximum size, and the growth increment. Sizes and growth increments can be specified in several ways. The SQL Server Books Online provides complete details in the reference documentation for the CREATE DATABASE statement.

The CREATE DATABASE statement also specifies attributes for the physical storage of the database's transaction log. Much like the data file specifications, the log file specification includes a logical name, the physical filename, the size, the maximum size, and the growth increment.

Specifying Filegroups for SQL-NS Database Objects

The <FileGroup> element can be used in event class, subscription class, and notification class declarations to instruct the SQL-NS compiler to use a particular filegroup when creating the corresponding database objects. Listing 13.2 shows the use of the <FileGroup> element in the music store application's ADF.

Listing 13.2. Specifying Filegroups for the Event, Subscription, and Notification Classes

 <Application>   ...   <EventClasses>     <EventClass>       <EventClassName>SongAdded</EventClassName>       <FileGroup>Primary</FileGroup>       ...     </EventClass>   </EventClasses>   <SubscriptionClasses>     <SubscriptionClass>       <SubscriptionClassName>NewSongByArtist</SubscriptionClassName>       <FileGroup>SecondaryGroup</FileGroup>       ...     </SubscriptionClass>     <SubscriptionClass>       <SubscriptionClassName>NewSongByGenre</SubscriptionClassName>       <FileGroup>SecondaryGroup</FileGroup>       ...     </SubscriptionClass>   </SubscriptionClasses>   <NotificationClasses>     <NotificationClass>       <NotificationClassName>NewSong</NotificationClassName>       <FileGroup>Primary</FileGroup>       ...     </NotificationClass>   </NotificationClasses>   ... </Application> 

Each <FileGroup> element must specify the name of a filegroup in the application's database (the database specified in the ADF's <DatabaseName> element, not shown in Listing 13.2). This can either be the name of a filegroup declared in one of the <NamedFileGroup> elements, if the database is created by the SQL-NS compiler, or it can be the name of a previously created filegroup if the database was created earlier. In the case of the music store sample, the database is not created by the SQL-NS compiler; the filegroup names specified here are the names of the filegroups defined in the CREATE DATABASE statement in the script that sets up the music store database, as described in the sidebar "Creating Filegroups With T-SQL" (p. 463).

In Listing 13.2, the event class and notification class specify the Primary filegroup, and the subscription classes specify the SecondaryGroup filegroup. The events table, the notifications table, and other objects associated with the event and notification classes will be physically stored on the files associated with the Primary filegroup. The subscriptions tables and other related objects will be physically stored on the SecondaryGroup filegroup.

Caution

You can update the <FileGroup> element on an event, subscription, or notification class, but doing so causes the compiler to drop and re-create the associated database objects, including the tables. This means that any data in these tables is lost when you perform the update. Before updating an instance when you have changed the <FileGroup> values, be sure to back up any data in the tables you may need.

Choosing Appropriate File Sizes When Defining Filegroups

When specifying the size attributes for the data and log files used to store instance-level data, the most important consideration is the expected size and growth rate of the subscriber base. Because subscriber data (and subscriber device data) is stored in the instance database, specifying appropriate initial size and growth increment values for the instance database files will prevent SQL Server from having to perform costly file expansion operations as the subscriber base grows. Refer to the "Database Resource Planning" topic in the SQL-NS Books Online for information on calculating an appropriate size for your instance database.

Also, for best performance, it's important that the files in the filegroups you use for the event, subscription, and notification data are sized appropriately. When specifying initial size and growth increment values for the files, you must consider the expected size of the data and how quickly you expect it to grow. Again, the "Database Resource Planning" topic in the SQL-NS Books Online provides detailed guidance on calculating the size of the entities in the application database.

The size of the transaction log file is also important. The log file is used heavily during compilation operations, such as creating and updating the instance. Because both creating and updating typically involve many transacted operations, a significant amount of log space is required. The exact amount varies with the complexity of the instance and its applications, but for most applications, a minimum of 10MB of log space is recommended. If you set a maximum size on the log file, this should be quite large to ensure that create and update operations don't fail due to a lack of log space.

Setting the Database Recovery Model

You can specify the recovery model for a database to control the way that SQL Server logs the transactions performed against it. SQL Server supports three recovery models: simple, full, and bulk-logged.

When the simple recovery model is used, SQL Server does minimal logging for transactions executed against the database. This provides the best performance (because the logging overhead is small), but there is a potential for data loss in the case of a system crash or database corruption. SQL Server cannot always restore the database to an arbitrary point in time because complete logging information is not always kept.

In both the full and bulk-logged recovery models, SQL Server maintains detailed log information. Using this information, SQL Server can roll back or forward all previously executed transactions. The full and bulk-logged models differ in exactly when the log information is written and therefore make different guarantees about data loss. However, both models offer much stronger guarantees than the simple recovery model. This added reliability comes at the cost of increased logging overhead, which can bring down performance.

For most SQL-NS applications, the full recovery model should be used for the instance and application databases. Using the full recovery model minimizes the potential for data loss should a catastrophic failure occur. However, if you want the highest possible database performance and can tolerate some potential data loss, it may be appropriate for you to use the simple recovery model.

When a database is created, its recovery model is set to the recovery model of the SQL Server's model database. You can change the recovery model of a database after it is created using an ALTER DATABASE statement or by using Management Studio. See the "Selecting a Recovery Model" topic in SQL Server Books Online for more detailed information on the supported recovery models and the specific instructions for changing a database's recovery model.

Note

There are no dedicated ICF or ADF elements for specifying the recovery models for the instance and application databases.