Help! Upsizing from Access 2003 to SQL Sever 2005

[bsmeby]

I have about 10 databases we are thinking of moving the back-end to a SQL Server 2005, due to expansion (more and more users are using these databases daily) and speed issues. Most of the databases were created either with Access 2000 or 2003. I did a little research and was told not to use .adp projects, but instead use linked tables to the SQL Server because of compatibility problems.

Will this slow down the performance/speed of the database if we still use Access as a front-end and have linked tables to the SQL Server? Instead of using queries in the Access database, would stored procedures and view be a better route with this setup?

Most of these databases needs to be normalized to also help with our performance issues, once that is done most of the old queries and forms will have to be changed or redone entirely.

Would it be a better idea to jump to a new interface (probably done with a .NET language) than trying to stay with Access for the time being? Eventually we would like to try to get away from Access and keep all of our data in one central location.

Any comments or suggestions are appreciated. Thanks.

 

[ksj]

Thoughts on SQL Server

The thing about linked tables is that you are introducing the ODBC driver into the mix. So you have SQL, the Jet engine, AND the ODBC driver to worry about. In my experience, using the ODBC driver causes an unnecessary number of page locks on the SQL server, and will significantly hamper your ability to add/update/delete records quickly. This is not so much of a problem in a reporting situation, though (a lot of people like to link Access to SQL tables to use the oh-so-wonderful reporting tools that Access provides, and as a general rule, the ODBC driver supports this nicely.)

If you do pursue this solution, and find yourself getting some peculiar results, be sure to google your question with "ODBC" in the question - there are a few common bugs (like Access has boolean fields (Yes/No) and SQL has boolean (bit) fields, and everything should be OK, except that the ODBC driver will mistranslate a "Null" in SQL to be a "No" in Access, and weird results occur.) Same kind of thing happens with date math. Generally speaking, though, a solution will be found online.

Putting your queries into SQL server will dramatically improve performance (normally), because it puts the processing on the SQL server which is optimized for data handling, instead of on a PC's CPU. It also will minimize the network traffic (if that's a constraint in your environment, and it often is.)

But the non-normalized data obviously makes me wary, because as you've obviously realized, you can't write a perfect query against a bad ERD. So you may not get the standard performance gains.

As for the change from Access to .NET (or LAMP, or whatever it is you decide to go to), that's a tough question without more details. I like Access, but I am keenly aware that it has built-in limitations. On the pro-Access side, if you're moving all your data to a SQL repository, you will in fact have accomplished the "all the data in one central location" regardless of the front-end tool you choose. So if Access meets your needs, it can continue to have a place in your organization, and it is significantly cheaper to develop in than non-rapid-application-development tools. Budget is always a factor for me.

If I was running this project (and I do mostly project management and architecture, not programming), I'd start with the data. You currently have 10 databases - instead of having 10 SQL databases, would you be better off with 1 database with all the data (normalized, of course)? Is the data logically connected? From there, you may find that you have 8 databases that have lots of redundency and should be combined into one, and you want to spend the time and effort to design a new front-end (in whatever tool makes sense for your environment - # or users, read/writes to the db, budget, presence of a SQL dba, etc.) for that, and just tweak the two "stand-alone" databases to continue using their Access front-ends on an upsized back-end.

There are a lot of variables! I have a current client who wasn't getting the performance they thought they should out of a SQL/Access reporting database, and they were blaming Access (and talking about spending a lot of money upgrading to .NET/SQL reporting services (which are AWESOME, by the way).) It turned out that the internal network was the constraint, it wasn't able to pass recordsets quickly enough, because the something-or-other-doo-dad-switchplate was bad (I don't do networking, at all, but the contractor we brought in to troubleshoot took about 15 minutes to isolate the issue, once we saw on SQL monitoring tools that the network was the problem. I do love the SQL monitoring tools.)

Sorry for the rambling response, hope something in here is helpful.