How to Migrate SQL Server to Azure Using dbForge Studio
Cloud migration is a key modern trend. It improves performance, reduces costs, and enhances security. As more businesses transition from on-premises to cloud-based solutions, leading providers deliver more advanced functionality and better cost efficiency. Here, Azure SQL is known as one of the most popular managed cloud-based database solutions.
The SQL Server engine hosted in Azure enables users to easily migrate applications built with SQL Server to the cloud and manage them using familiar tools and resources. Additionally, many database management tool vendors offer solutions that help users quickly migrate and work with their databases in the cloud.
This article explains how to migrate SQL Server databases to Azure using dbForge Studio for SQL Server.
Why migrate SQL databases to Azure?
Migrating databases to Azure provides a scalable, secure, and fully managed cloud infrastructure that significantly reduces the complexity and cost of maintaining on-premises systems. As a Microsoft-backed service, Azure ensures stability, reliability, and seamless integration with other Microsoft products.
Key benefits of migrating your databases to Azure include:
- Modern application development: Azure enables you to build, deploy, and scale modern applications by providing a flexible, cloud-native environment that supports the latest development frameworks and tools.
- Scalability: Azure automatically adjusts resources to handle growing data volumes and workloads, ensuring your applications perform smoothly during peak demand.
- Cost efficiency: By migrating to Azure, you eliminate the need for costly on-premises hardware and infrastructure maintenance, paying only for the resources you use.
- Enhanced security: Azure delivers advanced security features, including real-time monitoring by expert teams, threat detection, and compliance with industry standards.
- Easier maintenance: As a fully managed service, Azure handles patching, updates, and infrastructure management, freeing your IT team from routine maintenance tasks.
- Disaster recovery: Azure offers built-in redundancy, geo-replication, and automated backups, helping you quickly recover from any failures.
- Global access: Azure's data centers offer low-latency access to your data and applications from anywhere, enabling flawless support for international business operations and remote teams.
For SQL Server users, migrating to Azure SQL Database provides a familiar environment with the benefits of the cloud. Azure SQL is also an attractive choice for users of other database management systems looking to modernize their infrastructure.
Understanding Azure SQL features
Let's explore the technical features of Azure SQL that make it a popular choice among modern software developers and database administrators. Specifically, we'll focus on the features that enhance scalability and flexibility, enabling organizations to migrate their on-premise applications to the cloud.
Azure SQL Database (single database & elastic pools)
The Azure SQL platform offers two deployment options: a single database or an elastic pool, each with distinct features.
A single database functions like a traditional SQL Server database but is hosted in the cloud. It is isolated from other databases and managed through a logical server. This model is ideal for cloud applications that use a single data source. You can scale the resources for each database up or down independently as needed.
An elastic pool allows multiple databases to share a pool of resources. This model is well-suited for managing unpredictable or variable workloads, as resources are dynamically distributed based on demand. However, the pool has a fixed storage limit. If this limit is exceeded, all databases in the pool become read-only.
Elastic pools support the addition or removal of resources at any time without downtime, making them a cost-effective solution for managing multiple databases. These deployment options make Azure SQL a strong fit for modern, cloud-native applications.
Azure SQL Managed Instance
Azure SQL Managed Instance is a cloud-based SQL Server engine that offers familiar tools and functionality for managing your databases. You can customize the engine, configure recovery models, perform maintenance tasks, and more. Managed Instance is an ideal solution for migrating workloads to the cloud with minimal changes.
SQL Server on Azure VMs
SQL Server on Azure Virtual Machines (VMs) lets you run full versions of SQL Server in the cloud while retaining complete control over the operating system and database settings.
This option combines the flexibility of a traditional on-premises SQL Server environment with the scalability and availability benefits of Azure, including a pay-as-you-go model without the need to manage physical hardware. It supports all existing SQL Server features and enables easy migration of applications without requiring modifications. This solution is ideal for organizations that want full control over their SQL Server instances while taking advantage of cloud infrastructure.
Now, let us examine how to migrate SQL Server databases to Azure SQL in the cloud with dbForge Studio for SQL Server.
Pre-migration checklist with dbForge Studio for SQL Server
One of the most influential and user-friendly tools for migrating databases to the cloud is dbForge Studio. This versatile IDE handles all database-related tasks, from writing code to version control, both on-premises and in the cloud. Since this article focuses on SQL Server, we will demonstrate the use of dbForge Studio for SQL Server, a robust alternative to the default SQL Server Management Studio (SSMS) that enables database migration to the cloud with just a few clicks.
dbForge Studio for SQL Server offers no-code and low-code migration options, allowing database managers to configure and execute migrations visually without manual coding. Additionally, these tasks can be automated using command-line tools and scheduled for regular execution when needed.
Before we proceed to the database migration task, let us check some prerequisites that we need to ensure in advance:
- Choose the migration strategy: Determine whether you require a simple lift-and-shift approach or a more complex modernization.
- Assess compatibility: Verify that your current database features and configurations are supported on Azure.
- Review dependent services: Check connected applications and integrations for compatibility and update as needed.
- Plan schema redesign (optional): Optimize the schema for cloud features, such as scaling and partitioning, before migration.
- Create backups and recovery plans: Ensure reliable backups are made and tested, with clear recovery procedures in place.
- Configure firewall rules: Set up a firewall and network security to allow authorized access while protecting the database.
This tutorial assumes you have an active Azure subscription and a cloud database. If not, start by creating a free account and a single database to deploy your on-premises database. Microsoft provides detailed instructions, making the setup quick and easy.
How to migrate a SQL database to Azure using dbForge
The simplest way to migrate a database to Azure is by using the Schema Compare and Data Compare tools included in dbForge Studio for SQL Server. These tools work by first copying the database schema to the cloud and then migrating the table data. Thus, you obtain a full copy of your on-premises database in the cloud, which you can then manage directly with the Studio.
Step 1. Connect to your local SQL Server
Before migrating an on-premises database to the cloud, you need to create a server connection in dbForge Studio for SQL Server.
Open the Studio, go to the Database menu, and select New Connection.
In the Database Connection Properties dialog, fill in the connection details: Server name, Authentication method, Login, and Password (saved by default; to turn this off, clear the Allow saving password checkbox).
Click Test Connection to verify the credentials, or click Connect to proceed. The Studio will connect to your server.
The next step is connecting to your Azure SQL Database.
Step 2. Connect to Azure SQL Database
To connect to your Azure SQL Database, you need:
- The fully qualified server name (shown in the Overview section next to the Server name).
- The login and password specified during the database creation.
By default, the Azure SQL Database blocks external connections. You must configure a firewall rule to allow access from your IP address.
In your Azure portal, go to SQL databases and select your database (for instance, mySampleDatabase).
Click Set server firewall to open the firewall settings page. You will see the list of current rules and allowed networks.
Click Add your client IP to create a new firewall rule. Save the changes.
This rule ensures that Port 1433 (used by Azure SQL Database) is open to the specified IP address. You can also manually add other IP ranges by clicking Add a firewall rule.
Now that access is allowed, return to dbForge Studio for SQL Server: create a new connection, enter the Azure SQL connection details, and click Connect.
Once connected, your cloud databases will appear in the Database Explorer pane, ready for use.
Step 3. Compare and deploy a schema
The first stage of the database migration process is schema migration. This step ensures that the database in the cloud has the same structure and all necessary objects as the source.
In dbForge Studio for SQL Server, go to the Comparison menu and select New Schema Comparison.
Follow the wizard to configure the schema comparison. On the Source and Target page, select the server connections and databases to compare. Set the on-premises database as the Source and set the empty Azure SQL database as the Target.
On the Options page, configure your comparison preferences.
On the Schema Mapping page, check and adjust schema mappings.
On the Table Mapping page, verify table mappings.
Click Compare. dbForge Studio will display the results visually, highlighting all differences between the source and target schemas.
Once the discrepancies are reviewed, you can proceed to synchronize the schemas. Click the green arrow at the top of the results window to launch the Schema Synchronization Wizard.
On the Output page, choose your output options. The tool generates a deployment script that you can view, execute directly against the target database, or save to a file for future use.
On the Options page, you can use the default settings or customize them to suit your needs. If you configure settings for your task, save them as My Defaults for future use.
Optionally, configure pre- and post-synchronization scripts by entering them directly or selecting local .sql files.
View the Summary and click Synchronize to apply the schema changes.
To automate schema synchronization, click Save Command Line in the bottom-left corner of the wizard. This operation creates a .bat file with the task settings. You can use this file in Windows Task Scheduler to run the job regularly.
After the synchronization completes, the Studio generates a deployment script. You can run it immediately to apply the schema to your Azure SQL Database.
At this stage, you have a cloud-based database with the complete schema, but no data has been added yet. The next step is to migrate the data, which can be done using the Data Compare tool.
Step 4. Migrate data
You can perform data comparison in much the same way as schema comparison. The Data Compare tool identifies differences between the source and target databases, allowing you to synchronize data with Azure and ensure consistency and accuracy across environments.
In dbForge Studio for SQL Server, go to the Comparison menu and select New Data Comparison.
Specify the Source (your on-premises database) and the Target (your Azure SQL Database).
Adjust the Options and Mapping just as you did during schema comparison. Then click Compare to begin analyzing the data differences.
Once the comparison is complete, you'll see which records are missing or different in the target database. To begin synchronization, click the green arrow at the top of the results window.
The synchronization wizard works similarly to schema synchronization, so you can choose how to handle the generated synchronization script: view it, execute it directly, or save it for review or editing.
Configure additional options and specify if any pre- or post-synchronization scripts should be executed. Then review the Summary and click Synchronize to apply the changes.
If data synchronization needs to run regularly, you can automate it: click Save Command Line in the wizard and get your settings as a .bat file, which can be scheduled with Windows Task Scheduler.
Alternative method: Data export and import
Data migration can also be performed via export and import. You can export data into any of 14 popular formats and import data from 10 formats into another database with precise control, down to individual columns and rows. Additionally, you can perform cross-platform migrations via ODBC drivers (e.g., PostgreSQL to SQL Server).
It is also possible to automate both export and import tasks by saving them as command-line scripts. This method provides flexibility for advanced scenarios and broader platform compatibility.
Step 5. Post-migration validation
After completing the data synchronization task, refresh the comparison results window. This will confirm that both the schema structure and table data have been successfully transferred to your Azure SQL Database.
You can now start working with your cloud database: optimize it for the new environment, run queries, analyze data, and manage all other database tasks directly in dbForge Studio for SQL Server.
dbForge Studio works with cloud databases just as efficiently as with on-premises ones. It offers a full-featured toolkit for:
- Writing, analyzing, and optimizing queries
- Importing and exporting data
- Designing individual objects or entire databases
- Applying version control to databases
- Managing users and permissions
These are only some of many features available in the Studio. It allows you to handle all aspects of SQL Server development and administration in one unified environment, whether on-premises or in the cloud.
