Normally, client/server applications are developed with the client requesting operations on the server (e.g. AddCustomer, ModifyCustomer, DeleteCustomer, DoSomethingWithCustomer, etc). There's usually also a client side cache so lookup and other frequently used data are loaded into the client.
Moving towards developing smart clients, the cache is made robust so it handles synchronization of data with particular synchronization strategies, handling offline mode, etc.
With the introduction of SQL Express and assuming all future machines will be preinstalled with SQL Express, could we not write the client as though it's a 1-tier application reading/writing straight to the local SQL Express database and somehow configure SQL Express to do the synchronization If the synchronization configuration is not possible, maybe write your own custom synchronization layer that ensures data is synchronized between client and server
Looking forward to hearing the different views on this.
Data Synchronization with Client/Server
Sayure
This is one of the scenarios we target with SQL Server Service Broker. Reliable communications, asynchronous, queued service execution on the server, and client side queuing can make this attractive. If you think about an asynchronous connection to the server, when the client wants to interact with the server, is sends an asynchronous message to the server. The server pulls the message off the queue, processes it and sends a message back to the client. The client then pulls the messsage of the queue and processes it - probably transactionally updating the client database. If the client is connected to the server, this whole process takes a second or less. If it's not connected, it might take days or weeks depending on how long it's disconnected. The cool thing is that the interaction works the same whether your connected or not - it just takes longer if you're disconnceted.
The other advantage of using Service Broker is that the communication is bi-directional. The client can send to the server or the server can send to the client at any time. If you want to keep the client data in sync with the server, the server can send a message to the client when something changes. If the network is connected, the changes are sent immediately. If not, the changes are sent when the connection is restored. Again, you just build the system to use reliable, asynchronous messages and there's no difference between connected and disconnected operations.
Katarina C.
Kind of new to this whole Service Broker concept. How does it work Assuming a two-tier application with SQL Express on one side and SQL 2005 on the other, where does the Service Broker sit
shinji360
Sorry if this is posted twice - I thought I posted this yesterday but it isn't showing up today.
Service Broker is part of the database so if you have SQL Express on the client and SQL 2005 on the server then Service Broker is the part of the database engones that talks from the client to the server. You use TSQL commands to send data from either database to the other.
Here's some information that may help.
http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/sqlsvcbroker.asp
http://msdn.microsoft.com/library/default.asp url=/library/en-us/dnsql90/html/sql2k5_SrvBrk.asp
http://msdn2.microsoft.com/en-us/library/aa964144.aspx
http://sqlservicebroker.com/
http://www.amazon.com/gp/product/1932577270/sr=8-1/qid=1145380635/ref=pd_bbs_1/102-5814073-9717766 %5Fencoding=UTF8
Kadett
It's easy when you only read the database. The real problems start when users are writing into the same record and even the same fields.
I worked on some three-tier solutions where this problem already exists, but we could handle this by interacting with the user in case something is wrong.
The question is, do you want to go back to the user when some kind of sync issue exists
Nadav Levintov
it's a common way to manage synchronistation between client and server, but it's not real time you have to schedule the synchronisation within sql server. When you want real time up to date data you need an other solution. I think some of the data can be synchronized with sql synhronization and some need something like services with offline capability.
Kapil Aggarwal
Yes, definitely will want to notify users of sync issues.