Monday, November 12, 2018

Azure Managed Instances Procuring & Connecting




Azure Managed Instances
Procuring & Connecting





Below steps help you creating a new Azure Managed Instance from Azure portal and shows how to connect to it.

  1. Create a new managed instance in Azure portal. Click on “Create a resource” and search for “Azure SQL Managed Instance”. In the result that appears. Click Create.





    2. Provide Instance Name, Login information, resource group, location and  Virtual Network to host managed instance. We are using below settings.

Resource Group - NaviDemoDBAInstance
Instance Name - navidemodbainstance
User Name - NaviSQLAdmin
Password - "Your Password"

Once all information is provided click create.

Create Managed Instance


           3. You will get a warning while creating a new VNet that First instance in the VNet could take upto 6 hours to complete.



       4.  Once the creation is in progress, you can click on the notification bell icon and choose to view the progress of the operation. Ours took 3 Hours and 37 Mins to complete.


               5. Create a new Virtual network for the VM you will connect your instance through. We are creating it in the same region “East US”.





               6. Create a new VM in the same resource group. Use the VNet “MyJumpVMVNet” for this Virtual machine. We opened the RDP ports to this machine.



Machine Name – MgdInstJumpVM
User Name – NaviSQLAdmin
Password - "Your password"

Below is the configurations of the VM created.



             7. Once done create a peering between the VM Vnet and Managed Instance VNet. Below are the 2 networks we will peer.


             8. Go to “Peerings” section of one of the network and click “Add”.


         9. On the blade that opens up, specify a name for peering and select the other Virtual network. In our case it is the Managed instance network selected.


               10. Once created you should have the peering visible on your VM Network


               11. Now go to the Managed instance Virtual network and create peering towards your VM Virtual network as we did before in other direction.




               12. Now Connect to your VM created earlier. You can download the RDP file from the portal for connection. Use the credentials provided while creation of VM.



          13. Once logged in Open Internet explorer and download the latest version of SQL Server Management Studio. We will use this for testing connectivity between VM and our Managed Instance.

                14. Once downloaded Start the setup and install it on your Azure VM


                15. Now go to you managed instance on azure portal and copy the complete Instance name. Instance name should be like “MyInstancename.3ed349523e65.database.windows.net”



                16. Go to your VM, Open SQL Server Management Studio and paste the server name and login information as below


                17. You should be able to connect to your instance now. By default the version you will receive would be SQL Server 2014 RTM (12.0.2000.8).


 Hope you find the steps helpful and easy to follow.
Posted by at
Labels: , ,

5 comments:

  1. Er. Ankit SrivastavaAugust 17, 2020 at 7:31 AM

    Hello Abhijeet, thanks for the illustrative article.
    I have the below situation, could you please throw some insights:

    - Managed Instance have SQL agent jobs, which can have steps of Powershell cmds as well. We are facing issues while using commands from modules like SQLServer, Az.sql etc.

    The error we are getting is:

    The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 2 in a PowerShell script. The corresponding line is 'Import-Module SQLServer '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLServer' was not loaded because no valid module file was found in any module directory. ' A job step received an error at line 3 in a PowerShell script. The corresponding line is 'Import-Module Az.sql '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'Az.sql' was not loaded because no valid module file was found in any module directory. ' A job step received an error at line 5 in a PowerShell script. The corresponding line is 'Import-Module PowerShellGet -Force '

    Question: I believe the actual box where the SQL MI is hosted needs to have these PS modules be installed, the question is how can I do it?

    ReplyDelete
    Replies
    1. Real Time With SaidurDecember 30, 2020 at 9:45 AM

      Can i get your contact information. I need some training from you.

      Delete
  2. AbhijeetAugust 22, 2020 at 10:03 PM

    Hi Ankit,

    You don't have any control on the underlying operating system in any ways. Managed instance is just another flavor of PAAS services. If there is a module that isn't supported, chances are you won't get it unless Microsoft starts supporting it from their end.
    Hope that answers your question.

    ReplyDelete
  3. UnknownFebruary 16, 2022 at 6:37 AM

    Hi Abhijeet,
    i have one virtual machine and three sql managed instance. i am not able to peering all three to the Vitrual network of VM. Instead i am able to peer any of the the three managed instace.
    Could you suggest how to connect 3 managed instance to one VM, please.

    ReplyDelete
  4. HarsimranjeetsinghAugust 11, 2022 at 2:04 AM

    Hi Abhijeet,

    First of all I would like to thank you for taking time and consolidating such post and helping the community.

    I have some doubts regarding the steps performed here.
    Is there a limitation that Azure SQL Managed Instance can be connected via VM on Azure, not from Local desktop machine like Azure SQL ?

    Thanks & Regards,
    Harsimranjeet Singh

    ReplyDelete

Subscribe to: Post Comments (Atom)