Walk-through to Upsize Databases
When MUST upsizes your database it uses a fast, repeatable and logical series of steps.
Unlike any other tool it will automatically detect, advise and then fix problems to
guide you through the process of conversion.
Below is a list of the sequence of steps that are followed when upsizing a database.
||Create a new SQL Server database, or use an existing SQL Server database.
(Optional Actions Before Analysis:)
- Create a list of schemas which will be used to split the Access database tables into logical blocks
- Set an option to add auditing fields to each table
- Enter patterns to match temporary or other unwanted table names
||Provide a list of one or more databases to upsize. Indicating whether each database contains
only data, only the application or both an application and data.
If using schemas assign a default schema
for each Access database.
Analyse your databases identifying and fixing any problems in the data or design.
The analysis can be repeated;
this is only required if you change the patterns for identifying unwanted tables, or change
other options such as adding auditing fields to the analysis.
Each database to be analysed has its own set of
options, for example auditing fields could be added to the tables in only one database. Selecting a database from the drop-down list of databases
allows the options to be changed for each database being analysed.
Review and reject any unwanted tables such as Paste 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 of identified tables and reject the unwanted tables.
The patterns need to be defined prior to Analysis (see Options And Settings Menu, table exclusions icon)
if patterns are changed after Analysis then the Analysis needs to be repeated
(a button is provided to allow the Analysis to be re-run).
(Optional Actions After Analysis:)
The tool has a Data Dictionary which lists all the tables, fields, indexes and relationships in the
analysed databases. You can edit the data dictionary for fine control over the migration.
- When using schemas, select which schema each table will be mapped into
- Exclude individual tables, indexes or relationships
- Rename tables in the SQL Server
Upsize table structures to SQL Server.
If using schemas then each schema will be created, the tables
are then mapped into the appropriate schemas, and synonyms created for each table in a schema.
An option 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).
||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.
||Verification shows record counts for each Access table and each SQL Server table (which should match).
If due to corruption in Access not all the data in a table was migrated, then this is easily identified here.
||All the physical tables in one or more Access application database are replaced with linked tables to SQL Server,
and changes the link names so they match the existing Access table names (ensuring that your application
continues to operate correctly).
If you want to view the TSQL used for upsizing
then TSQL script files can be generated for the conversion.
This option is useful when TSQL migration scripts are passed to DBA's for execution in
During analysis MUST will also tune your application's 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 updateability on the tables
- Missing indexes on foreign keys are added
- Duplicate indexes are remove
- 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 keeps all indexing
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 very large Access databases).
After conversion you can upsize other Access databases into your SQL Server database,
before adding a new database we recommend
loading the existing table list (Data Dictionary; list of tables, fields and indexing) from SQL Server
to check for any conflicts in table names. This strategy is used
when you either have a very large application consisting of many Access databases which will be
migrated over several months, or where you intend to
continue developing new Access databases to be later intergrated into the main SQL Server database.