I am putting together a series for people who are excited to bring Azure into their software development life cycle and use Azure cloud’s extensive services to their full potential.
In this series, I will cover as below:
- Getting Started with Azure Development, Create App Service Plan and Publish MVC project using Visual Studio
- Deployment Slots and Slot Swap on Azure App Service using Visual Studio and Azure SDK
- Remote debugging App Service using visual Studio, monitoring and configuring alerts
- Diagnostic logs, live stream, process explorer and KUDU
- How to use Azure SQL Database in Dot.net Applications
- How to use Azure DocumentDB or Azure Cosmos DB in our Dot.net Applications
- How to use Visual Studio Team Service to do continuous Integration and continuous delivery
- Azure storage data services types and how to store files in azure storage account 1/2
- Azure storage data services types and how to store files in azure storage account 2/2
- How to use Azure Functions and trigger on new image/blob creation in Azure Storage using BlobTrigger 1/2
- How to use Azure Functions and trigger on new image/blob creation in Azure Storage using BlobTrigger 2/2
If you do not know where to start, please check my blog post, which covers detail about getting subscription and setting up. In this series, we assume that you already have active Azure subscription and Visual Studio 2013 or later installed on your system.
5. How to use Azure SQL Database in Dot.net Applications
At this point, if you register with the app, which we have built earlier in this series, you will get an error, as we have not created or configured the Azure Database. It is time to get our hands on Azure SQL database.
Figure 1 Error while registering for new user.
Go to Azure Portal and Click on Add and search for SQL Database | select SQL Database and click on create.
Figure 2 Create SQL Database
When you create a SQL Database it will ask you to create Server as well, but you should not think this as a physical installation of the SQL server, in fact it is not even a virtual database server on azure but a logical container, this is just a container which will have databases you create. This databases you create in same container/Database server, do not mean that they will be in the same database server physically, Microsoft manages the infrastructure and although this logical container/database server will behave the same as if, you are using SQL management tools to manage it on premises installed MS SQL Server.
Figure 3 Create SQL Database and SQL Database Server container
Next in our list is elastic Pool, it is to manage the performance level and pricing etc. on a group of databases rather than selecting the performance level etc. in pricing tier for single database, which we do in case of single database creation mode. Be noted the pricing plan we are choosing, is per database basis and is not per container/database server as we are not going for elastic pool. If you have multiple databases, then choosing elastic pool will save you a lot of money.
Click on pricing tier and select the appropriate tier, we will choose the basic one as this is developer guide but in case of production workload, please go for at least standard pricing tier.
Figure 4 SQL Database pricing tier
As you can see, we have DTU as compared to previous metric, where we had CPU and RAM etc. DTU is just representation of the relative resources assigned to database, which include CPU, memory and read-write rates.
Select Basic | apply | create, to create database. It may take some time to create, do not worry if it takes time to complete. Once the database is created, Click on database and you will see screen like below:
Figure 5 SQL Database dashboard
By default, the SQL Database is accessible inside the Azure data center by other azure services and is not accessible outside, luckily, there is an option to add your current IP address and that way it is could be accessible to you outside azure as well. Note that your current IP address may change over time, and it not your true static IP address, may be latter you connect to Azure your public IP change or can be assigned to other users, so it is best to remove it when you are not accessing database outside azure. You have an option of connecting this database in virtual network as well, in case you have some VM(s) or other services to communicate with each other.
Figure 6 Firewall settings SQL Server
Click Add client IP and save.
Now it is time to connect to the database using SQL Management Studio, if you do not have it, download it and install or you can even use visual Studio to connect to the database server. We are going to use SQL Management studio, open SQL Management Studio and put your credentials and connect, you will notice that the ICON of the server is changed that is because it is connecting to Azure SQL database server, also notice that the database is listed under database heading, which we had created earlier.
Figure 7 Connecting to Azure SQL Server using SQL Management Studio
Please note that the User we have used is admin user and it have access to all the databases that we have in our container/Database server, in order to limit the access you need to create new user. If it asks for you to connect with your Azure Subscription account while connecting to database server then do it, because by default, the Azure SQL Server will not be accessible to any one and by logging with your Subscription Account will add your IP address to white list for you automatically.
Let us create a new user for our application, after login go to Object Explorer | Security | Login | New Login, enter your credentials and Press F5 to execute the query.
CREATE
LOGIN myWebAppUser
WITH
PASSWORD
=
‘YRY:)Q5t[{VB‘
GO
Figure 8 Create New User using SQL Management Studio
After that we need to create the user on database level and map this to Login we created in previous step and give the database role, for us we gave “db_owner” role, you can coordinate with your DBA to get the appropriate roles for your application user.
CREATE
USER myWebAppUser
FOR
LOGIN myWebAppUser
WITH
DEFAULT_SCHEMA
= dbo
GO
— Add user to the database owner role
EXEC
sp_addrolemember
N’db_owner’,
N’myWebAppUser’
GO
Figure 9 Mapping login user to Database user and giving database owner role
Now let us create our connection string and add it to our portal, you can also add it to release web.config in visual studio but to make it secure, it is best practice to not mention the staging or production connection string in source code.
Figure 10 Web.config
Go to Azure Portal| database | connection strings | Ado.net copy the connection string and update it with your username and password in any text editor.
Figure 11 Get Azure SQL Database connection string
<add
name=“DefaultConnection“
connectionString=“Server=tcp:scientistzdbserver.database.windows.net,1433;Initial Catalog=scientistzDB;Persist Security Info=False;User ID=myWebAppUser;Password=YRY:)Q5t[{VB;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;“/>
Now we will configure our App Service with the above connection string, for this we need to know the connection string name that is being used in our application. Go to Web.config and Connection string section, as we can see it is “DefaultConnection”
Figure 12 Get name of the connection string
Go to your App Service | Application Settings | Connection strings | add connection string you have created in previous steps | check Slot setting check box and click save.
Figure 13 Add Connection string to App Service
Now try to register again on your main application.
Figure 14 register new user in application
It will take some time to complete this first request, when the website automatically refresh you will see now you are successfully registered in the application.
Figure 15 Successfully Registered on App Service
If you go back to SQL Management Studio and refresh the tables you will see that our application have created new tables, when we registered for first time, that is why it took time for the very first time to complete the request. All the later requests for registrations will happen immediately. Right click on “dbo.AspnetUsers” and select “Select Top 1000 rows” and you will see the record created by entity framework and you can see the “_MigrationHistory”, which mean code first entity framework model is used.
Figure 16 New Tables are created after first registration request
With this congratulation, you know now, how to use Azure SQL database in our dot.net Applications
You can also visit official page of Azure SQL Database
Coming up next How to use Azure DocumentDB or Azure Cosmos DB in our Dot.net Applications