Nguyen Tran

Protecting Microsoft® SQL Server® 2014 with Hitachi Data Instance Director in a Single Site

Discussion created by Nguyen Tran Employee on Apr 6, 2016

Business Problem Statement

SQL Databases form the backbone of many critical applications. Traditionally their data is protected using VSS and Microsoft DPM or Symantec Netbackup which stream data and logs to a backup target, such as tape. With the growth in data, this model is becoming strained. The amount of time needed to complete backups and recoveries are not meeting business requirements.

Backup windows are under pressure to shrink due to the performance impact of backup and the 24/7 nature of modern business, but the amount of data to protect is constantly growing, so something has to give. Traditional backup processes create a full backup of the databases and logs, which can takes hours to complete. This means that a backup can be completed, at best, once per day, supporting a recovery point objective (RPO) of 24 hours. As much as 24 hours of new database records may be at risk of loss, or need to be rolled back or re-entered, in the event of a failure of any type.

To address these problems, customers are increasingly turning to hardware-based snapshots and replication but this is not easy. Generally, in-depth knowledge is needed to create scripts to manage this hardware based data protection. Changes to the environment such as the addition of capacity need careful management and constant updating of scripts. Customers rely on staff with this deep technical knowledge during setup, maintenance and restoration of data. There is risk in having human generated scripts and there is risk in reliance on a handful of staff with this specialized knowledge.

Business benefit and Solution Value Propositions

This blog describes at a high level answer to this problem from HDS is the Hitachi Data Instance Director (HDID) to protect SQL Server. HDID has a seamless end-to-end optimized Solution for Backup, Replication, and Recovery for SQL Server and Data Protection.

Protecting Microsoft® SQL Server® 2014 with Hitachi Data Instance Director in a Single Site Best Practices Guide is based on proven Microsoft SQL Server database to propose Disaster Recovery (DR) for on-premises systems.

This pretested, backup, replication solution and recovery can be used to accelerate data protection with reliability, redundancy, scalability, high availability while processing small-scale to large-scale OLTP workloads and management with HDID centralized console.

Overview

This blog provides a high level view of the tested architecture and test results on protecting such SQL Server environment in a single site and also using clone features for SQL Server Database using HDID.

High-level concepts covered in the white paper:

  • How to Back up SQL database – Scheduled Snapshot with Hitachi Thin Image (HTI)
  • How to take a scheduled snapshot (HTI)
  • How to Restore SQL database – Scheduled snapshot (HTI)
  • Restore database using revert snapshot (HTI)
  • Restore datafiles from mounted snapshot (HTI)

Storage Architecture

This section describes the storage architecture used for the testing documented in the white paper. It takes into consideration Hitachi Data Systems and Microsoft recommended practices for the deployment of database storage design.

storage.png

Test Results

These are the test results of protecting SQL Server with Hitachi Data Instance Director in a single site.

Metric

Value

SQL Database size

1 TB

Total number of data files

4

Database fill factor

90%

Snapshot size

1 GB

Snapshot time taken

(reported by HDID)

1 Minutes 35 Seconds

Snapshot time taken

(reported by SQL Server)

2 Minutes 20 Seconds

Snapshot Revert time

2 Minutes 34 Seconds

Outcomes