Many database users begin their adventure in the world of databases by creating a Microsoft Access database that meets the basic requirements of their organizations. Often, this is a dramatic improvement from an unwieldy collection of spreadsheets, text files or other documents that the organization previously used to manage information. However, eventually the business may outgrow that Access database and need to make the move to a more advanced database product.
Why Make the Move to SQL Server?
This move may be driven by the size of the database or the number and types of users that require access to the information that it contains. Here are a few questions that you might ask yourself when considering the move from Access to Microsoft SQL Server:- Is the size of the database growing beyond the capacity of Microsoft Access? Remember, an Access database is limited to 2GB in size. If your database is creeping toward that limit (or you expect it to do so), you should consider upgrading to SQL Server.
- Do you have many users trying to access the database at the same time? While Microsoft Access is an excellent tool for environments with a single user or a limited workgroup, it does not scale well to larger environments. That’s exactly the type of situation that SQL Server was designed to accommodate.
- Do you require advanced analytic technology? SQL Server’s business intelligence capabilities go far beyond the reporting capabilities of Microsoft Access.
Migrating Your Database to SQL Server
In the remainder of this tutorial, we’ll explain how you can migrate your Access database to Microsoft SQL Server using the Access Upsizing Wizard. This is the Access-driven approach to migrating your database. If you're looking for a SQL Server tool that offers a similar migration path, you may wish to look at the SQL Server Migration Assistant.Here’s the process that you need to follow to start the Access Upsizing Wizard and migrate your database to SQL Server:
- Open your database in Microsoft Access. Follow the normal process to start Microsoft Access, locate the ACCDB file that contains your database and open it within Access.
- Choose the Database Tools tab in the Ribbon. Once you have selected this tab, locate the Move Data section on that tab.
- Click the SQL Server button located in the Move Data section. This will open the Upsizing Wizard shown in the image.
- Select whether you would like to import the data into an existing database or create a new database for your data. We'll assume that you're trying to create a new SQL Server database using the data in your Access database. Click Next to continue.
- Provide the connection information for your SQL Server installation. You'll need to provide the name of the server, credentials for an administrator with permission to create a database and the name of the database you wish to connect. Click Next after providing this information.
- Use the arrow buttons to move the tables you wish to transfer to the list labeled Export to SQL Server. Click the Next button to continue.
- Review the default attributes that will be transferred and make any changes desired. You have the option to preserve settings for table indexes, validation rules and relationships, among other settings. When done, click the Next button to continue.
- Decide how you want to handle your Access application. You may choose to create a new Access client/server application that accesses the SQL Server database, modify your existing application to reference the data stored on SQL Server, or copy the data without making any changes to your Access database.
- Click Finish and wait for the upsizing process to complete. Once you are done, review the upsizing report for important information about the database migration.
That’s all there is to migrating your data from Access to SQL Server. You may wish to take the time to read more about SQL Server, starting with our article on Choosing the Correct SQL Server Edition for your needs, the list of New Features in SQL Server 2012, and the Introduction to SQL Server 2012.