Join Adam Wilbert for an in-depth discussion in this video What is SQL Server Migration Assistant?, part of Migrating Access Databases to SQL Server.
- In the last chapter, we exported a data table from Access and brought it over to a SQL Server database, then went through the steps of setting up the data type mappings and creating our relationships. While it wasn't really a difficult process, there were a lot of steps, and we only did it with a single table. Luckily, Microsoft has created a tool that will help us automate all of that work when we want to move an entire database from Access to SQL Server, and it's called the SQL Server Migration Assistant. The Microsoft SQL Server Migration Assistant, which is often abbreviated to just SSMA, comes in different versions that support the migration from several of the most common database formats.
The one we're most interested in is SSMA for Access, but there's also separate versions that will migrate from MySQL databases, or Oracle databases, or a Sybase, or many others. SSMA is a free tool that greatly simplifies the database migration process when moving from Access to SQL Server in four key ways. First, SSMA for Access automates the conversion of Microsoft Access database objects to their SQL Server counterparts. Then, it gives you fine control over the data type mappings.
It then loads the objects into SQL Server, followed by a migration of your data from Microsoft Access to its new home in SQL Server. Finally, the Migration Assistant will even update your Access database with links to the new data tables, so that your downtime is kept to a minimum during the transition. SSMA for Access is currently at version 6.0, but is designed to support migration from Microsoft Access 97 and higher to all editions of SQL Server 2005, 2008, 2012, and 2014, as well as Microsoft's cloud hosted SQL Azure databases.
For more information about SSMA, you can visit the product team's blog at the URL listed here. Now let's dig in and see how we can make use of this powerful tool.
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.
- Why upgrade?
- Importing a SQL database
- Exploring data types and server objects
- Installing SSMA
- Mapping source and target data types
- Using the Migration Wizard
- Linking to migrated tables in SSMA
- Working with views and stored procedures
- Linking Access to SQL Server databases