Clever Geek Handbook

Sql server sync alternatives

We have two Sql 2008 R2 databases. The first and foremost is on-site, which is used by our inventory management and accounting systems. The second is for our website and it is hosted by a third party hosting company and this database is manually updated using a standard XML file operation (create XML from a local database, send it to a web server, read from an XML file, and insert / update / delete web database)

we need to get rid of this manual XML operation and synchronize it with the two databases automatically, but the problem is that our hosting company does not allow using Replication or Linked Server objects on their Sql Server system. Thus, we cannot synchronize the databases with these "Sql Server" objects.

I am trying to find sync alternatives for this scenario without changing the hosting system.

What options can we use?

Can Microsoft Sync Framework be used for this scenario? (I'm not sure if this is only for Microsoft Azure Sql system)

Thank...

+3


source to share


3 answers


if you're coding great then Sync Framework can do it.

Sync Framework can sync SQL Compact, Express, Server, Azure or LocalDB



But keep in mind that the Sync Framework will need to create some Sync related objects in your databases (triggers, stored procedures, tables, etc.)

+1


source


It sounds like you need a hosting provider that provides these services.

It is typical for a hosting provider to provide an on-demand or always VPN connection to production servers from the client's location to the servers at the hosting location. This service is often required to support online systems. If you had such a connection, then SQL Server synchronization would not be a problem.



Any major vendor (Rack Space, Peer1, etc.) can set up such a system.

It probably won't be as inexpensive as your current provider.

0


source


Well, if changing a supplier is not an option, listed in the short term and you need something right now, you can always automate parts or all of your current process.

For this, you can at least use:

  • PowerShell (or even plain old cmd.exe) script (batch) and bcp.exe to export and import your files at both ends and PowerShell and ftp to transfer files from one server to another.
  • On premises SQLServer Integration Services to export and send files via ftp to your location. And SSIS to grab and import those files at your location.
0


source






More articles:


All Articles