Questions About Azure SQL Database You Were Too Shy to Ask

You've heard of Azure SQL Database, you've nodded wisely in discussions and you might even have tried it out on some sample data. However, you have some nagging basic questions that would seem slightly ridiculous to ask in public. For all those questions that you're too shy to ask, Murilo gives a reassuring answer.

The Questions

  1. How do I create an Azure SQL Server instance?
  2. How would I go about creating a SQL Database?
  3. How to add entries to the SQL Database firewall?
  4. How can I change the database Service Tier to either scale-up or down?
  5. How can I do a point-in-time restore?
  6. How can I recover a dropped Azure SQL database?
  7. How would I partition an Azure SQL database?
  8. Is In-Memory OLTP available on SQL Database?

1. How do I create an Azure SQL Server instance?

There are two ways of creating an Azure SQL Server instance. You can either install SQL Server in a VM (IaaS) or use the Azure SQL Database service (DaaS). Azure SQL Database uses the idea of hosting one or more databases on a ‘logical’ instance.

In order to create SQL Databases, you first need to create this instance, and then you can create/group the SQL Databases that you need. Unlike a regular SQL Server instance, databases belonging to the same SQL Server logical instance are not able to access each other.

To create a SQL Server logical instance…

    1. Click on ‘SQL Servers’ in the menu. If the item is not there, go to ‘Browse>SQL Servers’.

    2. On the top of the opened panel, click on ‘+ Add’

  1. Another panel will be opened, where you will need to fill the following:
    • Server Name – The name of you Logical Instance. This will be used to identify and connect. The address will be in the format: <Server Name>.database.windows.net.
      This name should be unique and in lowercase.
    • Server Admin Login – Login name used to administer the logical instance.
    • Password – The password from the Server Admin Login.
    • Subscription – In case you have more than one subscriptions, you will need to double check if you are creating the logical instance in the correct one.
    • Location – The Azure Location where you instance will be created. You can find the full list of locations here.
  2. Click on ‘create’, and wait for the notification of success.

2. How would I go about creating a SQL Database?

Before you can create a SQL Database, you will first need a SQL Server logical instance. See the previous question for an explanation of how to do that.

You also need to be clear as to what service tier you’ll need, because you will be asked to select one. You can refer to my previous article, Azure SQL Database – How to Choose the Right Service Tier, which explains the importance and the role of a service tier.

Creating a SQL Database is simple:

    1. Click on ‘SQL Databases’ in the menu. If the item is not there, go to ‘Browse>SQL Databases’.
    2. A new pane will be opened. Click on ’+ Add’.

  1. The new SQL Database pane will be shown.
    In this pane you will need to set the following:

    • Database Name – Differently from the SQL Server logical instance, the database name can contain both lower and upper-case letters.
    • Subscription – In case you have more than one subscript, make sure to select the right one.
    • Resource Group – You can choose to create a new one or reuse an existing one.
    • Select Source – There are three options here:
      • Blank: Empty database
      • Sample: A sample database with objects and data will be created, for testing purposes.
      • Backup: Used to create a database based on an existing backup.
    • Server – You can select an existing SQL Server logical instance or create a new one.
    • Pricing Tier – Select the tier associated to this database.
    • Collation – Specify the collation to be used on the database. Usually, the default one is the best choice, but check your requirements before you set it.
  2. Click on ‘Create’ and wait for the notification that the task has been completed.

3. How to add entries to the SQL Database firewall?

There are two approaches towards setting firewall rules for SQL Database, either at the server-level or at the database-level. It is better to set the rules as needed at the database-level, but there are cases where it is easier, or necessary, to define the rules at the server-level.

Defining the rules from the server-level:

    1. Click on ‘SQL Servers’ in the menu. If the item is not there, go to ‘Browse>SQL Servers’.

  1. Click on the SQL Server that you want to set the rules.
  2. Click on settings in the menu on the top.
  3. In the opened panel, click on the ‘Firewall’ option.
  4. The following panel will open:

    When allowing IPs to connect, at this level, you will be allowing access to all the SQL Databases under this server.

    By selecting ‘On’, on the ‘Allow access to Azure services’ option, you are allowing Windows Azure services to connect to this server, which is the same as allowing the IP 0.0.0.0, to connect. This option will be selected if you check the option ‘Allow Azure services to access server’ when creating the SQL Server.

  5. If you are connecting from the computer where you want to open connectivity, just click on the ‘Add client IP’ button, on the top of the panel and then click on ‘Save’.
  6. If you want to add a rule to a specific IP or a range of IPs, fill the fields ‘Rule Name’, ‘Start IP’ and ‘End IP’; and then click on ‘Save
  7. After saving, the referred IP will be able to connect to the SQL Server and its contained SQL Databases.
  8. If you want to delete an entry, just click on ‘…’ and select ‘Delete’. After that, click on ‘Save’.

Defining the rules from the database-level:

There’s (still) no way to do this using the Azure portal, so you will need to connect to the database using SQL Server Management Studio (SSMS), with an user login that has the “CONTROL” permission and from a computer that is allowed to access the SQL Server logical instance.

Remember to ensure that you are connected to the database where you need to set the rules

There are three main commands to list permissions, set and delete rules:

  1. In order to check the existing rules:

  2. To create a new rule:

    Example:

  3. To delete a specific rule:

    Example:

Even if an IP is not allowed to connect to a SQL Server logical instance, but is allowed to connect to a SQL Database, the connection attempt is going to work, but you would need to explicitly specify the name of the database to which you are connecting, otherwise the connection will fail.

4. How can I change the database Service Tier to either scale-up or down?

One of the great advantages of a cloud services such as DQL Database is its capacity to easily scale up and down. It is possible to adapt the “horsepower” of your SQL Database depending on the current needs.

This process will create a replica of the original database, under the new service tier. Once this part of the process is done, all the connections will be redirected to the “new” database, and the active connections will be disabled.

The process of changing the ‘Service Tier’ is simple, just follow the steps:

  1. Click on “SQL Databases” in the menu. If the item is not there, go to ‘Browse>SQL Databases.
  2. Select whichever database has a Service Tier that you wish to change.
  3. A new pane will be shown, select ‘Pricing Tier (scale DTUs)

  4. A pane with all the available tiers will be exposed. Click on the square representing the new tier, and then click on the ‘Select’ button.

  5. A message will pop-up on the top of the page, confirming the change request.

    The time taken to complete this request varies on the database size and chosen tier.
  6. You can confirm the change, by looking to the database properties pane.

5. How can I do a point-in-time restore?

The point-in-time restore is available on all the three editions – Basic, Standard and Premium. Be aware that each one of the editions has different backup retention policies. For an updated information, check this link.

A restore will always create a new database. This database can be used to recover data to the original database or to fully replace the original database, which can be performed by renaming the restored database, using the ALTER DATABASE command:

  1. Click on “SQL Database” in the menu. If the item is not there, go to Browse>SQL Database.
    02-08-2016-11-38-44.png
  2. Click on the database to be restored.
  3. Click on “Restore”
  4. In the new opened pane:
    1. Confirm (or edit) the database name – you cannot restore over the existing database.
    2. Set the date and time from when the database will be restored.
    3. Select the target SQL Server to restore the database
    4. Click “Ok”

  5. The restore process will begin, and it is possible to follow the updates in the alerts icon on the top-right

Once the database is restored, you can access it and copy the data from the recovered database to the live one. As alternative, it is possible to rename both databases and switching roles.

6. How can I recover a dropped Azure SQL database?

Accidentally deleted your database? Don’t worry (too much), you can still recover it!

Thanks to the automatic backup process, available on all the tiers, you will be able to still recover the latest backup of your database. There’s a scheduled full backup every week, a daily differential backup and log backups on every 5 minutes.

Even if a database is dropped, the backups will remain stored for the period associated to the selected Service Tier. These are the steps that are needed to recover a dropped database:

  1. Click on ‘SQL Databases’ in the menu. If the item is not there, go to ‘Browse>SQL Databases’
  2. A new pane will be opened. Click on ‘+ Add’.
  3. The new SQL Database pane will be shown. In this pane you will need to set all the options as when a new database is created, but the tricky here is on the ‘Select Source’ option, where you will need to choose the ‘Backup’ option.
  4. A new setting ‘Backup’ will be required, click on this option in order to select which of the backups should be used as the source for this new database.
  5. A list containing all the backups for your subscription will be shown. Select the database that you want to restore (the dropped one).
  6. The fields on the ‘New Database’ pane will be automatically filled. Correct the Database Name, Service Tier and Resource Group, if needed, and click on ‘Create’.
  7. Wait for the message that the database has been created, and your database will be ready to be used again.

7. How would I partition an Azure SQL database?

If you are used to the traditional partitioning on SQL Server (on-premise), you may be wondering how to work with this on SQL Database, as we have no control over files and filegroups.

The answer is simple: map all the partitions to the “PRIMARY” filegroup. This way, the partition scheme will look like the following:

Under the hood, the storage engine is balancing the load in order to provide the best performance. This way, even in Azure, you can use partitioning to achieve scalability, performance and availability.

Apart of the traditional partitioning, Azure offers another option for horizontal partitioning: Elastic Database. Check out this article for more information.

8. Is In-Memory OLTP available on SQL Database?

The quick answer is ‘yes’ (at this moment it is in preview): But, as with most things related to SQL Server, it depends. In this special case, it depends on your service tier. If you try to create a memory-optimized table on a SQL Database based on a tier other than Premium, this is what you’ll get:

It is also possible to get this error message if your database was created before the introduction of the In-memory OLTP preview on Azure. A more elegant way to find you if you database supports this feature is by running the following:

If the result of the query is “0” (zero), this mean that In-Memory OLTP is not supported, otherwise you can start creating your memory-optimized tables and natively compiled objects.

In order to create a memory-optimized table, just add the option MEMORY_OPTIMIZED=ON, as follows:

If you are new to In-Memory OLTP, you can check the series of articles that I’m writing on the topic.