When you create a database, you also define a SQL Database server to manage it and place it within Azure resource group in a specified region.

Follow the steps to create and configure managed instance in Azure SQL Database–

>>Login to Azure portal.

>>Select Create a resource in the upper left-hand corner of the Azure portal.

>>Select Databases and then select SQL Database to open the Create SQL Database page.

>>On the Basics tab, in the Project Details section, type or select the following values:

  • Subscription: Drop down and select the correct subscription, if it doesn’t appear.
  • Resource group: Select Create new, type sqldbresourcegroup, and select OK.

>>In the Database Details section, type or select the following values:

  • Database name: Enter dynamicssqldb.
  • Server: Select Create new, enter the following values and then click OK.
    • Server name: Type dynamicssqlserver along with some numbers for uniqueness.
    • Server admin login: Type testadmin.
    • Password: Type a complex password that meets password requirements.
    • Location: Choose a location from the drop-down, such as Australia Central.
    • Make sure “Allow Azure Services to access server” is unchecked.

 

>>click “configure database” to select required compute + storage option as per your requirement-

>>Click on “Looking for basic, standard, premium?” option if you need less compute and storage for your DB-

>>Leave the rest of the values as default and select Review + Create at the bottom of the form.

>>Review the final settings and select Create.

Once the SQL Database is created, Click on “go to Resource”, click on “Connection Strings” and copy the string value shown under ADO.NET tab-

Now we will connect to this SQL database using local SQL Server Management Studio. But before that we will add the client IP to SQL Database Firewall to allow the connection. You can open this url from the client where SSMS is installed to get your IP.

>>Go to “Overview” and then click on “Set Server Firewall”

>>Add the client IP and Click Save

Open SSMS and provide below details to connect to Server (you will get the data from connection string)

Server Type: Database Engine

Server Name: dynamicsqlserver.database.windows.net,1433

Authentication: SQL Server Authentication

Login: testadmin

Password: <the password you set while creating SQL Server>

>>Object explorer will look like below

This confirms that the SQL Database is accessible now.