Azure VM with MS SQL Server Migration from local db to on-premises

1. You will require an Azure Virtual Machine with SQL Server installed. To create an Azure Account, review this link. To create a virtual machine, review this link.
2. A local machine with the SQL Server Engine Installed and a database to export with access to internet.
   The Cloud Adapter
   1. A Cloud Adapter. The cloud adapter is an Azure Service required for SQL Server and generates a SSL certificate In the Azure portal. In order to create it, go to Virtual Machines and click on the virtual machine name.
      
      Figure r1. The Azure virtual machines.
   2. Click on EndPoints.
      
      Figure r2. The Endpoints
   3. Press the New icon.
      
      Figure r3. Add new endpoints
   4. Add the Endpoint name, protocol and port (by default it is the port 11435).
      
      Figure r4. The cloud Adapter for SQL
   A certificate
   Another requirement is a certificate to connect to the Cloud. In order to do it do the following steps:
   1. Open the certmgr.msc. You can create your certificate or use an existing one. In this example we will use an existing one.
      
      R5. The certificate console
   2. Select the certificate right click and select All Tasks>Export
      
      
      R6. Export a certificate
   3. The wizard to export certificates will be displayed. Click next.
      
      R7. Export Wizard.
   4. Select if you want to have a private key.
      
      R8. Private key question
   5. Specify the format of the certificate.
      
      R9. Select the certify format
   6. Specify a name and a path.
      
      R10. Selecting name and path of the .cer file.
   7. Once the Wizard is completed, press finish.
      
      R11. Final step of the export wizard.
   8. Go to the Azure portal and click on the Virtual Machine.
      
      R12. The virtual machine.
   9. Click on the Management Certificates link.
      R13. The certificates
   10. Click the Upload button and upload the certificate created on step 7.
       
       R14. The option to upload certificates.

   Getting Started

   1. To connect to a Virtual Machine, you can use the SQL Server Account. In order to enable the SQL Server authentication, in the Azure Virtual Machine (VM), open the SSMS and connect to the database. Right click on your Server and select properties. Change the Server Authentication to SQL Server and Windows Authentication to connect from your local machine.
      Figure 1. Server properties
   2. You will be prompted a message about restarting the SQL Server Service.
      
      Figure 2. Information message.
   3. Restart the Service in order for the new configuration to take effect.
      
      Figure 3. Restarting the MSSQL Service
   4. Now that the SQL Server authentication is enabled, create a SQL Server Login.
      
      Figure 4. Creating a new login.
   5. Specify the login name and passwords. It is recommended to enforce the password policy for security reasons. In this example, we will omit the Enforce password policy to simplify the example.
      
      Figure 5. Login settings
   6. Go to the Virtual Machine in the Azure Portal and click on the Virtual Machine name.
      
      Figure 6. The virtual machine status
   7. Click the ENDPOINTS link and press the Add icon to add a new Endpoint.
      Figure 7. Endpoints
   8. Select the add Standalone Endpoint
      
      Figure 8. Adding endpoint
   9. Write the name of the Endpoint. The protocol should be TCP and the port by default in the default instance is 1433.
      
      Figure 9. Endpoint settings
   10. The ACL allows you to specify the range of subnets allowed to access to your Azure virtual Machine. You can provide the name and subnet. If you do not your Internet IP, you can use this link. For more information about the ACL list, you can check this link.
       
       Figure 10. The ACL details
   11. You also need to enable the ports used to access remotely. Go to the Windows Firewall and right click on the Inbound Rules and select new rule.
       
       Figure 11. The Inbound rules.
   12. Specify the port to which you want to apply a rule. In this case, it is the port 1433.
       
       Figure 12. The TCP IP SQL Server port
   13. In the action, select the allow the connection option.
       
       Figure 13. Allow permissions
   14. Select the rule that you want to apply.
       
       Figure 14. Rules to apply
   15. Write a name for the rule.
       
       Figure 15. Rule name
   16. Now connect to your Azure VM from the SSMS of the local machine.
       
       Figure 16. Connect to an Azure VM from the local SSMS
       If everything is OK you can now connect to the SQL Server machine with Azure from your local machine.

   How to deploy your database using the Deploy Database to Windows Azure VM

   Now we will export our local database to the Azure Vm.
   1. In the local SSMS, Select the database that you want to copy and select Tasks>Deploy Database to Windows Azure.
      
      Figure 17. The Deploy Database to Azure VM Wizard.
   2. The Introduction Wizard Windows will be displayed.
      
      Figure 18. The Introduction Wizard.
   3. Press the Connect button to connect to the local SQL Server.
      
      Figure 19. The local machine information
   4. Specify the Server name and credentials and press Connect.
      
      Figure 20. The local machine credentials.
   5. Select a database to export and a temporary location for the backup files. Make sure that the SQL Server has permissions on a temporary location.
      
      Figure 21. The Database to export and the temporary file.
   6. To connect to Azure, you will need a Certificate. You can also work with a publishing profile. For more information about a publishing profile, review this link.
      
      Figure 22. Certificate.
   7. Press the Select button and select the certificate created on the requirements section of this article. Copy the subscription ID of step 9 of the requirements in the certificate section.
      
      Figure 23. The subscription ID.
   8. Specify the Cloud Service name, Virtual Machine name and Storage Account and then press Settings.
      
      Figure 24. The subscription ID.
   9. Specify the Virtual Machine, your credentials and the Cloud Adapter port. Verify in the requirements the port used. By default, it is the port 11435.
      
      Figure 25. Cloud Adapter information.
   10. You will receive a message about the certificate. If you agree, press Trust Publisher and connect.
       
       Figure 26. The Trust message.
   11. Finally you will have your information like this:
       
       Figure 27. The Azure VM information and the Local SQL Server information