asked on

Converting from MS Access Backend DB to SQL - move the queries ?

We are in the process of converting an Access 2010 database structure to SQL server.
Is it necessary / beneficial to relocate the queries in the 'client' database to the server, or should they stay within the client ?

Not sure what best practices are and any guidance would be appreciated.

Thanks in advance

Jogai

From a optimal database view: Yes. You can transfer the queries to stored procedures. That way you can avoid many problems, but since you are migrating it is easier to leave the queries as they are.

JVWC

There are a few ways that you can migrate to SQL, so the answer to your question lies in "How far do you want to take it?
You can link the QSL to local tables and leave the queries as is, or migrate to server side stored procedures.
There is a good White Paper at this link, Step 3.
http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx

Cheers
JC

ASKER CERTIFIED SOLUTION

darbid73

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

SOLUTION

Scott McDaniel (EE MVE )

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

DaveHester

ASKER

Thanks for the guidance, in particular to darbid73 & LSMConsulting. We will try the approaches you have suggested and evaluate the outcome. Appreciated.

dportas

Futher to what LSMConsulting said, a database design suitable for Jet/ACE isn't necessarily appropriate for SQL Server. I suggest you review your database and redesign where appropriate.

Examples: Add keys if they are missing. Add extra columns if they are needed to replace "row ordered" functionality like DLOOKUP and FIRST/LAST. Eliminate nullable unique constraints and foreign keys (in some cases they work differently in SQL Server). Modify data types. The automated upsizing tools will not catch all of these changes.