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
- Server: Select Create new, enter the following values and then click OK.
- Server name: Type
dynamicssqlserveralong with some numbers for uniqueness.
- Server admin login: Type
- Password: Type a complex password that meets password requirements.
- Location: Choose a location from the drop-down, such as
- Make sure “Allow Azure Services to access server” is unchecked.
- Server name: Type
>>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
Password: <the password you set while creating SQL Server>
>>Object explorer will look like below
This confirms that the SQL Database is accessible now.