SQL Server configurations supported with SnapManager

SnapManager databases can be configured on one or more storage systems. This section shows the various ways that you can place the data of your SQL Server on storage system volumes.

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, perform a backup immediately following any configuration changes.

SQL Server configuration requirements for SnapVault backups

If you archive a database to a SnapVault backup (clustered Data ONTAP only), the database and SnapInfo directory must be on separate volumes.

SQL Server configuration requirements for SMB shares

• There are no restrictions on how databases are placed on SMB shares.

For example, you can place any number of databases on the same SMB share and you can span a database across multiple SMB shares.

• A complete database must reside on SMB shares.

You cannot spread a database's files across LUNs and SMB shares.

• The SMB share name used by SQL Server database files must use the CIFS server name on the storage system, instead of the IP address of the management LIF or other data LIF.

Configuration and volume mount points | 55

SnapManager does not recognize a share by the CIFS server's IP address. It recognizes a share by the CIFS server's name.

For example, the name of a CIFS server is FOX_VS01. The Vserver also has an IPv6 data LIF called fd20-8b1e-b255-303---ac11-5b5.ipv6-literal.net. The database files need to use ^\

\FOX_VS01\sharename as a file path to the share. The files cannot use \\fd20-8b1e-b255-303--ac11-5b5.ipv6-literal.net\sharename as a path to the share.

If the database already uses a path with an IP address in the share name, manually detach the database, then attach the database using the SMB share path with the CIFS server name in its share name. Since both share paths point to same share, no files are moved.

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.

Vol 1

LUN 1

LUN 2

Vol 2

LUN 1 Microsoft

SQL Server

Database 2: Data file Database 1: Data file

Database 2: Transaction log file

SnapInfo directory

Database 1: Transaction log file

Storage system

Multiple databases on one LUN

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

Vol 1

LUN 1

LUN 2 Microsoft

SQL Server

Database 2: Data file Database 1: Data file

Database 2: Transaction log file SnapInfo directory

Database 1: Transaction log file

Storage system

This is a simple configuration, and it can be applied to an SQL Server that supports about 35 databases per volume.

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. 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.

Configuration and volume mount points | 57

Microsoft SQL Server

Vol 3 Vol 1

LUN 1 Database 1: Data file

Database 2: Data file Database 3: Data file Database 4: Data file

Storage system

LUN 1 Database 1: Transaction log file

Database 2: Transaction log file Database 3: Transaction log file Database 4: Transaction log file

Vol 2

LUN 1

SnapInfo directory

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 select 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.

Microsoft SQL Server instance

Vol 1

LUN 3 LUN 2 LUN 1 Database 1: Data file

Database 2: Data file Database 3: Data file

Vol 2

LUN 3 LUN 2 LUN 1 Database 1: Transaction log file

Database 2: Transaction log file Database 3: Transaction log file

SnapInfo directory

Storage system Vol 3

LUN 1

Multiple SQL Servers and one storage system 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 Server instances, there is an increased possibility of creating a busy Snapshot copy.

• For information about busy Snapshot copies, see "Busy Snapshot error prevents deletion of backup set" in Explicitly deleting backup sets using SnapManager on page 172.

Configuration and volume mount points | 59

Storage system

LUN 4 Vol 1

LUN 2 Microsoft SQL

Servers

Database 1: Transaction log file Database 2: Data file

LUN 3 LUN 1 Database 1: Data file

Database 2: Transaction log file Vol 2

LUN 1 SnapInfo