Home > Workload Solutions > SQL Server > White Papers > Dell PowerMax 2500 and 8500 Best Practices for Mission Critical SQL Server Databases > Example: using snapshots to create a SQL Server database copy
PowerMax snapshots and clones can be taken using Unisphere, Solutions Enabler CLI, or REST APIs. Snapshots can be created manually or using Snapshot Policies. The following high-level steps show how to create a database snapshot:
The following figure shows an example of using Unisphere to create a snapshot manually. In the Storage section, select the appropriate SG and click Protect. Then, select Create a snapshot using SnapVX.
To select a snapshot after it is created, select the SG and review the available snapshots under the DATA PROTECTION tab. Then, select a snapshot and click Link, as shown in the following figure:
If the target SG for the snapshot is newly created, add a masking view that presents it to the hosts where it is mounted. In this example, a new target SG is created and presented to a different WSFC with an existing SQL Server instance.
By default, shared volumes disks appear as offline in Windows Disk Management.
When the disks are online, add them to WSFC:
If mount point file-placement is used, mount the disks. When using mount points, configure a root-directory with a drive letter ahead of time on shared storage. This example shows a small PowerMax volume with drive letter U:\.
U:\userdb1copy_data1, U:\userdb1copy_data2, …., U:\userdb1copy_log
To set dependencies between the SQL Server service and the disks:
The preceding steps ensure that the snapshot target volumes with a consistent point-in-time copy of the user database from the source WSFC is available to the target WSFC to attach. Attach the database with a new name from SQL Server Management Studio (SSMS) or from a command line. For example:
PS C:\scripts> cat .\attach_userdb1copy.txt
USE [master]
GO
CREATE DATABASE userdb1_copy ON
( FILENAME = N'U:\userdb1copy_data1\SQLSERVER\userdb1_data1.mdf' ),
( FILENAME = N'U:\userdb1copy_data2\SQLSERVER\userdb1_data2.ndf' ),
( FILENAME = N'U:\userdb1copy_data3\SQLSERVER\userdb1_data3.ndf' ),
( FILENAME = N'U:\userdb1copy_data4\SQLSERVER\userdb1_data4.ndf' ),
( FILENAME = N'U:\userdb1copy_data5\SQLSERVER\userdb1_data5.ndf' ),
( FILENAME = N'U:\userdb1copy_log\SQLSERVER\userdb1_log.ldf' )
FOR ATTACH
GO
PS C:\scripts> $SQLSERVER = “SQLAUSTIN1”
PS C:\scripts> Invoke-Sqlcmd -ServerInstance $SQLSERVER -InputFile C:\Scripts\attach_userdb1copy.txt
The steps in this procedure can be accomplished in several ways. They can be scripted and automated to take place at regular times or on-demand.
When refreshing the snapshot target volumes, first remove the database disks dependencies from the target instance (if dependencies were created), remove the disks from the target WSFC, set the disks as offline in Disk Management, and refresh the snapshot target devices data. When the snapshot link operation starts, add the disks and their dependencies back to the cluster and attach the database.