Reliable replication alternative for continuous data synchronization between two databases
I have one central database and 25 client databases, and they all have the same schema.
I want that whenever some changes are made to some tables in the central database, those changes are sent to the client database.
The databases used are SQL Express so I cannot use replication. The solution I have today is to track the changes in a central database, and then the program makes a text file with those changes and sends them to the client databases. Another program reads these text files and updates the client database.
There are three problems with this: -
1. Files get lost or arrive out of order, which will spoil the client data
2. The process is slow 3. Programs sometimes terminate, so the entire synchronization flow stops.
Is there a reliable alternative that is fast and secure? I wonder how banking software is made ... they never lose transactions and they are fast.
source to share
Add a column UpdateDate
to all objects that you want to replicate. On each client add linked server
to the central repository. Now, every 5 minutes or so, poll your central repository for changes using the latest UpdateDate
client object and grab the delta. Then use merge
or insert
and update
to combine the data on the client. This is a very reliable way of doing home replication. To keep track of deleted items, you would like to mark them as deleted, or have another table to keep track of the entity type and its reference, combined with UpdateDate
for replication.
Update Then you mention transactions and banking software. When you do replication across files, we are not talking about transactional replication here, not a long shot.
If you want consistency of transactions, you need to subscribe to the data warehouse transaction stream.
source to share
I don’t mean to be useless and you didn’t provide any details about your business needs, but you need to decide if your priority is really “fast and safe” or if it is really “cheap”. Replicating changes across multiple databases in a reliable, sequential manner is not easy (as you know), and it is unlikely that you can develop your own solution that has the features, stability, and performance of SQL Server replication.
SQL Express can be a replication subscriber by the way, so it doesn't understand why it doesn't meet your needs. But if this is not the case, you must evaluate the value of your business (or client) to solve the problems caused by an unreliable solution: your time, business downtime, finding and fixing incorrect data, customer complaints, lost business, etc. Then compare that to the cost of 25 SQL Server licenses (you can certainly get a good discount when ordering this volume), additional hardware (if available), and training, consulting and / or training costs for replication. Then extrapolate these costs over 5 years or so. You may find it cheaper to simply buy the solution you want. And of course, buying the full version of SQL Server means you get tons of other new features,which may be useful to you.
If you (or your boss) are really determined to get something for free, you can look into PostgreSQL or MySQL. They both have free replication solutions that seem to be widely used enough to be reliable for many companies. Of course, you need to calculate the cost of moving to the new database platform.
source to share
If you have one central database and 25 clients, you can easily do this with one (yes only one) SQL Server license for the primary database. Subscribers to this database can run SQL express. As long as users access the customer databases, you don't even have to buy SQL CALs.
Go back to banking software, make sure they pay good money for their server licenses! So don't be surprised if they are reliable and fast ...
source to share