Web database >> Office database

Hello - I imagine a load of you guys work in a similar set up - you have your website, it collects data and then that data is sent into your office database that people in your office can view/edit etc..

What I want to know is, do you tend to keep your web database seperate from your office databse? If so, how do you go about transfereing the data? Or do you just have your website write staright to your office datasbe?

Cheers.

Any time you have two databases, you will have them out of sync. Replication can take care of some of this, but the only viable method is a single database.

Jeff

Thanks - in that case, is there any difference in security between hosting a website on a server that is also on your office network - so that machine can directly access one database.. OR... Host a website on a remote machine and just set up a few web services that perform the database functions you require and call those services from your website?

You can use a DTS or Integration services package to transfer the data between both databases just make sure you use Unique index with IGNORE_DUP_KEY option in your create index statement. Replication is very resource intensive option that require planning with a data person who understand your needs. But anybody can create and run DTS package with a Job schedule. Try the link below for sample DTS code. Hope this helps.

http://www.sqldts.com

loydall:

Thanks - in that case, is there any difference in security between hosting a website on a server that is also on your office network - so that machine can directly access one database.. OR... Host a website on a remote machine and just set up a few web services that perform the database functions you require and call those services from your website?

My preference, and a popular standard, is the web server in the DMZ and database server on the LAN. Open port 80 / whatever to the DMZ from the internet, and only the port you use for SQL from DMZ to the database server. Use a non-standard port for the SQL Server and you're about as safe as can be and still have the functionality. Proper coding, strong passwords, etc. of course.

Jeff