Configure SQL Server in Azure Virtual Machine

We know that Microsoft Azure has a great capability for Virtual Machine. It’s also great because it provides you the pre-defined image of the system you need to run your business. When you are business or application is a data driven, then the most essential thing you need in Microsoft Azure is VM with SQL Server pre-installed. But there are some things that still needs to be configured in order to run SQL Server in your own Virtual Machine. So, here is a guide for you.

Create a Virtual Machine

Step 1: Login to your Azure Portal at http://manage.windowsazure.com


            Figure 1: Login

Step 2: Go to Virtual Machines and then click new at the left bottom portion of the Notification Bar.

Step 3: Since you are creating a predefined image VM so click select from gallery.


                                                         Figure 2: Crate a Predefined Image

Step 4: Navigate to SQL Server Category then click on the desired edition.



                                                                     Figure 3: Navigate

Step 5: Fill in the required fields you are asked. Select release date of the SQL Edition you want to use, machine name, pricing tier, size of the VM and new username and password.


                                                                  Figure 4: Fill in the Field

Step 6: Enter the cloud service name, create storage account and affinity group of VM (I will select my existing sets).


                                                         Figure 5: Enter the Cloud Service

Step 7: Click enter or select a value and select MSSQL, a public port and private port (Endpoints) will be created for SQL Server.


                                                                        Figure 6: Click Enter

Step 8: Click next two times in a row. Then it will start creating an Azure VM with SQL Server pre-installed.

Connecting VM with Remote Desktop Connection

1. Click on the connect button on the bottom bar of the selected VM. This will download a .rdp connection file for the VM.
   
   
                                                                  Figure 7: Connect Button
   
   
2. Open the file which will show the following prompt. Click on Connect.
   
   
                                                               Figure 8: Click on Connect
   
   
3. Select "Use another Account" then enter the login credentials and click on yes on another window.
   
   
                                                            Figure 9: Use Another Account

Configuring Firewall Rules

1. Press Windows Key + S and search for firewall.
   
   
                                          Figure 10: Press Windows key
   
   
2. Select "Inbound Rules" then click on create new.
   
   
   
                                                                           Figure 11: Select Rules
   
   
3. Click on Port and then click next.
   
   
                                                                           Figure 12: Click Next
   
   
4. Select TCP Port, enter the port no 1433 and click next.
   
   
                                                                              Figure 13: Select TCP
   
   
5. Select Action "allow the connection" and click next.
   
   
                                                                  Figure 14: Allow the Connection
   
   
6. Leave default on Profile and click next and on name section give name to rule and click finish.
   
   
                                                                           Figure 15: Click Finish

Connecting the SQL Server in own SQL Server Management Studio

For now we have done all the parts of configuration. Assuming the readers to be a SQL Guy. Rest of the part of creating logins, giving them database rights and access etc. will leave on you guys. Still if you want guidance on that leave a comment below I will try to help you on that. Now let’s go how to connect the SQL Server of Azure Virtual Machine to our SQL Server Management Studio.

1. Search for SQL Server Configuration Manager.
   
   
                                                        Figure 16: Search
   
   
2. Go to Protocols for MSSQLServer and check if TCP Port is enabled or not. If not right click on it and click on enable.
   
   
                                                                  Figure 17: Go to Protocol
   
   
3. Go to SQL Server Services and Restart SQL Server.
   
   
                                                                     Figure 18: Go to SQL Server
   
   
4. Login with "Windows Authentication Mode" then right click on SQL Server and click on properties.
   
   
   
                                    Figure 19: Login with Windows
   
   
5. Click on Security then SQL Authentication and Windows Mixed Mode and click on OK.
   
   
                                                                  Figure 20: Click on Security
   
   
6. Right click on SQL Server, click on Restart then click on Yes to restart the SQL Server.
   
   
   
                              Figure 21: Right Click on SQL Server
   
   
7. Create new logins and provide rights and access to the login as sysadmin and etc.
   
   
8. Open SQL Server Management Studio on your local machine. Supply the DNS Name of the VM on the Server Name field.
   
   
                                             Figure 22: Open SQL Server Management Studio
   
   
9. Enter login credentials you just made before in step 7.
   
   
10. You can see I have a test database on my Azure VM SQL Server so do I get access on it at my local SSMS.
    
    
                                                          Figure 23: Test Database