AnsweredAssumed Answered

Protect Microsoft® SQL Server® with Hitachi Application Protector

Question asked by Nathan Tran on Dec 20, 2013

This technical post shows how your organization can use Hitachi Application Protector (HAPRO) to protect Microsoft® SQL Server® 2012. HAPRO protects your SQL database from the scenarios in Table 1.

Table 1. Test Cases

   

Test Case

Pass/Fail Criteria

Result  

SQL database protection from the following:

§  Accidental deletion of data

§  Logical database file corruption

§  Physical file corruption caused by disk or hardware   failure

Full database recovery from snapshotSuccessful

Use Hitachi Application Protector to schedule or perform backups of your SQL Server databases on an as needed basis. You can rapidly restore SQL Server databases in the event of accidental data deletion or data corruption. This leverages Hitachi Thin Image snapshot technology and Volume Shadow Copy Server from Microsoft to provide application-consistent data protection.  Hitachi Application Protector provides the following:

§  Backup, recovery, and data protection software that is snapshot based and is application consistent

§  Off-loaded host server backup overhead with storage snapshots

§  Disk-to-disk-based backup and recovery management leveraging Volume Shadow Copy Service infrastructure from Microsoft and file clone software from Hitachi.

§  Ability to schedule single, daily, weekly, or monthly snapshots

This posting provides the following;

§  A proof point of basic functionality of this solution

§  High level technical reference for considering of this solution

§  High level reference of the use case implementation

This posting does not cover the following;

§  Performance measurement

§  Sizing information

§  Best practice

§  Implementation details

 

 

NoteTesting of this configuration was in a lab environment. Many things affect production environments beyond prediction or duplication in a lab environment. Follow the recommended practice of conducting proof-of-concept testing for acceptable results in a non-production, isolated test environment that otherwise matches your production environment before your production implementation of this solution.

Use Case Overview

Microsoft SQL Server is a very critical part of many businesses. Ensuring data protection is one of the highest priorities of the database administrator's job.

Hitachi Applications Protector provides you protection for you data and database. You also have the ability to rapidly recover from database failure to minimize the loss of productivity that is associated with a service outage.

Tested Components

These are the hardware and software components that were used for the Hitachi Application Protector testing environment.

 

Table 2. Hardware Components                   

Hardware

Description

Version

Quantity

Hitachi Unified Storage VM§  Dual controllers

§    2 × 8 Gb/sec Fibre Channel ports used

§  166 GB cache memory

§  8 × 600 GB SAS disks

73-02-00-00/01

1

Hitachi Compute Blade 500 chassis

§  8-blade chassis

§  2 management modules

§  6 cooling fan modules

§  4 power supply modules

§  2 Brocade 5460 Fibre Channel switch   modules

§  2 Brocade 10GbE DCB switch modules
SVP: A0135-D-6829

1

520H B1 server blade

§  Half size blade

§    2 × 8-core Intel Xeon E5-2680   processor, 2.70 GHz

§  160 GB RAM

§  Emulex 10GbE CNA onboard network   adapter

§    Hitachi 8Gb/sec Fibre Channel Mezzanine Card

BMC: 01-56

2

 

Table 3. Software Components                 

Software

Version

Hitachi Application Protector2.7.0.7
Hitachi Dynamic ProvisioningMicrocode Dependent
Hitachi Thin Image Licensed on Hitachi Unified Storage   VM
Command control interfaceCCI-01-30-03
VSS Hardware Provider 4.9.1
Microsoft Windows Server2012 Standard Edition
Microsoft SQL Server2012 Enterprise Edition

High Level Test Infrastructure

The testing of Hitachi Application Protector functionality used the following:

§  Hitachi Compute Blade 500 chassis with a half size server blade for Microsoft SQL Server 2012

§  Hitachi Unified Storage VM for the SAN storage

 

Figure 1illustrates the high-level test infrastructure.

                        

Figure 1

SQL HaPRO diagram.png

Test Result

This is the test result and success criteria for testing.

 

Table 4. Test Results   

Test   Case

Description

Result

Backup and restore

This test case performs the   following:

§  Backup of the SQL database

§  Validate the ability to restore the database successfully   in the event of data deletion or corruption to the SQL data or log files

Passed

Expected result:

§  Successfully backup of the SQL database

§  Successfully restore of the SQL database

Using Hitachi Application Protector

Hitachi Application Protector uses a snapshot technology based on the storage system. It leverages Volume Shadow Copy Service from Microsoft to give you application-consistent data protection.

Application Protector understands how and where the storage is of the primary database file and supporting files (like log files). As a result, Application Protector ensures the tracking and backup of all application-related data changes at the requested point-in-time to guarantee recovery.

The storage system-based snapshot technology creates backup images quickly. The technology uses storage space efficiently to maintain changes to the database and log files.

Snapshot technology simplifies recovery because the protected data appears in a mirrored file system, just like the original. Right-click to recover the database and related files.

To learn more about Hitachi Application Protector, visit the Hitachi Data Systems website.

Using Hitachi Application Protector in a Microsoft SQL Server Clustered Environment

Using Hitachi Application Protector in a Microsoft SQL Server clustered environment requires manual steps to failover the Hitachi Application Protector metadata from one node to the other in the event of a SQL node failure.

Classify Hitachi Application Protector metadata into the following, based on their location.

§  On-system metadata, such as WMI DB, Task Scheduler, and registry keys

§  On-disk metadata, such as on-disk objects, logs, and configuration

When using on-disk metadata, the following are the available options for metadata storage through the configurable Hitachi Application Protector metadata directory. Set this location-configurable data to one of the following paths:

§  Non-shared volume

§  This can be shared as a CIFS share during manual failover.

§  Shared non-cluster volume

§  Cluster resource volume

The following describes each option.

Non-Shared Volume

When hosting the metadata on a currently active, non-clustered, non-shared location on a given node, Hitachi Application Protector runs on that node.

Configure a separate, currently passive clustered node for Hitachi Application Protector to run.

Fail Over (Move Metadata to the New Active Node)

In case of failover, perform the following steps:

1.     Copy the metadata from the volume of the old active node (Node 1) to the volume of the new active node (Node 2).

Example: To run from Node 1, when the path of Node 1 is shared over \\Node1\METADATA\Metadata, type the following:

# robocopy \\Node1\HAPRO\Metadata\ H:\HAPRO\Metadata

2.     Run the HAPROS_SYNC command to sync on-disk into on-system on the new active node (Server 2):

HAPRO_SYNC –sync system

Shared Non-Cluster Location

For the cases where using a non-clustered shared volume to store metadata, the shared volume may appear as different drives on various systems.

Note — Use different paths to configure on Hitachi Application Protector running on the various cluster nodes, as shown in Table 5.

 

Table 5. Valid Configuration Example         

ServerShared   VolumeDrive LetterMetadata Path

Node 1

Vol-1

H:

H:\HAPRO\Server1\

Node 2

Vol-1

G:

G:\HAPRO\Server2\

 

Table 6 is an example of an invalid configuration.

 

Table 6. Invalid Configuration Example          

ServerShared VolumeDriver LetterMetadata PathComment
Node1Vol-1H:H:\HAPRO\Metadata\Invalid configuration as it conflicts   with Server 2 (same location).
Node2Vol-1G:G:\HAPRO\Metadata\Invalid configuration as it conflicts   with Server 1 (same location).

Fail Over

In case of a fail over, perform the following steps.

§  If Node 2 is the new active node, and it just took over from Node 1, then run the following command to sync on-system metadata using on-disk metadata:

HAPRO_SYNC –sync system

Cluster Resource Location

When using cluster resources, the disks are visible only to the active node. Switch the cluster resource to the new active node during failover.

The volume is visible under the same driver letter on all of the cluster nodes. In this case, configure the Hitachi Application Protector metadata path as in the case of Shared Non-Cluster Location. It can be the same path on all the nodes. Table 7example configuration:

 

Table 7. Cluster Resource Location Example     

ServerShared   VolumeDrive LetterMetadata   Path
Server 1Vol-1H:H:\HAPRO\Metadata\
Server 2Vol-1H:H:\HAPRO\Metadata\

Fail Over

In case of a fail over, perform the following step:

§  If Node 2 is the new active node, and it just took over from Node 1, then run the following command to sync on-system metadata using on-disk metadata:

HAPRO_SYNC –sync system

Notes applicable to all syncing of Hitachi Application Protector metadata:

§  The HAPRO_SYNC command syncs the on-system metadata using the on-disk metadata that was recently copied with robocopy. Here the snapshot metadata path need not be specified explicitly, assuming the path conforms to the configured metadata path. During this operation, HAPRO_SYNC reads the on-disk metadata from the path from which it is configured as the snapshot metadata path.

§  If you need to additionally check for the consistency of the on-system metadata, combine the {–check|-c} flag with –sync flag, run the following.

HAPRO_SYNC –sync system -check

§  If you wish to perform only on-system metadata consistency checking, which is recommended after running HAPRO_SYNC –s on the system, run the following:

HAPRO_SYNC {–check|-c}

§  If the user needs to copy Hitachi Application Protector on-disk metadata from the source location to the destination location, use this command:

HAPRO_SYNC [ {–replicate|-r}  {“source,destination”} ]

General Statements

These general statements apply to using Hitachi Application Protector:

§  Although Hitachi Application Protector metadata can be hosted on a cluster resource, this may make manual metadata management a complex task.

§  Hitachi Data Systems recommends hosting metadata privately on a non-shared location, as Hitachi Application Protector is not yet fully cluster-aware.

§  As the command line interface can be used to copy the metadata, it should be possible to script the metadata movement successfully in case of a fail over. Hitachi Data Systems does not provide support for such scripts currently, but does support the correct functioning of the command line interface.

Outcomes