SQL Server to SQL Server Migration: 5 Efficient Methods

For small to large organizations, managing massive amounts of data is important for maintaining operational efficiency. SQL Server stands out as a popular choice for businesses across various industries, including E-commerce, ERP, or CRM systems. While its primary objective is secure database management, SQL Server offers built-in support for advanced analytics and machine learning. 

As data volumes increase, organizations require a more scalable SQL Server edition capable of handling increased data loads with higher performance. Migrating from one SQL Server to another newer version is a strategic business decision that embraces the latest advancements in functionality, security, and scalability. 

Utilizing an advanced SQL Server version can help organizations gain valuable insights, generate reports, and execute operations to improve business performance. 

Let’s look into the migration from SQL Server to SQL Server in detail. 

Why Migrate SQL Server Data to Another SQL Server?

Organizations migrating to a higher version of SQL Server gain the following capabilities:

• Persistent Memory (PMEM) Support: PMEM is a fast memory capable of storing data even after a power shutdown. It facilitates in-memory data processing without utilizing transmission channels and makes query processing 30% faster for extensive I/O workloads.  
• Intelligent Data Analysis: The higher version of SQL Server integrates with Apache Spark, a highly efficient in-memory engine. Spark is a widely used tool for machine learning and advanced visual analytics at a large scale. Its integration with SQL Server streamlines the intelligent data analysis workflow.
• Enhance Decision-Making: SQL Server integrates with Azure Synapse Link and Azure Purview. This integration enables organizations to derive valuable insights, predictions, and governance from large data volumes and improve the decision-making processes.

SQL Server: A Brief Overview

SQL Server is an RDBMS software product introduced by Microsoft to store and retrieve data efficiently. It is also known as Microsoft SQL Server or MS SQL Server. With SQL Server, you can perform data analysis, reporting, and business intelligence faster as it integrates with Azure cloud services, Power BI, and Excel.  

SQL Server supports high-availability features such as Always On failover clustering, Always On Availability Groups, and mirroring to determine minimal downtime.

You have several options for connecting to SQL Server, including SQL Server Management Studio (SSMS), the command prompt, high programming languages, or ODBC drivers.

Methods to Load Data From SQL Server to SQL Server

To load SQL Server data to another SQL Server, you can utilize Hevo Data, Linked Servers, or Google Cloud Console or follow one of Microsoft’s recommendations, such as Copy Database or the Generate and Publish Scripts Wizard. 

Method 1: Convert SQL Server to SQL Server Table Using Hevo Data

Hevo Data is a no-code, real-time ELT pipeline platform that lets you cost-effectively automate data pipelines that are flexible to your needs. With the integration of over 150+ data sources, you can extract data from sources, load it to destinations, and transform it for in-depth analysis.

Some of the key features of Hevo Data are:

• Data Transformation: Analyst-friendly transformation options, such as Python-based scripts or drag-and-drop blocks, help you clean, prepare, and transform data before loading it to a destination of your choice.
• Incremental Data Load: Hevo Data facilitates real-time data migration, which optimizes bandwidth utilization on both sides of the data pipeline.
• Auto-Schema Mapping: Hevo Data’s auto-mapping feature automatically identifies the structure of incoming data and copies it to the destination schema, eliminating manual schema management. You can select full or incremental mappings according to your data replication needs. 

Let’s explore the SQL Server SQL Server migration using Hevo Data.

Step 1: Set Up MS SQL Server as Your Source 

Before the setup, ensure the following prerequisites are met:

• MS SQL Server version is 12 or above.
• Check MS SQL Server is running. 
• Enable TCP/IP protocol with TCP port 1433.
• Enable change tracking.
• Create a database user and grant SELECT privileges to them.
• You have been assigned the role of a Pipeline Administrator, Team Administrator, or Team Collaborator in Hevo.

Here are the steps to set up SQL Server as the source in Hevo:

1. Once you log in to your Hevo account, choose the PIPELINES option from the Navigation Bar. 
2. Click the + CREATE button from the Pipelines List View.
3. Choose SQL Server as your source type from the Select Source Type page. 
4. Enter the mandatory information in the Configure your SQL Server Source page.

5. Click the TEST CONNECTION > TEST & CONTINUE button to finish the source configuration.

For more information about source configuration, read SQL Server documentation in Hevo.

Step 2: Set Up MS SQL Server as Your Destination 

Before the setup, ensure the following prerequisites are in place:

• MS SQL Server version is 12 or above. 
• Verify the SQL Server is running.
• Allow access to the SQL Server instance.
• Create a SQL Server database instance and obtain the database host’s IP and port number. 
• Grant permissions to the database user.
• You have been assigned a Team Collaborator or any administrator role, excluding the Billing Administrator role in Hevo.

Here are the steps to set up SQL Server as the destination in Hevo:

1. Go to the Navigation Bar and select the DESTINATIONS option. 
2. Click the + CREATE button in the Destinations List View.
3. Select SQL Server as your destination type from the Add Destination page. 
4. Provide the required information in the Configure your SQL Server Destination page.

5. Click the TEST CONNECTION > SAVE & CONTINUE button to complete the destination configuration.

For more information about destination configuration, read SQL Server documentation in Hevo.

Get Started with Hevo for Free

Method 2: Migrate SQL Server Data to SQL Server Using Linked Servers

Linked Servers allow SQL Server to fetch data from multiple databases across different SQL Server instances using any one of the following ways:

1. Run a T-SQL Statement Using the sp_addlinkedserver Stored Procedure

• Launch SQL Server Management Studio.
• Connect to the SQL Server where you want to create the linked server.
• Open a new query window and execute the following stored procedure:

sp_addlinkedserver

    [ @server = ] N'server'

    [ , [ @srvproduct = ] N'srvproduct' ]

    [ , [ @provider = ] N'OLEDBprovider' ]

    [ , [ @datasrc = ] N'datasource' ]

    [ , [ @location = ] N'DBlocation' ]

    [ , [ @provstr = ] N'providerstring' ]

    [ , [ @catalog = ] N'catalog' ]

    [ , [ @linkedstyle = ] linkedstyle ]

[ ; ]

To learn more about the above T-SQL statement, refer to the sp_addlinkedserver stored procedure. 

• After executing the stored procedure, verify that the linked server has been successfully created by executing the following T-SQL statement:

sys.servers

2. Using the SQL Server Management Studio (SSMS) User Interface

• Open the SSMS and expand the Server Objects under the Object Explorer.
• Right-click on the Linked Server folder and click the New Linked Server option.
• Specify the mandatory information in the New Linked Server window.

• Authenticate the Linked Server instance by choosing the Security option under the Select a page section on the New Linked Server window. For more information about the linked server authentication, read how to authenticate your linked server.  
• Click OK to create a new linked server, and you will be able see it under the Linked Server folder.
• Right-click on the new linked server and click the Test Connection option to verify the linked server works properly.

The linked server will fetch the data from the source SQL Server. For practical assistance, refer to the tutorial video on SQL Linked Server. To query data using the linked server, read how to query data. 

Limitations of SQL Server to SQL Server Migration Using Linked Server

• Lack of Real-time Capabilities: Using linked servers can lead to latency and performance degradation when handling complex queries or transferring large data volumes between SQL Servers. Such issues can cause challenges for applications requiring real-time data synchronization.   
• Inability to Cache Data: If you run the same query repeatedly through a linked server, SQL Server does not cache the previous results. Instead, SQL Server establishes different connections to the linked server after each query execution and retrieves data from scratch each time. 

Method 3: Load Data from the Local SQL Server to the Cloud SQL Server Using Google Cloud Console

Before starting the migration, ensure the following are met:

• Create a Google Cloud account.
• Choose or create a Google Cloud project.
• An active billing account linked to your Google Cloud project.
• Enable the required Google Cloud APIs.
• Install the gcloud CLI and enable the Admin API option.
• Install the Cloud SQL Auth Proxy.

Here are the steps to start the data migration using the Google Cloud console:

Step 1: Create a Cloud SQL Instance

1. Log into the Google Cloud console and navigate to the Cloud SQL Instances page.
2. Choose the Create Instance option and click the Choose SQL Server.
3. Specify an instance name “sqlserver” in the Instance ID field.
4. Set a password for the SQL Server user and click the Create button.

Step 2: Initialize and Authenticate gcloud CLI to Interact with Cloud SQL

1. Open the bash shell command prompt or Windows Powershell and execute the following command:

gcloud init

2. Authenticate the gcloud CLI using the following command:

gcloud auth login

3. Run the Cloud SQL Auth Proxy by executing the following command:

 ./cloud-sql-proxy InstanceConnectionName

Step 3: Connect to Your Cloud SQL Instance Using SQL Server Management Studio

1. Open the SSMS and choose Connect Object Explorer from the File tab.
2. Enter the following:
   1. Server Type: Database engine
   2. Server name: IP address of your cloud SQL Server instance 
   3. Authentication: SQL Server Authentication 
   4. Username: sqlserver 
   5. Password: The one that is used when the cloud SQL instance is created.
3. Click the Connect button.

Step 4: Create a Database and Upload Data Using SQL Server Management Studio

1. Open the SSMS and go to the Object Explorer window.
2. Right-click the Databases node under your SQL Server instance.
3. Choose the New Database option, specify the database name, and click OK.
4. Right-click the Tables node under the name of the newly created database. 
5. Click on New > Table and provide the necessary information in the Create table dialog box.

6. Choose Save <table_name> from the File tab.

5. Right-click the newly created table under the Databases node and click the New Query option.
6. Use the SQL INSERT statement to add records to the newly created table and click the Execute button to update the new table. 

Your data is now available in your Google Cloud Project, and you can perform the SQL queries using Google Cloud Console and the SSMS.  

Limitations of SQL Server to SQL Server Migration Using Google Cloud Console

• Network Bandwidth: The speed and volume of data transfer can be limited by the available network bandwidth. A slow network connection between the local SQL Server and the cloud SQL Server can affect the speed at which the data is transferred.

Method 4: Migrate SQL Server Data to SQL Server Using Microsoft’s Copy Database Wizard

The Copy Database Wizard can be used to migrate databases between SQL Servers without server downtime. To perform this migration, you must configure the source SQL Server, destination SQL Server, SSIS package, and transfer method. Then, schedule the SSIS package based on the selected transfer method to load data from the source to the destination SQL Server. 

Here are the prerequisites you need before you begin:

• Set up SQL Server Management Studio (SSMS).
• Check the SQL Server Agent is started on the destination SQL Server. 
• Verify the data and log file directories on the source SQL Server are accessible in the destination server.
• For a detach and attach method, you must create an SQL Server Agent Proxy on the destination server for the SSIS package.
• You must be assigned a sysadmin fixed server role on the source and destination servers.  

Here are the following steps to start the migration process:

1. Open the SQL Server Management Studio (SSMS).
2. Go to the Object Explorer window and expand the Databases node to list the available databases.
3. Right-click on the database you want to copy and choose Tasks > Copy Database.
4. When Welcome to the Copy Database Wizard splash page appears, click the Next option.
5. Select the name of the source SQL Server and click Next after specifying its login credentials. 
6. Choose the destination server in the same way.
7. Select any transfer method from the following options:
   1. Detach and attach method: This will make the database inaccessible to users during the transfer process.
   2. SQL Management Object Method: Users can still access the database throughout the transfer process. 
8. After selecting the transfer method, select the database to be copied from the source SQL Server to the destination server and click Next.
9. Configure the destination server for each database to be copied. To learn more about configurations, read how to configure the destination database. 
10. Configure the SSIS package created by this copy database wizard using the following steps in package configuration.
11. If you want to specify when to start the database transfer, perform any of the following ways:
    1. Run immediately: This will allow the SSIS package to run after completing the copy database wizard.
    2. Schedule: This lets the SSIS package execute based on the schedule. To modify the schedule, click the Change Schedule button and configure the New Job Schedule window as desired. When you are finished configuring it, click the OK button. 
    3. Integration Services Proxy account: Applicable only for detach and attach transfer method. 
12.  When you complete the wizard, click the Finish button to create the SSIS package or choose the Back button to modify any options.

After refreshing your destination SQL Server, you will be able to see the newly migrated database from the source SQL Server. For more information about copy database wizard, read how to use it. 

Limitations of SQL Server to SQL Server Migration Using Microsoft’s Copy Database Wizard

• Limited Database Options: The Copy Database Wizard option will not allow you to copy the system databases and databases with log files stored in Microsoft Azure storage to the destination SQL Server.
• Migration Failure: If SQL Server Agent jobs refer to databases that are not present on the destination server, transferring them will result in a migration failure. 
• Transfer Method Challenges: When using the SQL Server Management Object transfer method to replicate the full-text catalog, it is important to re-populate the index following the replication. Any active sessions associated with the database being copied cannot attached when using the detach and attach transfer method. 

Method 5: Migrate SQL Server to SQL Server Using Microsoft’s Generate and Publish Scripts Wizard

The Generate and Publish Scripts Wizard helps you migrate databases from one SQL Server instance to another. It creates SQL scripts for a database on the source SQL Server using SQL Server Management Studio (SSMS). Then, import the generated scripts into an Azure Data Studio notebook and execute them on the destination SQL Server. 

Verify the following prerequisites are met before you begin:

• Check an instance of source and target database running on the SQL Server 2005 or higher. 
• Install Azure Data Studio and SQL Server Management Studio.

Here are the steps to perform the migration:

1. Connect with your SQL Server source instance in the SQL Server Management Studio.
2. Expand the Database node and choose the database that you want to copy.
3. Right-click on the selected database and select Tasks > Generate Scripts.

4. On the Generate and Publish Scripts window, go to the Choose Objects page after reading the steps for generating scripts in the Introduction page and specify the required details. 
5. Configure the Set Scripting Options page with the mandatory options and specify how you want this wizard to create scripts in the Advanced Scripting Options.
6. On the Summary page, you will be able to see the selected options for this wizard.

As you can see on the summary page, the generated SQL scripts are saved in an Azure Data Studio notebook.

7. Open the Azure Data Studio containing the generated SQL scripts. 
8. Connect to the destination SQL Server using Azure Data Studio. 
9. Execute the scripts in the notebook to create the database objects and replicate the data from the source server to the destination. 

For more information about generating scripts, read how to generate and publish the wizards. 

Limitations of SQL Server to SQL Server Migration Using Microsoft’s Script Wizard

• Proficiency in Multiple Platforms: To perform migration from one SQL Server to another SQL Server, you must utilize SQL Server Management Studio and Azure Data Studio. When working on multiple platforms, you must be proficient with the interfaces and functionalities of each tool, which may require additional training or expertise. 
• Effort Intensive: Before executing the SQL scripts on the destination server, you must create database objects and define the schema. 

Use Cases of SQL Server to SQL Server Migration

• Backup and Restore: To minimize downtime and ensure data integrity, you can preserve your entire database structure by moving it from one SQL Server to another.
• Security: Migrating to a new SQL Server instance with updated security features and configuration can help organizations secure large data volumes from unauthorized access. 
• Cloud Benefits: Migrating SQL Server databases from on-premises servers to cloud platforms like Azure SQL Database or Amazon RDS SQL Server is beneficial. It can provide benefits such as elasticity and reduced maintenance effort.    

Conclusion

Your organization migrates data from one SQL Server to another to leverage the features of modern SQL Server instances. This allows you to optimize your database infrastructure to meet business needs. In this article, you have explored three methods for transferring data to another SQL Server. 

Loading data into another SQL Server using the Linked Servers, Google Cloud Console, Copy Database, and Generate and Publish Scripts Wizard can be time-consuming and require manual intervention. Additionally, it does not offer real-time data synchronization and requires waiting for the next export/import cycle for updated data. With Hevo Data, you can transfer your data in real-time and adjust the destination schema according to the source database using its auto-mapping feature. 

You can also utilize Hevo Data to load SQL Server to Databricks, SQL Server to BigQuery, SQL Server to Snowflake, and connect SQL Server to many other data warehouses. 

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. Also checkout our unbeatable pricing to choose the best plan for your organization.

Share your experience of SQL Server to SQL Server integration in the comments section below!

Frequently Asked Questions (FAQs)  

1. How can I transfer the SQL Server 2014 from my old PC to my new one?

A. Install the latest version of SQL Server on your new PC and copy the mds and ldf files from your databases to your PC. Alternatively, you can move the databases with the BACKUP/RESTORE. 

2. How will I load a SQL file into SQL Server 2008?

A. If your SQL file is larger than 50 MB, then you can use sqlcmd utility within the SQL Server package. Alternatively, you can utilize SQL Server Management Studio by simply executing the SQL file.