Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Considering Moving to SQL Server


Considering Moving to SQL Server

Author
Message
todd.ayers
todd.ayers
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
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.
david.alcock
david.alcock
SSC Veteran
SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)SSC Veteran (259 reputation)

Group: General Forum Members
Points: 259 Visits: 1157
http://technet.microsoft.com/en-us/library/ms143506(v=sql.100).aspx

'Only he who wanders finds new paths'
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3144 Visits: 3816
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 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search