MS-Access / Getting Started

---

Upsizing Wizard and the SQL Server Migration Assistant

In the following two sections, you look at migrating an Access databases to SQL Server by using the Upsizing Wizard and the SQL Server Migration Assistant (SSMA). To stretch both tools, we have used the sample database Northwind_ProblemsAndFixes.accdb, which contains known problems when migrating data. If you want to use this database, you can see how the tools handle the problems, or you can fix the problems as described earlier in this tutorial, by running the appropriate tests to look for illegal dates, and so on.

The Upsizing Wizard

Open your database. On the Database Tools tab on the ribbon, under the Move Data group, click the SQL Server icon, The Upsizing Wizard opens. On the first page, you have the option to either create a new database or use an existing SQL Server database.

On the second page, you can choose to use either the (local), which is the default instance of SQL Server, or specify the instance name (which you can determine when you start the SQL Server Management Studio). You also have the option to use either Windows authentication or a SQL Server Logon.

On the next page, you select the tables that you want to upsize. On page four of the wizard, you are presented with a number of optional choices. You can leave these at the default settings. Ensure that the Add Timestamp Fields To Tables? option is set to Yes, Always.

Select the Use DRI option to upsize your relationships. The other option, Use Triggers, is not recommended because it is less efficient than using DRI and it is a throw-back to older databases when referential integrity (RI) was not always supported.

On the last wizard page, you need to complete details. There are three options on this page: leave your database unaltered, replace your tables with links to SQL Server, or create a new Access client/server application (ADP). You will now look first at the option for adding links to the SQL Server tables.

The Save Password And User ID option is only applicable if you are using a SQL Server Logon and not Windows authentication. And even in that circumstance, if you plan on using multiple SQL Login accounts for different users, embedding the password and user ID in the links is both unsecure and probably not what you want to happen, so leave this option cleared.

After you are finished with the wizard, you are given a report of the setup, along with any error messages.

If you performed the upsize with the illegal dates, you get an error indicating that the relationship was not created. The problem here is that the illegal dates prevent the data from being migrated, and no data in the Orders table prevents the relationship with Order Details from being switched on. If you look in SQL Server, you can see that the Orders table is empty. If you fix the dates and then repeat the upsize, the table converts correctly. The report also contains a warning that a Unique Index could not be created on the Employees, EmploymentCode field; this is because you have a Unique Index Which Ignores Nulls on that column (as described earlier in this tutorial). You could then add your own non-unique index and a trigger to satisfy this requirement (also described earlier in this tutorial).

That the process has renamed the original tables and created new linked tables to SQL Server.

Upsizing Wizard strengths and weaknesses
The least attractive feature of the Upsizing Wizard is that is does not upsize Required fields; it should set the attribute NOT NULL on any Access column which is a Required Field. Even though you can work around this by writing trigger code to perform a ROLLBACK if it detected a NULL in certain fields, it would be both tedious and inefficient.
The wizard does handle UNIQUE IGNORE NULL indexes by skipping over them. It also pleasingly adds a DEFAULT of (0) to any Yes/No field (Bit field), to which it also adds the NOT NULL attribute. You also need to deal with any multi-value fields and attachments, as described earlier.
The Upsizing Wizard does not have any features for controlling alternative mappings on data types, so you are restricted to having UNICODE data types and accepting its data type mapping.
Because the Upsizing Wizard cannot be repeated to just convert the data, when you come to go live with a system, you need to repeat the upsizing process. So during development, you should maintain all your design changes in script files, which you can then easily re-run when making the system live and repeating the upsizing process.