Zfs replication and databases

[offerlam]

Hi

Im looking at setting up at 3 node cluster where node a replicate via zfs a vm to node b and c.

My vm has a sql server runing.
How sure can i be this will work? Dont i risk to lose a write action in db if im unlycky with the sync and when it goes offline so my db wont start on say node b because of oncistent db log?

 

[Johannes S]

Did you consider to setup replication/clustering on the database Level?

 

[floh8]

sysops use this solution for all VMs and container and did never have a problem. Dont forget its async, so one loose some data in case of fail over

 

[offerlam]

Did you consider to setup replication/clustering on the database Level?

yes we also talked about clustering with share storage or ceph. But for this solution we want some KIS (keep it simpel)
The replication feature looks very nice on paper, since we can live with data being spooled back as it was 2 min ago.

CEPH and share storage introduces a higher cost along with a more compleks setup to maintain and evolve knowhow for.

EDIT: Misread you post No we haven't considered it on the database level since we want as simpel a setup as possible.

 

[offerlam]

sysops use this solution for all VMs and container and did never have a problem. Dont forget its async, so one loose some data in case of fail over

just to be clear.

So sysops ( it sounds like i should know them ) has run with this 3 node in ZFS replication for a long time and have never had any issues with missing transaction logs because the server was spooled back 1 minut? or two. Correct?

 

[floh8]

No. your one can find sysopstv on youtube.
sysops didnt get inconsistency with zfs replication and DBs. The data between replication your one loose in any case.

 

[offerlam]

No. your one can find sysopstv on youtube.
sysops didnt get inconsistency with zfs replication and DBs. The data between replication your one loose in any case.

ok - its a german show and im not german. So i can't understand what they are saying.
But you are saying they had no issue with transaction logs using ZFS replication and databases.
Ofcause you loose the data that hasn't been replicated yet but that is it. DB will come up and run just fine with the data it has.

 

[Johannes S]

So sysops ( it sounds like i should know them ) has run with this 3 node in ZFS replication for a long time and have never had any issues with missing transaction logs because the server was spooled back 1 minut? or two. Correct?

I wouldn't trust any production data on something, some guy on youtube claimed to work. I also wouldn't trust anything somebody on a forum said who doesn't have his own experience but just repeat something from youtube.

yes we also talked about clustering with share storage or ceph. But for this solution we want some KIS (keep it simpel)

I wasn't talking about shared storage (Ceph is a type of shared storage) but application-level clustering. Most SQL Databases have support for clustering and replication so you won't have any data loss like with ZFS storage replication. So imho SQL Clustering IS KIS compared to the rabbit hole of Ceph.

One caveat though: Veeam doesn't support application-aware backups of SQL databases but to be honest: I wouldn't use Veeam to backup them anyhow but the native backup function of the database to create sql dump files (which then can be backed up with Veeam, Proxmox Backup or whatever).

 

[floh8]

I wouldn't trust any production data on something, some guy on youtube claimed to work. I also wouldn't trust anything somebody on a forum said who doesn't have his own experience but just repeat something from youtube.

I wasn't talking about shared storage (Ceph is a type of shared storage) but application-level clustering. Most SQL Databases have support for clustering and replication so you won't have any data loss like with ZFS storage replication. So imho SQL Clustering IS KIS compared to the rabbit hole of Ceph.

One caveat though: Veeam doesn't support application-aware backups of SQL databases but to be honest: I wouldn't use Veeam to backup them anyhow but the native backup function of the database to create sql dump files (which then can be backed up with Veeam, Proxmox Backup or whatever).

Oh floh8 is no human that use a persons name and therefor has no body.
sysops is of course a cook and had 2 left hands and as hobby he plays with zfs. Thats why my own refer to him.
Veeam can application-aware backups of MS SQL for example.

 

[Johannes S]

Veeam can application-aware backups of MS SQL for example.

Yes but until now I assumed that they don't support backups for clusters (since they don't support it for other sql databases) which was the whole point of my argument. If one can't live with the potential loss of data due to the asynchronous nature of ZFS send/receive (and only OP can know whether this is true for his use case or not, not you, not me and definitively not some guy on youtube although he call himself a sysop. I could create a youtube channel on coocking named BocusePupil and still wouldn't be on the level of an apprentice of Bocuse ) then doing application-level clustering on the database might be an option to get high-availability without data loss. But then of course you need to ensure that your backup software can deal with it.

Regarding SQL and Veeam:

• Veeam Agent does not support backup of PostgreSQL clusters. ( https://helpcenter.veeam.com/docs/agentforlinux/userguide/postgresql_backup.html )

• Oracle Real Application Clusters (RAC) are not supported. ( https://helpcenter.veeam.com/docs/agentforlinux/userguide/oracle_backup.html )
• MySQL Cluster versions are not supported. ( https://helpcenter.veeam.com/docs/agentforlinux/userguide/mysql_backup.html )
• Veeam Plug-in supports backup and restore of Microsoft SQL Server databases that operate as part of a failover cluster. B https://helpcenter.veeam.com/docs/backup/plugins/mssql_plugin_cluster.html

Please note that their support for psql, mysql and oracle has further limitations so I wouldn't consider Veeam for them even if the clustering would work.
Now OP didn't write which SQL database he uses but in my book the limitations of a popular backup software with databases are quite important to consider before implementing a cluster with them. ( Why this backup software is even that popular in "enterprise environments" with such limitations will never cease to amaze me but this is another story).


So please at least try to read and understand what I write, thanks.

 

[floh8]

That this statement is according sql cluster is not clearly defined.
Nevertheless why comes your own with veeam around the corner? Use his own veeam?
His own wrote their own want it keep simple. Thats why his question.

ok - its a german show and im not german. So i can't understand what they are saying.

new feature on yt. It can translate it in subtitles.