Deciding between Access and MSDE

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?
Thanks,
Dan
dan@.builtbydan.com
> 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).
That depends on you workload and structure, its hard to say, but for a
normal application
it will fit your needs. It it wont, its no proble to upgrade your db to SQL
Server 2000, or to the SQL Server Express database which woont have this 2
GB Limit at all.

> 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?
Yeah could be a choice, but i rather sould store all data in one database as
long as the size will allow.

> 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?
MSDE doenst cost that much CPU time that you cannot install it on a file
server or a workstation. So installing the MSDE on very other workstation,
detach /Attach the db file and switch over your application settings to the
new storage isnt a hassle.
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"Dan Fontenot" <dan@.builtbydan.com> schrieb im Newsbeitrag
news:059301c545c6$fc6e10c0$a401280a@.phx.gbl...
> 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?
> Thanks,
> Dan
> dan@.builtbydan.com
|||hi Dan,
Dan Fontenot wrote:
> 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).
personally I never hit that limit with "standard" data type, but this depend
on the customer's needs...

> 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?
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...

> 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?
yes, distributing JET database really is a breeze, but you can have simple
deployment scenarios like
http://msdn.microsoft.com/msdnmag/is...baseinstaller/ ,
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/de...stsql_84xl.asp),
valid for all databases, or at runtime, specifying specific path in the
CREATE DATABASE statement
(http://msdn.microsoft.com/library/de...reate_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/?u...seoverview.asp ,
partially available in SQL Server 2000 too via the sp_attach_db method
(http://msdn.microsoft.com/library/de...ae-az_52oy.asp)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply