Deciding between Access and MSDE

Microsoft SQL Server

We are a small company writing primarily in-house
software, but also potentially developing software to
market.

We are converting from writing all of our software
completely in Access to writing in Visual Studio (C#). We
are confident this the best decision for both our in-house
and potentially marketable software.

What is a bit of a question mark is whether it is best to
go with Access/Jet databases (questionable since MS has
deprecated the Jet engine and is not upgrading anymore) or
MSDE. My feeling is that for future functionality and
scalablity MSDE (SQL Express 2005 when it comes out of
Beta and we upgrade to .NET 2.0) is a better choice.

Here are my concerns:

1) How common is it to hit the 2GB per database limit?
That sounds very large to me and seems unlikely to be a
problem except in extremely large applications. We are not
storing pictures or documents, only standard data types
(text, int, bool).

2) In the 2GB data structure, is there any reason I can't
have an archive database to move old data into to keep the
size of the working database down if necessary?

3) From a deployability standpoint, Access is a breeze.
Copy a file to wherever you want it and point to it. Is
there a way to build easy deployment of MSDE into our core
application so there is minimal effort (choosing a
directory or location, even if installed on a server) on
the part of the end user?

Dan
[email protected]
hi Dan,
personally I never hit that limit with "standard" data type, but this depend
on the customer's needs...


nope... you can go for that design implementig a sort of partitioning...
something called vertical partitioning like, where all columns relatated to
a particular object model are stored in one database and are not related to
objects in the other one..
but this has little to do with other performance tuning including
partitioned servers and federated database...


yes, distributing JET database really is a breeze, but you can have simple
deployment scenarios like
http://msdn.microsoft.com/msdnmag/issues/04/09/customdatabaseinstaller/ ,
and you can easily use the default SQL Server data path (..\Program
Files\Microsoft SQL Server\MSSQL\Data [for a default instance]) or specify
custom target prosition, both at MSDE installtime, using the DATADIR
parameter
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/distsql/distsql_84xl.asp),
valid for all databases, or at runtime, specifying specific path in the
CREATE DATABASE statement
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_1up1.asp)...
usually the database folder should not be shared among "standard" Windows
users for security reasons, but you can actually put them wherever you want
as long as the Windows account running SQL Server service is able to reach
them....

in SQL Express scenario, you have further options, where you can take
advantage of a so called "Application XCopy"
http://msdn.microsoft.com/library/?url=/library/en-us/dnsse/html/sseoverview.asp ,
partially available in SQL Server 2000 too via the sp_attach_db method
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp)...