Veeam B/R used with Replicated Sql Server database

[rmueller]

We have a product from Qlik called QDI-Replicate. It's the old Attunity product purchased by Qlik. The purpose is to replicate and do change data capture for data warehousing applications. Replicate, reads transaction log files for CDC. We use Veeam to backup the database (daily) and transaction logs (every 30mins). Transaction logs are kept until corresponding image-level backup is deleted. Veeam is setup to 'process transaction logs'.

Given the transaction logs are back'ed up and truncated every 30mins, there is always a chance that Replicate might be in the middle of a CDC scan of the logs when Veeam is performing a log backup/truncation or a backup

What setups do we need, so that the logs can be back'ed up/truncated every 30mins and copied in a format that Replicate can read through the logs to find what it needs?

Ron

[PetrM]

Hi Ron,

We store log backups in the repository using our proprietary format and there is no option to change it. Did you try to ask this question on Qlik Community Forums? Basically, the same problem would also exist if you did native SQL log backups on schedule.

Thanks!

[rmueller]

I did post it on the Qlik forum...got some comments no real advise.

We have the processing settings General - Transaction Logs, set to Process transaction logs (recommended). Would the Perform copy only be the option we need. I've never used it before, so not sure what the behavior is.

If we did use the perform copy only option, where do the logs get copied to?
Follow on question - currently under SQL we backup the logs every 30mins, that option (SQL) goes away when I select copy, how would we copy/truncate logs every 30mins?

Ro

[PetrM]

Hi Ron,

If you use copy-only mode, Veeam does not take care of logs: there is no log backup or truncation. The idea is to preserve backup chain LSN if you protect logs by another application, for example you perform SQL native backups. You can get more information about copy-only on this page but I'm not sure that you should go this way because as far as I understand the idea of QDI-Replicate is to replicate changes by scanning logs but not to produce log backups.

Thanks!

[rmueller]

Hmmm...I'm crying on the inside.

To sum it all up, there isn't any way to use Veeam to backup the database, weekly full/daily incremental, backup/truncate the logs ever 30mins...while allowing QDI-Replicate to get at the log backups in native format.

Any final words of advice would be appreciated

Ron

[PetrM]

@rmueller
Maybe I misunderstood but If QDI-Replicate scans backups of logs in native format (not database logs as I thought initially), then you can perform native SQL log backups and to use Veeam in copy-only mode.

Thanks!

[KarmaKuma]

What if you do SQL-native transaction log dumping (either sql maintenance/agent jobs or via Powershell+scheduled task - I could hand you our fully automated full-dump+10min-log posh-script if you are interested) to some place and have VEEAM pick up the dumps via ordinary file/folder backup?

With the right settings on your database (logging type "full" which is required for replication anayways I guess) and the "right" type of dump-job (no-truncate) this should be doable.

SQL DB restore would be a two-step job unfortunately (restore dumps from veeam to accessible folder then DB restore using SQL native tools).

Just be careful with mixing backup types on the same SQL source as one job could truncate the chain of the other job and you end up with unusable backups on both ends - or in your case, a broken replication requiring reinitialization...

[rmueller]

@PeterM QDI only reads logs - current transaction log and it can be pointed at a folder containing copies of native transaction @logs. Doesn't move or any other file manipulation.

@KarmaKuma - that's a clever idea. Yes, DB is full logging. I'd definitely would be interested in seeing the script. I don't know if you can attach it or might it fit in a reply in this post, that would be great.

I'm just amazed there isn't a documented, standard (even on the Qlik site) technique, this tool (as Attunity) has been around since the 90's, someone figured this out long ago. It's like everything Attunity/Qlik QDI-Replicate points to Qlik.com, not many blogs or independent posts...very strange.

I'll keep at it, thanks for the help. I'll experiment and post my final results.

Ron

[KarmaKuma]

P/M me to get in contact and I'll send you the script. I don't know if I can/may attach it here.

But, may I challenge your problem solving in a different way maybe:

What is your desired goal with DB mirroring? Low RPO or low RTO? Or, doing it because you always did it this way with this specific application?

RPO:
Mirroring does not really improve RPO. Well, mirroring can technically be used for RPO, but thats misuse in my point of view. Backup freqency and solid and well traind redo playbooks/SOPs are king here.

RTO and because we always did...:
Until a few years ago we were running most of our main MS SQL DBs in mirrored mode aswell (the now discontinued mirroring mode with optional witness that has been replaced many moons ago by Always-On). In the hopes of keeping RTO as low as possible. And because we could

Turns out that with current tools and on reasonably current virtualization technology, simply taking a damaged DB or even a damaged SQL server off the grid and replacing it with an import from a current backup (full+tlog-redo, or even the SQL server VM before this in worst case), in most cases repeatedly works out faster than resorting to a mirror SQL system.

Why?
A lot of applications do not fully support adding failover SQL servers end-to-end (not talking about Always-On here!). Like necessary ODBC connection driver settings, trouble when trying to solve it with DNS CNAME trickery (auth failuers due to kerberos protection, etc)... ...There's always something (and sometimes a lot!) that has to be adapted in the whole layercake when failing over. Sometimes down to the fleet of end-user devices where the front-application is installed on...

Oh that was the glory days

And Always-On has its pitfalls too, besides being complex and expensive enough that there better be a REAL cash-cow business-case supporting it...

If you are willing to challenge your business, sit down, try and compare both methods for actual real-world end-to-end RTO (not just how long it takes to have the DB back online but until the business can use the application again), as well as TCO maybe, and depending on the outcome, let go of old ways and fully use todays technology in a simpler and therefore most likely more robust and trustworthy way...

At least thats wat we started doing. Robust, well known SQL server and DB redo playbooks/SOPs that are applicable to ANY AND ALL of our SQL servers in EXACTLY the same way, in case of DB and/or System failure instead of relying on once cool but quirky and unfortunately difficult-to-handle-in-the-bigger-picture tools...

Maybe your business might even accept another half hour or so of downtime in return of knowing that their data is 100% safe and in worst case restorable by any external ad-hoc hired semi-specialist if desaster happens at worst of times (standardized tools well known by inside IT aswell as by a large number of worldwide outside IT crowd vs. specialty tools only known and manageable by inside IT silverbacks).

No matter the way you want to go, P/M me for a copy of our SQL backup script. It is actually a central piece in our "no trickery, no specialtes" toolbox.

[rmueller]

Thanks @KarmaKuma...I'll pm in a bit.

For our purposes, QDI-Replication feeds a near-real-time data warehouse built entirely on the Qlik-QDI platform (replication, compose, catalog, qlikview/qliksense). The tool provides data replication as well as CDC tables for type-2/change-over-time dims and facts.

The databases are BU'd with BU-copies via Veeam, and the vm servers they sit on are replicated (with veeam) to our secondary data center.

I'm good when it comes to backup/recovery. I actually have the luxury of a 2-3 day recover time, without dire consequences to the business, that alone is a blessing. Our tested recovery for this server is just under an hour...Fire up the replica, restore the database...and we are good. I just need for QDI-Replication to be able to read the t-logs, which it does 20some odd hours of the day without fail. It's only when its reading the logs trolling for transactions, and backup truncates the logs right out from under it, that we have an issue. Supposedly, QDI is issuing a transaction which should hold off the log truncation until it releases the transaction. I'm investigating if that is actually working, as there are QDI errors every day during backup times.

Qlik's proposed solution is to point QDI to a folder that holds the older t-logs for it to read through as it needs

In the next month or two, we lift/shift this all to Azure SQL managed instances. I'm on a mission to empty the datacenters, so I can go on a vacation and not have to worry about the UPS running dead or the sprinklers turning on for no reason. We are in the middle of re-thinking all the BU/Recovery strategies along with that. But, for right now, I just need to keep log files around so QDI can read them.

Ron

[PetrM]

Hello,

to point QDI to a folder that holds the older t-logs for it to read through as it needs

I'm not sure that it's a reliable approach as everything will depend on a script that copies transaction logs to another folder but what if the script is failed due to whatever reason?

As far as I understand, the main problem is that QDI-Replicate scan and periodic log backups schedules overlap. How often does QDI-Replicate need to scan logs? Maybe you can trigger this scan by leveraging pre-job script somehow? In this case you avoid overlapping schedules of Replicate and regular log backups.

One more option might be to use this setting and logs will be truncated just once after image-level backup is completed but you need to check that you don't have issues with the transaction log growing too fast. I suppose 30-min periodic log backup is not critical in your case as long as RPO is about 2-3 days.

Thanks!