Microsoft SQL Server database migration
Database migration is defined as moving a database to and from the database server. You can migrate your Microsoft® SQL Server database between servers or instances in various ways. You might want to move a single database, multiple databases, logins, or stored procedures and views. You can use any of the following methods or combinations of methods to migrate Microsoft® SQL Server databases.
Why migrate?
- Moving to an entirely new server.
- Moving to a different instance of SQL.
- Restoring databases from a backup.
- Creating a development server or going live to a production server.
- Versioning - A specific database or its version has features that offer more benefits than their existing database or its version.
Migration overview diagram
Use cases
- On-premises to IBM Cloud VPC.
- Other cloud service providers to IBM Cloud VPC.
- IBM Cloud classic bare metal or virtual server instance to IBM Cloud VPC.
Prerequisites
Before you begin your Microsoft® SQL Server database migration, review and complete the following prerequisites:
- Conduct an inventory of the databases that needs to be migrated.
- Assess the databases for potential migration issues or blockers, and then resolve any items that you uncovered.
- Ensure that enough disk space is available for the target server of the new database.
- Ensure that the user has appropriate rights or permissions to perform migration activity.
- SQL Server Management Studio needs to be installed on each server for few methods.
- Define a plan for the migration.
- Back up all the data.
Migration considerations
- Migrating a server during nonpeak times.
- Choosing the right migration strategy.
- Reducing complexity by migrating the database in a phased approach instead of a single step.
For more information, see Migration considerations.
Migration methods
| Migration tools and solutions | Use cases |
|---|---|
| RackWare Management Module (RMM) | Database on single server or clustered database on multiple servers. For a Microsoft SQL server clustered database migration, user needs to take care of licenses, shared disk configuration, updating the correct IP address in DNS records of DNS / active directory server, setting up DNS server for all node servers. |
| Backup and restore with Windows® IBM Cloud Backup for Classic Agent | Large data migration, full database backup, moving database data to another drive, moving database data between different versions of SQL server. |
| Detach and attach | Full database backup, moving database data to another drive, moving database data between different versions of SQL server. |
| Import and export data from SQL Server | Large data migration, full database backup |
| Copy Database Wizard | Small data migration |
| Generate a script | Small data migration or schema-only migration |
| Backup and restore with SQL Server Management Studio | Large data migration, full database backup, moving database data to another drive, moving database data between different versions of SQL server. |
| Transactional replication | Copying and distributing data and database objects from one database to another, synchronizing between databases to maintain consistency. |
| Database mirroring | Copying or mirroring of an SQL Server database on a standby server. |
| Always On availability groups | Large data migration |
| Always On distributed availability groups | Large data migration |
| Data Migration Assistant (DMA) | Large data migration, migration assessment, server upgrade |
| Smart Bulk Copy | Large data migration, other cloud service providers to IBM Cloud VPC, on-premises to IBM Cloud VPC. |
RackWare Management Module (RMM)
RackWare Management Module (RMM) is a simple workload migration solution that is provided by IBM’s partner RackWare. RMM provides an automated, and convenient process to migrate existing compute workloads to IBM Cloud. It tracks data changes on the source server until cutover, and performs delta syncs to the target server in IBM Cloud. This tool migrates a server with everything on it including the operating system, along with its installed database application and data (lift and shift migration). RMM can do database migration if it is a platform-based database (for example, it can access database workloads with a public IP address). For more information, see On-premises VMware VM to IBM Cloud VPC migration with RMM.
RMM can migrate single MSSQL database or clustered databases. The following things must be considered after migration:
- Licenses for database applications on target servers.
- Update DNS records with correct IP addresses on/for target servers.
- Configured shared disk on the target side.
- Ensure all the configuration of the cluster is correct and all services are up and running correctly.
- Known issue: When migration is performed for servers with clustered node "the trust relationship between this workstation and the primary domain failed". So even if you enter the correct domain credentials post migration, it does not allow to user to log in.
- Workaround: A simple solution to this problem is to log in to the node by using local admin account and explicitly unjoin node machine from domain and rejoin domain. After the server rejoined the domain, log in to the target machine by using domain credentials. This process is automated in the form of a script. To download and use this script, see this public GitHub repository.
The RMM tool does not support migration of Amazon Relational Database Service (AWS RDS) or similar databases.
This tool can be found in the IBM Cloud catalog as RackWare - CloudMotion.
Backup and restore with Windows® IBM Cloud® Backup Agent
To protect Microsoft® SQL Server databases, you can install the SQL Server plug-in with the Windows® IBM Cloud Backup for Classic Agent on the server where the SQL Server is running. Then, you can add and run backup jobs that specify which SQL Server databases to back up and where to save the backup data. After you back up the SQL Server databases by using the SQL Server plug-in, you can restore databases directly to an SQL Server instance, or restore databases to flat files. For more information, see Configuring MSSQL database backups and Restoring MSSQL Database.
Detach and attach
The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or move the database on a different computer. You can use this feature of the SQL Server Management Studio tool to copy, move, or upgrade an SQL Server database. For more information, see Detach a database and Attach a database.
Import and export data from SQL Server
You can use various methods to import data to, and export data from, the SQL Server. You can also import and export data in various data formats. These formats include flat files, excel, major relational databases, and various cloud services. For more information, see Import and export data from SQL Server.
Copy Database Wizard
The Copy Database Wizard moves or copies databases and certain server objects easily from one instance of the SQL Server to another instance, with no server downtime. For more information, see Use the Copy Database Wizard.
Generate a script
The generate script wizard generates a script of all the objects in a database or a subset of the objects that are selected, and you can decide whether you want to include permissions, collation, constraints, and so on. The generated scripts can be run on another instance of the database engine or the SQL Server database. This method is used to copy schema as well as data. It's not ideal if you are copying data for large databases. For more information, see Generate Scripts (SQL Server Management Studio).
Backup and restore with SQL Server Management Studio
A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. You can create a full database backup in the SQL Server by using SQL Server Management Studio. Table-level backups cannot be created. For more information, see Backup and restore with SQL Server Management Studio.
Transactional replication
Transactional replication is a method for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. It enables changes to be transferred between one database and another, which includes data, tables, stored procedures, views, and so on. Transactional replication is typically used in database clustering or server-to-server environments and is used for high availability of database and high throughput. However, this method can be used for database migration. For more information, see Transactional replication.
Database mirroring
Database mirroring in SQL Server allows you to keep a copy, or mirror, of an SQL Server database on a standby server. Mirroring ensures two separate copies of the data always exist, providing high availability and complete data redundancy. This method is usually used for increased availability of a database, as well as during production database upgrades. This method can also be used for database migration. For more information, see Database mirroring.
Always On availability groups
SQL Server Always On availability groups provide high availability and disaster recovery solutions and are available in versions of SQL Server 2012 and newer. This feature can be used to migrate your existing SQL Server databases to IBM Cloud with minimal downtime. If you have an existing Windows® Server Failover Cluster with Always On availability groups, you are able to extend the cluster temporarily during migration by creating an extra secondary replica with asynchronous replication. During a maintenance window, a manual failover can be performed to enable the cut-over. Always On availability groups provide high availability, disaster recovery, and read-scale balancing. These availability groups require a cluster manager. In Windows®, the failover clustering feature provides the cluster manager. For more information, see What is an Always On availability group?.
Always On distributed availability groups
An SQL Server Always On distributed availability group spans two distinct availability groups. Each availability group is configured on two different Windows® Server Failover Clusters (WSFC), one at the source location and one in IBM Cloud VPC. The operating systems and SQL Server versions do not have to be the same version if they are able to support WSFC and availability groups. This migration method is suited to rehost mission-critical SQL Server databases. For more information, see Always On distributed availability groups.
Data Migration Assistant (DMA)
Data Migration Assistant is a freely available database assessment and migration tool that is provided by Microsoft®. It finds database compatibility issues and feature parity in terms of database assessment. It is used mainly to check the compatibility issues that might affect the database functions when you migrate databases to a new SQL Server. The Data Migration Assistant provides you with the ability to configure the number of databases to migrate in parallel. For more information, see Overview of Data Migration Assistant.
Smart Bulk Copy
The Smart Bulk Copy tool is used to move data from one SQL Server database to another. It uses physical partitions and parallel copy tasks for faster transfer of data. It can also be used to move data from two instances of SQL Server running in two different cloud providers or to move from on-premises to the cloud. This method is suitable for large-size data migration. For more information, see Smart Bulk Copy.
Set up target on IBM Cloud VPC
You need to set up a target Microsoft® SQL Server instance per your requirement. For more information, see About Microsoft® SQL on VPC.
Post migration
Review the following post-migration tasks:
- Compare source database size with target database size.
- Sample test for table data.
- Sample test for indexes or column constraints.
- Sample test for triggers, stored procedures, views.
- Make sure that the application configuration is updated to consume new database and test the application.
- Apply licenses to SQL Server databases.