January 31, 2023 | Bitscape Team, Cloud Migration
A Guide for Migrating SQL Server in Azure, Smooth Sailing The Microsoft SQL Server database management system is referred to as “SQL Server in Azure” and is hosted on the Microsoft Azure cloud computing platform. Without having to handle the infrastructure and upkeep of the database server, it offers businesses and organizations a scalable, secure, and high-performance database solution for their applications.
The process of planning the migration, evaluating the current environment, selecting a migration approach, and establishing the target environment Carrying out the migration, validating the migration, and decluttering is coverage in the guide.
Several of Migrating SQL Server in Azure’s features include
Here’s a step-by-step guide to Migrating SQL Server in Azure:
Prepare the source database: back up the database, check for compatibility, and resolve any issues.
Here are more detailed steps to prepare the source database:
Choose a migration method: Azure Database Migration Service, Azure Arc-enabled SQL Server, or backup and restore.
Here’s a brief comparison of the three migration methods:
The choice of method depends on the particular requirements of the migration, such as database size, network connectivity, and downtime requirements. Each method has benefits and drawbacks. The ideal approach for your particular migration scenario may require consultation with a Microsoft-certified expert.
Create a target in Migrating SQL Server in Azure by setting up a SQL Server instance on a virtual machine in Azure, an Azure SQL database, or both.
Azure SQL Managed Instance:
a. Open the Azure portal.
b. Select “Create a resource.”
c. Search for an Azure SQL Managed Instance.
d. Select Azure SQL Managed Instance and click Create.
e. Provide the required information, such as instance name, subscription, resource group, and virtual network.
f. Select the desired configuration options, such as pricing tier and subnet.
. Click Review + Create and then Create to deploy the Azure SQL Managed Instance.
SQL Server on an Azure virtual machine:
a. Open the Azure portal.
b. Select “Create a resource.”
c. Search for SQL Server on an Azure virtual machine.
d. Select SQL Server on the Azure virtual machine and click Create.
e. Provide the required information, such as the virtual machine name, subscription, resource group, and virtual network.
f. Select the desired configuration options, such as operating system, size, and storage.
Note: You may also use the Azure CLI or Azure Powershell to create the target in Azure. Before creating the target, make sure you have the necessary permissions, such as the ability to create resources in Azure and configure virtual networks.
Configure the firewall: open the firewall to allow connectivity from the source database to the target in Azure.
Here are the steps to configure the firewall:
Note: If you are using SQL Server on an Azure virtual machine, You need to configure the network security group (NSG) associated with the virtual machine to allow connectivity from the source database.
It is recommended to use the Azure Virtual Network Service Endpoints or Azure Private Link to further secure the connection to the target in Azure. These features allow you to restrict connectivity to only your virtual network, providing enhanced security and reducing exposure to the public internet.
Migrate data: use the chosen method to transfer the data to the target in Azure.
Here’s a brief overview of how to perform the data migration:
Azure Database Migration Service (ADMS):
a. Create an instance of the Azure Database Migration Service in the Azure portal.
b. Connect to the source database and target in Azure.
c. Choose the databases you want to migrate and configure the migration options.
Azure Arc-enabled SQL Server:
a. Connect your on-premises SQL Server to Azure Arc.
b. Create an Azure SQL Managed Instance in the same virtual network as your on-premises SQL Server.
Backup and Restore:
a. Create a backup of the source database using SQL Server Management Studio or other tools.
b. Copy the backup file to Azure Storage.
Make sure you have the required permissions, such as the capacity to backup and restore databases, and that your source database is in a consistent state before beginning the migration. It is also recommended to perform a test migration and validate the data in the target in Azure to ensure accuracy and completeness.
Connect to the target as a test to ensure that the data was migrated correctly.
Here are the steps to test the target in Azure:
Connect to the target:
a. Open the Azure portal.
b. Go to the Azure SQL Database, Azure SQL Managed Instance, or SQL Server on an Azure virtual machine where you migrated the data.
c. In the left-side navigation, select Query Editor (Preview).
Verify the data:
a. Use the Query Editor to run SELECT statements to verify the data in the target.
b. Compare the data at the source and target to ensure that everything was correctly migrated.
Note: It is recommended to perform a comprehensive set of tests, including functional, performance, and security testing. to validate the data in the target and ensure that the target meets your requirements and business needs. You may also consider using automated testing tools and scripts to verify the data in the target and reduce manual effort.
Finalize migration: switch over to the target, remove the source database, and cleanup resources.
Here are the steps to finalize the migration:
Switch over to the target:
a. Update your applications and services to use the new target in Azure.
b. Test the target in a staging environment to make sure everything is working as expected.
Remove the original database:
a. Once you have confirmed that the target is working correctly, you can remove the source database.
Clean up resources:
a. Delete any resources that are no longer needed, such as Azure Database Migration Service instances, Azure Storage accounts, and virtual machines.
Note: It is recommended to plan and perform a dry run of the switchover process before finalizing the migration to minimize downtime and ensure a smooth transition to the target in Azure. You should also monitor the target in Azure after the switchover to make sure that it is functioning as expected and that no issues have arisen during the migration.
Here are the steps to connect to a SQL Server virtual machine on Azure:
Navigate to the virtual machine:
a. In the left-side navigation, select Virtual Machines.
Configure the firewall:
a. In the left-side navigation, select Networking.
Connect to the virtual machine:
a. Open SQL Server Management Studio.
b. Enter the login information and server name in the Connect to Server dialog box. The server name is in the format of “VM name>.region>.cloudapp.azure.com.”
c. Click on SQL Server Authentication, and then type the virtual machine’s username and password.
Note: Before connecting, make sure the virtual machine is active in Azure and that you have the right permissions to access it. Additionally, using Azure virtual network security tools like network security groups and Azure Active Directory authentication to secure the virtual machine is advised.
You can select the kind of connectivity you want to use when you provision a SQL Server virtual machine in Azure. The choices consist of:
You can select the kind of connectivity that best meets your needs based on your needs. It is important to choose the right type of connectivity to ensure the security and performance of your SQL Server virtual machine in Azure.
Your options for connectivity include:
Connect to SQL Server over the internet.
Connect to SQL Server from within the same virtual network.
Connect to SQL Server locally on the same virtual machine.
The following sections explain the public and private options in more detail.
When you choose the public endpoint option during the provisioning process of a SQL Server virtual machine in Azure, the portal takes care of enabling the TCP/IP protocol. configuring the firewall rule to open the SQL Server TCP port, enabling SQL Server authentication, and configuring the network security group on the VM. This makes it easy for clients with internet access to connect to the SQL Server instance using the public IP address or a DNS label assigned to that IP address.
However, it is important to note that while this enables connectivity for clients over the internet, it does not imply that anyone can connect to your SQL Server instance. Outside clients still have to use the correct username and password to connect.
For additional security, you can configure SQL Server to listen on a custom port number, other than the well-known port 1433. This helps to reduce the risk of unauthorized access to your SQL Server instance. When you query SQL Server on the virtual machine over the internet, all outgoing data from the Azure datacenter is subject to normal pricing on outbound data transfers.
Server=sqlvmlabel.eastus.cloudapp.azure.com;Integrated Security=false;User ID=<login_name>;Password=<your_password>
This is a sample connection string used to connect to a SQL Server virtual machine on Azure. In this connection string, the server name is specified as “sqlvmlabel.eastus.cloudapp.azure.com”, which is the DNS label assigned to the public IP address of the virtual machine. The “Integrated Security” option is set to “false” to indicate that SQL Server authentication is being used. and the “User ID” and “Password” options are set to the specific login name and password that the client should use to connect to the SQL Server instance.
The connection string can be used in various programming languages, such as C# or Python, to create an application that connects to the SQL Server virtual machine on Azure.
enabled, and that your client machine is joined to the same domain as the SQL Server VM:
Server=<SQL Server VM computer name>;Database=<database name>;Trusted_Connection=True;
If SQL authentication is being used, the connection string would include the user name and password:
Server=<SQL Server VM computer name>;Database=<database name>;User Id=<user name>;Change the SQL connectivity settings.ettings, follow these steps:
When changing SQL Server connectivity settings, Azure does not automatically enable the TCP/IP protocol for SQL Server Developer and Express editions. The steps below explain how to manually enable TCP/IP so that you can connect remotely by IP address.
First, connect to the SQL Server virtual machine with remote desktop.
The TCP/IP protocol should now be enabled in SQL Server Configuration Manager.
The steps below should be followed while using remote desktop to connect to the virtual machine and searching for SQL Server Configuration Manager:
For more information on enabling protocols for the SQL Server Database Engine, see Enable or Disable a Server Network Protocol.
The procedures that follow explain how to connect with SQL Server Management Studio (SSMS) and then create an optional DNS label for your Azure VM.
Consider making a DNS label for your public IP address in order to access the SQL Server Database Engine from the Internet. Although the DNS label creates an A record that is simpler to identify and abstracts the underlying public IP address, you can connect using the IP address.
If you only intend to connect to the SQL Server instance locally or within the same virtual network, DNS labels are not necessary.
Selecting virtual machines in the portal is the firsChoose your SQL Server virtual machine to display its properties. r VM to bring up its properties.
SQL Server connectivity issues can be manually configured and troubleshot by inspecting various components and making adjustments as necessary. Following are some steps to take in order to fix typical SQL Server connectivity issues:
These techniques can be used to manually configure and fix SQL Server connectivity problems.