Synchronizing Local and Web Data

[Alphonse Giambrone]

I am not sure if these are the correct groups to post this, so please
redirect me if there are better ones.

Client has a SQL Server db of organizations and member info that is in
pretty much constant use at his location.
He would like to make the maintenance of adding/editing member info
available on the web.
Since the data contains personal info the web app (built in ASP.NET) will
use SSL.
The unique identifier for a member is the combination of the organization
and a user supplied value. This can't be changed as the local front end is
already in use and built by another party.

My thought is to place a copy of the db with relevent tables on the
webserver (commercial host) and synchronize it with the local db.

Client's current internet connection is DSL, but he would be willing to get
fractional T1 if needed.

I would appreciate any thoughts, comments, suggestions on synchronizing the
data.

TIA

 

[Paul Ibison]

Alphonse,
as you are editing data at both sites, my recommendation would be merge
replication. Merge will allow autonomy (disconnected access between the 2
SQL Server databases) and latency. It will also solve and log conflicts if
your client edits a member's info while someone else does on the aspx page.
There is a good section on merge replication in BOL.
For the replication setup over DSL/T1 you'll need to arrange things a little
differently as presumably it'll be a VPN or non-trusted domain. Here is a
reference to help set it up: http://support.microsoft.com/?id=321822
HTH,
Paul Ibison

 

[Alphonse Giambrone]

Paul,

Thanks for the info and link.
What would be the feasability of just keeping the db at the client site and
having the web server accessing the data (read/write) directly from client's
SQL Server via a vpn?
Of course, upgrading from the DSL.

 

[Steven Cheng[MSFT]]

Hi Alphonse,

I also agree on Paul's suggestion on using SQLSERVER's replication
function. And as for the accessing the db via a vpn problem you mentioned,
I think maybe the main problem will focus on the connection between the two
machine since the VPN may have a firewall which will possibly prevent the
externel connection to the db server. Here is a kb article discussing on
this, you may have a look:

#INF: TCP Ports Needed for Communication to SQL Server Through a Firewall
http://support.microsoft.com/default.aspx?scid=kb;EN-US;287932

In addition, if the client machine(have the sqlserver db installed) can
host a webservice application(have iis and dotnet framework), I suggest
that you can consider building a webservice and expose all the sqlserver db
data manipulation and synchronizing via this webservice. Thanks.

Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Alphonse Giambrone]

Thanks Steven,

A web service is also a possibility. Clients server is SBS 2000 and if it
does not already have IIS/dotnet, he would get it installed (I assume SBS
will support it).

A couple of questions though:

Since the data is personal, how would I protect it from prying eyes if using
a web service?

Which method would provide noticably better performance:
Replication,
connection to client's server via vpn,
web service,
other suggestions?

 

[Paul Ibison]

Alphonse,
I always prefer to get the data nearer the application; it avoids problems
of network connectivity issues, is generally faster and also offloads
processing work from the production server.
Regards,
Paul Ibison

 

[Steven Cheng[MSFT]]

Hi Alphonse,

Thanks for the followup. Yes, the webservice generally send data as plain
xml text, so if we want to transfer sensitive datas via webservice, we need
to apply some security mechanism. The SSL is one option, and there're also
some other approachs for security webservice such as custom encrypt on the
data,

#Encrypting SOAP Messages
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspnet/ht
ml/asp09272001.asp

#SOAP Security: Encrypting SOAP messages
http://www.wimdows.net/articles/article.aspx?aid=17

As for the "which will provide noticably performance", I think the directly
acessing from DB server is generally more efficent. The XmlWebservice is
based on Xml SOAP message so the message parsing and processing will be the
performance concerns especially when transfering large quantity data.
However,
we should always think the webservie of a good means for transfering well
formatted interoperable data, that's its strengh.



Thanks.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Get Preview at ASP.NET whidbey
http://msdn.microsoft.com/asp.net/whidbey/default.aspx

 

[Alphonse Giambrone]

Thanks for the info Steven.
I will check out the articles.
Once I get a look at the actual db structure, etc. I will be able to make my
decision.