Solved Distributed Database Maintenance

[VRonin]

We currently have a centralised (MS SQL Server) database and everything is fine, unfortunately we are now required to "distribute the db" at the end machines (basically this is the equivalent of switching from svn to git but with dbs)

Since I don't want to to pay 60 licenses, I was thinking of going with postgre

do you know if it's possible/easy to maintain the structure of it? e.g. if I write a new stored procedure, is it easy/possible to just replicate it on all other machines in a batch script?

To be clear I would not need to sync the data inside the dbs, only its structures and automations

[kshegunov]

@VRonin said in Distributed Database Maintenance:

I was thinking of going with postgre

Any particular reason for postgre? I'd usually use sqlite if I need a local (standalone) database.

e.g. if I write a new stored procedure, is it easy/possible to just replicate it on all other machines in a batch script?

I think there's replication for postgre, however I have never used it. On the bright side it should be enough to meet your requirements.

[VRonin]

@kshegunov said in Distributed Database Maintenance:

Any particular reason for postgre?

I'd need to have some automation on the DB side (stored procedures, triggers, etc) that's why I was thinking postgre

[kshegunov]

You could in principle wrap your own, however I advise reading first on what postgre provides out of the box.

[JonB]

@VRonin
I have no idea exactly what you are intending to do, but are you aware that if you can use MS SQL Server Express for whatever it is you want to distribute then the licences are free, not paid for?

[VRonin]

@JonB said in Distributed Database Maintenance:

if you can use MS SQL Server Express

The 10GB size limit is stringent unfortunately

[Pablo J. Rogina]

@VRonin it looks like you may perhaps want to look at schema cloning in Postgresql.

[SGaist]

Hi,

This PostgreSQL wiki page about replication and clustering might be a good starting point.