Next few posts will be about one of my favorite subjects – SQL. When we talk about databases in Azure there is lot of options, depending on what you need. If you need more services that traditional MS SQL Server offered on premise, best way is to use IaaS, VM with MS SQL Server installed. Again there are different options here but we’ll talk about those later.
If you need only database, then Azure SQL is way to go. Azure SQL comes as PaaS (or DBaaS) and there are not many things you can configure here. All you have is your database (you can create tables, views, procedures…) and you have logins that you can create for different users. There is also great thing about firewall access that you’ll see how to configure in this post.
There are two pricing models for Azure SQL, single database or Elastic database pool. In single database model, you are charged per database (depending on tier you selected – size and DTU). You can create new server for each database or you can attach more databases to one server, you are still charged for each database, it doesn’t matter how many servers you have. For elastic database pool you are charged for server and you can attach more databases (as long as they don’t reach space limit for server you selected). Number of databases don’t affect price; you are still charged same no matter if you have 1 or 20 databases attached to your server.
In this case we’ll talk about single database model. So, we start with creating new database and new server.
In Azure portal, select new.
In new tile select ‘Databases’ and ‘SQL database’.
New tile will open with database configuration options. Enter name for your database, select subscription and select if you want to create new resource group or add your database to existing one. Next option is to select source. By default it will create blank database.
Second option for source is to select sample database where you have option of selecting AdventureWorksLT or AdventureWorksLT(V12).
Third option is to restore database from backup but you’ll need to provide location of valid database backup located in your subscription.
I’ll chose blank database and move to server configuration. Here you need to provide server name, administrator username (this user is similar to sa in on-prem version), password, location for your server and chose if you want latest version of Azure SQL or version before that. Last option is asking if you want to allow Azure Services to access your server. You’ll probably chose to allow as you want your website or other Azure Services to be able to access your database.
Last two options are pricing tier and collation. Here you can see that prices of Azure SQL are affordable.
After everything is prepared, klick create and wait for few moments (creating blank database usually takes under one minute).
After deployment is finished, go to databases.
Klick on your database and wait for new tile to open.
Klick on firewall. New tile will open where you need to enter firewall rule to allow you to access your database from your current IP address. This option allows only access from IP addresses that you approved. So even if someone manages to get your username and password for database, they still won’t be able to access your data if their IP address is not approved.
Open SQL Management Studio and enter your database name, chose SQL Server Authentication and enter your username and password.
You are connected to your database server and you can start using your database. Note that lot of options are missing when compared to on premise MS SQL Server.
So, your Azure SQL database is ready for use and you can start creating objects and inserting data. There are lot of other options that you can configure and set but we’ll talk about those in one of next posts.