How to create database restore points for a primary availability group with multiple storage arrays

Ops Center Protector Microsoft SQL Application Guide

Version
7.9.x
Audience
anonymous
Part Number
MK-99PRT009-07
It is assumed that the following tasks have been performed:
  • The Protector Master software has been installed and licensed on a dedicated node.
  • The Protector Client software has been installed on all nodes of the Microsoft SQL Server setup which should be protected.
  • The Protector Client software has been installed on the node that will act as a proxy for the Hitachi storage device where the SQL Server data is located. Note that for a Thin Image snapshot, the source and destination LDEVs are located on the same device.
  • The block storage device has been set up as per the Protector requirements and prerequisites. Refer to Hitachi Block prerequisites.
  • Permissions have been granted to enable the Protector UI, required activities and participating nodes to be accessed. In this example all nodes will be left in the default resource group, so there is no need to allocate nodes to user defined resource groups.
  • A user has been created that provides the required privileges as detailed in Microsoft SQL Server User Privileges. This user will be required when creating the SQL Server classification in the steps that follow.

This task describes the steps to follow when snapshotting databases that reside on a Hitachi Block storage device. The data flow and policy are as follows:

Figure. Microsoft SQL Server Node
Classification Type Parameters Value
Microsoft SQL Server Database Node Microsoft SQL Server application node hosting the instance.
Instance Instance hosting the databases
User User for backup
Included Items Refer to Microsoft SQL Server Database Selection Wizard on how to include database into the backup
Backup Mode Full Copy
Replica Backup Preference Primary
Operation Type Parameters Value Assigned Nodes
Snapshot Mode Hardware SQL Server application node
Hardware Type Hitachi Block
Run Options Run on RPO
RPO 2 hours
Retention 2 days
  1. Locate the source OS Host nodes for your Microsoft SQL Server environment in the Nodes Inventory and check that the nodes are authorized and online.
    These nodes represent the Protector Clients installed on your Microsoft SQL Server environment.
  2. Create a new SQL Server node (unless a suitable one already exists) using the Microsoft SQL Server Node Wizard.
    The SQL Server node type is grouped under Application in the Node Type Wizard.
    1. Select one node which is part of your Microsoft SQL Server environment.
    2. Select which configuration you want the SQL Server node to represent. In case of a standalone system you will only have one option. In the case of a clustered system, you can choose if the application node should represent just this host or a clustered instance.
  3. Locate the node in the Node Inventory that will control the Hitachi Block Device via a CMD (Command Device) where the SQL Server database data is located. Check that the node is authorized and online
    This node is used by Protector to orchestrate snapshot creation and is identified as the Proxy Node when creating the Hitachi Block Device node in the next step. This node is known as an ISM (Intelligent Storage Manager) node. The ISM node does not appear in the data flow.
  4. Create a new Hitachi Block Device node (unless one already exists) using the Block Storage Node Wizard and check that it is authorized and online.
    The Hitachi Block Device node type is grouped under Storage in the Node Type Wizard. Note that this node does not appear in a SQL Server snapshot data flow diagram but is identified when assigning the snapshot policy
  5. Define a policy as shown in the table above using the Policy Wizard, Microsoft SQL Server Classification Wizard and Snapshot Operation Wizard.
    The SQL Server classification is grouped under Application in the Policy Wizard.
  6. Steps 7 to 11 have to be repeated for every replica that uses a separate storage node, to configure a snapshot pool for each storage.
  7. Place the SQL Server Availability Group Node using the Data Flow Wizard.
    At this stage, the snapshot icon is not shown.
  8. Select the node you just added. In the AG Node Selection, choose all Servers which are connected to the current storage. If multiple replicas share the same storage they can be selected together.
  9. Assign the Snapshot operation to the SQL Server source node you just added. The SQL Server-Snapshot policy will then be assigned automatically. The Block Snapshot Configuration Wizard is displayed.
  10. Select the Storage Node corresponding to the Hitachi Block Storage device where the SQL Server database’s data is located for this replica. Then select a Snapshot Pool from one of the available Thin Image or hybrid pools.
  11. Leave the remaining Advanced Configuration options at their default settings, then click OK.
    Tip: Using Cascade mode (the default setting) will allow the database to be used for reverts after it has been mounted.
    The snapshot icon is now shown superimposed over the source node.
  12. Repeat steps 7-11 until all storage arrays are configured.
  13. Compile and activate the data flow, checking carefully that there are no errors.
  14. Locate the active data flow in the Monitor Inventory and open its Monitor Details.
    The policy will be invoked automatically to create a snapshot repeatedly according to the RPO specified in the policy. The policy can also be manually triggered from the source node in the monitor data flow.
  15. Watch the active data flow via the Monitor Details to ensure the policy is operating as expected.
    You should periodically see:
    • Backup jobs appearing in the Jobs area below the data flow that show progress percentage, ending in Progress - Completed.
    • Information messages appearing in the Logs area below the data flow indicating rules activation and snapshot events.
  16. Review the status of the Hitachi Block Device to ensure snapshots are being created.
    New snapshots will appear in the Block Snapshot Inventory periodically as dictated by the RPO of the policy. Old snapshots will be removed periodically as dictated by the Retention Period of the policy.