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

1. The ability to modify resource allocation to accommodate changes in workload.
2. High availability: Built-in features like automated failover and replication offer high availability and disaster recovery.
3. Encryption, secure networking, and role-based access control are just a few examples of the high-tech security features available.
4. Management: Simple management and administration via the SQL Server Management Studio or Azure portal.
5. Integration: The incorporation of Azure DevOps, Azure Backup, and Azure Monitor, among other Azure services.
6. Possibility of combining on-premises and cloud-based SQL Server workloads in a hybrid scenario.
7. Pay-as-you-go pricing and the capacity to reduce costs through resource optimization are examples of cost optimization.

Here’s a step-by-step guide to Migrating SQL Server in Azure:

1. Create a backup, check that the source database is compatible, and correct any issues to prepare it.
2. Select a migration strategy from the backup and restore SQL Server with Azure Arc support or Azure Database Migration Service.
3. As your target in Azure, create a SQL Server on Azure virtual machine three, an Azure SQL database, or an Azure SQL managed instance.
4. Configure the firewall to permit connections from the source database to the destination in Azure.
5. Data migration entails transferring the data using the chosen method to the desired location in Azure.
6. To make sure that the data is moved properly, connect to the target as a test.
7. Switch to the destination, remove the source database, and clean up the resources to complete the migration process.

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:

1. Make sure you have a current backup by backing up the database. You can utilize T-SQL scripts, SQL Server Management Studio, or backup prog like Red Gate SQL Backup.
2. Compatibility testing Use the SQL Server version and feature compatibility tool to check that the source database and the target in Azure are compatible.
3. Fix compatibility problems: If there are compatibility problems, you may fix them by altering the code, upgrading the database schema, or using compatibility capabilities in Azure SQL Database.
4. De-clutter the database: Eliminate unused dependencies, objects, and data from the source database.
5. Disable any database functionality Azure does not support: SQL Server Agent, SQL Server Integration Services, and SQL Server Reporting Services are a few examples of unsupported capabilities.

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:

1. Azure Database Migration Service (ADMS): This fully managed service for migrating on-premises SQL Server databases to Azure. ADMS automates the entire migration process and provides a centralized view of migration progress and performance.
2. Azure Arc enabled SQL Server: This method allows you to run SQL Server on-premises and Azure as a single entity. With Azure Arc, you can manage and monitor your on-premises SQL Server instances with the same tools you use to manage Azure resources.
3. Backup and Restore: This is a manual method of migrating data by creating a backup of the source database and restoring it to the target in Azure. This approach is appropriate if your database is large and you require a quick network connection between the source and the target.

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.

1. g. Click Review + Create and then Create to deploy the SQL Server on Azure virtual machine.

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:

1. Open the Azure portal.
2. Go to the Azure SQL Database or Azure SQL Managed Instance where you want to configure the firewall.
3. In the left-side navigation, select Firewalls and virtual networks.
4. Click Add client IP to automatically add the current IP address to the firewall. You can also add the specific IP addresses or IP address ranges that need access.
5. Click Save to apply the changes.

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.

1. d. Start the migration and monitor the progress in the Azure portal.

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.

1. c. Use the SQL Server Management Studio or other tools to transfer the data to the Azure SQL Managed Instance.

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.

1. c. Use the Azure portal, Azure CLI, or Azure Powershell to restore the backup to the target in Azure.

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).

1. d. Provide the required information, such as server name, database name, and login credentials.

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.

1. c. Check for any errors or missing data and resolve any issues as necessary.

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.

1. c. Change the production environment to use the target in Azure.

Remove the original database:

a. Once you have confirmed that the target is working correctly, you can remove the source database.

1. b. Make sure to backup the source database and store it in a safe location in case you need to access the data in the future.

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.

1. b. Monitor your Azure bill to ensure that you are only being charged for the resources that you are using.

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.

Connect to a SQL Server virtual machine on Azure

Overview

Here are the steps to connect to a SQL Server virtual machine on Azure:

1. Open the Azure portal: Go to https://portal.azure.com and sign in with your Azure account.

Navigate to the virtual machine:

a. In the left-side navigation, select Virtual Machines.

1. b. Find the virtual machine that you want to connect to and select it.

Configure the firewall:

a. In the left-side navigation, select Networking.

1. b. In the Inbound port rules section, add a new rule to allow inbound traffic to the virtual machine on port 1433, the default SQL Server port.

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.

1. d. Click Connect after choosing the database you want to connect to.

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.

Examples of connections

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:

1. With the Public Endpoint setting, the SQL Server instance is made publicly available and is thus reachable from anywhere. It is not advised for use in production environments due to its lower level of security compared to other options.
2. Through the use of a virtual network or ExpressRoute connection, you can connect to the SQL Server instance using the private endpoint option. Production environments are advised to use this option because it is safer than using a public endpoint.
3. Creating firewall rules that regulate incoming traffic to the SQL Server instance is possible using this option. More control over the security of the connection is given by the ability to specify which IP addresses or ranges are permitted to connect to the instance.

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:

Option

Description

Public

Connect to SQL Server over the internet.

Private

Connect to SQL Server from within the same virtual network.

Local

Connect to SQL Server locally on the same virtual machine.

The following sections explain the public and private options in more detail.

Connect to SQL Server over the internet

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.

Connect to SQL Server within a virtual network

enabled, and that your client machine is joined to the same domain as the SQL Server VM:

php

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:

php

Server=<SQL Server VM computer name>;Database=<database name>;User Id=<user name>;Change the SQL connectivity settings.ettings, follow these steps:

1. Open SQL Server Configuration Manager:

• On the start menu, click Run, type SQLServerManager14.msc, and then click OK.

1. Locate the SQL Server Network Configuration and expand it.
2. Right-click the Protocols for <InstanceName> and select Properties.
3. Select the appropriate protocol, for example TCP/IP, and then click the Enable button.
4. Click the OK button to close the Properties window.
5. Restart the SQL Server service for the changes to take effect.
6. To verify that the changes were successful, you can use SQL Server Management Studio to connect to the SQL Server instance using the appropriate protocol and verify the connection.
8. 
9. Wait several minutes for the update to complete.
10. 

Enable TCP/IP for Developer and Express editions

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.

1. After the Azure virtual machine is created and running, click the Virtual Machines icon in the Azure portal to view your VMs.
2. Click the ellipsis, …, for your new VM.
3. Click Connect.
4. 
5. Open the RDP file for the VM that your browser downloaded.
6. You receive a warning from the Remote Desktop Connection that the publisher of this remote connection cannot be determined; click Connect to proceed.
7. Click in the Windows Security dialog. Change your account. For this to be visible, you might need to click More options. Namely. enter the user name and password you set up when you created the VM. Before the user name, a backslash is required.
8. 
9. To connect, select OK.

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:

1. Type “SQL Server Configuration Manager” into the search bar after clicking the Start button on the taskbar.
2. In the search results, select the SQL Server Configuration Manager program by clicking on it.
3. You can then modify the SQL connectivity settings, as I mentioned in my earlier response, once the SQL Server Configuration Manager window has opened.
4. 
5. Expand SQL Server Network Configuration in the console pane of SQL Server Configuration Manager.
6. Click Protocols for MSSQLSERVER (the instance name by default) in the console pane. If TCP is not already enabled, right-click it in the details pane and select Enable.
7. 
8. In the console pane, click SQL Server Services. In the details pane, right-click SQL Server (instance name) (the default instance is SQL Server (MSSQLSERVER), and then click Restart, to stop and restart the instance of SQL Server.
9. 
10. Close SQL Server Configuration Manager.

For more information on enabling protocols for the SQL Server Database Engine, see Enable or Disable a Server Network Protocol.

Connect with SSMS

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.

Configure a DNS Label for the public IP address

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.

 Note

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.

1. In the virtual machine overview, select your public IP address.
2. 
3. Expand Configuration under the Public IP address’s properties.
4. Enter a DNS Label name, which serves as an A Record and enables connections to your SQL Server VM via name rather than IP address.
5. Select “Save” from the menu.
6. 

the Database Engine via a different computer

1. Open SQL Server Management Studio (SSMS) on a device with an internet connection. If you don’t already have it, you can download SQL Server Management Studio from this page.
2. Edit the server name value in the Connect to Server or Connect to Database Engine dialog box. The virtual machine’s IP address or full DNS name should be entered (this was determined in the prior task). Additionally, you can include a comma and the TCP port for SQL Server. For example, mysqlvmlabel.eastus.cloudapp.azure.com,1433.
3. Choose SQL Server Authentication from the Authentication box.
4. Enter the name of a legitimate SQL login in the Login box.
5. In the Password field, enter the login password.
6. Go to Connect.
7. 

Manual configuration and troubleshooting

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:

1. Check to see if the SQL Server service is active.

• If the SQL Server service is not running, start it by opening the SQL Server Configuration Manager and checking its status.

1. Check to make sure the appropriate network protocol is being used by the SQL Server instance:

• Check the status of the protocols by launching the SQL Server Configuration Manager. going to the SQL Server Network Configuration, and then It is necessary to enable the desired protocol (such as TCP/IP).

1. Check to see if the right port is being used by the SQL Server instance to listen:

• Select the Properties of the desired protocol (such as TCP/IP) by launching the SQL Server Configuration Manager, moving to the SQL Server Network Configuration, and then clicking. Verify that the right port is being specified. SQL Server’s default port is 1433.

1. Check to see if the SQL Server instance can be accessed through the firewall:

• Verify the Windows Firewall settings to make sure the desired protocol’s proper ports are open.

1. Check to see if the client computer can resolve the SQL Server instance’s name:

• Use the client computer’s hostname or IP address to ping the SQL Server instance.

1. Make sure the SQL Server authentication mode is set up properly.

• Check the authentication mode in the Security section of the Server Properties by connecting to the SQL Server instance using SQL Server Management Studio.

1. Check to make sure the client computer is using the right connection string:

• Verify the database name, hostname or IP address, and authentication credentials in the connection string being used by the client application.

These techniques can be used to manually configure and fix SQL Server connectivity problems.