Former MS Access MVPS take a closer look at two methods for migrating data from your Access tables to SQL Server. The process is covered from Pre-Migration planning through the migration itself and Post-Migration Clean up and validation.
Method one: Scripting tables in VBA and exporting the tables and data from Access to SQL Server.
Method two: Using SSMA to create tables in SQL Server and migrate data from Access to those tables.
The discussion includes advantages and disadvantages of both approaches and advice about how to approach each step.
A trouble-shooting opportunity presented itself during the presentation, which was resolved to the relief of all concerned.
Downloads at: [ Ссылка ]
Contents:
00:00 - Welcome and Meeting Intro
00:48 - Preparing for Data Migration
01:12 - Homage to Click and Clack
02:31 - Premigration Planning
02:37 - Basic Database Design Evaluation
03:16 - "Code Wadding" and Poor Table Design
03:46 - Identify User Tables That Should not Migrate
04:03 - Supported/Non-Supported Data Types
04:35 - Avoid Reserved Words in Names
04:45 - Intro to the Northwind 2.0 Tables
05:18 - Unique Indexes
05:49 - Unnecessary Indexes
06:07 - "5 + 5" Rule for Indexes
08:37 - Index Foreign Key Fields
09:57 - Unsupported Data Types -- Attachments, Multi-value Fields
10:48 - Data Macros Do Not Migrate
11:15 - Non-Supported Date Values, Date Ranges, Short Text
13:18 - Date and Time Data Types
14:02 - Appropriate Data Types for Intended Use
15:00 - Installing ODBC Drivers for all Users
15:39 - SDLC - The Software Development Life Cycle
16:00 - Pre-Migration Analysis Tools for Access
16:34 - Unique Names For Objects
17:22 - SQL Server Renames Indexes as needed
18:26 - Post Migration Planning
18:29 - Schema and Data Cleanup
19:38 - Filtered Indexes
20:36 - Restore any Lost Indexes or Relationships Post Migration
20:47 - Your Most important Responsibilities: Backups and Security
21:41 - Performance Tuning Post Migration
24:10 - Advantages of Using SSMA
24:16 - SSMA is a Free Download from Microsoft
24:19 - Guided Experience with an Optional Wizard
24:27 - Predefined Defaults for Data Types and Options
24:37 - Configurable for Various Target Servers
25:01 - Advantages of Using VBA
25:02 - Guided Experience
25:05 - Familiar Access Interface
25:08 - Highly Customizable
25:34 - VBA Generates Customizable TSQL Scripts
25:43 - SSMA Visual Reports
26:11 - SSMA generates Customizable TSQL Scripts
26:21 - SSMA Can Synch Schema
26:43 - Disadvantages of VBA
26:53 - No Organizational Support
26:58 - Inexperienced Access Developers Can Get Into Deep Water
28:31 - SSMA - Some Organizations Don't Permit 3rd Party Apps
29:01 - SSMA Has a Unique Interface
29:17 - SSMA - Some Selections Aren't Changeable
...Continued
