Recently I had the opportunity to work with a client that was utilizing SIOS DataKeeper in a Windows Server Failover Cluster to create a readable secondary in order to offload reporting. SIOS DataKeeper is an application that synchronizes data between two volumes, allows you to present local storage to a WSFC as shared storage, and gives you the ability to take snapshots of the mirrored volumes.
Taking a snapshot will make the mirrored volume available to the OS on the secondary. You can then attach those databases to an instance of SQL Server and have your users to connect for reporting. This is all doable with Standard Edition of SQL Server which can save thousands of dollars on SQL Server licenses.
Installing and configuring DataKeeper to mirror volumes and adding them to the WSFC was very simple and well documented. To keep the snapshot current with the previous day’s data, I needed to schedule a process that would drop any existing snapshots and take a new snapshot. I did this by using the built in command line tool EMCmd. To take a snapshot of the E and G drive on my secondary server named AGNODE6, I needed to run
EMCmd AGNODE6 TAKESNAPSHOT E G
To delete the snapshot I would run
EMCmd AGNODE6 DROPSNAPSHOT E G
I put together a batch file that would navigate to the proper folder and issue a drop snapshot to clear any existing snapshots and then take a new snapshot. This was scheduled daily on the secondary server.
The problem I encountered was dropping the snapshots during a failover event on the cluster. In order for the SQL Server clustered resource to failover to the secondary, all snapshots had to be dropped first.
Refreshing the snapshot was straightforward, but how do I drop the snapshots in a timely fashion when the SQL Server clustered resource needs to failover from the primary server, in my case AGNODE5 to the AGNODE6.
SIOS provides a sample VBScript named DKSnapshotCleanup that can be copied and modify to drop the snapshots on the secondary. For this to work I needed a folder on each server and a copy of the modified DKSnapshotCleanup script. Next, I had to add a resource to the cluster and point to the VB Script and make my DataKeeper volume dependent on the new resource. The instructions in the VB script are detailed and easy to follow, however I had an issue when it stated to create a “Generic Application” and point to the script, I had to choose “Generic Script” instead.
In my environment, I had a drive E for data files and drive G for log files. In my DKSnapshotCleanup script, I needed to drop the snapshot for both volumes. To do this, I added two lines, one for each volume toward the bottom of the script.
I assigned the DKSnapshotCleanup Script resource as a dependency on drive E.
When my cluster failed over to the secondary node and back, things didn’t go as planned. Occasionally drive G would fail to come online which failed the cluster.
I was able to quickly bring drive G: back online however that isn’t an acceptable course of action in production. This has to work consistently each time the cluster fails over.
I did some more testing and validated with SIOS support that the best course of action was to create a dependency for drive G on drive E.
This makes drive G have to wait for drive E to come online before it starts. Once I configured the second dependency, I was able to consistently fail the cluster over with both snapshots being dropped and both volumes coming online.
My overall experience with SIOS DataKeeper was positive. The support team was very quick to respond and the product was easy to install. Although I had a couple of configuration issues, I was able to resolve them quickly. Another cool thing about SIOS DataKeeper is that it works on both Windows and Linux, it is a great way to build in HA for storage without having to have a high dollar SAN replication solution.