April 16, 2013 at 9:55 am
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.
April 16, 2013 at 9:58 am
http://technet.microsoft.com/en-us/library/ms143506(v=sql.100).aspx
'Only he who wanders finds new paths'
April 17, 2013 at 2:41 am
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: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply