When MUST upsizes your database it is a fast, repeatable and easy process.
Unlike any other tool it will automatically detect, advise and then fix problems to
guide you through the process of conversion.
| select SQL Server |
Create a target database, or select an existing database in your SQL Server. |
| Access Databases |
Provide a list of one or more databases to upsize. Indicating whether the database contains
only data, only the application or application and data. |
| analyse Access data |
Analyse your databases and identify and then fix any problems in the data or design
(MUST can automatically do this for you), or you can make changes in your database and then
re-run the analysis. |
| review exclusions |
Reject any unwanted tables such as Past Errors; MUST has user defined pattern searching,
so for example you could identify all tables called temp_xxx or zzz_,
and then review the list and reject the unwanted tables.
The patterns need to be defined prior to Analysis (see Options And Settings Menu, table exclusions icon)
once changed the Analysis needs to be repeated (a button is provided to allow the Analysis to be re-run). |
| migrate structure |
Upsize table structures to SQL Server. Note that a button is provided to allow for this step to
be repeated, but first you would need to create a new SQL Server database using the 'select SQL server' menu. |
| migrate data |
This consist of three steps which invlove first migrating the data, then adding the indexes
and finally adding relationships and validation rules. If the data migration needs to be repeated,
buttons are provided to reverse the process, removing the relationships and validation, then
removing the indexing and emptying the tables. Data is migrated without indexing because this makes the
process faster, and without validation checks. If you prefer you could migrate the data with
relationships, indexes and validation checks in place, but all data must then meet all the validation
rules 9which may not be the case as Access allows validation to be added ignoring existing data. |
| verify row counts |
This screen should not show anything, it shows any problems where a SQL Server table does
not contain the same number of rows as in the Access table, this would indicate that a part of
the migration of data had a problem. |
| relink Access application |
This replaces all the physical tables in an Access database with linked tables to SQL Server,
and changes the link names so they match the existing Access table names. |
| data and structure script files |
If you want to find out exactly how the upsizing works, or see the code,
then you can generate script files which can also be run to upsize the databases. |
MUST will allow you to migrate multiple databases at the same time,
and by default operates in batch mode; processing all available databases.
This can be switched off if required to upsize the databases one at a time
(useful when you have several large Access databases).
At any later point in time, you can upsize more databases into your SQL Server and you can
also load the existing table list (Data Dictionary) from SQL Server before
adding a new database to check for any conflicts in table names (recommended).
Personally when undertaking a large new chunk of work,
I tend to continue developing with Access and then when
I have all the new components in place I upsize this to the existing
SQL Server database and then switch the rest of development to working in the
SQL Server; this means I exploit the great speed of development in Access,
but consolidate my designs to a solid SQL platform.
MUST will also check all your applications indexing using the following strategies.
- Tables missing a primary key are assigned a primary key (autonumber or identity property in SQL Server lingo), to maintain efficient updateability on the table.
- Missing indexes on foreign keys are added (Access normally has these, but they get checked).
- Duplicate indexes are remove (Access normally has these).
- Unique Index Ignore Nulls, this unusual type of index is automatically converted to a trigger and index combination.
- Non-selective indexes (indexes where each value holds more that 20% of the data) are rejected; such indexes are unlikely to be used by SQL Server. The threshold for rejection can be changed, and setting to 0 when upsizing a database with no data (to keep indexing is a possible option).