By: Vitor Montalvao | Comments (2) | Related: 1 | 2 | 3 | 4 | > Azure
Problem
This is my second tip that is part of a tutorial to help people that are moving for the first time to SQL Azure. If you did not read my first tip and want to learn how to create a database in SQL Azure you can read it now and then return to this tip to continue your learning.
You should use this information 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
In this tip I will explain how to work with the basic configuration settings for SQL Azure server from the Microsoft Azure portal itself.
Microsoft Azure Dashboard
If you are not signed into Microsoft Azure, go to http://portal.azure.com and provide your login credentials (in my previous tip I explained how to create an Azure login). After signing in you should immediately see the Dashboard.
If you are signed into Microsoft Azure then go to the Dashboard. In any case you will get the following screen or something similar:
You can verify the status of the SQL Azure databases on the right side. In this case I only have one database and it is online.
In the All resources section it will list all the SQL Azure servers and databases and you can access them directly by clicking on their name.
In this tip we will focus only on the SQL Azure server, so let’s start by clicking on the SQL Azure server name to learn more about it.
The SQL Azure Server
When clicking on a SQL Azure server name (it is the instance name for those readers who are coming from SQL Server world) a similar screen as below will appear:
From this screen you can configure and monitor your SQL Azure server.
At the top you will see the respective SQL Azure server name, so you will always know which server you are working on. On the left side is the menu and we will review each of those options. In the right pane, will appear the information relative to menu option that has been chosen.
In this tip I will explain the first five options in the left menu that will let you configure access to the SQL Azure server and some of the monitoring tasks.
Overview
The first and also the default option is Overview. As the name states, here will be presented an overview of the SQL Azure server. In the right pane a context menu at the top shows 6 options:
- New database – Create a new database on the current server. This option will call the create database option that I have written about here.
- New pool – Create a pool with the designated databases to be used in a SQL elastic pool. This is a performance option and we will cover it in a later tip related to SQL Azure performance.
- Import database – Import a database that exists in Azure storage to the current SQL Azure server.
- Reset password – Change the SQL Azure server’s admin password by providing the new password and retype it to confirm there are no typos. The press the Save icon for the new password to be immediately changed or Discard if you want to cancel the change:
- Move – this option allows you to move the current SQL Azure server to another resource group or subscription. The new resource group or subscription must exist. Keep in mind that when moving a SQL Azure server all related resources such as the existing databases in the SQL Azure server will be moved as well.
- Delete – This option deletes the current SQL Azure server and all related databases that exists in the SQL Azure server.
Below these options there is the Essentials pane that has the information about the current SQL Azure server.
You can see the Resource group and subscription where the current SQL Azure server belongs. If you click on the resource group and subscription names it will present detailed information about each of them respectively. There is also a change option for the Resource group and subscription that is basically a shortcut for the Move menu option that we have seen previously.
As informational only, we can check the SQL Azure Server Status, data center Location and the Server admin and Active Directory admin users. In my case I am not working with Active Directory, so I do not have any AD admin users configured. You can also set the firewall rules from this panel by clicking on the Show firewall settings option. This will show the following screen where it will show that all the allowed IPs that can access the actual SQL Azure server:
By default, it will show the IP that you are using, so you can add it immediately to the firewall settings. Provide a name and a range of IPs. If StartIP and EndIP are the same then it means only a single IP is allowed for that rule name.
You can allow more IPs to connect to this SQL Azure server by clicking on the Add client IP option on the top menu. When finished, click on the Save button also on the top menu. It might take some time for the changes to take effect.
You might have realized that there is no field for the TCP port and that is because the Azure SQL Database service is only available through TCP port 1433.
The rest of the information that appears in the Overview pane is informational only. It has a list of existing databases and their status in the current SQL Azure server and the SQL elastic pool and DTU quota utilization.
Activity log
The second option in the left menu is the Activity log. The activity log allows you to see all operations that happened in your Azure subscription during the last 90 days.
Following the Azure pattern, it will have a title so the user will always know where he or she is. In this case I am checking the Activity log for my SQL Azure server and the only entry found was the SQL Server update that happened a week ago when I created this resource for my first SQL Azure tip.
On the top of the right pane is the context menu. In this case we have the following 4 options:
- Columns – Allows you to define which columns from the activity log you want to see. Just check the column names that you want to present and click on Done for the change to take effect.
- Export – Allows you to archive the activity log to Azure storage. This option is still in Preview at the time of writing, but I do not think it will have many changes in the near future. It requires a Subscription and at least one Region and one destination (Azure storage or event hub or both). The maximum number of retention days is 365 days (1 year).
- Log analytics – This i a powerful feature offered by Microsoft Azure and I intend to write about it in a future SQL Azure monitoring tip.
- Operation log – Option that allows you to see the information using a classic view. Unfortunately, SQL Azure resources are not available in the classic view, so in my case I do not have any information to show:
Back to the main Activity log pane, there are many fields that can be used to filter the Activity log result. You can play with those fields as you needed. I want to call your attention to the first field. It is the select query field and has a save button next to it. This means after you set a filter you can save it as a query for later use. You can save as many queries as you want and provide a meaningful name you can recognize in the future. For example:
Clicking on any of the result rows will show more detailed information about the respective row. The result can be exported to a csv file by clicking on the link “Click here to download all the items as csv”:
There is an option at the very bottom of the Activity log pane that you might not have noticed:
The Add activity log alert allows you to create alerts that will be triggered when some defined event occurs. This feature will be explained in a future SQL Azure monitoring tip.
Access control (IAM)
The next option in the left menu is the Access control (IAM). IAM stands for Identity and Access Management. The access control pane allows you to see all users that have access to your SQL Azure server.
Again, the same Azure pattern, where it has a title with the current option location so we do not get lost. Then on the top of the right pane there is a context menu, followed by filtering fields and the results from the search. In my case I am the only user for my SQL Azure server, so there is only a single result line.
The context menu has 5 options:
- Add – Allows you to add more users to the SQL Azure server. Clicking on this option will open a new pane with the existing users listed and you will just need to select the users to whom you want to give the desired permissions by selecting the respective Role. Roles will be explained in an upcoming Security tip, but you can see them on the Roles option.
- Remove – This option will be enabled whenever a user is selected in the Access Control (IAM) main pane and it will let you remove users from the SQL Azure server.
- Roles – This option is for informational purposes only. Clicking on it will show all the available roles where you can see an explanation on what each role does by hovering the cursor over the small button next to each role name.
- Refresh – This option is used to refresh the list of the users based on the filtered options.
- Help – Will open a new pane with information that will let you learn more about the Azure Role-Based Access Control.
Tags
The next option in the left menu is Tags. Tags allows you to organize resources by categorizing them. For example, I can create a Tag with the Environment and set the value to Testing, so I can easily know the environment of the resource I am working with.
Provide a Name and a Value and click on the Save button to create the new Tag.
Diagnose and solve problems
This option will report issues the happened in the last 24 hours and provide a knowledge base article with a solution for common problems. I recommend you check the solutions when troubleshooting. If you cannot solve a specific issue, there is a link at the very bottom to open a support ticket with the Microsoft Azure support team.
The rest of the options are advanced settings and I will try to explain them in future SQL Azure tips.
Next Steps
- Stay tuned for the next tips in this series.
- Check out my previous tip in the series.
- Review all of the Azure tips on MSSQLTips.com.
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
