Microsoft SQL Server Database Protection Using EqualLogic Auto-Snapshot Manager / Microsoft Edition Using EqualLogic Auto-Snapshot Manager / Microsoft Edition to perform online Smart Copy protection and recovery for SQL Server databases hosted on PS Series arrays.
Revisions The following table describes the release history of this Technical Report. Report Date Document Revision 1.0 January 2012 Initial Release Auto-Snapshot Manager Microsoft Edition v4.0 2.0 January 2013 Updated with support for ASM/ME 4.5 3.0 October 2013 Updated with support for ASM/ME 4.6 4.0 September 2014 Updated with support for ASM/ME 4.7 and 4.7.1 THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAIN TYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES.
TR1081 | Microsoft SQL Server Database Protection Using EqualLogic Auto-Snapshot Manager / Microsoft Edition
Table of contents Revisions ............................................................................................................................................................................................. 2 Table of contents .............................................................................................................................................................................. 4 Preface .........................................................................................
7 6.1 Snapshot Smart Copy restore options ...................................................................................................................... 20 6.2 In-Place database restore............................................................................................................................................ 22 6.3 SQL Server database restore with Apply Logs option ............................................................................................ 23 6.
Preface PS Series arrays optimize resources by automating performance and network load balancing. Additionally, PS Series arrays offer all-inclusive array management software, host software, and free firmware updates. Visit WWW.DELL.COM/PSseries for more information. Audience The information in this guide is intended for administrators that have deployed SQL Server and are interested in using EqualLogic snapshots for efficient protection and recovery of SQL databases.
Software and firmware versions The following table shows the software and firmware used for the preparation of this Technical Report. Vendor Model Software Revision Microsoft® Windows Server 2008, 2012 (x86, x64, R2) All Versions* Microsoft® SQL Server 2008, 2008 R2, 2012, 2014 Standard and Enterprise Dell™ PS Series Array Firmware Version 7.0.7, 6.0.11 and later* Dell™ EqualLogic™ Host Integration Tools – Auto-Snapshot Manager / Microsoft Edition Version 4.6, 4.7, 4.7.
1 Introduction Database protection and disaster recovery are among the top concerns for SQL Server database administrators. Requirements for reducing database backup windows and restore times continue as demands increase for continuous SQL Server database uptime. PS Series arrays provide administrators the ability to create volume based copies of data using snapshots, clones and replicas. These copies are known as “point-in-time” copies of volume data.
2 Auto-Snapshot Manager /Microsoft Edition 2.1 Volume Shadow Copy Service Auto-Snapshot Manager/Microsoft Edition utilizes Microsoft’s Volume Shadow Copy Service (VSS) architecture to provide application integration with SAN copy operations, Figure 1. During the VSS operation flow, Auto-Snapshot Manager initiates the process by requesting the SQL Server VSS Writer to prepare a database for a Smart Copy operation.
2.2 Auto-Snapshot Manager User Interface The ASM/ME GUI includes host management capabilities beyond just data protection. There are HIT group summary views, volume views for capacity, utilization, and snapshot reserve, and views for MultiPath session information. The Hosts or object area (callout 1) shows the hosts that have been added to the managed HIT Group. A HIT Group is simply a group of hosts managed by that instance of ASM/ME.
2.3 Overview of HIT Groups A HIT Group is a group of one or more hosts that are managed from ASM/ME. HIT Groups are useful because they allow multi-host management from any machine that is running ASM/ME. For example, if an administrator has to manage and backup Microsoft SQL Server databases residing on multiple instances and servers, they can create a HIT Group on a single instance of ASM/ME and manage multiple servers from there. 2.3.
Figure 3 Add Hosts Options in ASM/ME Each host must be authenticated by a user account that must have admin rights on the managed hosts. If necessary the Add Hosts wizard will install or upgrade the Windows Host Integration Tool kit on the host being added. ASM/ME will then pass a SSL Certificate onto the host for secure communication between the ASM/ME management instance and the managed host. Operations are passed from the management instance to the managed host and run from the managed host.
3 ASM/ME Smart Copies for SQL Server Auto-Snapshot Manager/Microsoft Edition (ASM/ME) creates Smart Copies utilizing the built-in PS Series SAN protection capabilities of volume snapshots, clones, and replicas. The following section will cover the Smart Copy types and options supported for SQL Server. All Smart Copies are transportable, and can be mounted on the same or a different server.
With ASM/ME, Smart Copy clones can be used for Restore All as New or side-by-side restore operations to duplicate production environments or create exact copies of database environments for testing and development scenarios. In addition, these database copies can also be used to offload operations from the production database such as reporting or data mining. 3.
3.5 Smart Copy Schedules ASM/ME includes a scheduler to enable ongoing Smart Copy protection at various intervals. The ASM/ME scheduler is based on the Windows Task Scheduler service and supports Smart Copy schedules as frequently as 5 minutes. The scheduler supports a “keep count” parameter that retains only the specified number of active Smart Copies for an object. This ensures that storage resources are maintained while data recovery remains highly available.
4 Setup and configuration best practices This section details some best practices for using ASM/ME with SQL Server and PS Series groups. 4.1 Database File Layout To create SQL Server database Smart Copies with ASM/ME, the SQL Server host must be on the same iSCSI network as the PS Series Group and all of the database files must reside on PS Series volumes presented through the SQL server host initiator. ASM/ME recognizes all the underlying volumes that make up the database object.
operation. Clone Smart Copies can be brought online through ASM/ME by using the Restore as New option. Best Practice: Clone Smart Copies are complete copies of database objects and should be cleaned up after use to avoid storage resource consumption. Replica Smart Copies are created if replication is configured for a volume or volumes that make up SQL Server databases.
Figure 4 13 ASM/ME backup document location TR1081 | Microsoft SQL Server Database Protection Using EqualLogic Auto-Snapshot Manager / Microsoft Edition
For cluster environments the backup document folder must be in a “cluster-accessible” location so each node can read and access the backup docs created from other nodes within the cluster. For stand-alone systems it is recommended that each host have a separate folder within the backup document directory, see Figure 5 below. Figure 5 ASM/ME Backup Document Best Practice Note: By default, backup documents created by ASM/ME are saved as files with a .bcd extension.
In Figure 6, the protection scenario uses a volume share located on the PS Series group. By using a SAN volume for the backup document share, data protection can be handled using standard PS Series snapshots or any other supported backup application. Using this protection scenario, the backup document path can be mounted on one host and shared among multiple hosts that may need access to the backup documents for restore options.
Alternatively, you can configure Distributed File System replication (DFS/R) to replicate backup document files from one server to many servers. This may be a best practice if you are planning to have many servers accessing the same set of backup documents and Smart Copies. The backup document protection scenarios vary by configuration as well as complexity. With any data protection scenario, you should test and verify your solution before putting it into a production environment.
5 Creating SQL Server Database Smart Copies Your business needs will determine the type of Smart Copy to create using ASM/ME. If the objective is fast recovery of SQL Server databases, use snapshot Smart Copies because they consume minimal storage resources and can be used for a complete recovery strategy. If the objective is to create a copy of a SQL Server database for longer duration, clone Smart Copies are recommended because they mirror the original database volume structure.
Figure 9 Create Smart Copy Window Choose the Smart Copy type and the behavior based on the planned usage of the Smart Copy (database restore, test DB, development, or another use). Note: For databases sharing the same volume, the Missing Components or Volumes window is shown (Figure 10). Torn Smart Copies mean that there were additional object components (database files) on the volume you selected for the Smart Copy.
Figure 10 Missing components or volumes Optionally, create a schedule to create SQL Server database Smart Copies by right clicking on the database and choosing the Configure New Schedule option or from the actions toolbar in ASM/ME if the database is highlighted. You can give the new schedule a name and add options to configure a repeating Smart Copy schedule. If Notifications are not set up, the schedule service will ask you to configure notification prior to creating the schedule.
6 Restoring SQL Server databases with Smart Copies There are various methods to restore and recover SQL Server databases using Smart Copies. The underlying PS Series architecture can either restore a volume from a Smart Copy or mount a Smart Copy online to a host on a mount point or as a new volume. These operations allow many different SQL Server database restore possibilities.
Restore selected database – Performs an “in-place” database restore and automatically starts a Selective Database Restore of only the chosen database files. If other databases share the smart copy volumes, the Selective Restore operation protects other database components from being damaged during the restore process. Restore All – Performs a restore of all the data in the Smart Copy and brings the database online.
6.2 In-Place database restore Assuming the database file layout has followed the best practices guidelines, this restore is useful for performing a fast point-in-time restore of SQL Server databases. The Smart Copy options that can be applied for an in-place SQL Server database restore are: Restore All Restore selected database When all the original database files reside on their own volumes, the Restore All operation should be used for the fastest restore operation.
Figure 12 Restore All – In-Place Restore Options 6.3 SQL Server database restore with Apply Logs option Applying log file backups after a database Smart Copy restore operation is a manual process and is therefore considered an advanced operation. For SQL Server databases running in the “Full Database Recovery Model”, it is vital to maintain a regular database log backup schedule to control growth of the database log file.
--Create tail-log backup. BACKUP LOG testdb TO DISK = 'Z:\SQLServerBackups\testdb_tail.bak' WITH NORECOVERY; GO --Restore Database using appropriate Smart Copy in ASM/ME with Apply Logs option. --Restore the regular log backup (from backup set 1). RESTORE LOG testdb FROM DISK = 'Z:\SQLServerBackups\testdb_log.bak' WITH FILE=1, NORECOVERY; --Restore the tail-log backup (from backup set 2). RESTORE LOG testdb FROM DISK = 'Z:\SQLServerBackups\testdb_tail.
6.4 Restore as New or “Side-by-Side” database restore A side-by-side database restore can be extremely useful to recover object level data within a database without taking the database offline to do so. In ASM/ME, a side-by-side restore of a SQL Server database means importing and mounting the Smart Copy to a host as a new copy of the original database.
6.5 Selective restore of SQL Server databases Selective restore of SQL Server databases refers to restoring databases that share the same PS storage volumes with other databases, as well as restoring from Smart Copy collections. During the Smart Copy creation, ASM/ME detects if databases are sharing the same volume with other databases. In that case, a Torn Smart Copy Warning message is displayed at the time of the Smart Copy creation.
For databases included in a Smart Copy collection, any individual database in the collection may be restored at any time. The advantages of using database Smart Copy collections are: A single schedule can create Smart Copies of many databases If one of the databases in the collection fails, ASM/ME allows a selective restore of that one database using the Restore selected database option.
6.6 Clone Smart Copy restore options There are three restore options for clone Smart Copies: Mount – This option mounts the Smart Copy set on the drive letter you specify. It does not restore the database. By default, the Smart Copy is mounted read-only but you can make the Smart Copy read-write. You can restore the database as a separate step manually.
7 ASM/ME Operations in a Windows Server Cluster ASM/ME is fully cluster aware and supports Smart Copies of clustered SQL databases. This section describes the various operations used to protect and recover SQL Server databases in a Failover Cluster. General understanding of how to configure and administer failover clusters is assumed. For additional information refer to the Host Integration Tools documentation. Note: At the time of this document, the ASM/ME v4.7.
The same ASM/ME restore options are available in failover clusters. Any or all of these options can be used to recover SQL Server data depending on the recovery method needed. Refer to the Restoring SQL Server Databases with Smart Copies section for more information on the following options. 7.2.
7.2.3 Restore as New – Drive Letter Option The Restore as New process will import the Smart Copy on all nodes and automatically connect to the Smart Copy using the iSCSI initiator. 1. Use ASM/ME Restore as New and choose the Assign the following drive letter option. 2. Assign drive letters to the Smart Copy volumes – Next 3. Add new database name if necessary – Restore a. This will connect to the Smart Copy using the iSCSI initiators on all nodes in the cluster.
Figure 18 Smart Copy Connected in Initiator 5. Add the same set of disks to the SQL Server cluster service, Figure 19. Figure 19 SQL Server – Add Storage 6. Add the same set of disks to the SQL Server dependency list, Figure 20.
Figure 20 Add SQL Server Dependencies 7. Attach the database to the SQL instance using the SQL Server Management Studio or an Attach command as in Figure 21. Note: The Create Database command must be used if the file stream feature is enabled for the instance.
Figure 21 Attach Database Options It is important to properly clean up the environment when finished with the Smart Copy Set. When finished with the mounted Smart Copy, follow these steps to detach and dismount the Smart Copy set from the cluster. 1. 2. 3. 4. 7.2.
4. Add the hosting volume and the restored Smart Copy set mount points as new disks in the cluster. a. Be sure to have all the disks associated with host volume and mount points in the Add Disks to a Cluster window, Figure 22. Figure 22 Add Hosting Volume and Mount Points to Cluster Follow the same steps as above to add the disks as resources of the SQL Server service and attach the database. Use the same cleanup process as above to detach the database and log off the Smart Copy set.
8 Advanced Operations with SQL Server Databases Auto-Snapshot Manager / Microsoft Edition, ASM/ME, has advanced operations and management capabilities that offer database administrator’s additional functionality beyond normal day-to-day protection. These operations may require some manual operations that are not automated by ASM/ME or a higher understanding of SQL Server techniques.
The following steps describe how to use this option: 1. Prepare the new host to access the PS Series group that is hosting the Smart Copies, including configuring the correct CHAP credentials. Note: To Mount or access a Smart Copy from a remote system the CHAP credentials should be set for Global Smart Copy access on each SQL Server system in the ASM/ME Settings. 2.
Figure 24 Importing a Smart Copy from one host to another 4. Once the Smart Copy backup document is imported, it will show up in the Smart Copies list on the new host. Now run ASM/ME on the new host, and select the Restore as New option for the database Smart Copy you want to restore on the new server. The Restore as New process will look for the SQL Server instance that is listed in the backup document.
Once the database is fully restored it can be used for other purposes, such as test and development or offloading operations such as reporting or data mining. This process can be applied to SQL database servers throughout the datacenter. 8.2 SQL Server Database Distribution using Template Databases A feature added with PS Series array firmware v5.x is template volumes and thin clones.
Create DB Clone Smart Copy Convert to Template Volumes Verify iSCSI Initiator Login Restore DB Clone as New Locate Name of Clone Volumes Refresh Services on SQL Instance (Set Read-Only Flag) Create DB Copies Using Thin Clones Figure 27 Figure 27: Thin Clone Database Process Flow 8.3 Step by Step Template Database Creation First prepare the database copy for template volumes. 1. Use ASM/ME to create a Clone Smart Copy of the SQL database being used 2.
1. In SQL Server Management Studio, set the new read only database offline prior to converting the clone to a template volume. 2. Select the Smart Copy used for the read-only copy of the database and locate the names of the volumes that were created in the properties window of ASM/ME, Figure 29. 3. Log into the PS Series Group Manager and locate the Smart Copy Clones in the Group Manager Volumes view.
Figure 30 Locate Volumes Used for Template Volumes Now that the volumes have been converted to template volumes in the PS Series Group Manager, services will need to be refreshed on SQL Server host to recognize the template database. Do not restart these services. 1. First refresh the Microsoft iSCSI initiator – Targets list and make sure the template volumes are logged in (connected). Log into the template volumes if necessary. 2. Next refresh the SQL Server instance. 3.
Figure 31 Create Thin Clone Using ASM/ME 8.4 SQL Server Database Replication using Smart Copies Replica Smart Copies can be useful for restoring SQL Server databases in the event of a primary site disaster. They can also be used to offload data mining and backup operations from the primary site to the remote site. A remote site refers to a host connecting to the replication partner or remote PS Series group. This section describes these options in more detail.
The Smart Copy restore options for replicas include: Mount as read-only – This operation imports the replica set (and pauses all incoming replication) and mounts the selected replica (not the entire replica set) as a read only volume so that it can be used as backup if necessary. When finished, the replica must be un-mounted which demotes the replica set and allows replication to continue. Restore selected database – This option will selectively restore a database from the replica smart copy.
Figure 32 Unmount and Resume Replication The Clone and Clone and Restore as New options both create a clone of the replica smart copy and list the clone in the ASM/ME Smart Copies area as non-VSS type smart copies. Replica clones can be used as replica copies without pausing replication between the PS Series groups. The Clone option will only create the clone and place the clone as an option in the Smart Copies list. An additional operation is required to mount the clone on the host chosen.
8.6 SQL Server Database Disaster Recovery with Smart Copy Replicas The recommended method for restoring SQL Server Databases from Smart Copy replicas is to use the Clone and Restore All as New option. This creates a clone of the Smart Copy replica, mounts the clone to the host you choose, and restores the database online to the host. This process looks for the server name in the backup document and times out. Add the new server name to the database server dialog box and continue with the restore process.
2. Use the Promote to Volume option for the database replica sets. This will pause inbound replication on the database volumes. Optionally add access control to the promoted volumes from the hosts that will mount the volumes to restore the database. (This step can also be done at a later time) 3. Attach the database volumes to the SQL instance. Access control will need to be granted to the volumes if not done already. The SQL database should now be up and running in the instance at the secondary site.
SQL Servers Figure 33 Off-Host Backup Flow In order for this process to work correctly, the backup software must support VSS backups for the application and the PS Series VSS Provider Service must be installed on the backup server as well as the application server for proper communication. Note: Be sure to verify that any backup schedules set from the 3 rd party vendor software do not collide with schedules set from Auto-Snapshot Manager Microsoft Edition.
9 Summary Auto-Snapshot Manager/Microsoft Edition (ASM/ME) can substantially increase SQL Server data availability by using PS Series array protection technologies. Snapshot, Clone, and Replica Smart Copies all play a vital role in robust SQL Server protection scenarios for on-demand data recovery and SQL Server database restores, as well as providing instant point-in-time copies for scalable distribution of SQL data.
A Command line options If you have existing scripts for running backups or performing other background operations, you can also schedule the creation of smart copy sets by adding an ASM/ME command to the script. Note: For a full set of PowerShell commands and scripting options see the PowerShell Tools User’s Guide and Quick Reference Guide on the Dell EqualLogic Host Integration Tools for Microsoft download page on the EqualLogic support site. https://eqlsupport.dell.com/support/download.aspx?id=3199 A.
This will run through the process of creating a Smart Copy Set and at the end instead of creating the Smart Copy, generate the script used as in Figure 35. This command can then be copied and used to create the Smart Copy in a script or batch file. Figure 35 Generated Command Scripts like this can be created for other Smart Copy operations and used accordingly. For a detailed description of script commands and sample usage, see the Host Integration Tools User Guide.
Technical Support and Customer Service Dell support service is available to answer your questions about PS Series SAN arrays. Contacting Dell 1. If you have an Express Service Code, have it ready. The code helps the Dell automated support telephone system direct your call more efficiently. 2. If you are a customer in the United States or Canada in need of technical support, call 1-800-945-3355. If not, go to Step 3. 3. Visit https://eqlsupport.dell.com/secure/login.aspx 4.
