SQL DBs with HyperBac files suspect after server restart

cnilsson

Over the last three times we have rebooted servers with files in the SQL Storage Compress/HyperBac format, we have had trouble with databases after the restart.

The first time it happened, it was a replication subscriber database (all tables published) that functions as a reporting database and disaster recovery db. I tried to use ALTER DATABASE [name] SET ONLINE but no go, so I ended up restoring and rebuilding replication to recover.

It happened again with more than one database on more than one server during the restarts after Windows Updates a weekend ago. On one SQL server three databases came back as "in recovery" but after a couple of hours two of them finished recovering on their own before I had to do anything. The other one was the same database as before, and again I had to restore it, but this time I was able to reinitialize rather than having to rebuild replication from the ground up.

One of the SQL Servers that I rebooted this weekend turned out fine, but at least one HyperBac database on each of the other SQL Servers had to be fixed.

So my questions are: are there any steps that we can take to mitigate this, or to keep it from happening? Would it help to say stop all processes (SQL Replication, SQL Agent jobs doing logshipping, other SQL Agent jobs, Windows Scheduled Tasks) and disable them so nothing is active when the reboot happens? Do I need to move all of my dbs out of SQL Storage Compress and stop using the software?

Any ideas?

Thanks.

Chris Nilsson
Senior Systems Engineer
Altos Solutions

javen

The internal recovery process will recover the files after an abrupt shutdown, depending upon the activity or state of the database at the time, this recovery time can vary, and in some instances can take longer than usual.

To mitigate this, if you know a shutdown or restart is imminent (such as when Windows Updates are to be applied), you can take each of the compressed databases offline (using ALTER DATABASE or SSMS) prior to the shutdown. This will typically close down the files in a controlled manner, then recovery upon restart will be much faster.

PDinCA

The solution

if you know a shutdown or restart is imminent (such as when Windows Updates are to be applied), you can take each of the compressed databases offline (using ALTER DATABASE or SSMS) prior to the shutdown. This will typically close down the files in a controlled manner, then recovery upon restart will be much faster.

appears unworkable to those of us using hosted solutions. There have been sufficient incidents in the 6 months since transitioning to a well-known hosting Company (also used by RG I am told) to have bred a low level of confidence that our 24x7 App can be correctly suspended and reactivated for simple monthly Windows/SQL patches without introducing further risk-points of specifically taking databases offline...

Is there some issue whereby when SQL Server is instructed to stop, the databases under HyperBac auspices cannot shutdown each and every time in a manner whereby a huge "recovering" window is not risked? If a "normal" shutdown cannot guarantee my databases will ALL be ready and available immediately upon restart, then I have no option but to suspect the technology that renders them "recovering". Taking 150GB databases potentially an hour to recover is untenable!

Can you alleviate my fears/suspicions whereby I DO NOT have to intervene to take my databases offline just so SQL Storage Compress doesn't mess up?

PDinCA

HyperBac have a fix for this in QA.

This is a known issue which is being addressed, the fix is going through our QA system now to be released as a patch. The issue is that the SQL Server service starts and the HyperBac service is not completely ready, therefore SQL reads what it thinks is invalid data when it tries to bring the databases online, as it is not reading the files through the HyperBac filter. It is a timing issue, but the files themselves are not corrupt, you can take the databases offline and bring them back online after SQL has started and the HB service is running, failing this you can take the databases offline and run the hyperutil program with the –R switch on each compressed data file, then bring them back online.

There are a few workarounds for this until the patch is available:

1) Take all compressed databases offline before a scheduled restart -> bring them back online 60 seconds or so after the SQL Server service and HyperBac service are back up, this will work every time
2) After an unscheduled restart, take the databases offline and then manually bring them back online (as stated above)

Good news to those of us hesitant to purchase due to this issue...

rthsw

Hi, we are hosting a Archive-server with over 40 Databases (one Copy of the productive System per Day of the Month). After everey restart we loose 1-3 Databases.

NEVER Use Storage Compress for productive Systems!!!
But after we buy this tool, we would like to use it, WITHOUT pulling out hairs.

epetro

This issue appears to still exist. We recently began testing this product on a server with 6 databases. I converted them all from native SQL to SSC. We made the effort to stop SQL services, set to manual, and then stop HyperBac prior to the reboot. We then waited about 5 min after the server was up and we had a remote connection before starting SQL. This should be more than ample delay for a simple service(HyperBac) to be ready.

Is this issue still being addressed?
Am I the only 1 seeing IN RECOVERY for at least 45 min after restart?
What is the current version others are successful with?

javen

There was an issue relating to startup sequence, whereby SQL would attempt to recover the SSC or SVR databases before the HyperBac service was fully loaded, this would result in the databases being flagged as suspect, when in fact they were not. This has been resolved in the latest build which is due for release within days. However to workaround this, and to greatly reduce recovery times, if you have a planned shutdown, take all SSC databases offline manually before the shutdown, then bring them back online once both the SQL and HyperBac services are fully up and running.