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, you might not be able to perform an up-to-the-minute restore with existing backups. Therefore, perform a backup immediately following any configuration changes.
Any LUN or VMDK configuration is supported if you enable the unrestricted database layout option. If you do not enable the unrestricted database layout option, you should use only the configurations listed in this section. For details about the unrestricted database layout option, see Enabling SnapManager to allow databases on any LUN or VMDK configuration on page 294.
If you place your databases on NetApp LUNs without using the configuration wizard, the databases might be in an invalid state if the unrestricted database layout option is not enabled and you use an unsupported configuration. When a database is in an invalid state, you cannot use SnapManager to back it up. To correct the issue, you either need to use the Configuration wizard to
move the databases to a supported configuration or you need to enable the unrestricted database layout option.
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
• 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 any number of 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.
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 Storage Virtual Machine (SVM) 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 if the unrestricted database layout option is not enabled. 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.
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 154.
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
directory
Vol 3
LUN 1 SnapInfo directory
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.
Vol 3 LUN System database
LUN SnapInfo directory Microsoft
SQL Server instance
LUN 4 Vol 1
LUN 3 LUN 2 LUN 1 Database 1: Data file
Database 2: Data file Database 3: Data file Database 4: Data file
LUN 4 Vol 2
LUN 3 LUN 2 LUN 1 Database 1: Transaction log file
Database 2: Transaction log file Database 3: Transaction log file Database 4: Transaction log file
Storage system
Note: Using three volumes prevents the loss of the complete SQL Server environment and makes it quicker to restore from unmanaged media if a volume goes offline.
Multiple file groups belonging to the same database on different LUNs
The following illustration shows multiple file groups belonging to the same database residing on different LUNs within the same storage system volume.
LUN 3 LUN 2 LUN 1
Storage system Microsoft
SQL Server
Transaction logs
SnapInfo directory Database
Vol 2 LUN 1
LUN 2
Vol 1
File 2 Filegroup 1
Filegroup 2
Filegroup 3
File 1
File 2 File 1
File 2 File 1
Multiple VMDKs belonging to the same database on different volumes
The following illustration shows multiple file groups belonging to the same database residing on different VMDKs on the same datastores.
VMDK 3 VMDK 2 VMDK 1
Storage system Microsoft
SQL Server
Transaction logs
SnapInfo directory Database
VMDK 1
VMDK 2 File 2 Filegroup 1
Filegroup 2
Filegroup 3
File 1
File 2 File 1
File 2 File 1
Datastore 1
Datastore 2