Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Azure Create Database Tutorial


By:   |   Read Comments   |   Related Tips: More > Azure

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

This tip intends to help those professionals that are moving for the first time to SQL Azure. You should use this article as a tutorial and follow it step-by-step. I will also try to share my experience on the differences that I have found when I was moving from SQL Server to SQL Azure for the first time.

Solution

If you are moving from an on premises environment to the cloud, you will need to consider a new mind set. Microsoft SQL Azure is a Database as a Service (DBaaS) or we can also say that a SQL Azure database is a virtual database. This means that you do not need to have a Windows Server nor install a SQL Server instance. It also means that resources such as network or storage are also not needed. SQL Azure will provide everything that you need for your SQL Azure database work without issues.

Microsoft Azure login

To connect to Microsoft Azure, go to http://portal.azure.com and provide your credentials.

Sign in Microsoft Azure portal - Description: Sign in Microsoft Azure portal

If you do not have an Azure account, provide your email or telephone number and it will verify that you are not in the system and will invite you to create an account. There is an option for a free trial account with $200 USD in credits for 30 days, so you can play with Azure resources without spending money during those 30 days.

After logging into Microsoft Azure, you will be presented with a dashboard for your subscription:

SQL databases option in Microsoft Azure - Description: SQL databases option in Microsoft Azure

Create a SQL database in Azure

To create a database, click on the “SQL databases” option in the left menu. This will show a similar screen as the next one:

Create SQL Azure database - Description: Create a new database in SQL Azure

Click on the “Create SQL databases” button to proceed with the database creation as the following image shows:

Provide information for the new SQL Azure database - Description: Provide information for the new SQL Azure database

All fields marked with a red star (*) are required fields so they must be provided.

So let's start with providing the Database name and choose the Subscription and Resource group where it will be stored. If you do not have any resources created you can create a new one now. Otherwise select one from the existing resources. Then you must select the source from where you want to create a database:

  • Blank database - Create a new and empty database
  • Sample database - Create a copy of the AdventuresWorksLT sample database
  • Backup - Create a database from a backup that needs to exist in Azure

For this tip I choose the sample database, so it will have a schema and data loaded.

Now we must choose a Server where the database will be stored. For people used to working with SQL Server this Server is the SQL Server instance name.

If you do not have a server, you are required to create a new server as shown below where you will need to provide a server name and the admin credentials. Note that this server name needs to be unique across Microsoft Azure, meaning that it might to be hard to find a name that is not already in use.

The nearest Azure data center Location will be provided by default and you can change it as you need or want.

Create new server (instance) - Description: Create new SQL Azure server (instance name)

For the purposes of this tip I will go with the basic options so I will not want to use the SQL elastic pool and will choose the basic Pricing tier.

The Pricing tier will provide the number of Database Transaction Units (DTU) and this is a ratio of CPU, Memory and I/O. I will choose the Basic tier that will let me have 5 users and a database with a maximum size of 720MB. The following table outlines all of the service tier options:

Service Tier Max DTUs Included Storage Max Concurrent Logins Max Concurrent Sessions
Basic 5 2 GB 30 300
Standard (S0) 10 250 GB 60 600
Standard (S1) 20 250 GB 90 900
Standard (S2) 50 250 GB 120 1200
Standard (S3) 100 250 GB 200 2400
Standard (S4) 200 250 GB 400 4800
Standard (S6) 400 250 GB 800 9600
Standard (S7) 800 250 GB 1600 19200
Standard (S9) 1600 250 GB 3200 30000
Standard (S12) 3000 250 GB 6000 30000
Premium (P1) 125 500 GB 200 30000
Premium (P2) 250 500 GB 400 30000
Premium (P4) 500 500 GB 800 30000
Premium (P6) 1000 500 GB 1600 30000
Premium (P11) 1750 4096 GB 2400 30000
Premium (P15) 4000 4096 GB 6400 30000
Premium RS (PRS1) 125 500 GB 200 30000
Premium RS (PRS2) 250 500 GB 400 30000
Premium RS (PRS4) 500 500 GB 800 30000
Premium RS (PRS6) 1000 500 GB 1600 30000

*** NOTE *** - This table outlines the actual service offering tiers from Microsoft Azure at the time of writing. Please check here for any service updates.

The SQL elastic pool is useful when you have many databases and you want to let Microsoft Azure manage the DTUs of the databases. It will automatically remove the DTUs from databases that have light resource consumption and add those DTUs to databases that are facing high demand resource consumption.

Select the pricing tier - Description: Select the pricing tier

Since I am using a free trial account, I cannot choose a Premium or PremiumRS tiers since they cost more than $200 USD. After selecting the Pricing Tier your screen should looks like the following one:

Create SQL Azure database validation - Description: Create SQL Azure database validation

When choosing to create the database from a sample, the Collation cannot be changed. You can only provide a Collation when creating a blank database. A Collation is used to specify the language characters used in char and varchar datatypes. It also specifies sorting rules, case, and accent sensitivity properties for the data. You can learn more about collations by reading this MSDN article.

Validation should be successful to create the desired database and if you want to have the new database available in the dashboard just check the Pin to dashboard option. The dashboard is presented by default when you log in to the Microsoft Azure portal, but you can always go to the dashboard anytime by clicking on the Dashboard option in the left menu of the portal.

What is also interesting is that Azure gives you an option to generate the code for creating the database. For that click in the Automation options link to check what is available. At the time I am writing this tip the following options are available:

Automate options template - Description: Automate options template

Those automation options are out of the scope of this tip, but I wanted to show them anyway because it might be useful for you.

It will take a few minutes to create the database and it can be followed in the main Azure Portal screen:

Deploying SQL Azure database - Description: Deploying SQL Azure database

When finished the Azure Server and the SQL Azure database will be displayed in the Dashboard. From here you can access your SQL Azure server and databases:

SQL Azure dashboard - Description: SQL Azure dashboard

In the my upcoming tips I will show you how can you work and manage the databases in Microsoft Azure and with SSMS and how to connect to a SQL Azure from an application.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Vitor Montalvao Vitor Montalvão is a SQL Server engineer with 20 years of experience in SQL Server, specializing in performance & tuning, data modelling, migration and security.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools