SQL Server configuration rules with SnapManager

Before you configure your SQL Server environment or migrate your database and transaction files to LUNs, read and understand the following topics:

Maximum configurations supported by SnapManager

The following table lists the maximum configuration capacities tested and supported for the SnapManager environment.

Configuration capacity	Maximum
SQL Server instances per SQL Server computer	6
Databases per SQL Server instance For one or two SQL Server instances per SQL Server computer For three or more SQL Server instances per SQL Server computer	253 100
Databases per SQL Server computer	600
Databases per LUN	100
Databases per storage system volume	100
Filegroups per database	10
Filegroups per SQL Server computer	600
Database files per SQL Server computer	1200
Storage system volumes That can be used to store a single database That can be used to store LUNs connected to an individual SQL Server computer	8 12

Although SnapManager does not prevent you from creating configurations that exceed these limits, such configurations are untested and unsupported. NetApp strongly recommends that you do not exceed any of these limits.

SQL Server database configurations supported

This topic describes some basic SQL Server data configurations supported by SnapManager. For more examples of supported configurations, see SQL Server configurations supported with SnapManager.

Distributing the files of a database

You can spread the files belonging to your database across multiple LUNs on different volumes. However, there are restrictions on this configuration, as described on the following topics:

Multiple databases sharing one or two LUNs

Multiple databases and all their associated files and transaction logs can share one or two LUNs provided that all files belonging to those databases are contained on that LUN or LUNs.

An illustration of multiple databases sharing one LUN is shown in Multiple databases on one LUN.
An illustration of multiple databases sharing two LUNs is shown in Multiple databases sharing two LUNs.

In this configuration, all databases in the shared LUN are backed up at the same time. However, you can restore any number of databases from the backup set.

SQL Server database configuration restrictions enforced

Before migrating your SQL Server 2000 or 2005 databases to LUNs, note the following restrictions enforced by the SnapManager Configuration Wizard.

All the files belonging to a single database

For any database that you migrate to LUNs for use with SnapManager, all of the files (the data files, as well as the transaction log files) must be migrated to LUNs.

Single database on multiple LUNs

The files belonging to an individual database can be spread across two or more LUNs, as long as those LUNs are not used for storing database files belonging to other databases.

No database files on the same LUN as the SnapInfo directory

You cannot migrate a database to the LUN on which the SnapManager SnapInfo directory resides.

No database files or SnapInfo directory on a SAN boot LUN

You cannot place database files, transaction log files, or a SnapInfo directory on a SAN boot LUN (a LUN configured as a boot device for a SAN host).

No user database files on the SQL Server 2005 root LUN

You cannot migrate a user database to a LUN that hosts the SQL Server 2005 root path, data root path, or SQL error log path.

SQL Server database configurations to avoid

If you add more databases or move databases to different LUNs without using the Configuration Wizard, you can create an invalid configuration that could cause SnapManager backup or restore operations to fail.

Note
Always run the Configuration Wizard after adding or moving SQL Server databases. The Configuration Wizard ensures that the SQL Server databases are stored in valid locations so that SnapManager backup and restore operations can be completed successfully.

Before you migrate your SQL Server data files and transaction log files to LUNs, note the following recommendations against certain invalid configurations that could be created outside of the SnapManager Configuration Wizard.

No other files on LUNs used by any database files

Do not manually store any directories or files (including system paging files) on the LUNs used for the SQL Server database files. These LUNs should store only the SQL Server database files (data files and transaction log files) that are placed there by the SnapManager Configuration Wizard.

No other files on the LUN used by the SnapInfo directory

Do not manually store any directories or files on the LUN used by the SnapInfo directory. This LUN should store only the directories or files that are placed there by the SnapManager Configuration Wizard.

Recommendations

If full-text indexing is enabled on an SQL Server 2005 database, NetApp recommends that you store the full-text search index catalog files on the same LUN as the database files.