Integrating web and backend databases

[aspdaddy]

I have a quite complex purchasing/crm system (SQL2K) that I need to port to the web so that initial ordering can be done online via several websites.

The problem is that orders stay live for up to 3 years, in this time they can be updated and changed by both customers and staff, part payments can be made etc so there needs to be exchanges of data both ways between Intranet and Web facing systms. I also need to preserve primary keys and data integrity etc in two systems.

Any thoughts how to solve this? Any links or names of tools would be good. Thanks!

[carguy84]

you have to separate systems, one for the intranet and one for the application that you want to port to the web?

Or you have one system/application right now that you want to port to the web - and then still be able to use the application for updates?

or?

[aspdaddy]

Yeah its the second scanario.

I looked into using SQL merge replication but because I need to update both systems its not really a good solution.

[Red_Eye]

I would use a webservice (asp.net works well with sql server and is dead easy to build) to put an internet facing interface onto your database. This way you can only allow customers limited access to the database (or full access if that it what you want)

Once you have your interface designed you can then link these number of websites to it, or design your own website that connects to it.

The webservice will work with most programing/scripting languages that website are built with these days. But I think that at the end of the day you will get the most from a custom application/webservice, then you would with an off the shelf solution

[aspdaddy]

Does the webservice talk unencrypted on port 80?

[Demaestro]

If it were me and I do this a lot is, I would set up a series of imports and exports. Just write some scripts on the server that will pull data from the db and store it in a tab delimited file or whatever format you like, store that file on a the server machine. Then write a series of import scripts that will grab those files, using wget or whatever and import them into the other database. Do that for both if you want data to be aligned in each system. Just make sure that the new database you are using will use a sequence of primary keys that will never conflict with the one being generated in the other db, this way you will know which db the record originated from. Also set a flag on exported records, like a boolean saying it has been exported so you don't export the entire db everytime.

Set up a few cron tasks to run nightly at like 3:00am or whenever traffic is low and away you go. If you need to keeep the data aligned more frequently set it to run every hour or whatever, just make sure your SQL is really optimized if you run it often so it doesn't bog down the machines.

It sounds like a lot of work but when you think about it you can take a bunch of imported files once you have a nice collections and zip and it is a whole second set of data back-up. Which is never ever a bad thing.

[Dijkgraaf]

You can put webservices on https: and you can go further by having it use WSE2.

I would suggest you get an export in system architecture in and discuss your options with him, as there are various ways this can be done, however the amount of information for us to make an informed decision as which is best for you is probably a bit beyong the scope of help here :-)

[aspdaddy]

Thanks, I figured that. I have spoken to a couple of consultants but its still worth asking around where it doesnt cost :)

Funnily the advice from one consultant was to put the Intranet into a data centre and just have one system...

[aspdaddy]

Well I have decided to go with a solution just like Demaestro suggested.

The thing about the architecture experts is they like the technology rather than the business challenges. They love to solve problems using the technology

My initial thoughts were import/export as I have seen some offshore planning systems in Asia work like this and it has its limitations but the users understand them.

It does feel a bit backward though when all the experts are talking about webserices and replication :)

[Demaestro]

Another advantage to this is it is easy to expand tables with new fields and you can even do some data parsing to 'clean stuff up as it gets moved back and forth, Labor intensive? Somewhat, but the beauty is you have full access to the methods and full knowlage of how they work and therefor can make changes as neccesary.

When you take on someone elses technology you have deal with their solutions and when you know what you want to do it isn't always easy to get something someone else made to do that one task you have in mind.