SQL Server Performance Forum – Threads Archive

need hint on upsizing to MSSQL

Hi Guys,
My question looks like duplicate but I searched and read many docs in the site that there is no clear answer to my question. While ago I took MS Access responsibility in my office. The application was developed during 6 years of span and now I am just maintaining it. Although it is a single user application as the data grows up it is getting slower. And I am asked to make it faster. The application is mainly consist of 3 MDBs;
Dbase1.mdb –> keeps all the tables and only tables. (54 tables)
Dbase2.mdb –> linked to Dbase1 (37 tables) and used for data entry/view.
has a a lot of queries, one big main form (with many sub forms) and event procedures (written in VB which is accessing to DB via DAO) My plan is to use MSSQL as database engine, right now there is no way to leave Access front end in short term due to complex forms and procedures. First of all, by using ‘upsize’ tool I migrated Dbase1.mdb to a new database in MSSQL (Dbase1sql).
Then, I used the same tool to upgrade Dbase2.mdb by choosing ‘link to existing DB’ option. I didn’t save any of them in MS Access Project format. The migration is done in MSSQL Server 2000 from Access 2003. My problem is the performance is not better and I am not sure if I missed any important step or not. Any suggestion, hint or at last confrontation? ps: in one of the doc it was saying ‘don’t use MSAccess as frontend’, what are other options, especially for complex forms?
ps: link "http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/bldsysarch.mspx" is not good enough…

hi,
if you are talking about performance of ms sql then you need to create index and if already existst you need to defreg index on havily used table / on the fields which used for creteria.
AND FOR YOUR COMPLEX FORMS THEIRS A BUTTON "CODE" WHICH IS ON FORM DESIGN , GIVE IT TRY.
Regards Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

I think it is a common misunderstanding that the move to SQL Server mysteriously boost performance. The main strength of SQL Server is reliability and stability in a multi-user environment. However, a poorly designed database can bring even the best RDBMS to its knees. Well, you’ve made the first step in the migration. Your data now lives in SQL Server. Checking indexes is desirable. Checking your queries and application code, too. However, I think the main issue here might be that you didn’t choose an ADP project. Using the MDB format means, Access’ Jet Engine is in control of the data access, not SQL Server. That in turn means, that all data is send to the client and evaluated and processed there. The server is not much more than a data storage place. This constellation should give you about the same performance you had before. I would say, next steps are to migrate your Access queries and review your application code so that you finally can make the move to the ADP format. The Dbase1 issue you might be able to solve using linked servers, if you can’t get rid of it. Sounds like a lot of work for you. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Once the data export has been finished from Access to SQL server then you must defrag the indexes and run other database consistency checks in order to sustain the performance. There are lots of posts in this regard and articles to fine tune the performance in this website. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks a lot,
It is not going to be easy just by ‘upsizing’.
I will need to learn how to use the tools etc. May be he best is to take certification courses.

If you encounter any problems, come back and post your issue here. Most likely someone has been there before and can provide help. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

Links are available on web, for your joy:
http://support.microsoft.com/kb/241743/
http://support.microsoft.com/support/kb/articles/Q176/6/14.ASP
http://www.sqljunkies.com/Article/BD909D1E-3EA1-4323-889E-5E3A5415B977.scuk
http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlservertheupsizingwizard.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

And another one: I think there are also several books dealing with Access as front-end to SQL Server. You might want to check out the books section here or ask this in an Access community like the MS Access newsgroups.
http://www.microsoft.com/communitie…3c3-c10e-4523-9213-cdfd537838f1&lang=en&cr=US
or specific to Access ADP
http://www.microsoft.com/communitie…3c1-4861-428d-be18-b0d9a39f74b7&lang=en&cr=US
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>