Two database synchronization

[saubz]

Hello. I am curious if there is some software that can synchronize my online database with the database from my physical store. Not all items in the store's database are on the web and the two databases are not in the same format. I am wondering if there is a program out there that will compare the two database files and update them both accordingly. thanks for the help

 

matt

[FalseDawn]

Very unlikely. Synchronization is very tricky to do properly, even with homogenous databases.

Your best best would be look at something like ErWin, although it ain't cheap and isn't a beginner's tool.

It will allow importing of database schemas and abstraction of database logical and physical layers and also provides some degree of synchonization via either scripts or direct database commands.

Whatever, there's going to have to be a lot of manual tweaking to achieve what you need.

[wizardsandwars]

It would definatly be work, but it can be done with jsut scriptiong as well. Something like once an hour, you could extract, zip, transfer, transform, and insert/update. There's nothing built for it already, but it wouldn't be to hard to put together, if you have a little experience with shell scription and ETL.

[FalseDawn]

Yes, "one-way" updating is not too bad, but if the problem is to keep them synchorized properly, then it has to be a two-way process, which adds to the complexity (and work required).

Plus it depends what exactly needs to be kept synchonized - just the products, or customer data as well?

[♥Vger]

Plus, there's the problem if you are on a shared server of whether or not your hosting company will allow you to connect to the database automatically from outside of the server.

 

Vger

[jon_l]

What format is the database in your store?

 

If there are odbc drivers available for it, and I'd imagine there are, you could use something like Access to do most of the work for you. I used to do this, main database was on a local pc in our shop and I'd rebuild the products tables every so often.

 

Jon.

[saubz]

one database has to be imported/exported using .csv format. the other can use .sql or .csv

 

How is access used?

[wizardsandwars]

Plus, there's the problem if you are on a shared server of whether or not your hosting company will allow you to connect to the database automatically from outside of the server.

 

Traditionaly, I wouldnt' attempt to connect from one database to the other, unless it's strictly for ad hoc reporting purposes, i.e. SELECT permission only.

 

Syncing ,the like of which he is describing, is almost always done either with a shell script that extracts to csv, zip up the output file, ftp (or the more secure rcp or that new putty file transfer), then on the other side you'd unzip, transverse the dataset, and insert/update where appropriate. If it needs to be a sync both ways then you'd have the re-verse.

 

Unfortunatly, this is what I do for a living all day long, everyday. ETL, specifically in support of analytical reporting, so I often need to transform the data by pre-aggregating it before it gets loaded into a star or snowflake schema. I do this with shell scripts, informatica, DTS, Ab Inito, or whatever I can get my hands on.

 

I say 'unfortunatly' because I'd much rather be writing php and working with web apps, but the pay scale isn't exactly commesurate.

[wizardsandwars]

How is access used?

 

If you install the MySQL odbc driver, you can use MSAccess to link to your MySQL database, and basically download (and/or transform) the data to your desktop that you want. Usually this is used in a reporting capacity. You can even scedule these types of jobs in the windows scheduler to run at predetermined times.

 

However, Access really isn't robust enough to be doing mass updates, especially since you'd be using an odbc connection instead of native drives and it's a OLTP you'd be updating.

 

I'd advise against using this method.

[saubz]

I dont really need to connect to the databases. The way I would do it is download the database from my website in sql/csv format, export my store's database in csv format. Then all I need to do is subtract the products from the store's database with what was sold on the internet. Then update the website's database with the current numbers on the store's database. The only numbers being altered is product quantities.

 

So this would have to be done with some custom code, not with MS Access or Excel or another "over-the-counter" program?

[wizardsandwars]

Yes, unless both datbases are from the oscommerce schema, you owuld need custom code to do this, becuase no third part application would be able to map what columns from what database go to what columns in the other database. Not to mention there may be granularity issues, or snowflaking that a third party application wouldn't be able to decipher on it's own.

 

That's why guys like me have jobs.

[wizardsandwars]

Forgot to mention, there are third part applications that could help. Last time I checked, a single license for Informatica is about 20k. If you own a license for MS SQL Server - Enterprise Manager, you can use DTS, but that single license probably is about equal costs. But even with these, you have to map where each individual column will go, and what the transformation, if any, there will be.

 

Much more cost effective to do it in a shell script or two, unless you plan on syncing lots of databases.

[saubz]

thanks for your help. i definately dont have 20k budget. any advice on reading material to learn this crap?

[wizardsandwars]

i definately dont have 20k budget

 

Exactly. Me neither. That's why I suggested just using a sync script in korn shell or php. An experienced shell or php programmer could do it in 5- 10 hours depending on the level of complexity for the transformation.

 

I can't think of any documentation that would help off the top of my head.