Akeesoft

Akeesoft Logo

SQL Server Database Migration:
Step-by-Step Guide for SQL Server

sql server database migration process

Are you ready to embark on a seamless journey of database migration? Look no further! In this comprehensive guide, we will delve into the intricacies of database migration in SQL Server, providing you with the ultimate roadmap to success.

Whether you’re a seasoned professional or a novice, we’ve got you covered with step-by-step instructions on how to perform database migration. From data migration prerequisites to SQL database migration steps, we leave no stone unturned.

In this article, we will guide you through the steps and processes required to perform database migration from on-premises to on-premises.

So, fasten your seatbelts and get ready to unlock the power of seamless database migration. Let’s dive into the on-premises to on-premises SQL Server database migration.

What is Database Migration?

Database migration is the process of moving data from one place to another. SQL database migration specifically refers to moving a database or data from one SQL Server instance to another SQL Server instance.

Types Database Migration?

For SQL server database migration can be done using two different options

Side-By-Side Database Migration
Side-by-side database migration in SQL Server involves setting up a new SQL Server environment parallel to the existing one. 
Database is migrated to the new environment keeping existing server available for any rollback activity.
This approach helps minimizing downtime and allowing thorough testing before fully transitioning, ensuring a seamless and controlled migration process and easy to rollback as old server is already available.

In-Place Upgrade/Database Migration
In this approach, instead database getting migrated to another server separately , same server version will be upgraded example existing version is SQL 2019 , on the same server 2019 will upgraded to SQL 2022,then database in the SQL Instance will also be upgraded.

Side by Side is mostly preferred due the flexibility of less downtime and quick rollback.


Pre-Requisite for SQL Server Database Migration

Dealing with data is very critical as business run on data, data missing or data not available will pose huge financial risk to the business using the databases.

So ,each step need to be crafted with utmost care ,as part of pre-requisite all the steps need to be performed as per the checklist.

Checklist for SQL Server Database Migration Before Downtime.

  • SQL Server Installed in Destination with desired version.
  • Validate Storage or Disks in the new server , same or more than the present environment
  • Check CPU and Memory are same or more than the present environment
  • Configure Memory allocation to SQL Server with 75% of total memory.
  • SQL Server Collation is same as present server
  • Server properties are same as present server in new server.
  • List of databases to be migrated from present version to new sql version
  • Pre Prod migration testing and application validation completed or not
  • Maintenance completed on present server like Update Stats, reindex, CheckDB
  • Script of logins using sp_help_revlogin script.
  • Confirm Service Account is part of admin group and set to password never expire option.
  • Configure Log Shipping to New Server if database size is more, so that data will be in sync and during the downtime ,less data only will be available to do final sync in less time.
  • Application confirmation to proceed with Migration
  • Downtime Confirmation from Application owner.

We all the checklist validate we are ready to go for the SQL Database Migration.

Performing Side-By-Side SQL Server Database Migration

As a seasoned SQL DBA, one of the common tasks you might encounter is the need to migrate databases from one server to another. 

Side-by-side database migration involves setting up a new environment and migrating data from the old server to the new one while both systems run concurrently. 

This approach minimizes downtime and allows thorough testing before the final cutover. Below are the detailed steps to execute a side-by-side database migration.

Downtime Starting Time:
Backup and Restore:
Take a full backup of the source database and restore it on the target server.

--Source Server Run backup Command
BACKUP DATABASE [YourDatabase] TO DISK = 'C:\Backup\YourDatabase.bak';
--Copy the backup to New or Destination Server
--Run restore command on the new destination Server
RESTORE DATABASE [YourDatabase] FROM DISK = 'C:\Backup\YourDatabase.bak';

Check for Orphaned Users:

Ensure that database users on the target server are properly mapped to logins and no orphaned users in sql server.

USE  <DATABASE_NAME>
GO
EXEC SP_CHANGE_USERS_LOGIN 'REPORT'
GO

Learn how to fix the orphaned users in sql server by using a detailed post on the ways to fix the orphan user.

Performing In-place SQL Server Migration/Upgrade

In-place SQL Server migration involves upgrading or moving your SQL Server instance on the same hardware. This method is useful when you want to minimize the cost and complexity of managing new hardware, but it requires careful planning to avoid downtime and ensure data integrity. Below are the detailed steps for performing an in-place SQL Server migration.
 
1. Pre-Migration Planning and Assessment
Assess Current Environment:
Verify the current SQL Server version, edition, and service pack level.
Ensure hardware and software compatibility with the target SQL Server version.
Inventory of Databases and Objects:
List all databases, their sizes, and dependencies.
Document SQL Agent jobs, linked servers, logins, and other configurations.
Backup and Disaster Recovery Plan:
Take full backups of all databases.
Ensure that you have a rollback plan in case of failure.
2. Prepare for Migration
Check System Requirements:
Verify that your server meets the system requirements for the target SQL Server version.
Download SQL Server Installation Media:
Download the installation media for the target SQL Server version.
Check SQL Server Edition Upgrades:
Confirm that your current SQL Server edition can be upgraded to the desired edition.
3. Backup and Document Current Configuration
Full Backup of Databases:
Take full backups of all user and system databases.
BACKUP DATABASE [YourDatabase] TO DISK = 'C:\Backup\YourDatabase.bak';

Script Out SQL Server Configurations:

Script out all configurations including logins, linked servers, jobs, and other settings.

-- Example to script out logins
EXEC sp_help_revlogin;

Install SQL Server Update

Run SQL Server Setup:

Launch the SQL Server setup application from the installation media.

Choose Edition Upgrade:

If upgrading editions, select the “Edition Upgrade” option and follow the prompts.

Perform SQL Server Upgrade:

Select the “Upgrade from a previous version of SQL Server” option and follow the installation wizard steps.

5. Post-Upgrade Tasks

Update Statistics:

Run UPDATE STATISTICS on all databases to ensure optimal performance.

Validate and Test

Functionality Testing:

Validate that applications can connect and function properly with the upgraded SQL Server instance.

Performance Testing:

Compare performance metrics before and after the upgrade to ensure there are no regressions.

Check SQL Agent Jobs:

Ensure that SQL Server Agent jobs are functioning as expected.

7. Reconfigure Maintenance Plans

Set Up Maintenance Plans:

Set up and test database maintenance plans (backups, index maintenance, etc.) on the upgraded server.

Reconfigure Alerts and Operators:

Ensure that all alerts and operators are correctly configured and operational.

8. Monitoring and Optimization

Monitor Server Performance:

Monitor the server closely for any performance issues or errors.

Optimize as Needed:

Make any necessary optimizations based on monitoring results.

9. Documentation and Reporting

Document the Upgrade Process:

Maintain detailed documentation of the upgrade process, including any issues encountered and their resolutions.

Report to Stakeholders:

Provide a report to stakeholders summarizing the upgrade, any issues, and the current status of the databases.

Why SQL Server Database Migration?

The requirement for database migration arises for various reasons, including:

  • Out of support SQL Server version
  • Application requirements
  • Advanced features of SQL Server
  • Existing server problems
  • Restructuring the Database Infra Architecture

The above-listed reasons are the most common ones which triggers the database migration requirement.

Out of support SQL Server version

An out-of-support SQL Server version poses significant risks, including security vulnerabilities, lack of technical support, and incompatibility with modern applications.

Migrating databases from an Old SQL Server version to a new SQL Server version is crucial for maintaining data integrity, security, and performance.

This migration ensures access to the latest features, patches, and improvements, thereby enhancing operational efficiency and compliance with industry standards. Proactively upgrading to a supported version mitigates potential downtime and data loss, fostering a robust and secure database environment essential for business continuity and growth.

Application requirements

Application requirements often drive database migration to ensure compatibility, performance, and functionality. As applications evolve, they may demand more advanced database features, increased storage capacity, better performance or improved processing power available only in newer SQL Server versions.

Migrating the database accommodates these needs, enabling seamless application performance and leveraging enhanced capabilities such as better indexing, improved security, and more efficient data management.

Meeting application requirements through database migration also supports scalability, ensuring that the database can handle growing data volumes and user demands, ultimately for a more responsive and reliable application environment.

Advanced features of SQL Server

Migrating to a newer SQL Server version enables access to advanced features that enhance database functionality, performance, and security.

Features such as in-memory processing, advanced analytics, improved indexing, and enhanced security protocols provide significant advantages. In-memory processing accelerates query performance, while advanced analytics supports deeper data insights.

Enhanced indexing optimizes data retrieval like columnstore index and improved security features protect sensitive information.

Utilizing these advanced capabilities through database migration not only boosts operational efficiency but also aligns with modern business requirements, ensuring the database infrastructure is robust, scalable, and secure.

Restructuring the Database Infra Architecture

Restructuring the database infrastructure architecture during a migration allows organizations to optimize performance, scalability, and manageability.

This process involves redesigning schemas, normalizing data, and implementing new storage solutions to better meet current and future business needs. By migrating to a modern SQL Server environment, organizations can take advantage of distributed architectures, cloud integrations, and advanced disaster recovery solutions.

This restructuring enhances data flow efficiency, reduces latency, and improves resource utilization. Ultimately, a well-planned restructuring ensures the database architecture is robust, adaptable, and capable of supporting evolving technological demands and business objectives.

Conclusion

Database migration, whether side-by-side or in-place, is a critical task for any SQL Server DBA. It demands meticulous planning, thorough testing, and precise execution to ensure minimal downtime, data integrity, and continued application functionality.