How To Create SQL Server 2019 Virtual Machine And Use As Database Server With Minimum Cost

Introduction

 

Usually, when we create a virtual machine in Azure, it is created with a fresh image of the targeted operating system and no other software is available.

 

If the requirement is to provision a machine that can be used as a database server then we need to go ahead and install SQL server as a separate instance inside that and in that scenario licensing cost will be additional with windows server license.

Now we have an option to create a virtual machine that can act as a database server and it comes with preinstalled SQL server image at the cost of a windows server license.

 

Benefits

1. We do not need to pay the SQL server license additional cost separately as it comes with a windows server license. Go through pricing to get more details.
2. We can dissociate the PublicIP of the database server VM and use it as an integrated service with another Azure service. Check the below articles for details,
   
   
1. How to enable virtual network integration in-app service
2. How to integrate a database server with AppService through the private virtual network

Steps to provision

1. Go to the marketplace and search for virtual machines.
   
   

   
   
2. Enter the values of the required fields.
   
   

   
   
3. Click on “see all images” and search “SQL Server 2019 on Windows server 2019” image from the marketplace.
   
   

   
   

   
   
4. Click on next and choose the correct subnet under Networking Tab. I have chosen an existing virtual network “database-vnet” and subnet “Database-subnet” that I have created in my previous article How to enable virtual network integration in app service otherwise you can also add a subnet to a virtual network by clicking on “Manage subnet configuration”.
   
   

   
   
5. Go ahead with default values of other Tabs like “Disk, Management, and Advanced” and use the below settings in “SQL Server settings” TAB and go ahead till “Review and Create”. Login Name and Password will be prompted as already set credentials under the “Basic” tab; you can go ahead with default credentials then the same credentials will be used for SQL Server. 
   
   

   
   
6. Deployment is in progress and takes 2-3 min to complete.
   
   
7. Go to resource group once the deployment is completed,
   
   
8. Select “SqlDbVM” and copy the PublicIP of the VM,
   
   

   
   
9. Connect to SQL Server Management Studio of your local machine,
   
   

   
   
10. Connect to Azure SQL server with the help of created virtual machine PublicIP and create SQL server credentials.
    
    

    
    
11. Now, we can connect with our Azure VM SQL server.
    
    

    
    
12. To confirm the connectivity, use the below queries to create a database, table, and insert some records. We can insert and select the records.
    

    create database EmpDB
    GO
    use EmpDB;
    GO
    create table Employee
    (
       EmpId int identity (1,1) not null,
       [Name] nvarchar(50) null
    )
    GO
    insert into Employee([Name]) values('Employee1');
    insert into Employee([Name]) values('Employee2');
    insert into Employee([Name]) values('Employee3');
    GO
    select * from Employee;
    GO

Note

This connectivity is via public internet which is not recommended, hence we can dissociate the PublicIP from the machine once we are done and use the VM via virtual network as private. 

 

Conclusion

We have successfully created a database server in Azure that can be integrated with another Azure Service. It is a useful service which is available to reduce the cost to some extent.