SQL Server configurations supported with SnapManager

SnapManager databases can be configured on one or more storage systems. The illustrations in this section show the various ways you can place the data of your SQL Server on storage system volumes.

The supported configurations are as follows:

Note
If you change the database configuration after performing a SnapManager backup, you might not be able to perform an up-to-the-minute restore using that backup. Therefore, NetApp recommends that you perform a backup immediately following any configuration changes.

Multiple databases on different LUNs within the same volume

The following supported configuration shows multiple SQL Server databases sharing the same volume but residing on different LUNs. This is the simplest configuration, and it can be applied to an SQL Server that supports between 10 and 20 databases.

Multiple databases on one LUN

The following illustration shows multiple SQL Server databases and all their associated files and transaction logs on one LUN.

When an SQL server configuration has to access many small databases, several databases must share a single LUN. The databases should be grouped according to criteria, such as performance, acceptable downtime, or backup requirements.

Note
In this configuration, all databases in the shared LUN are backed up at the same time, even if certain databases have not been selected for the backup. However, you have the option to select which databases you want to restore from a multiple-database backup.

Multiple databases sharing two LUNs

The following illustration shows an example of multiple SQL Server databases and all their associated files and transaction logs sharing exactly two LUNs. The database files cannot reside on any other LUNs. This configuration requires a third LUN for the SnapInfo directory. The LUNs can be located on the same or different storage system volumes. The illustration shows an example in which each LUN is located on a different volume.

By placing the data files for multiple databases on one LUN and the transaction logs for those databases on the other LUN, SQL database performance is improved by separating the random I/O patterns of the data files from the sequential I/O patterns of the transaction log files.

Note
If you choose to restore only a subset of the databases that reside on one or two LUNs shared by multiple databases, then a stream-based restore method is used rather than the online Snapshot restore method.

Single SQL Server and multiple storage system volumes

The following illustration shows a configuration in which the data and transaction log files of an SQL Server database reside on separate storage system volumes. Placing all transaction logs on one volume and using another volume for all the database files is partly due to performance. If the volume with the data files fails, it is still possible to back up the log file, restore the last full backup, and then apply all backed-up current transaction logs. This configuration requires another volume for the SnapInfo directory.

Multiple SQL Servers and one storage system volume

The following illustration shows multiple SQL Servers with LUNs housing all the server's data files and transaction log files on the same volume.

When the SQL Server environment does not generate high I/O load, a single volume can optimize the use of disk and volume space. However, this configuration has two disadvantages:

If the volume fails, all databases are lost, including the current transaction log files.
With a single volume housing databases for multiple SQL Servers, there is an increased possibility of creating a busy Snapshot.
For information about busy Snapshot copies, see Busy Snapshot error prevents deletion of backup set.

Note
If your storage system is equipped with FlexVol^TM technology (available beginning with the Data ONTAP® 7.0 release family of software), you can minimize the risk of busy Snapshot copies with this configuration by configuring one FlexVol per SQL Server.

Multiple SQL Server instances on the same storage system volume

The following illustration shows a storage system volume with LUNs containing the data files of multiple SQL Server instances residing on a storage system volume that is different from the volume on which the LUNs for the transaction log files reside.

Note
Using three volumes prevents the loss of the complete SQL Server environment and makes it quicker to restore from archive if a volume goes offline.

Multiple SQL Server file groups belonging to the same database on different LUNs

The following illustration shows multiple SQL Server file groups belonging to the same database residing on different LUNs within the same storage system volume.