How to make the case for Access->SQL Server migration?

[samb]

Hi, please help me make the case for migration to SQL Server...

 

I've just joined a new company as lead/senior developer at the start of a 9 month winforms project, upgrading a VB6 rich GUI app that uses Access DB's as the backend (accessing them directly over the network - no object or data tiers). I'm not the PM and the project plan is already in place.

 

They want to migrate to SQL Server as Phase 2 of the project - once the UI and object layers have been redeveloped against the existing Access backend - I want to migrate now, as part of the redevelopment.

 

I am ashamed to say I am losing what should be a very straight-forward and persuasive argument.

 

Please help me out. What would be your Top 5 arguments in favour of SQL Server migration?

 

Let me know if there's any further information about the project you need. Thanks

[Joe Mamma]

In no particular order. . .

1. Maintenance

2. Multi-user stability

3. Performance

4. Enterprise integration

5. Security

 

one major thing, for developers, is the ability to use the SQLClient .NET libraries.

 

Access is not a true RDBMS - it has some relational capabilities but it is not a SQL-92 compliant system - no triggers, no procs, no functions.

Access corrupts as it is a file based system. Let a couple of users open the Access database simutaneously and simulate a power failure by unplugging the machines. Then try to open the database again. . .chances are the file will need to be repaired.

Access needs to be compacted regularly

 

That being said it still might not be worth moving to SQL server. . .

How many users? How big is the database? Are you running a Windows 2000 or 2003 domain?

[Joe Mamma]

about my power failure simulations. . .

 

have one of the users put a recordset in edit mode with a transaction.

and do the power failure test on the machine that has the database on it, too.

[Afraits]

2. Multi-user stability

Let a couple of users open the Access database simutaneously and simulate a power failure by unplugging the machines. Then try to open the database again. . .chances are the file will need to be repaired.

 

A few years ago I used Access as the back end for a little app for the purchasing dept to keep track of the suppliers used. Without getting into details it used the primary key (autonumber field) of the main table as part of the link between GUI and data using a VB int (surely 32k different suppliers for a company our size would be more than enough :rolleyes: ). As this was a small app - only a couple of concurrent users I thought it would be safe. A few months ago some runtime overflow errors occurred On investigation I found that the autonumber field had incremented past the VB6 integer limit. There were only a couple of hundred entries in the table but the autonumber field would occassionally make large jumps - 2000 here 5000 there, in 1 case it was 15k and I find it difficult to believe that user error could result in 15,000 wrong and deleted entries before they got it right :eek: . Hence must have been the Access DB itself. It was simple to change Int to Long but enough to convince me never to use Access in a multi user environment again.

 

There are ways around this but in my opinion not worth the effort of having to setup and track your own ID fields & hopefully an actual example of data corruption may give added weight to your position.

Edited July 19, 2005 by Afraits

[samsmithnz]

For a start, you'll save about a second everytime you create a connection, access is very slow at this.