SQL Server database migration to SQL Database in the cloud environment without Downtime

SQL Server database migration to SQL Database in the cloud environment without Downtime


We need to ensure that the source database is compatible with Azure SQL Database using the Data Migration Assistant (DMA). SQL Database V12 is approaching feature parity with SQL Server, other than issues related to server-level and cross-database operations. Databases and applications that rely on partially supported or unsupported functions need some re-engineering to fix these incompatibilities before the SQL Server database can be migrated.


When we cannot afford to remove your SQL Server database from production while the migration is occurring, we can use SQL Server transactional replication as your migration solution. To use this method, the source database must meet the requirements for transactional replication and be compatible for Azure SQL Database.

To use this solution, configure Azure SQL Database as a subscriber to the SQL Server instance that wish to migrate. The transactional replication distributor synchronizes data from the database to be synchronized (the publisher) while new transactions continue occur.

With transactional replication, all changes to data or schema show up in Azure SQL Database. Once the synchronization is complete and we are ready to migrate, change the connection string of your applications to point them to our Azure SQL Database. Once transactional replication drains any changes left on your source database and all your applications point to Azure DB, you can uninstall transactional replication. Your Azure SQL Database is now your production system.

SQL server Database Migration form DB2, MySQL, Oracle, Sybase ASE and Access.

SQL server Database Migration form DB2, MySQL, Oracle, Sybase ASE and Access.

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

Once you decide to migrate to SQL Server – SmartDog SQL service is just what you need. We are help out to 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 migrate user LOGIN information between servers, save output for 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 Read 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 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 are 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 stake holding servers (Network Team)
  • Check the SQL Server Error Log and Windows Error logs for any failures
  • Confirm application functionality with end users