Provision a SQL Server virtual machine in Azure
Azure has two different deployment models for creating and working with resources: Resource Manager and classic. This article covers using the classic deployment model. Microsoft recommends that most new deployments use the Resource Manager model. .
The Azure virtual machine gallery includes several images that contain Microsoft SQL Server. You can select one of the virtual machine images from the gallery and with a few clicks you can provision the virtual machine to your Azure environment.
In this tutorial, you will:
- Connect to the Azure classic portal and provision a virtual machine from the gallery
- Open the virtual machine using Remote Desktop and complete setup
- Complete configuration steps to connect to the virtual machine using SQL Server Management Studio on another computer
- Next steps
This article describes how to provision a SQL Server VM with the existing portal. However, it is also possible to create and manage SQL Server VMs in the new portal. There are some advantages to the new portal, such as defaulting to the use of Premium Storage, and other options, such as Automated Patching, Automated Backup, and AlwaysOn configurations. Future content will cover step-by-step instructions.
On the Azure classic portal, at the bottom left of the web page, click +NEW, click COMPUTE, click VIRTUAL MACHINE, and then click FROM GALLERY.
On the Choose an Image page, click SQL SERVER. Then select a SQL Server image. Click the next arrow at the bottom right of the page.
For the most up-to-date information on the supported SQL Server images on Azure, see SQL Server on Azure Virtual Machines Overview.
If you have a virtual machine created by using the platform image SQL Server Evaluation edition, you cannot upgrade it to a per-minute paid edition image in the gallery. You can choose one of the following two options:
- You can create a new virtual machine by using the per-minute paid SQL Server edition from the gallery and migrate your database files to this new virtual machine by following the steps at Migrating a Database to SQL Server on an Azure VM
- Or, you can upgrade an existing instance of SQL Server Evaluation edition to a different edition of SQL Server under the License Mobility through Software Assurance on Azure agreement by following the steps at Upgrade to a Different Edition of SQL Server. For information on how to purchase the licensed copy of SQL Server, see How to Buy SQL Server.
On the first Virtual Machine Configuration page, provide the following information:
- A VERSION RELEASE DATE. If multiple images are available, select the latest.
- A unique VIRTUAL MACHINE NAME.
- In the NEW USER NAME box, a unique user name for the machine's local administrator account.
- In the NEW PASSWORD box, type a strong password.
- In the CONFIRM PASSWORD box, retype the password.
- Select the appropriate SIZE from the drop down list.
The size of the virtual machine is specified during provisioning:
- For production workloads, we recommend using Premium Storage with the following minimum recommended sizes: DS3 for SQL Server Enterprise edition and DS2 for SQL Server Standard edition. For more information, see Performance Best Practices for SQL Server in Azure Virtual Machines.
- The selected size limits the number of data disks you can configure. For most up-to-date information on available virtual machine sizes and the number of data disks that you can attach to a virtual machine, see Virtual Machine Sizes for Azure.
After entering your VM configuration details, click the next arrow on the bottom right to continue.
On the second Virtual machine configuration page, configure resources for networking, storage, and availability:
- In the Cloud Service box, choose Create a new cloud service.
- In the Cloud Service DNS Name box, provide the first portion of a DNS name of your choice, so that it completes a name in the format TESTNAME.cloudapp.net
- Select a SUBSCRIPTION, if you have multiple subscriptions to choose from. The choice determines which **storage accounts **are available.
- In the REGION/AFFINITY GROUP/VIRTUAL NETWORK box, select a region where this virtual image will be hosted.
- In the Storage Account, automatically generate an account, or select one from the list. Change the SUBSCRIPTION to see more accounts.
- In the AVAILABILITY SET box, select (none).
- Read and accept the legal terms.
Click the next arrow to continue.
Click the check mark in the bottom right corner to continue.
Wait while Azure prepares your virtual machine. Expect the virtual machine status to proceed through:
- Starting (Provisioning)
- Starting (Provisioning)
- Running (Provisioning)
When provisioning completes, click on the name of your virtual machine to go to the DASHBOARD page. At the bottom of the page, click Connect.
Click the Open button.
At the Windows Security dialog box, click Use another account.
Use the name of the machine as the domain name, followed by your administrator name in this format:
machinename\username. Type your password and connect to the machine.
The first time you log on, several processes will complete, including setup of your desktop, Windows updates, and completion of the Windows initial configuration tasks (sysprep). After Windows sysprep completes, SQL Server setup completes configuration tasks. These tasks make cause a delay of a few minutes while they complete.
SELECT @@SERVERNAMEmay not return the correct name until SQL Server setup completes, and SQL Server Management Studio may not be visible on the start page.
Once you are connected to the virtual machine with Windows Remote Desktop, the virtual machine works much like any other computer. Connect to the default instance of SQL Server with SQL Server Management Studio (running on the virtual machine) in the normal way.
The following steps demonstrate how to connect to the SQL Server instance in over the internet using SQL Server Management Studio (SSMS). However, the same steps apply to making your SQL Server virtual machine accessible for your applications, running both on-premises and in Azure classic deployment model. If your virtual machine is deployed in resource manager model see Connect to a SQL Server Virtual Machine on Azure (Resource Manager)
Before you can connect to the instance of SQL Server from another VM or the internet, you must complete the following tasks as described in the sections that follow:
- Create a TCP endpoint for the virtual machine
- Open TCP ports in the Windows firewall
- Configure SQL Server to listen on the TCP protocol
- Configure SQL Server for mixed mode authentication
- Create SQL Server authentication logins
- Determine the DNS name of the virtual machine
- Connect to the Database Engine from another computer
The connection path is summarized by the following diagram:
In order to access SQL Server from the internet, the virtual machine must have an endpoint to listen for incoming TCP communication. This Azure configuration step, directs incoming TCP port traffic to a TCP port that is accessible to the virtual machine.
If you are connecting within the same cloud service or virtual network, you do not have to create a publically accessible endpoint. In that case, you could continue to the next step. For more information, see Connection Scenarios.
On the Azure Management Portal, click on VIRTUAL MACHINES.
Click on your newly created virtual machine. Information about your virtual machine is presented.
Near the top of the page, select the ENDPOINTS page, and then at the bottom of the page, click ADD.
On the Add an Endpoint to a Virtual Machine page, click Add a Stand-alone Endpoint, and then click the Next arrow to continue.
On the Specify the details of the endpoint page, provide the following information.
- In the NAME box, provide a name for the endpoint.
- In the PROTOCOL box, select TCP. You may type 57500 in the PUBLIC PORT box. Similarly, you may type SQL Server's default listening port 1433 in the Private Port box. Note that many organizations select different port numbers to avoid malicious security attacks.
Click the check mark to continue. The endpoint is created.
Connect to the virtual machine via Windows Remote Desktop. Once logged in, at the Start screen, type WF.msc, and then hit ENTER.
In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
In the New Inbount Rule Wizard dialog box, under Rule Type, select Port, and then click Next.
In the Protocol and Ports dialog, use the default TCP. In the Specific local ports box, then type the port number of the instance of the Database Engine (1433 for the default instance or your choice for the private port in the endpoint step).
In the Action dialog box, select Allow the connection, and then click Next.
Security Note: Selecting Allow the connection if it is secure can provide additional security. Select this option if you want to configure additional security options in your environment.
In the Profile dialog box, select Public, Private, and Domain. Then click Next.
Security Note: Selecting Public allows access over the internet. Whenever possible, select a more restrictive profile.
In the Name dialog box, type a name and description for this rule, and then click Finish.
Open additional ports for other components as needed. For more information, see Configuring the Windows Firewall to Allow SQL Server Access.
While connected to the virtual machine, on the Start page, type SQL Server Configuration Manager and hit ENTER.
In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration.
In the console pane, click Protocols for MSSQLSERVER (he default instance name.) In the details pane, right-click TCP, it should be Enabled for the gallery images by default. For your custom images, click Enable (if its status is Disabled.)
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.
Close SQL Server Configuration Manager.
For more information about enabling protocols for the SQL Server Database Engine, see Enable or Disable a Server Network Protocol.
The SQL Server Database Engine cannot use Windows Authentication without domain environment. To connect to the Database Engine from another computer, configure SQL Server for mixed mode authentication. Mixed mode authentication allows both SQL Server Authentication and Windows Authentication.
Configuring mixed mode authentication might not be necessary if you have configured an Azure Virtual Network with a configured domain environment.
While connected to the virtual machine, on the Start page, type SQL Server 2014 Management Studio and click the selected icon.
The first time you open Management Studio it must create the users Management Studio environment. This may take a few moments.
Management Studio presents the Connect to Server dialog box. In the Server name box, type the name of the virtual machine to connect to the Database Engine with the Object Explorer. (Instead of the virtual machine name you can also use (local) or a single period as the Server name. Select Windows Authentication, and leave your_VM_name\your_local_administrator in the User name box. Click Connect.
In SQL Server Management Studio Object Explorer, right-click the name of the instance of SQL Server (the virtual machine name), and then click Properties.
On the Security page, under Server authentication, select SQL Server and Windows Authentication mode, and then click OK.
In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.
In Object Explorer, right-click your server, and then click Restart. (If SQL Server Agent is running, it must also be restarted.)
In the SQL Server Management Studio dialog box, click Yes to agree that you want to restart SQL Server.
To connect to the Database Engine from another computer, you must create at least one SQL Server authentication login.
In SQL Server Management Studio Object Explorer, expand the folder of the server instance in which you want to create the new login.
Right-click the Security folder, point to New, and select Login....
In the Login - New dialog box, on the General page, enter the name of the new user in the Login name box.
Select SQL Server authentication.
In the Password box, enter a password for the new user. Enter that password again into the Confirm Password box.
To enforce password policy options for complexity and enforcement, select Enforce password policy (recommended). This is a default option when SQL Server authentication is selected.
To enforce password policy options for expiration, select Enforce password expiration (recommended). Enforce password policy must be selected to enable this checkbox. This is a default option when SQL Server authentication is selected.
To force the user to create a new password after the first time the login is used, select User must change password at next login (Recommended if this login is for someone else to use. If the login is for your own use, do not select this option.) Enforce password expiration must be selected to enable this checkbox. This is a default option when SQL Server authentication is selected.
From the Default database list, select a default database for the login. master is the default for this option. If you have not yet created a user database, leave this set to master.
In the Default language list, leave default as the value.
If this is the first login you are creating, you may want to designate this login as a SQL Server administrator. If so, on the Server Roles page, check sysadmin.
Security Note: Members of the sysadmin fixed server role have complete control of the Database Engine. You should carefully restrict membership in this role.
For more information about SQL Server logins, see Create a Login.
To connect to the SQL Server Database Engine from another computer, you must know the Domain Name System (DNS) name of the virtual machine. (This is the name the internet uses to identify the virtual machine. You can use the IP address, but the IP address might change when Azure moves resources for redundancy or maintenance. The DNS name will be stable because it can be redirected to a new IP address.)
In the Azure Management Portal (or from the previous step), select VIRTUAL MACHINES.
On the VIRTUAL MACHINE INSTANCES page, under the Quick Glance column, find and copy the DNS name for the virtual machine.
- On a computer connected to the internet, open SQL Server Management Studio.
- In the Connect to Server or Connect to Database Engine dialog box, in theServer name box, enter the DNS name of the virtual machine (determined in the previous task) and a public endpoint port number in the format of DNSName,portnumber such as tutorialtestVM.cloudapp.net,57500. To get the port number, log in to the Azure Management Portal and find the Virtual Machine. On the Dashboard, click ENDPOINTS and use the PUBLIC PORT assigned to MSSQL.
- In the Authentication box, select SQL Server Authentication.
- In the Login box, type the name of a login that you created in an earlier task.
- In the Password box, type the password of the login that you create in an earlier task.
If you can connect to an instance of SQL Server running on an Azure virtual machine by using Management Studio, you should be able to connect by using a connection string similar to the following.
connectionString = "Server=tutorialtestVM.cloudapp.net,57500;Integrated Security=false;User ID=<login_name>;Password=<your_password>"
For more information, see How to Troubleshoot Connecting to the SQL Server Database Engine.
You've seen how to create and configure a SQL Server on an Azure virtual machine using the platform image. In many cases, the next step is to migrate your databases to this new SQL Server VM. For database migration guidance, see Migrating a Database to SQL Server on an Azure VM.
The following list provides additional resources for SQL Server in Azure virtual machines.