branch offices forms.net sql server

[Blast]

I was wondering if someone can tell me what Microsoft Technologies I
need to employ for the given situation:

As a small business, we keep all of our data currently in sql server
and we only have one office. We use forms.net as interfaces to the sql
server database. However, we are looking into expanding to multiple
branch offices. We are also looking into a way to expose some of the
data to the internet, so certain data can be viewed anywhere.

My question is, all of the data from all of the offices need to be
synchronized. So, how does one go about doing this? Should the database
be held in a single physical location as everyone from branch offices
all remotely update and retrieve data? If so, how do you secure this
setup ( authenticate, authorize, etc)?

What other ways is there? Can anyone point me to some strategies? I
have read about ERP systems. They store all of their data in a single
database, how does that work when people from different office
locations need to connect to data?

 

[Plamen Ratchev]

There are many different solutions for what you need and they depend on many
factors, I will just list a few below:

1). What type of connectivity is available between the offices?
2). How much IT personnel you have to support the systems?
3). How many data transactions you have?
4). Is the data segmented for each branch or shared?

A coupe possible solutions are:

1). Keep a central database and provide interface to all branch office via
Internet application (you can install SSL certificates to encrypt the data).
That will require to develop a Web application to manage your data.
2). Similar to the solution above but using VPN. Then you can use a Windows
application to access the data (I assume this is what you have currently,
based on you mentioning forms.net). Each branch office can have the Windows
application installed and it will connect over the VPN to the central
database.
3). Install databases at each location and then replicated the data with the
central office. You can still use VPN to connect to the central office to
guarantee secure communications. However, this solution may be less cost
effective and will require higher maintenance. But if you data is segmented
for each branch office and your connectivity is not good then this will be a
good option.

Here are a couple links that might be helpful:

Microsoft Solutions for Branch Offices:
http://www.microsoft.com/windowsserversystem/solutions/branch/default.mspx

Branch Office Case Studies:
http://www.microsoft.com/windowsserversystem/solutions/branch/casestudies.mspx

Building Secure ASP.NET Applications (this is more on the technical side):
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/secnetlpMSDN.asp

Regards,

Plamen Ratchev
http://www.SQLStudio.com

 

[Blast]

Excellent response.

Yeah, I think i'm leaning towards number 2. But 3 sounds like it may
suffer less down time in case you can't connect to the centralized
database. I've never done replication before... I take it you use
GUIDs?

Any other key distinctions between 2 or 3?

We're using active directory, so I take it once you setup the VPN to
the branch office you can authenticate yourself using windows
authentication with active directory?


Again, great response. Good information here.

 

[Plamen Ratchev]

Yeah, I think i'm leaning towards number 2. But 3 sounds like it may
suffer less down time in case you can't connect to the centralized
database. I've never done replication before... I take it you use
GUIDs?

It is not necessary to use GUIDs for replication (rather having primary keys
on all tables participating in the replication model). You can get more
details about replication here:
http://msdn2.microsoft.com/en-us/library/ms151198.aspx

Any other key distinctions between 2 or 3?

There are some other differences to consider, but again it depends a lot on
your implementation. For example, with option 3 you would have to perform
data backup at each branch (but that can be fully automated), there is some
data replication latency to consider, etc. Also, with option 3 your
application will have to handle some special scenarios, like if record with
ID=1 is entered at one branch, that should not be entered in another branch
or there will be a conflict. Normally this is resolved by adding a branch ID
to all records (or maybe use GUIDs like you mentioned).

We're using active directory, so I take it once you setup the VPN to
the branch office you can authenticate yourself using windows
authentication with active directory?

Yes, VPN can use Active Directory authentication. Here is more about VPN:
http://technet2.microsoft.com/Windo...a616-4422-bbd7-9cb8de066b291033.mspx?mfr=true

Regards,

Plamen Ratchev
http://www.SQLStudio.com

 

[Blast]

One more thing...

Also, we're looking into using sql notification services for our
applications to get near real time data updates (instead of polling).
If you have a centralized database ( case 2) is it possible to create
the situation where users in the remote branch offices can recieve
notifications that the data has been changed?

Always complications...

 

[Plamen Ratchev]

If you use case 2 then the users at the branch will see immediately the
changes in the data as they use a central database. But if you want to
provide notifications via e-mal, SMS, data files, then yes, you can use
Notification Services. Basically there are events that trigger actions when
the specified data is changed. Then you have subscriptions and subscribers
that get notified. I cannot think of any complications.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

 

[Blast]

Ok. Thanks for your replies. I'll have to check out SQLStudio.