Local vs. Server DB's

Ascerbus · February 7th, 2009, 04:42 AM

I was wondering if there is some sort of list of Pros and Cons that I could be pointed to in regards to deploying a website with a local copy of a database as opposed to a server copy. I have read that installing a local copy in a website project makes it easier in terms of admin rights and such, but I am looking for a bit more detail.

In particular, I am trying to plan a project where I would be using the same database for both a web component and a more streamlined desktop interface for different users over an intranet. I am an Access database developer and I don't think Access can handle what the project requires (maybe just barely with a lot of coding tricks). I plan on using Access as the desktop front end for 3-5 users who will perhaps be using the database simultaneously with (in an absolute worst case scenario, and highly unlikely) about 25 users on the web interface.

The one and only reason I am looking to use Access or SQL Express is cost. This is for a non-profit organization with very limited funds which is still using a paper and pen for many of its functions. I am hoping to offer a much cheaper solution in order to get my feet wet and help these guys out.

Am I completely off base here?

By the way, the book is fantastic: very easy to use and a nice transitional text for a guy with my background. I have been using the 24/7 library and absolutely love it. It is a little rough figuring out what individuals are talking about when they post with a title like "Question about paragraph x on pg. y" on these boards, but it is a minor inconvenience at worst.

Imar · February 7th, 2009, 04:55 AM

Quote:

Am I completely off base here?

Absolutely not.

SQL Server Express is a fine replacement for Access but equally free. The good thing about the Express edition is that it's fully compatible with the commercial versions, So when the need comes to upgrade to a full version, you don't have to upgrade or change your code.

I would opt for a "server based" version of the database. IMO, it has the following advantages;

1. Databases are more "visible". You can manage them all using the "SQL Server Management Studio" making it easy to see what user has what rights, the databases you're running and so on.

2. Changes will be easier to make. Since your databases can be made using "SQL Server Management Studio" you can do so on the database, rather than through your own application. This makes it easy to quickly see data, insert / update records and so on.

3. Backups will be easier, You can let SQL Server make backups of the database while they are in use. Depending on your backup software this may not work with a database in App_Data.

Using a local database in App_Data is great during development. It's easy to use, easy to configure security and so on. But once I deploy I always attach them to a real server version of SQL Server.

Quote:

By the way, the book is fantastic: very easy to use and a nice transitional text for a guy with my background. I have been using the 24/7 library and absolutely love it. It is a little rough figuring out what individuals are talking about when they post with a title like "Question about paragraph x on pg. y" on these boards, but it is a minor inconvenience at worst.

Thank you, much appreciated. For me, with a paper copy on my desk, he page and paragraph references make a lot of sense, but I do see your point... ;-)

Cheers,

Imar

Ascerbus · February 7th, 2009, 05:53 AM

I am very impressed with how fast you replied to both of my posts!

Taking into consideration that this application (system) will be intranet based, I am assuming I will be going with the Express edition. From what I gather, the limited amount of users each day will probably allow this to function fine without the organization needing to purchase full versions with licenses. Time to move on from Access (though I will probably be making some user specific desktop front ends in Access that will connect to SQL Server).

All of your good points aside, does an application take a performance hit at all if the database is on a server? What I would gather is that it does simply because of the time it takes for data to travel back and forth, though it may be negligible. Purely a professional curiosity.

And regarding the 24/7 versions: I think the biggest draw for me is actually being able to copy and paste the code into my demo apps. Over the years of writing VBA code I have learned that ctrl-c and ctrl-v are my best friends =).

Imar · February 7th, 2009, 06:04 AM

Quote:

All of your good points aside, does an application take a performance hit at all if the database is on a server? What I would gather is that it does simply because of the time it takes for data to travel back and forth, though it may be negligible. Purely a professional curiosity.

A server based version of a SQL Server database doesn't necessarily mean a separate physical server. It's not uncommon to have both IIS with the web site and SQL Server with the database on a single server, removing the network overhead.

Whether or not it's useful to have a separate server depends; yes, you do have the netork overhead. However, since the database is on a separate -possible dedicated - SQL server, that server may be able to get at your data quicker. So, it largely depends on the hardware, the network between the systems, the type of data you're querying and so on.

On top of this, a local database in App_Data may in fact be slower as the database needs to be attached on the fly for the requests.

Cheers,

Imar