In last post I wrote about creating new Azure SQL database. At the end of post, I mentioned that features available from SSMS when connected to Azure SQL are very poor when compared to full MS SQL Server.
When we compare these two, we can see that in Azure SQL we are missing Server objects, Replication, AlwaysOn, Management and Integration Services catalog. Fortunately we still have a lot of those options available in Azure portal.
When you open your Azure SQL database in portal, by default you will get overview some basic information about your database such as server name, tier, location, status etc.
First interesting option, when we consider administration of Azure SQL, is ‘Activity Log’. Here we can see all events and have different filtering options to narrow our search.
In ‘Pricing Tier’ you can change your database size. You can select form options that offer different storage limits and DTUs.
One of best options in Azure SQL is ‘Geo- replication’. In just few clicks you can set up your database to replicate to different region.
As my database is located in North Europe, I will choose West Europe region. New tile opens where you can change pricing tier, secondary database type and target server. Secondary database type can be readable and unreadable where later one can be accessed only if replication is stopped. I will choose pricing tear as basic and secondary type as readable. For target server you have option of choosing existing Azure SQL server in region you selected or creating new one. As I have no Azure SQL servers in West Europe, I will choose new one and enter needed parameters.
After you click create, wait till deployment is finished and go to your SQL databases screen. You will see that new database is created. Old database will have replication role ‘Primary’ and new one will have ‘Secondary’.
Next feature we’ll talk about is ‘Auditing & Threat detection’. By default, your settings here will be greyed out as database inherit settings from server. You can either untick box with this option or click on server audit settings. In both cases you will get identical set of options.
I will uncheck Inherit settings from server and get option to change my settings on database level. Now I can enable auditing and select if I want audit logs to be stored in table or blob. In storage details, you need to select storage for your audit logs (you can choose existing or create new one and you can choose retantion period) and what type of events you want to be audited (I will choose ‘All’). Threat detection is still in preview mode but you can try it out. This option detects anomalous database activities that can potentially be security threat. If you chose to enable threat detection, you will get option to enter email address to receive alerts of potential threats and chose if you want admins and co-admins to receive this alert as well.
Another feature we’ll talk about is ‘Dynamic data masking’. This option enables you to hide sensitive information from users such as email, passwords, Credit card information etc. To enable data masking, click on add mask, select schema, table and column you want to mask and chose masking format. I will choose my table ‘user’ and mask column ’email’.
Once masking rule is created all users will have masking option enabled and you need to create rule to exclude users that you want to be able to read this data. Note that administrators are always excluded.
‘Transparent data encryption’ enables you to encrypt your data at rest. Great thing about it that with just two clicks (set encryption ON and save) you encrypt all associated data – backups, replications, transaction logs – all will be encrypted.
‘Alert rules’ can help a lot in Azure SQL administration. Click on Alert rules and then click add alert.
You can choose from lot of different options. You can create alerts based on different metrics, conditions and periods. At the end of creating alert, you can enter your email (and/or emails of other admins and co-admins). For example, you can create alert to notify you if CPU percentage (metric) is greater 80% (condition) for more than 5 minutes (period). This way you can be notified right away and investigate what is cause of this or simply change tier of your database.
So when all is set, you have another great feature. Go to ‘Automation scripts’, click and new tile will open. This deployment script can be used to deploy another instance of your database with all setting you spent some time setting up. You can either download script, save it to library or deploy from this window. When you use this deployment script, database you create will have identical settings as this one – replication, alerts, data masking, encryption…