The Upsizing Wizard and the SQL Server Migration Assistant - MS Access

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_ Problems And Fixes. acc db, which contain 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 chapter, on page 543, 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.

Enter your server details and the name for the new SQL Server database in the Upsizing Wizard.

Enter your server details and the name for the new SQL Server database in the Upsizing Wizard.

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.

Additional choices can be made, including to upsize only the structure and not include the data.

Additional choices can be made, including to upsize only the structure and not include the data.

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 choice of application changes allow you to decide what changes should be made to your existing database.

The choice of application changes allow you to decide what changes should be made to your existing database.

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 chapter). You could then add your own non-unique index and a trigger to satisfy this requirement (also described earlier in this chapter). The process has renamed the original tables and created new linked tables to SQL Server shown below.

The Order Details table has been converted, but the Orders table has not been fully migrated (and the link is missing) because of illegal dates in that table.

The Order Details table has been converted, but the Orders table has not been fully migrated (and the link is missing) because of illegal dates in that table.

INSIDE OUT 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.

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.

Upsizing to Use an Access Data Project

In this section, you take a look at Access Data Projects (ADPs). Referring back to Figure 15-3, you can use the option, Create A New Access Client/Server Application to generate a new ADP. If you choose this option, you not only create a new database, but the Upsizing Wizard performs a lot of additional processing. Constructing an ADP means that you will no longer have any queries left in Access; these queries will need translation into SQL Server Objects (a process with which the wizard can assist you).

The Upsizing Wizard converts queries to SQL Server Objects.

The Upsizing Wizard converts queries to SQL Server Objects.

An ADP is a radical departure for Access in some respects because you do not use the ACE database engine. This means that you can no longer have local tables or queries or utilize the Data Access Objects (DAO) model. Instead of DAO, you use an alternative light-weight technology called ActiveX Data Objects (ADO), which we discuss in topic “Using ADO and ADOX.” The database window becomes a window into SQL Server (it’s a bit like a ”lite” version of the SQL Server Management Studio inside Access). Once you have converted to produce an ADP, go into a query in Design for a View.

This query is a SQL Server View.

This query is a SQL Server View.

The interface looks similar to the Access Query grid and Management Studio; of course, you have the flexibility to use Management Studio to edit this. You will also notice that you have three kinds of queries

The top two queries are Views, the next is a stored procedure, and the third and fourth are Table-valued functions.

The top two queries are Views, the next is a stored procedure, and the third and fourth are Table-valued functions

It is interesting to note that the wizard chose to build a function for Current Product List. This is because the SQL contained an ORDER BY clause, which is only allowed in a View when you use a special TOP syntax (you will see more about this when we look at SSMA). This illustrates an interesting strategic difference between SSMA and the Upsizing Wizard. What is even more interesting is that if you open a function, such as Current Product List, you will find that it is updateable. This is quite radical, because in an Access database (an .accdb or .mdb), you cannot link to a function, and in Management Studio, you cannot open a function and edit the data; in the background there are some very clever things going on inside an ADP.

Query Conversion

Because you can use Access to write SQL in an infinitely-varied number of forms with a subtle interplay of VBA code, it is unlikely that you will ever get a 100% conversion, but it will have made a good attempt at translating your SQL. Sometimes you will find a query like Product Sales For 1997, which at first glance appears to have been converted, in fact has not been effectively converted, because it fails when you try to open the query. In this case, the problem can be fixed very easily because the syntax 'Qtr ' + datepart(q,ShippedDate) will fail, you would need 'Qtr ' + CONVERT(VARCHAR, datepart(q,ShippedDate)) to make this work; sometimes you need a lot more reworking on the SQL. Also note that the translation of SQL in crosstab queries is not supported (the equivalent in T-SQL is the PIVOT statement). An ADP also provides integration for developing SQL Server-specific objects.

With an ADP, you have tighter integration to SQL Server features.

With an ADP, you have tighter integration to SQL Server features.

INSIDE OUT ADP strengths and weaknesses

In general, ADPs are not the most common choice when converting a database, but they have a following, and if Microsoft continues to enhance ADP, maybe it will become the preferred path. An ADP can offer better performance, because it ensures that you create your equivalent queries in SQL Server. This can also be a weakness because in any conversion you are forced to spend time possibly fixing a large number of queries, but with a standard Access database, you can focus on only converting those queries with poor performance. You also need to judge the impact on your productivity as a result of losing the ability to have local tables and local queries in any existing application.

SSMA

SSMA is a more generic tool than the Upsizing Wizard and is designed to migrate data from various databases into SQL Server. SSMA is free and can be downloaded from the Microsoft sites.

Note

The version of SSMA described here is 5.0 for Access, upsizing to either SQL Server or SQL Azure. After you download and install SSMA on a 64-bit version of Windows, you will find two program shortcuts, one for 32-bit Access and the other for 64-bit Access. If you try to run the wrong version, you will see a message warning that you are running the incorrect version of SSMA for the available Access libraries. If there is a problem with any libraries, or you install SSMA on a computer without Access 2010, then links are provided to download the Access 2010 runtime libraries required by SSMA. For a 32-bit Access installation, run the 32-bit version of SSMA.

The Migration Wizard

When you start SSMA, by default, the Migration Wizard is active and ready to guide you through the migration process.The first page of the wizard, in which you name your project and specify either a SQL Server or SQL Azure migration.

Naming your migration project.

Naming your migration project.

On the next page, you can add your multiple Access databases to the project, after which the following list appears from which you the select individual tables and queries in each database to be migrated.

SSMA acts independent of whether you use an Access database or an ADP project, and allows the selection of both tables and queries.

SSMA acts independent of whether you use an Access database or an ADP project, and allows the selection of both tables and queries

Following the previous selections, you are able to specify the SQL Server and either select an existing database or type in a new database name. On the next page in the Migration Wizard, you can select the Link Tables check box (the default is not-selected), which replaces your tables with links to SQL Server. You will most likely want to select this option. The next page is the synchronization page , which provides a detailed roadmap of how the migration will proceed; you can leave all the options on this page unchanged when performing this for the first time.

This wizard page controls of the data transfer for each table.

This wizard page controls of the data transfer for each table.

After the conversion is run, a summary page opens . This page contains links to lists describing any problems that occurred during the migration.

Reports provide detailed information on problems that might have occurred during the migration.

Reports provide detailed information on problems that might have occurred during the migration.

One very nice feature in SSMA is a graphical view, with which you can easily see all the objects in your database and identify and investigate those objects that were not converted.

The metadata on your Access database means that you can drill down to find information on objects for which there have been conversion problems.

The metadata on your Access database means that you can drill down to find information on objects for which there have been conversion problems.

Mapping Data Types

SSMA has a mapping system for converting Access to SQL Server databases, which means that you can fully customize your data type mappings . You display this by clicking the Tables folder in the Access Metadata Explorer.

Click the Edit button to change the Target Type for each data type mapping.

Click the Edit button to change the Target Type for each data type mapping.

Using Schemas

SSMA does not offer an option to simply assign combinations of your tables onto different schemas, but it is still possible to make use of schemas with SSMA. In this example, you want to consider a more sophisticated conversion, for this type of conversion you will not want to use the wizard, but perform the conversion with a series of your own steps. You start by creating a new project, and then adding your Access database.

A database has been added, but tables haven’t been selected yet.

Highlight the database, and then edit the data type mapping to use non-Unicode data types.

The mapping for memo fields after it has been changed from nvarchar(max) to varchar(max) and text fields from nvarchar to varchar.

The mapping for memo fields after it has been changed from nvarchar(max) to varchar(max) and text fields from nvarchar to varchar.

Because this version of SSMA doesn’t seem to easily refresh the schema list, once you have registered your database it is a good idea to create a blank database and define some schemas.

New schemas for partitioning your tables.

New schemas for partitioning your tables.

Returning to SSMA, click Connect To SQL Server, and then select the SQL Server database. Note the new schemas.

In each schema you can see a folder into which you will migrate your tables.

In each schema you can see a folder into which you will migrate your tables.

After you click to highlight the Tables folder in the Access Metadata Explorer, you can select the Schema tab, and then change the Schema from dbo to Companies.

Changing the active target schema.

Changing the active target schema.

Select the tables that you want to migrate into the active schema.

Select the tables to migrate into the active schema.

Select the tables to migrate into the active schema.

Note in this illustration that Queries is also selected; these should also be cleared after the first migration. Next, click the Convert, Load And Migrate icon to migrate these tables. Clear these table selections and repeat the procedure to migrate the other tables to their appropriate schemas until you have migrated the set of tables.

Northwind tables migrated onto separate schemas.

Northwind tables migrated onto separate schemas

After performing this migration, you most likely want to create a set of synonyms for each table.The only restriction on this approach is that because you are effectively performing multiple migrations, SSMA cannot implement all your relationships across the schemas; you can use the database diagram in SQL Server to add back any missing relationships.

Comparing Table Conversion in the Upsizing Wizard and SSMA

SSMA and the Upsizing Wizard use different strategies when performing a conversion. In our sample databases, we have intentionally inserted known problems so that you can compare how each tool handles these issues. SSMA can convert the Employees table, but it doesn’t populate any of the data. This is because our table contains a UNIQUE INDEX with IGNORE NULLS. Unfortunately, this version of SSMA simply adds a UNIQUE INDEX to the table; a better strategy would be to ignore any indexing of this type.

SSMA, like the Upsizing Wizard, adds a default of 0 to any Yes/No field, which avoids the problems described earlier of Bit fields without defaults. Unlike the Upsizing Wizard, which cannot handle Required fields, SSMA correctly maps these to NOT NULL in the database structure. SSMA can also partly handle NULL values in a Required field or illegal dates in the data. The strategy here is to attempt to migrate data that does not have problems. In the sample data, you can see the order number 10257, which contained an illegal date, 01-Jan-100. This then resulted in the order header record not being migrated, but the order detail records are migrated; this can mean that your data is inconsistent with the RI. However, you might feel that it is better to have some data rather than no data. There is no substitute for running the tests, for checking dates and missing values in required fields before migrating your data. SSMA produces output advising you of where data has been partially migrated, as shown here:

SSMA provides a split window to compare the data in Access against the migrated data in SQL Server.

The Split data view shows the top 200 rows in Access and SQL Server. The illegal date in the record with OrderID 10259 is not migrated.

The Split data view shows the top 200 rows in Access and SQL Server

At one time in the distant past, the behavior of Access changed so that when you added a new text field, it always set the AllowZeroLength property to No. This was then promptly changed back to default AllowZeroLength to Yes; this setting tended to make data imports and other activities more difficult in Access. If you have an older database that has this set on all your text fields, then when converting by using the Upsizing Wizard, it ignores this attribute. In the past, SSMA generated a Check constraint to do this (which is probably not what you wanted). The new version of SSMA generates a dummy Check constraint, which does not actually perform any checking, an example of which is shown in the following code:

This strategy is a compromise between not adding the Checks, and providing placeholders for them. If you want to avoid this, you can write some code to scan through the fields in each Access table, changing the AllowZeroLength to Yes before migrating the database.

Comparing Query Conversion in the Upsizing Wizard and SSMA

In SQL Server, you are not allowed to have the SQL ORDER BY in a View (which conforms with the SQL standard), and for that reason, the Upsizing Wizard creates a function to return a result, as shown in the following:

The Upsizing Wizard also makes extensive use of creating extended properties, which are used to record display settings when displaying the function on a Query grid. There is a way to put an ORDER BY inside a View, and SSMA will create a View for this query by using this strategy, which involves including the TOPWITH TIES statement, as shown here:

The very large number, 9223372036854775807, is used to avoid the need to say TOP 100%, for reasons of internal efficiency.If you are using an ADP, then the previously mentioned approach with functions can return updateable data, but if you want to use an Access database, you can only utilize a function by using a pass-through query (which is read-only); you cannot link an Access database directly to a function. Another nice feature of the Upsizing Wizard is that when it converts a query that contains a reference to an Access parameter, it generates a parameterized function, as shown below:

The original SQL used in the Access query was as follows:

SSMA is not able to handle this kind of query with references to screen parameters and does not convert the query. In summary, if you are planning to use an ADP, then the Upsizing Wizard offers a large number of advantages over SSMA when converting queries; but if you are planning to use an Access database (.accdb or .mdb) linked to SQL Server, then you will find that SSMA produces more favorable results when converting your SQL; you can, of course, run a conversion through both tools and copy and paste the preferred results between the systems.

INSIDE OUT SSMA strengths and weaknesses

If you want better control of the Data Type mappings, and you want to ensure that Required fields are correctly translated to NOT NULL column attributes, SSMA offers a better choice than the Upsizing Wizard. The support for migration onto Schemas is also a very attractive feature of the product, but it would benefit from a more structured approach that permits a single migration to split multiple tables over schemas and thus preserve the relationships during the migration process. The only weakness we came across in SSMA was in attempting to translate a UNIQUE INDEX which ignores Nulls. That can be easily avoided by checking for this in your database prior to conversion.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics