Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wotrac
Yak Posting Veteran
98 Posts |
 Posted - 2005-02-12 : 15:07:32
|
| I am running a website which runs on sql server. The database on the site is a clone of the one on the Back Offic and is also on sql server. I want to ensure that when a new customer is inserted the website, it also enters in the back office.The stored procedures on both datasets are NOT FOR REPLICATION, so I have decided to do it via Linked Servers and distributed queries.I have only one problem. If the connection to to the back office fails, the two datasets will get out of Sync.Could anyone suggest the best way to handle this situation.Any solution will need to work both ways, as there is the possibility that users will insert at the website and the back office. |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-13 : 07:19:22
|
| We do it by Batch process. Changes to a record cause the status to change to "Needs to update remote database". Periodically a process runs that transfers the flagged records to the other server, and resets the status to "Normal". It complains if the same record, in both databases, is set to "Needs remote update" - i.e. changed on both databases within the same timeframe.if the link is down the pocess fails, and the records wait until the next successful run.Kristen |
 |
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-02-13 : 07:38:30
|
| I'm driving this from the web front end primarily.If the link is up the back office will get updated and the web database will be flagged as SUCCESS.If the link is down then the web database will be flagged as FAILED.I want to run the batch process from the back office end, or both.Do you think that is feasible? |
|
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-02-13 : 07:38:35
|
| I'm driving this from the web front end primarily.If the link is up the back office will get updated and the web database will be flagged as SUCCESS.If the link is down then the web database will be flagged as FAILED.I want to run the batch process from the back office end, or both.Do you think that is feasible? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-13 : 07:55:29
|
| Sounds fine to me. So like we do with the added benefit of immediate update IF the link is up.We do it in batches as it is more efficient - single row transfer to linked server is quite "expensive" compared to multi-row. For example, our clients can buy 100,000 new customer names and import them into their back office system - we want to get them up to the web in chunks, possibly over several days!, rather than bring everything to its knees getting the updates there in sync.Kristen |
|
|
|
wotrac
Yak Posting Veteran
98 Posts |
Posted - 2005-02-13 : 08:08:02
|
| Sound like we could reduce some of the web programming work, ifwe run the batch processing frequently.It would actually make things much simpler, as we would onlyhave to worry about pulling in the newly created data, without the worry over what could already exist in both databases. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-02-13 : 12:24:40
|
| Dunno that one ... but as the original posted you can, if you so wish, use the "Delete Reply" icon to get rid of the dupe.Kristen |
|
|
|
|
|
|
|
|
Linked Server