Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Considering Moving to SQL Server Expand / Collapse
Posted Tuesday, April 16, 2013 9:55 AM

Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 1:56 PM
Points: 59, Visits: 120
My company currently uses Access 2007 and wants me to look at moving to SQL Server 2008/2012. My question is what all is needed in order to move our current database structure from Access 2007 to SQL Server 2008/2012? My question is geared towards a hardware and software perspective.
Post #1442828
Posted Tuesday, April 16, 2013 9:58 AM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 26, 2016 4:13 AM
Points: 251, Visits: 1,113

'Only he who wanders finds new paths'
Post #1442832
Posted Wednesday, April 17, 2013 2:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 27, 2016 12:41 PM
Points: 3,019, Visits: 3,652
I am assuming that you have got your Access applications split into Front-End and Back-End databases. It is the back-end tables that you will be moving to SQL Server.

It is possible to start off by migrating a few of your Access back-end databases to SQL Server Express. This edition is free, and as you are a new user you should go for SQL 2012 Express. SQL Express will only use up to 4 CPU cores, 1GB memory, and a maximum database size of 10GB (but you can have multiple 10GB databases).

When you outgrow Express, move on to SQL Standard Edition. As you are coming from Access, there is nothing you are currently doing that would justify Enterprise Edition.

After you have migrated your tables to SQL Server, it is worth looking at your Access queries, as some of them will perform faster if re-written to use a SQL Server view. This is particularly true if you have nested queries or join (say) 4 or more tables in a single query.

If you have any VBA code in your back-end database you need to work out how to deal with this. Some of the code may be suitable for re-writing as a SQL Stored Procedure, but you are likely to have some VBA code that needs to remain VBA.

A good approach for VBA you need to keep is to just keep it in your back-end database and define it as a Resource in Access. You will need to write some VBA code that allows you to capture a new version of the Resource DB, but this is much the same as the code you probably already have for capturing a different version of the back-end database.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2016, 2014, 2012, 2008 R2, 2008 and 2005. 11 May 2016: now over 37,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1443148
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse