Migration Planning - Access to SQL Server

Mike Wolfe -

Successfully migrating an Access database to SQL Server requires far more than just copying data from one platform to another.

In this comprehensive presentation, Kent Gorrell walks through the critical planning stages and key considerations for migrating Access databases to SQL Server. The talk covers everything from initial infrastructure decisions and security setup to schema migration, data validation, and application modifications. Kent's approach emphasizes the importance of creating repeatable processes and maintaining data integrity throughout the migration.

Whether you're considering a SQL Server migration or already in the planning stages, this detailed overview provides essential insights to help ensure a successful transition.

Initial Setup and Infrastructure

Hosting Options

  • Local PC on peer-to-peer network
  • Dedicated server (managed by IT)
  • Cloud options (Azure, AWS)
  • Azure VM with SQL Express

SQL Server Editions

  • SQL Server Express (free, simpler licensing)
  • Standard Edition
  • Enterprise Edition
  • Developer Edition (free, development only)

Security Considerations

  • SQL Server authentication (username/password)
  • Windows authentication
  • Active Directory integration
  • IT department involvement

Environment Setup

Instance Configuration

  • Default instance recommended (port 1433)
  • Collation settings critical
  • Consider language requirements
  • Difficult to change after setup

Development Architecture

  • Separate development, test, and production environments
  • Prevent cross-environment connections
  • Ensure proper relinking when moving between environments
  • Maintain data isolation between environments

Migration Process

Schema Migration

  • SQL Server Migration Assistant (SSMA) for initial schema transfer
  • Review and optimize resulting structure
  • Add missing constraints and indexes
  • Address foreign key relationships
  • Handle hidden Access indexes

Data Migration

  • Create repeatable process
  • Implement hierarchical data loading
  • Validate data integrity
  • Handle problematic records individually
  • Fix source data issues

Application Modifications

  • Update DAO/recordset code for ODBC
  • Handle autonumber differences
  • Adjust connection strings
  • Review form and query behavior

Common Challenges

Data Validation

  • Check for orphaned records
  • Validate date ranges
  • Compare record counts
  • Review business-critical reports
  • Client involvement in testing

Technical Considerations

  • Identity insert limitations
  • Collation impacts
  • Performance optimization
  • Error handling during bulk inserts

Best Practices

Planning Phase

  • Review existing database design
  • Understand business requirements
  • Document current processes
  • Plan for data cleanup

Implementation

  • Create automated migration scripts
  • Test thoroughly in development
  • Validate in test environment
  • Plan rollback procedures

Client Involvement

  • Set realistic expectations
  • Share responsibility for testing
  • Document validation procedures
  • Train users on new procedures

Resources and Documentation

  • Example queries for data validation
  • Connection string templates
  • Migration scripts and tools
  • Testing checklists

Conclusion

The presentation emphasizes that successful SQL Server migration requires careful planning, thorough testing, and a systematic approach. While tools like SSMA can help, understanding the full scope of the migration process and addressing potential issues proactively is crucial for success.

Recording

The full recording is available on YouTube:

Join Live!

Want to get even more out of these presentations? Join the live Access User Group events! The next upcoming events are listed on the AUG Event Calendar.

Attending live gives you the opportunity to:

  • Interact directly with presenters during Q&A sessions
  • Network with other Access developers
  • Share your own experiences and challenges
  • Get immediate answers to your specific questions
  • Participate in group discussions

With multiple user groups across different time zones (and languages!), you're sure to find a meeting time that works for your schedule.

Acknowledgements

  • Base cover image generated by FLUX-schnell
  • Initial draft generated by Claude-3.5-Sonnet