HA with SQL Express and Pervasive SQL VMs?

[scd]

Hey all,

we currently have a 1-Node VMware setting in one of our locations which runs two VMs with one sporting SQL Express (currently still 2017) whilst the other has a Pervasive SQL database. Backups are done via Veeam to an external storage at a separate location via "broadband" (quite slow).

We would like to make the VMs fit for HA to reduce possible downtime because of the backup location. Can I just configure a HA 3-Node-Cluster with replication (or alternatively with shared NFS storage)? Or will we get problems regarding data integrity in case the active host fails? If so, what would be the solution here?

 

[Azunai333]

You can configure a ZFS replication with HA. But you can only sync the storages up to every minute. This means the database will lose the data written in the last minute.

If this isn't acceptable you'll have to use a shared storage like Ceph or NFS. See also [1].
If you use for example NFS on a centralized storage, you may get a single point of failure on the storage side.

[1] https://pve.proxmox.com/wiki/Storage#_storage_types

 

[scd]

You can configure a ZFS replication with HA. But you can only sync the storages up to every minute. This means the database will lose the data written in the last minute.

If this isn't acceptable you'll have to use a shared storage like Ceph or NFS. See also [1].
If you use for example NFS on a centralized storage, you may get a single point of failure on the storage side.

[1] https://pve.proxmox.com/wiki/Storage#_storage_types

Thank you! So we won't have any problems with data integrity using HA with Express and Pervasive? I'm no DB guy so I'm not sure if there could be any problems like data inconsistency (e.g. database writes while replication happens).

 

[itNGO]

Thank you! So we won't have any problems with data integrity using HA with Express and Pervasive? I'm no DB guy so I'm not sure if there could be any problems like data inconsistency (e.g. database writes while replication happens).

It mostly depends on how your database is working and acting with transactions. For example, if any write back cache in memory is in use, you can loose the database on a system-failure. The replication itself does not know if there is an ongoing transcation while the snapshot for replication is created. So to be sure, it is more a job for CEPH.

 

[leesteken]

HA at the hypervisor level can only make sure the VM is restarted (after a minute or so) on a working node. Maybe use the facilities of the database software itself to provide redundant and uninterrupted service instead?