Should I upgrade from Access to MSDE?

[converge]

Our company currently distributes a database which uses Access 2000 runtime as the front-end/back-end. This database is used by single users on desktop machines to retrieve scientific data. However we are near reaching the 2GB data limit. The database is also noticeably slow (one table contains 8 million records).

My question is this: what database should we consider upgrading to? I was thinking SQL Server Desktop Engine (MSDE) and use the current Access front-end code. Unfortunately MSDE also has a 2GB limit. But could we link multiple databases together in MSDE? We are actually doing this in Access 2000 but it is quite slow. What other desktop databases would anyone recommend?

 

[SQLePO]

If this database is critical to your business you should really talk your manager into using SQL (Preferably 2000) you should not use MSDE or Access, you need to upgrade to some variation of SQL

 

[SQLSister]

Yes, you should definitely go with SQL Server 2000 with that number of records. You can still use your Access front end, but you may need to convert your queries to SQL Stored Procedures to improve speed.

 

[hsitz]

I'm a little curious why you've gotten 2 responses to definitely go with SQL Server rather than with MSDE.

My understanding is that MSDE essentially _is_ a SQL Server with built-in "crippling". Whether to go with SQL Server instead of MSDE, then, would depend on whether the crippled functionality in MSDE would affect your app.

The main limitation of MSDE compared to full-blown SQL Server, as far as I know, is that it's optimized for about 5 concurrent users and designed to have deteriorating performance as the number of concurrent users grows beyond 5.

Assuming you can get around the 2GB limit in the way you're hoping (I don't know but I bet you can) then it seems to me like MSDE is the obvious choice if you don't have more than, say, 8 or 10 concurrent users. Even if you have more, you might at least try MSDE before going to full blown SQL Server, since they're file compatible and you could just detach the data from MSDE and attach to a real SQL Server instance to upgrade.

I'm curious whether there are other differences between MSDE and SQL Server that should affect Converge's decision of whether to upgrade to MSDE or to SQL Server? (Like the other two posts, I agree that some sort of upgrade from Access is the obvious choice. I'm surprised that it's performing reliably at all with data size of close to 2GB.)

 

[Niv3k]

I agree with hsitz... Rather than blown $1000, $5000, possibly $25000 depending on the version of SQL server you went with, and licensing, you should build the new database in MSDE first. Then you can make sure it works, without spending money you don't need to spend. It will probably be fine for your needs... Here is the one piece I don't think hsitz mentioned -> You cannot link MSDE 'servers'. But you can link MSDE 'databases'. And while the MSDE has the 2 GB per database limit, there is no limit (other than your hard drive, really) to the number of databases you can have on one server. So if you link the tables across the databases right, you can easily blow past the 2GB limit up to 10, maybe 20 gig.

Once you have gotten to the point where the database(s) and users (the 5 limit) have been exceeded, you can go with SQL Server.

One other thing - 5 concurrent users means exactly that. If the front end is written correctly, once a user has finished their immediate work, the connection they were using can drop, leaving you with maybe 20-30 regular users or over 100 part time users with no real performance degradation.

 

[sguslan]

Hope this will help you too :

http://www.microsoft.com/sql/howtobuy/msde.asp

http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

 

[zemp]

If money is the issue then you could look into a MySQL database. It is a free opensource database available on the net. I haven't used it yet, but I have heard good comments about it. Check out

http://www.mysql.com

for more details. Thanks and Good Luck!

zemp

 

[hsitz]

I think MySQL is a great database for some purposes (e.g., smaller projects, or back end for a web site), but probably not for many situations where you're using Access as a front end.

First and foremost of the reasons for this is that beginning with Access 2k, Access has "Access Data Projects" that simplify the integration of Access with a true database server, but only so long as that database server is MSDE or SQL Server. It's more of Microsoft looking after their own, but it also makes it a really good idea to use MSDE or SQL Server, because the .adp's give a really sweet development environment that you lose out on if you choose another back end.

Other reasons to not use MySQL are (1) no stored procedures or triggers, which you'll want to use if you're taking full advantage of a back end, (2) no transactions, (3) limited means of access, I'm not sure whether there is even an ADO driver for MySQL, you might have to use ODBC, which, again, will reduce functionality. Note that at least the transactional limitation has been remedied by several add-ons to MySQL (e.g., Gemini, Innodb), but that complicates the setup and I'd maybe be a little worried about stability and support.

If I were using a back end other than MSDE or SQL Server, I would use PostgreSQL (an open source database that is more full-featured than MySQL -- comparable to SQL Server -- but which has to be run on a Linux server) or Firebird (another full-featured open source database based on Borland's Interbase, not as widely know as MySQL or PostgreSQL, but it should be:

http://firebird.sourceforge.net/index.php?op=useful).

 

[zemp]

hsitz, Thanks for the info! Thanks and Good Luck!

zemp

 

[cj92713696]

Where can you d/l a copy of MSDE?

 

[Terpsfan]

According to Microsoft MSDE is only a development tool to upsize to SQL Server and is optimized for only 5 concurrent connections. MSDE is basically SQL Server's lightest weight db. If you have Access 2000 or 2002 you can download it from your installation CD. Then after you download MSDE on your machine you can convert your Access mdb into an Access data project connection to MSDE. Where X is your CD Drive X:\SQL\X86\SETUP\SETUPSQL.exe. You can also can get a copy of MSDE if you have Visual Basic Professional. I think it comes with the supplemental disks. I'd try running your db on MSDE first. If it doesn't do the job, then you have to go to SQL Server. I think in the end, that's where you're going to be headed anyway.