Scott Carsten

I have a 2-tier architecture question.

What are the problems associated with accessing data remotely in a situation where a client application is directly accessing a SQL Server database over the internet

There are obviously security issues related to preventing unwanted access to the database, but what about performance issues Are there connection reliability issues, network limitations, etc., related to the effectiveness of a direct remote connection to SQL Server

An alternative architecture we are considering is a 3-tier model with a middle-tier application server (web service).


Re: Architecture General 2-Tier Problems Accessing Remote Data

C l e m e n s

This can get a long answer when you want al the tradeoffs, pros and cons between the two types of architecture you mention. So I will give some thoughts about this.
You have to make some functionality that the client app still works when there is no connection, with webservices you can work with queues with database with client side databases. The development time with direct database connections is less than with webservices [ or you can use the service factory, will increase development time ]. with a direct connection you can't share functionality [if you want to]. If you want to go over the Internet security is with both options an issue [try the threat modeling tool]. Performance, I think with direct connections the developers are thinking that they are working on a client server application, with the risc that they are going to often to the database, with allot of network traffic.... I would go for the 3-tier if you need to enter the Internet on a intranet you can think of direct connections... but there are more tradeoffs you need to make.

Re: Architecture General 2-Tier Problems Accessing Remote Data


if u r developing a serious application, go for the 3 tier model, and her is why:

- More secure, because you restrict access to the database just to the application server. In other words, clients submit a request and it is the server that performs the real database access after checking the validity of the request.

- More management. The application server will provide you with the ability to manage the access of the clients, establish rich & complex security policies, as well as managing users and groups of users. Not to forget logging and auditing per user.

- Data consistency. Although the validation of data can be made in stored procedure, however when the validation becomes more complex and difficuly, it becomes harder to put it into SP and managing it will be even harder. So the application server is better fit to do this role.

- Easier update of business logic. Instead of updating N client applications, all you need is to update only the application server.

- Better performace. Usually databases cache data per type of queries, so subsequent queries should be the same in order to benefit from any cached data, while the server is more aware of the intent of the client and can rely on its own hand-made cache or a 3rd party cache to quickly fetch data.

that's all what I can think of at this moment.

However this is serious business, so if you are developing an application for your own department to solve a simple problem, like employees time sheet, you don't have to go into all this mess. However if you are developing a big application for your customers and you want to give them something professional, I don't think u have a choice other than 3 tier model.


Re: Architecture General 2-Tier Problems Accessing Remote Data

Arnon Rotem Gal Oz

You should think about your requirements then try to decide what's the solution that would fit your situation

There are many other considerations you may need to handle like availability, security zones (and firewalls), load balancing and many others


Re: Architecture General 2-Tier Problems Accessing Remote Data

Ollie Riches

I agree with the positives Z.Y.S defines.

One of the downsides of moving to an n-tiered model (usually 3 tier) is the 'cost' of making a call - the time taken to return data from a database is approximately 1000 times slower than reading a value from memory, therefore the time taken to retrieve data from a database to a UI tier will be considerably longer if you implement a distributed application tier.

This is not a reason not to implement a distributed application tier, just a negative side effect to be aware of, you can negate this downside by analysing the data you wish to store in the database and determining which data is reference only (never changes), infrequently & frequently changing data. Once you know these you can then implement a data caching strategy in the application tier - this has 2 main benefits: reduce time taken to retrieve data and improve scalability of the application by reducing the number of requests made to the database.

If you are using MS Sql Server 2005 then you can utilise Notification Services, this allow you to receive events when data changes in Sql Server 2005 - they have been muched improved over the Sql Server 2000 model.


Ollie Riches