Protection and instant access of Microsoft SQL database - DevOps with Actifio

By | November 12, 2017

Recently one of my customer contacted me because they had some issues with their backup environment. They use one of my favorite backup system and backup of logs of really big databases stopped working and logs filled up disk space and I don't have to write what happened next. After some investigations and solving a problem, during audit of backup system I saw that customer restores many big databases with different names (alternate restore). I saw many restore jobs already completed. I asked why they do that and I was informed that they need to restore some backups just for DevOps purpose.  A typical backup solution (e.g. as they use) can backup database/logs often however what happens when we need to bring a copy of database from backup quickly, a db size is 5TB and also we have to do it often? We need time and space to recover it or we have a solution to provision databases directly from backup storage. I recollected that last year I saw a great presentation about Actifio product at Tech Field Day 11 that can do that.

Actifio Diagram

source: www.actifio.com

Actifio Copy Data Management is a solution that can be used not only to protect Microsoft SQL or Oracle Databases, VMware or Hyper VMs but also for instant access (means within minutes) or standard recovery. Application data is captured at the block level, in application native format, according to a specified SLA. A Golden copy of that data is created and stored once, and is then updated incrementally with only the changed blocks of data in an “incremental forever” model (synthethic full is created automatically). Unlimited virtual copies of the data can then be made available instantly for use, without proliferating physical copies and taking up additional storage infrastructure. Global deduplication and compression are used as well.

Actifio physical architecture is based on two types of appliances:

  • Actifio CDS - 2U physical appliance built on dual servers with battery backed cache, acting as a fully redundant storage controller. Each server consist:
    • 4x 10GbE and 3x 1GbE for host connections
    • 4x 8Gb FC for host and storage connections
    • 1x 100MbE for IMM system management
  • Actifio Sky - a virtual appliance installed in ROBO or Cloud.
Actifio - End-State Technical Architecture

source: www.actifio.com

Actifio for Microsoft SQL Server

In this post I would like to focus on Actifio features for Microsoft SQL Server. Actifio allows to capture SQL data in below ways:

  • Individual and groups of databases on physical or virtual servers.
  • Consistency groups (a group of databases that are quiesced and captured together via a single policy).
  • Microsoft SQL Server databases on a VM along with the VM's boot volume.
  • Entire VMs.
SQL DevOps with Actifio

source: Actifio presentation at TFD 11

To capture selected SQL databases we should install the Actifio Connector (a small-footprint service) on virtual or physical SQL Server. The Actifio Connector makes use of Microsoft SQL Server VSS Writer (SqlServerWriter) for discovery, capture, and access operations. SqlServerWriter is installed by default on most Microsoft SQL Servers. Then a virtual staging disk is created and mounted on DB server. Mentioned connector using filter driver keeps tracking what blocks have been changed and those blocks are copied and merged. The staging is unmounted and that Actifio takes an internal snapshot to create a point in time (PIT) ready for instant access. All steps are done automatically and all the time.

Actifio - Databases Instant Access

source: Actifio presentation at TFD 11

When a DB copy is needed, an Actifio appliance provides two ways to mount an Microsoft SQL Server database:

  • The standard mount presents and makes the captured Microsoft SQL Server data available to a target server as a file system, not as a database.
  • The Application Aware mount presents and makes the captured Microsoft SQL Server data available to a target server as an Microsoft SQL Server database.

Actifio mounts a virtual LUN and attach/bring a database online. Everything is done automatically. What we have to do is choose a desired Point in Time 🙂

SQL Server Databases can be replicated (sync up to 300KM apart or async) to a second Actifio appliance or to the cloud for recovery, disaster recovery, or test/ development purposes.

To see a SQL Server Demo by Actifio, please watch the below video:

Summary

Actifio is an interesting solution that allows creating multiple copies of data very quickly without creating as many physical copies of the data. These instant virtual copies such virtual DBs with automated data masking can be used not only for multiple standard things such backups, DR but also mentioned DevOps or Analytics. This year Actifio is presenting again at Tech Field Day 15. Stay tuned for updates from Actifio!

Worth reading or watching resources

An SQL Server DBA’s Guide to Actifio Copy Data Management

Restoring DevOps to Infrastructure with Actifio by Julian Wood

Leverage Your Data Copies by Alastair Cooke

Author: Mariusz

Architect (~ 15 years experience based on passion...) with strong background as a System Administrator and Engineer. Focused on Data Center Solutions: Virtualization/Cloud Computing and Storage/Backup Systems. Currently living in Poland.