Migrating Access Databases to SQL Server

Leverage what you already know about databases to migrate Access tables to SQL Server, and gain access to a more powerful toolset.

At one point Access was adequate. But what happens when your database grows and you need more powerful tools? You can move it! Migration allows you to take advantage of SQL Server's advanced features, and maintain a link to the data in Access.

Watch this course to learn how to leverage what you already know to take that next step. Adam Wilbert reviews the basics of SQL Server Management Studio (SSMS) and introduces two workflows for migrating Access data: one using a simple export/import process and the other using the free SQL Server Migration Assistant (SSMA). He also shows how to convert Access to SQL data types, link Access to SQL data via an ODBC connection, and address special concerns for Access 2010 data.

Introduction

• Welcome
• What you need to know
• Using the exercise files

1. Getting Started

• Understanding front ends and back ends
• Benefits of a SQL Server back end
• Understanding SQL Server editions

2. Digging into SQL Server

• Installing SQL Server Express 2014
• Starting and stopping SQL Server
• Using SQL Server Management Studio
• Importing a SQL database
• Understanding SQL Server data types
• Importing SQL Server objects
• Editing the table design in SSMS

3. Using SQL Server Migration Assistant

• What is SQL Server Migration Assistant?
• Downloading and installing SSMA
• Creating the empty SQL Server database
• Preparing the Access database
• Setting default SSMA project options
• Mapping source and target data types
• Identifying the Access objects to migrate
• Creating an SSMA project
• Creating an assessment report
• Fixing common migration errors
• Running the SSMA project

4. Making the Transition

• Backing up the database
• Using the SSMA Migration Wizard
• Exploring the results in SQL Server Management Studio
• Exploring the changes to Access
• Relinking tables after structural changes
• Linking new SQL tables from a production database

5. Connecting Access to SQL Server Databases

• Linking to ODBC databases
• Attaching the SQL database
• Creating the data source
• Establishing the table links in Access
• Adding records to linked tables
• Linking to SQL Server views

6. Upsizing Access 2010 databases

• Preparing to upsize
• Using the Upsizing Wizard
• Editing tables
• Migrating queries

Conclusion

• Next steps