The Microsoft SQL Server Migration Assistant, which is often abbreviated to just SSMA, comes in different versions that support the migration from several of the most common database formats. The one we're most interested in is SSMA for Access, but there's also separate versions that will migrate from MySQL databases, or Oracle databases, or a Sybase, or many others. SSMA is a free tool that greatly simplifies the database migration process when moving from Access to SQL Server in four key ways. First, SSMA for Access automates the conversion of Microsoft Access database objects to their SQL Server counterparts. Then, it gives you fine control over the data type mappings. It then loads the objects into SQL Server, followed by a migration of your data from Microsoft Access to its new home in SQL Server. Finally, the Migration Assistant will even update your Access database with links to the new data tables, so that your downtime is kept to a minimum during the transition. SSMA for Access is currently at version 6.0 but is designed to support migration from Microsoft Access 97 and higher to all editions of SQL Server 2005, 2008, 2012, and 2014, as well as Microsoft's cloud-hosted SQL Azure databases. Migration Sources

• SQL Server Migration Assistant for Access
• SQL Server Migration Assistant for DB2
• SQL Server Migration Assistant for MySQL
• SQL Server Migration Assistant for Oracle
• SQL Server Migration Assistant for Sybase ASE

Once you decide to migrate to SQL Server – SmartDog SQL service is just what you need. We are helping out to the client to build a road plan for migration. Migration should be conducted in three phases:

• Pre-migration inventory / checks
• Database migration
• Post-migration configuration / checks

Pre-migration phase: For the pre-migration phase, review and document the following tasks on the SOURCE server.

• Database sizes
• Data and Log file location
• Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, etc.)
• Collect the information of dependent applications, make sure application services will be stopped during the database migration
• Database logins, users, and their permissions
• Dependent objects (SQL Agent Jobs and Linked Servers)
• Document maintenance plans
• On the TARGET server, conduct the following tasks:
• Verify adequate disk space exists
• Appropriate folders have been created
• SQL Server is correctly installed and configured with latest Service Pack and Cumulative Update
• Set SQL Server properties; memory, tempdb size, autogrowth and drive location.
• Appropriate connectivity exists with application servers, web servers, etc.
• Create databases consistent with planned database migrations

Database migration phase

• From SOURCE server, get recent list of DB and file location list
• On SOURCE SQL Server, Script migrates user LOGIN information between servers, save output for the subsequent step.
• Use native SQL Server to back up databases from SOURCE, use backup compression to reduce size of backup (optionally, use detach database, copy DB files, then reattach database)
• On SOURCE server, place databases in Reading Only mode
• Restore databases to TARGET
• On TARGET SQL, apply script to migrate users/group security (Created in Step 3)
• Update any custom SSRS DSNs to point to the TARGET server.
• On SOURCE server, script any required SQL Server Agent Jobs (select Job, right click, Script Job as > CREATE to > File ) to create
• On TARGET, using the file create, then transfer/execute job creation on TARGET. Create schedules as needed.
• Need to look out the datatype part if database is migrated from different source like Oracle Sybase

Post-migration phase

• Point the application to the new DB server address, or Availability Group LISTENER for AlwaysOn Availability Groups (Connection strings, etc. to altered by the application support team)
• Restart/review Network connections between all stakeholding servers (Network Team)
• Check the SQL Server Error Log and Windows Error logs for any failures
• Confirm application functionality with end users