Updating database on website from another data store

views:

120

answers:

+1 Q:

Updating database on website from another data store

I have a client who owns a business with a handful of employees. He has a product website that has several hundred static product pages that are updated periodically via FTP.

We want to change this to a data-driven website, but the database (which will be hosted at an ISP) will have to be updated from data on my client's servers.

How best to do this on a shoestring? Can the database be hot-swapped via FTP, or do we need to build a web service we can push changes to?

+1 A:

Ask the ISP about the options. Some ISPs allow you to ftp upload the .mdf (database file). Some will allow you to connect with SQL management studio. some will allow both.

you gotta ask the ISP.

Cheeso 2009-07-15 02:31:49

Thanks, Cheeso.

Robert Harvey 2009-07-15 05:10:18

It turned out that we can connect directly to the SQL Server on the ISP using an ordinary connection string. Once we figured that out, writing a helper app to upload the data was trivial.

Robert Harvey 2010-03-29 22:24:46

+1 A:

Last time I did this we created XML documents that were ftp'd to the website. We had an admin page that would clear out the old data by running some stored procs to truncate tables then import the xml docs to the sql tables.

Since we didn't have the whole server to ourselves, there was no access to SQL Server DTS to schedule this stuff.

Jeff O 2009-07-15 03:13:33

Interesting. Kind of like a poor-man's web service.

Robert Harvey 2009-07-15 03:15:13

Since users had to process data locally to begin with, they didn't mind the extra step. I got the idea from a book that used this technique to copy data for users and lookup tables. I figured just keep repeating for daily updates.

Jeff O 2009-07-15 03:37:03

+1 A:

There is a Database Publishing Wizard from MS which will take all your data and create a SQL file that can then be run on the ISP. It will also, though I've never tried it, go directly to an ISP database. There is an option button on one of the wizard screens that does it.

it does require the user to have a little training and it's still a manual process so mabe not what you're after but i think it will do the job.

griegs 2009-07-15 03:22:55

Do you have to recreate the schema each time, or can you move data only?

Robert Harvey 2009-07-15 05:09:47

Either. There is an option on i think the 1st window that sets that.the downside is that it does (all) data which can be a pain as the db grows. But if it's small at the moment maybe this will do until a better solution is found.

griegs 2009-07-15 05:23:30

Scratch that! it's on the 4th step and is the last row inthe options grid.

griegs 2009-07-15 06:10:36

+1 A:

Long-term, building a service to upload the data is probably the cleanest solution as the app can now control it's import procedures. You could go grossly simple with this and just have the local copy dump some sort of XML that the app could read, making it not much harder than uploading the file while still in the automatable category. Having this import procedure would also help with development as you now have an automated and repeatable way to sync data.

Wyatt Barnett 2009-07-15 13:55:16

This is what I usually do:

You could use a tool like Red-Gate's SQL Data Compere to do this. The tool compares data between two catalogs (on same or different servers) and generates a script for syncing them.

Canoehead 2009-07-15 18:47:32

ansaurus

tags:

views:

answers:

Updating database on website from another data store

related questions