Microsoft Edition: Advanced Operations

TECHNICAL REPORT SQL Server Database Protection Using AutoSnapshot Manager / Microsoft Edition: Advanced Operations ABSTRACT This Technical Report wi...
Author: Kory Dalton
0 downloads 2 Views 926KB Size
TECHNICAL REPORT

SQL Server Database Protection Using AutoSnapshot Manager / Microsoft Edition: Advanced Operations ABSTRACT This Technical Report will discuss advanced operations and techniques on how to use AutoSnapshot Manager / Microsoft Edition to protect SQL Server databases as well as enhance SQL Server operations in the datacenter.

TR1061 V1.0

Copyright © 2010 Dell Inc. All Rights Reserved. Dell EqualLogic is a trademark of Dell Inc. All trademarks and registered trademarks mentioned herein are the property of their respective owners. Possession, use, or copying of the documentation or the software described in this publication is authorized only under the license agreement specified herein. Dell, Inc. will not be held liable for technical or editorial errors or omissions contained herein. The information in this document is subject to change. July 2010

WWW.DELL.COM/PSseries

PREFACE Thank you for your interest in Dell EqualLogic™ PS Series storage products. We hope you will find the PS Series products intuitive and simple to configure and manage. PS Series arrays optimize resources by automating volume and network load balancing. Additionally, PS Series arrays offer all-inclusive array management software, host software, and free firmware updates. The following value-add features and products integrate with PS Series arrays and are available at no additional cost: •

PS Series Array Software o

o



ƒ

Group Manager GUI: Provides a graphical user interface for managing your array

ƒ

Group Manager CLI: Provides a command line interface for managing your array.

Manual Transfer Utility (MTU): Runs on Windows and Linux host systems and enables secure transfer of large amounts of data to a replication partner site when configuring disaster tolerance. You use portable media to eliminate network congestion, minimize downtime, and quick-start replication.

Host Software for Windows o

o •

Firmware – Installed on each array, this software allows you to manage your storage environment and provides capabilities such as volume snapshots, clones, and replicas to ensure data hosted on the arrays can be protected in the event of an error or disaster.

Host Integration Tools ƒ

Remote Setup Wizard (RSW): Initializes new PS Series arrays, configures host connections to PS Series SANs, and configures and manages multipathing.

ƒ

Multipath I/O Device Specific Module (MPIO DSM): Includes a connection awareness-module that understands PS Series network load balancing and facilitates host connections to PS Series volumes.

ƒ

VSS and VDS Provider Services: Allows 3rd party backup software vendors to perform off-host backups.

ƒ

Auto-Snapshot Manager/Microsoft Edition (ASM/ME): Provides point-in-time SAN protection of critical application data using PS Series snapshots, clones, and replicas of supported applications such as SQL Server, Exchange Server, Hyper-V, and NTFS file shares.

SAN HeadQuarters (SANHQ): Provides centralized monitoring, historical performance trending, and event reporting for multiple PS Series groups.

Host Software for VMware o

Storage Adapter for Site Recovery Manager (SRM): Allows SRM to understand and recognize PS Series replication for full SRM integration.

o

Auto-Snapshot Manager/VMware Edition (ASM/VE): Integrates with VMware Virtual Center and PS Series snapshots to allow administrators to enable Smart Copy protection of Virtual Center folders, datastores, and virtual machines.

o

MPIO Plug-In for VMware ESX: Provides enhancements to existing VMware multipathing functionality.

Current Customers Please Note: You may not be running the latest versions of the tools and software listed above. If you are under valid warranty or support agreements for your PS Series array, you are entitled to obtain the latest updates and new releases as they become available. To learn more about any of these products, contact your local sales representative or visit the Dell EqualLogic™ site at http://www.equallogic.com. To set up a Dell EqualLogic support account to download the latest available PS Series firmware and software kits visit: https://www.equallogic.com/secure/login.aspx?ReturnUrl=%2fsupport%2fDefault.aspx

TABLE OF CONTENTS Revision Information........................................................................................................ iii  Introduction .........................................................................................................................1  SQL Server Database Restore to a New Server .............................................................2  SQL Server Database Distribution Using Template Databases ..................................5  SQL Server Database Replication using Smart Copies ................................................9  Data Mining with Smart Copy Replicas .................................................................10  Restoring SQL Server Databases with Smart Copy Replicas .............................11  Off-Host Backup and Recovery .....................................................................................13  Summary ............................................................................................................................14  Technical Support and Customer Service ...................................................................15 

REVISION INFORMATION The following table describes the release history of this Technical Report. Report

Date

Document Revision

1.0

July 2010

Initial Release

The following table shows the software and firmware used for the preparation of this Technical Report. Vendor

Model

Software Revision

Microsoft®

Windows Server 2003, (x86, x64, R2)

Service Pack 2 or later

Microsoft®

Windows Server 2008, (x86, x64, R2)

All Versions

Microsoft®

SQL Server 2008, (32bit, 64bit)

Microsoft®

SQL Server 2005, (32bit, 64bit)

Version 9.0.3042 (Service Pack 2 or later)

Dell™

PS Series Firmware

Version 5.0.0 and later*

Dell™

EqualLogic™ Host Integration Tools – Version 3.4 and later* Auto-Snapshot Manager / Microsoft Edition

*For earlier version support see the Product Release Notes The following table lists the documents referred to in this Technical Report. All PS Series Technical Reports are available on the Customer Support site at: support.dell.com Vendor

Document Title

Dell™

EqualLogic Host Integration Tools v3.4 Documentation

Dell™

Auto-Snapshot Manager / Microsoft Edition User Guide

Dell™

EqualLogic Technical Reports: http://www.equallogic.com/resourcecenter/documentcenter.aspx

Dell™

Dell Database Solutions: SQL Server http://www.dell.com/content/topics/global.aspx/sitelets/solutions/software/db/mi crosoft_sql_2005_sit?c=us&cs=555&l=en&s=biz

Microsoft®

SQL Server 2005: Deployments and Tests in an iSCSI SAN

Microsoft®

SQL Server Books Online: Overview of the Recovery Models

Microsoft®

SQL Server 2008 Home

Microsoft®

How to Delay Loading of Specific Services

Microsoft®

How to retrieve a specific table or rows from database backups or transaction log backups in SQL server

SQL Server Database Protection Using ASM/ME: Advanced Operations

iii

INTRODUCTION Auto-Snapshot Manager / Microsoft Edition, ASM/ME, has advanced operations and management capabilities that offer database administrator’s additional functionality other than 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. Advanced operations discussed in this report will include: • • • •

Restoring SQL Server databases to new servers using transportable Smart Copies SQL Server Database Distribution Using Template Databases SQL Server Database Replication Using Smart Copies o Data mining capabilities Using Smart Copy Replicas o Restoring SQL Server Databases with Smart Copy Replicas Off-Host Backup and Recovery

This document should only be read after understanding basic ASM/ME operations with SQL Server databases documented in the technical report: Microsoft SQL Server Database Protection Using EqualLogic Auto-Snapshot Manager / Microsoft Edition. This technical report will discuss these operations and management features and give best practices on how they can be implemented and included to increase SQL Server data availability. The table below lists the restore capabilities of ASM/ME. This will form the basis of the operations used in this technical report. Table 1: Restore Options for Smart Copy Types Restore Option

Snapshot

Clone

Replica

Mount – Mounts the smart copy to a mount point or drive letter (does not restore the database)

Yes

Yes

No

Restore selected database Restores database in place through a selective restore of selected database

Yes

Yes

Yes on local instance only

Restore All - Restores all the volume data in place, the fastest restore option

Yes

No

No

Restore All as New - Restores all the volume data to a new location, Side-by-Side database restore using a mount point or drive letter.

Yes

Yes

No

Mount as Read-Only – Mounts a Smart Copy replica to the file system as read-only to a mount

No

No

Yes

SQL Server Database Protection Using ASM/ME: Advanced Operations

1

point or drive letter. Clone and Restore All as New – Creates a clone of a Smart Copy replica and restores the database from the clone to a mount point or drive letter.

No

No

Yes

Clone – Creates clones of the volumes that make up a Smart Copy replica.

No

No

Yes

SQL SERVER DATABASE RESTORE TO A NEW SERVER Using Smart Copies to copy SQL Server databases to additional servers requires some advanced management of the backup documents. In order to restore a database Smart Copy to another physical SQL Server host, that physical host needs to have ASM/ME installed and access to the Smart Copy backup documents. There are a few different options to share backup documents between hosts. See ASM/ME Backup Documents later on in this section. The Smart Copy option to restore a database Smart Copy to a new host is: •

Restore All as New

The following steps describe how to use this option: Step 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 properties. Step 2: Add the new CHAP Access Control Record for the volumes that make up the database, and set the ACL for Snapshots Only (Figure 1).

SQL Server Database Protection Using ASM/ME: Advanced Operations

2

Figure 1: CHAP – Snapshot Access Control Step 3: On the host that created the database Smart Copies, share the location of the backup documents (Figure 2). It is recommended to locate this share on protected storage reachable by all database servers.

Figure 2: Mount Backup Document Share Step 4: On the new host, mount the share hosting the ASM/ME backup documents and point ASM/ME to the backup document share using the properties options. Once the Backup Document share has been defined, the new host will see the available Smart Copies (Figure 3).

SQL Server Database Protection Using ASM/ME: Advanced Operations

3

Figure 3: Available Smart Copy Components Step 5: Run ASM/ME on the new host, and select the Restore All as New option for the database Smart Copy you want to restore on the new server. Step 6: The Restore All as New process will look for the SQL Server instance that is listed in the backup document. This process will eventually time out. In the field labeled ‘Database server to use’, enter the SQL Server instance name you want to restore the Smart Copy to (Figure 4).

SQL Server Database Protection Using ASM/ME: Advanced Operations

4

Figure 4: Enter the New Database Server to use Once the database is fully restored it can be used for other purposes, such as test and development or reporting. This process can be applied to SQL database servers throughout the datacenter.

SQL SERVER DATABASE DISTRIBUTION USING TEMPLATE DATABASES A new feature with PS Series array firmware v5 is template volumes and thin clones. This allows PS Series volumes to be designated as template volumes where the template volume holds a read-only locked configuration and thin-clones can be created from the template volume that are read-write and highly distributable. For more information on template volumes and thin-clones see the PS Series Firmware v5 Group Administration Guide. In a SQL Server world this can be useful to quickly distribute large numbers of database copies to test and dev teams without costing valuable storage space. Thin-clones utilize the read-only data of the template volume and new writes get written to thinclone space. In a test and dev environment, this would mean any new builds or tests on a thin-clone database will take up very minimal storage space and if a build doesn’t work or breaks, the database can be easily reverted back to the state of the template database. Figure 5 shows the process Flow to create database Thin Clones using ASM/ME and the PS Series Group Manager.

SQL Server Database Protection Using ASM/ME: Advanced Operations

5

Create DB  Clone

Convert to  Template  Volumes

Verify iSCSI  Initiator Login

Restore DB  Clone as New

Locate Name  of Clone  Volumes

Refresh  Services on  SQL Instance

Create DB  Copies  Using  Thin Clones

Figure 5: Thin Clone Database Process Flow First prepare the database copy for template volumes. The following steps to accomplish database distribution using template databases: 1. Create a Clone type Smart Copy of the SQL database being used 2. Restore the Clone Smart Copy using the Restore All as New method a. Be sure to check the “Make database READ-ONLY after attach” check box The database should show up as a read-only copy of the database in SQL Server Management Studio, Figure 6.

Figure 6: Read-Only Database in Management Studio Now the database volumes can be converted to template volumes using the PS Series Group Manager GUI. 1. 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 7.

SQL Server Database Protection Using ASM/ME: Advanced Operations

6

2. Log into the PS Series Group Manager and locate the Smart Copy Clones in the Group Manager Volumes view. Select the Smart Copy Clones and using the Activities pane of Group Manager, select the Convert to template option circled in Figure 8. a. Repeat this process for each clone volume in the database Smart Copy. 3. The volumes will be set offline and converted into template volumes. Once in template form, set the template volumes online using the Activities pane in Group Manger.

Figure 7: PS Series Volume Names of Smart Copy

SQL Server Database Protection Using ASM/ME: Advanced Operations

7

Figure 8: 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. Bring the database online in the SQL Server instance. a. If the database fails to online, refresh the SQL instance again. 4. Lastly refresh Auto-Snapshot Manager to recognize the changes. Use ASM/ME to create Thin Clones of the created template database (Figure 9), and follow the steps above in SQL Server Database Restore to a New Server section to distribute Thin Clone copies for space efficient test and dev environments.

SQL Server Database Protection Using ASM/ME: Advanced Operations

8

Figure 9: Create Thin Clone Using ASM/ME

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.



Clone and Restore All as New – This option creates a clone of the selected replica and brings the clone online as the new database at the remote site. This allows replication to continue in the background because the clone is a copy of the Smart Copy replica and the replica set does not need to be promoted.



Clone – This option will create a clone of the selected replica. Once the clone is created, this option offers the ability to mount the replica clone without disrupting replication processes on the original database volumes. SQL Server Database Protection Using ASM/ME: Advanced Operations

9

To perform these operations, ASM/ME must be running at the remote site and have access to the ASM/ME backup documents. See the section on ASM/ME Backup Documents for information on how to secure and manage these documents. Data Mining with Smart Copy Replicas Data mining operations can be done using Smart Copy replicas by using the Mount as Read-Only or Clone options from the restore options. While both of these operations will accomplish the same result, the Mount as read-only pauses inbound replication on the volumes that make up the database smart copy. The Mount as Read-Only option promotes the replica set and mounts the selected replica Smart Copy to the host of your choice. The mounted replica is read-only and available for backup and mining operations. This operation will pause replication on the replica set until it is un-mounted. The mounted smart copy replica will be designated with an “i” in the smart copy icon. To un-mount and resume replication, right-click the replica Smart Copy in ASM/ME and choose Unmount and Resume Replication (Figure 10).

Figure 10: Unmount and Resume Replication The Clone option will create a clone of the smart copy replica and list the clone in the ASM/ME GUI as non-VSS smart copies, (Figure 11). The clones can then be used to mount a copy of the replica without pausing replication between the PS Series groups.

SQL Server Database Protection Using ASM/ME: Advanced Operations

10

Note: ASM/ME creates a clone of each volume that makes up the replica smart copy. Be sure to mount all cloned database volumes when using the Clone and Mount options. Like a mounted smart copy, an “i” will designate the mounted clone. To un-mount the clone, right-click in ASM/ME and choose Unmount and Logoff…

Figure 11: Non-VSS Smart Copies Using Replica Clones Restoring SQL Server Databases 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. Once the database is online, you can put it into production use and have it take over the production database tasks. The Clone and Restore All as New option does not promote the entire replica set and does not affect other replication processes. The Smart Copy replica clone looks like a regular volume in the PS Series Group Manager GUI. If the cloned replica was not used for production purposes you can clean up the replica smart copy using ASM/ME. Use the Detach Database (Figure 12) option within the ASM/ME to detach the database from SQL Server and log off using the same steps

SQL Server Database Protection Using ASM/ME: Advanced Operations

11

as earlier in the Data Mining section. You can also choose to delete or detach the database from the SQL Server and disconnect the clone volume from the host using the Microsoft iSCSI Initiator. Then, delete the clone from the PS Series Group Manager GUI.

Figure 12: Detach Database Option If the clone replica is used for production purposes you may want to replicate the volumes back to the primary site if/when it is available. To do this you will have to configure replication on the replica clone(s) and replicate these back to the primary site so that the changes made at the secondary site will be available to the primary site. Note: There is no support for the PS Series failback capability with ASM/ME replication. Once production has been started on the remote group, the only way to propagate changes back to the primary group is by replicating the clone volumes to the primary group when it is back up and operational.

SQL Server Database Protection Using ASM/ME: Advanced Operations

12

OFF-HOST BACKUP AND RECOVERY Auto-Snapshot Manager / Microsoft Edition is a valuable tool for maintaining point in time copies of application data that can be recovered easily and quickly but it is not positioned as a long term data retention backup solution. Therefore it is best practice to incorporate a backup and recovery solution for long term data retention purposes. The EqualLogic Host Integration Tools for Windows is bundled with the PS Series VSS provider. The VSS provider is fully compatible with third-party backup applications for off-host backup operations (Figure 13). The off-host backup operation incorporates PS Series snapshots and a dedicated backup server running 3rd party software that initiates the backup operation on the application server. Once the snapshot copy is created, the backup server mounts the copy onto the media server and in turn, backs the snapshot up to disk or tape. This process allows the application server to continue application operations while the snapshot is backed up by a backup server thus offloading any additional processing from the production server. This may safely extend the backup window.

Figure 13: 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. An advantage of using snapshots for long term backup and recovery operations is that they can provide a stable copy of data for copying to backup media. Also, snapshots

SQL Server Database Protection Using ASM/ME: Advanced Operations

13

provide a space-efficient way of backing up data, compared to clones or full database backups.

SUMMARY By understanding simple protection and recovery capabilities that ASM/ME offers for SQL Server databases, administrators can incorporate advanced operations that will improve the way databases are distributed for test and development, enhance data protection capabilities for disaster recovery and enable offloading of operations from the production SQL Servers. By using ASM/ME with regular backup methods, you can ensure your SQL Server, Exchange Server, Hyper-V, and NTFS data is protected and available at all times.

SQL Server Database Protection Using ASM/ME: Advanced Operations

14

TECHNICAL SUPPORT AND CUSTOMER SERVICE Dell's support service is available to answer your questions about PS Series SAN arrays. If you have an Express Service Code, have it ready when you call. The code helps Dell's automated-support telephone system direct your call more efficiently.

Contacting Dell Dell provides several online and telephone-based support and service options. Availability varies by country and product, and some services might not be available in your area. For customers in the United States, call 800-945-3355. Note:

If you do not have access to an Internet connection, contact information is printed on your invoice, packing slip, bill, or Dell product catalog.

Use the following procedure to contact Dell for sales, technical support, or customer service issues: 1. Visit support.dell.com or the Dell support URL specified in information provided with the Dell product. 2. Select your locale. Use the locale menu or click on the link that specifies your country or region. 3. Select the required service. Click the "Contact Us" link, or select the Dell support service from the list of services provided. 4. Choose your preferred method of contacting Dell support, such as e-mail or telephone.

Online Services You can learn about Dell products and services using the following procedure: 1. Visit www.dell.com (or the URL specified in any Dell product information).

2. Use the locale menu or click on the link that specifies your country or region

SQL Server Database Protection Using ASM/ME: Advanced Operations

15

Suggest Documents