Microsoft SQL Server Cluster Backup and Restore using Pure Storage and Catalogic ECX Posted on Sep 4th, 2019 by Nathan Oyama

Categories: ECX, Pure Storage

Catalogic ECX offers a unique, effective yet user-friendly solution to protect and use your valuable assets in Microsoft SQL Server™ databases. With ECX, you can help ensure your business continues and mitigate business impact under various types of unexpected disasters. And you can also quickly deliver working copies of SQL databases for business use cases such as test-dev, DevOps, reporting, etc. 

ECX automates SQL Server-aware snapshot copies on enterprise-level storage arrays including Pure Storage FlashArray, NetApp ONTAP based storage, and multiple storage systems from IBM Storage. You can manually create a snapshot whenever you want, or schedule and automate backup jobs with ECX’s Service Level Agreement (SLA) policies you define.

ECX coordinates the copy process between the application and operating system layers and the underlying storage platform to create database-aware snapshots.

You can choose to restore any snapshot of a desired date and time, even re-play log files to restore to a specific point-in-time. Database restores, generally speaking, take two forms:

  • Recovery – Overwrites the original database or VM and is used for true data recovery scenarios. 
  • Data Delivery – Maps servers to clones of databases or spins up VMs for non-recovery use cases (test-dev, etc.).

Restore becomes more complicated with a SQL failover cluster configuration. By default, the same disk signature will be reused which prevents you from restoring databases back to the original location. For this reason, it only works for limited disaster situations.

If a database clone is presented to a server and duplicates an existing disk signature, it cannot be connected to the server node because it creates a conflict.

To successfully map restored databases back to the original location while the original database is still running, you have to first map the restored databases to a different server (“proxy application server”), update the disk signature to avoid conflicts, and then map the newly signatured copy to the cluster.

This recovery method is effective but manually re-signaturing is complex, time-consuming and error-prone. This is not something you want during a recovery scenario when time to service is critical. It also impedes any efforts that involve data delivery. How can this be made easier?

ECX can orchestrate and automate this type of recovery efficiently and quickly, while allowing you to set up your recovery plan using a user-friendly web interface.

ECX automatically maps a clone to a proxy server. The proxy server updates the disk signature to avoid conflict.

ECX then maps the updated disk image to the SQL cluster, successfully completing the recovery process.

In the next section, you will see a step-by-step procedure to restore Microsoft SQL Server databases in a different location using ECX.

Configuring ECX

Open ECX from a supported web browser. From the navigation pane, click the Configure tab. Ensure you are seeing Sites & Providers and from the list under Provider Browser, right-click Application Server > Register.

In the Register Application Server dialog, click SQL and you will see a new dialog for Register Application Server (SQL). Fill in the required fields for each node of your Microsoft SQL Server cluster. You may select from either a physical server or a virtual machine hosted on VMware vCenter. Click OK to close the dialog.

Click the Jobs tab. By default, an Inventory Job starts as soon as you register the SQL Server. The Inventory Job will catalog the SQL cluster and identify the location(s) of the database files, log files, etc. 

In the tree pane, go to All Jobs > Database > SQL, right-click it and you will see three options: Backup, Restore and Inventory. Click Backup and you will see the New DB/FS Backup Job dialog. Choose the template Standalone and Failover Cluster.

From the tree pane, expand Sites to see the database instances and databases that you want to protect with snapshots. You will also see a list of SLA policies in the right pane. These are created separately and applied to storage volumes, databases and/or VMs. Select the SLA policy to associate with the database(s), name this new backup job and click Create Job. This closes the dialog and starts the job immediately.

Go back to the Jobs tab, ensure SQL is selected and you will see a list of jobs for Microsoft SQL Server in the right pane. Click History in the bottom pane and click the latest job which usually is the one you created and started in the previous step. In the job window, you will see the ECX server orchestrating the backup job with your storage services (e.g. Pure Storage FlashArray) and Microsoft SQL Server databases. Ensure the backup job status changes into COMPLETED and close the window.

Now that your backup job has completed, you’ve got a SQL-aware snapshot you can use to restore from a data loss event, or you can use it to deliver copies for other uses. Let’s walk through the recovery process. 

Go back to the Jobs tab, right-click SQL and click Restore. Choose the application Microsoft SQL Server (Standalone and Failover Cluster) and the template Instant Database Restore. In the New DB/FS Restore Job dialog, first ensure Source is selected in the left pane and from the Application Browse pane, select the sites, database instances and databases you want to restore. 

Since ECX catalogs and tracks all copies, it knows where your copies are. Any available copies will be displayed, including remote site copies if you set up a replication policy. Click Copy from the left pane and select the copy location you wish to use.  By default, the latest snapshot will be used to restore your data, but click Choose a specific version and you can choose any snapshot that was created on a specific date and time.

Third, click Destination from the left pane, select a site and a database instance. As seen in the Select a Windows Server to resignature LUNs section, ECX allows you to restore your databases in the same Microsoft SQL Server node (as well as different nodes) through a proxy application server. Although you may rename your restored database to avoid conflict with the original, ECX will reuse the original name by default. Click Advanced Options for additional options such as overwriting existing databases and specifying a mount point. Click OK, the dialog closes and you will see the restore job in the Activity list in the bottom pane.

Once it has done, the status of the job changes into RESOURCE ACTIVE. Click this job and you will see the restored databases have mounted and their LUNs are mapped to the target destination Microsoft SQL Server nodes. Log into the target Microsoft SQL Server and you will see the restored database(s).

Conclusion

As you have seen, ECX allows you to protect your Microsoft SQL Server databases with ease while offering many practical options to meet your needs in various situations. For more information about how ECX can protect your business assets, refer to the product documentation or contact our specialists at Catalogic Software. And if you want to see some live demos of ECX on Pure Storage, you can find them here