Appendix C: Application Database Design Guidelines

It is important to recognize that the database server installation on a target system is not always dedicated to a single application. Several different database applications may need to share the same database server. The guidelines proposed in this section help avoid conflicts between applications, and can help avoid problems or incompatibilities when installing an application database.

Application Database Naming Guidelines

The following guidelines concerning database names can be used to help avoid naming conflicts between applications.

Make application database names configurable at install time. If a conflict does exist, a new unique application database name can be selected and installation can proceed.
Make application database data and log file names and locations configurable at install time. If a conflict exists, a new unique file specification can be chosen and installation can proceed.
Do not hard code database names in application source code or in stored procedures. Implement them as variables or parameters when possible.
Locate your application database data and log files in a folder underneath the SQL Server data directory. Administrators will be able to find the files more easily, and storing them in a subdirectory helps keep them separate from the system database data and log files.

Database Option Guidelines

SQL Server and MSDE provide several application database specific configuration options. The following table lists each database option and the default and recommended settings for a typical application database. The term "Load Dependent" means the setting depends upon the relative workload supported by the database. Note that these recommendations are based upon the author's experience and may not apply in all situations.

Table 16. Database option guidelines

Option	Default Setting	Recommended Setting	Remarks
ANSI null default	Disabled	Disabled	Enable only for ANSI SQL-92 compatibility
ANSI nulls	Disabled	Disabled	Enable only for ANSI SQL-92 compatibility
ANSI warnings	Disabled	Disabled	Enable only for ANSI SQL-92 compatibility
auto create statistics	Enabled	Enabled	Enable for optimal performance
auto update statistics	Enabled	Enabled	Enable for optimal performance
autoclose	Load Dependent	Load Dependent	Enable for installations with medium to low workload, disable for high volume workload.
autoshrink	Load Dependent	Load Dependent	Enable for installations with medium to low workload, disable for high volume workload.
concat null yields null	Disabled	Disabled	Enable by way of client session if needed
cursor close on commit	Disabled	Disabled	Enable by way of client session if needed
dbo use only	Disabled	Disabled	Enable for administrative purposes only
default to local cursor	Disabled	Disabled	Enable for backward compatibility only
merge publish	Disabled	Disabled	Enable for replication support only
offline	Disabled	Disabled	Used for offline media only
published	Disabled	Disabled	Enable for replication support only
quoted identifier	Disabled	Disabled	Enable for backward compatibility only
read only	Disabled	Disabled	Enable for administrative purposes only
recursive triggers	Disabled	Disabled	Enable only if recursive triggers are required
select into/bulkcopy	Disabled	Disabled	Temporarily enable to allow fast non-logged operations
single user	Disabled	Disabled	Enable for administrative purposes only
subscribed	Disabled	Disabled	Enable for replication support only
torn page detection	Disabled	Enabled	Enable for improved reliability unless disk i/o subsystem supports battery backup
trunc. log on chkpt.	Disabled	Enabled	Enable for easier administration when transaction log backups are not used

Code Page and Sort Order Guidelines

One of the most common code page and sort order selected during the installation of SQL Server 7.0 or MSDE 1.0 is sort order id 52. This corresponds to the Windows ANSI code page 1252 using a dictionary order, case-insensitive sort order. Code page 1252 is fully compatible with the ISO 8859-1 standard character set, and is the most commonly used character set for the United States and countries that speak Western European languages including English, French, German and Spanish.

If your value-added business solution is intended to be installed in a region that speaks one of these languages, it is recommended that any file-based application database installation images being created for redistribution be originally built on a database server installation that uses this same code page and sort order. This will ensure maximum compatibility with the vast majority of database server installations.

If you intend to target several different regions that commonly use different code pages, such as the United States and Japan, you must either distribute multiple versions of your file-based application database installation image implemented in different code pages, or resort to using a script-based installation exclusively.

An error condition will occur if the code page and/or sort order of the target database server installation differs from that of the database server installation that created a file-based or backup-based application database installation image. If a script-based installation is not available, the database server will have to be reinstalled with a compatible code page and/or sort order in order to proceed.

Unicode Locale Id and Unicode Comparison Style Guidelines

One of the most common locale IDs selected during the installation of SQL Server 7.0 or MSDE 1.0 is LCID 1033. This is the most commonly used locale ID for the United States and countries that speak Western European languages including English, French, German and Spanish.

The default Unicode comparison style for LCID 1033 is 196609 (hex 0x3001), which corresponds to:

Case Insensitive.
Accent Insensitive.
Kana Insensitive.
Width Insensitive.

If your value-added business solution is intended to be installed in a region that speaks one of these languages, it is recommended that any file-based application database installation images created for redistribution be originally built on a database server installation that uses the same Unicode locale ID and comparison style. This will ensure maximum compatibility with the vast majority of database server installations.

If you intend to target several different regions that commonly use different locale ID and/or comparison styles, such as the United States and Japan, you must either distribute multiple versions of your file-based application database installation image implemented in different code pages, or resort to using a script-based installation exclusively.

An error condition will occur if the Unicode locale ID or comparison style of the target database server installation differs from that of the database server installation that created a file-based or backup-based application database installation image. If a script-based installation is not available, the database server will have to be reinstalled with a compatible Unicode locale ID and/or comparison style in order to proceed.

Security Guidelines

The following guidelines are provided to help create a secure an application database after installation and to simplify security administration tasks.

Securing the Database Server

Use Windows NT authentication if possible. Windows NT authentication is the preferred form of authentication for SQL Server 7.0 or MSDE 1.0 applications. Use of Windows NT authentication greatly simplifies the security administration for a database application because it eliminates the need to maintain separate database specific user accounts and passwords.
Restrict use of SQL Server authentication to database server installations on Windows 95 or Windows 98.
Change the sa password. By default, all database server installations are configured to support SQL Server authentication. The sa account is well known to anyone familiar with database server administration. Changing the password will help prevent unauthorized access to your database server and potential catastrophic data loss.
Do not create objects in system databases. Never use system databases to host application tables, views, or stored procedures. System databases are subject to dramatic changes when upgrading to a new release, and are impossible to redistribute.
When creating new SQL Server login ID programmatically, check to see if a duplicate ID exists first. If it does, use a different login ID because the duplicate may be in use by another application.
Do not hard code SQL Server login IDs and/or passwords in your application. This could result in conflicts if another database application uses the same login IDs. Make them configurable so that they can be set at install time or re-configured later.

Securing an Application Database

Limit membership to the system-defined sysadmin server security role. sysadmin members inherit database owner privileges in all application databases.
Grant database object permissions to database roles, not database users. This greatly simplifies creation and management of permissions and avoids site-specific user account configuration problems.
Create all database objects as dbo. Object ownership by individual database users can unnecessarily complicate security administration.
All new user accounts added to an application database are made members of the system-defined public database security role, including the special guest database user. For this reason, it is important to limit permissions granted to the public database role to the most generic of operations.
Avoid extensive use of the guest database user account. It complicates auditing and object ownership in databases that permit object creation by individual database users.
Use system-defined server roles instead of statement permissions to grant administrative privileges in a database. Role membership is easier to track and maintain than individual statement permissions.

Guidelines for Minimizing Administration

Configuring Database Server Options

The following table lists each database server configuration option exposed by SQL Server 7.0 and MSDE 1.0, including the option category and default setting. Setting database server options belonging to the performance category to the default value is the recommended to achieve optimal performance and to minimize administration for typical database applications. For more information on database server configuration options, see the SQL Server Books Online for SQL Server 7.0.

Table 17. Default server configuration options

Server Option	Category	Default
Standard Options
allow updates	security	0
default language	locale	locale dependent
language in cache	locale	locale dependent
max text repl size (B)	replication	65536
nested triggers	application	1
remote access	security	1
remote login timeout (s)	application	5
remote proc trans	application	0
remote query timeout (s)	application	0
show advanced options	security	0
user options	application	0
Advanced Options
affinity mask	performance	0
cost threshold for parallelism	performance	5
cursor threshold	performance	-1
default sortorder ID	locale	Locale dependent
extended memory size (MB)	hardware	0
fill factor (%)	performance	0
index create memory (KB)	performance	0
language neutral full-text	application	0
lightweight pooling	performance	0
locks	performance	0
max async IO	performance	32
max degree of parallelism	performance	0
max server memory (MB)	performance	2147483647
max worker threads	performance	255
media retention	application	0
min memory per query (KB)	performance	1024
min server memory (MB)	performance	0
network packet size (B)	performance	4096
open objects	performance	0
priority boost	performance	0
query governor cost limit	performance	0
query wait (s)	performance	-1
recovery interval (min)	performance	0
resource timeout (s)	performance	10
scan for startup procs	application	0
set working set size	performance	0
spin counter	performance	0
time slice (ms)	performance	100
two digit year cutoff	application	2049
Unicode comparison style	locale	Locale specific
Unicode locale ID	locale	Locale specific
user connections	performance	0