Disconnected or Connected Architecture

Question

Is a disconnected architecture too complex for the following story line? I'd be grateful if anyone could share any opinions or experiences that they may have.

There is a business application that runs inside the office network which consists of a desktop application and a SQL Server database. Some of the data is exposed to the general public via a web application. The web application is disconnected from the main database though – essentially it uses a cut down replica. We use T-SQL to synchronise the data between the two databases.

The system is now getting increasingly costly to develop and maintain because of the synchronisation layer. I can’t help thinking that if the web application talked to the office database through web services or WCF (removing the synchronisation layer) then the costs would reduce.

Answer 1

Depends on the volumes of data you are managing. Typically you would use an online transaction processing database with a larger data warehouse behind where you have a large amount of data and need the speed of a smaller db for the real-time stuff and the warehouse portion for reporting and offline data analysis.

Assuming you don't have a large amount of data to manage that would slow down the web side when querying/updating then maybe look at creating equivalent views within the main db and have the web site interact with those.

Answer 2

Have a look at Web Synchronization for Merge replication, a feature introduced in SQL Server 2005, and ideally suited for this scenario as it uses HTTPS.

Answer 3

I think going ahead with WCF service would be cost effective option. It would be easier to maintain than the synchronisation layer and WCF service is built by keeping in mind issues like this.