BE upgrade

[davidoc]

I have an access implementation separated into a FE and two BEs whose tables are linked in the FE. I have been considering upgrading the BEs from Access to SQL Server 2000 and from what I have read and tried this seems to be a fairly straighforward task.

However I have tried doing this using the Developer edition of SQL Server and am getting a significant performance *decrease*. I was expecting a performance increase...
Could this be due to the fact that I am using the developer edition on my PC (Win XP, MS Access 2003, SQL Server 2000, Intel Xeon, 2GB RAM)? I won't get a chance to do any testing with the actual implementation until quite late in the developement for various reasons. This performance problem is quite serious since many of the tasks the FE is performing are already time consuming.

thanks for any info
Dave

 

[sfreeman@co.mer]

It would be pretty weird to get a decrease in performance.

You should be getting a significant increase, since all query parsing should be occurring on the server.

I have never been able to upsize to SQL Server without much tweaking, and many queries will not upsize correctly, and have to be ajusted to work with SQLSVR. However, once 'tweaked' runs like a champ.

 

[pono1]

Dave,

Upgrading an Access backend to a SQL Server backend will not automatically speed up your database. It could very well, as you've discovered, result in slower performance. A successful migration almost always requires time and study and thought.

You will, for example, often need to re-think and re-write portions of your Access front-end to take advantage of what SQL Server offers. You may also need to learn how to tune SQL Server itself, assuming you don't have a DBA who can help you. These two things -- more than most anything else -- will influence performance.

I recommend you first get The Microsoft Access Developers Guide to SQL Server, reading it through, testing things out on a trial basis -- if you have that luxery. You may even discover a migration to SQL Server is not necessary given your situation.

Regards,
Tim

 

[davidoc]

thanks for the comments guys.

One of the main problems at the moment I think is the fact that many queries use joins between the tables in the two BEs. I was hoping that I could upsize each of the BEs to SQL one at a time and (at least!) not have any decrease in performance, and then tweak the queries at my leisure.