Improve performance, security, and reliability for your Azure VMs with Azure Advisor

by Scott Muniz | Jul 7, 2020 | Azure, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

Running virtual machines in Azure is great. However, there are a lot of things you need to think about to improve performance, security, and reliability. Your cloud environment is also constantly changing, so you will need to check your Azure VMs from time to time. Luckily, there is a service called Azure Advisor which is a personalized cloud consultant that helps you follow best practices to optimize your Azure deployments. It analyzes your resource configuration and usage telemetry and then recommends solutions that can help you improve the cost-effectiveness, performance, reliability, and security of your Azure resources.

With Advisor, you can:

• Get proactive, actionable, and personalized best practices recommendations.
• Improve the performance, security, and reliability of your resources, as you identify opportunities to reduce your overall Azure spend.
• Get recommendations with proposed actions inline.

How to check your Azure Advisor recommendations for your Azure virtual machines (VMs)

You can access Azure Advisor, for all Azure services through the Azure portal or directly in as an option in the Azure VM navigation.

Azure Advisor recommendations for Azure VMs

From here you can read more about the recommendation and get more details, as well as take action.

You can also create an Azure Advisor recommendation digest, so you can find your recommendations directly in your inbox.

Azure Advisor recommendation digest

Conclusion

Azure Advisor is a great tool to get recommendations for not just your Azure virtual machines, but also for other Azure services. If you want to learn more check out Microsoft Docs.

Microsoft Azure Advisor: How to Improve performance, security, and reliability of your Azure VMs

by Scott Muniz | Jul 7, 2020 | Azure, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

Running virtual machines in Azure is great. However, there are a lot of things you need to think about to improve performance, security, and reliability. Your cloud environment is also constantly changing, so you will need to check your Azure VMs from time to time. Luckily, there is a service called Azure Advisor which is a personalized cloud consultant that helps you follow best practices to optimize your Azure deployments. It analyzes your resource configuration and usage telemetry and then recommends solutions that can help you improve the cost-effectiveness, performance, reliability, and security of your Azure resources.

With Advisor, you can:

• Get proactive, actionable, and personalized best practices recommendations.
• Improve the performance, security, and reliability of your resources, as you identify opportunities to reduce your overall Azure spend.
• Get recommendations with proposed actions inline.

How to check your Azure Advisor recommendations for your Azure virtual machines (VMs)

You can access Azure Advisor, for all Azure services through the Azure portal or directly in as an option in the Azure VM navigation.

Azure Advisor recommendations for Azure VMs

From here you can read more about the recommendation and get more details, as well as take action.

You can also create an Azure Advisor recommendation digest, so you can find your recommendations directly in your inbox.

Azure Advisor recommendation digest

Conclusion

Azure Advisor is a great tool to get recommendations for not just your Azure virtual machines, but also for other Azure services. If you want to learn more check out Microsoft Docs.

[SQL Server on Azure VM] Automated Backups run daily when scheduled to run weekly

by Scott Muniz | Jul 6, 2020 | Azure, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

 When we enable Automated Backup for SQL Server as documented in https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-backup and if we setup manual schedule with Weekly backup, we will continue to see the backup of the databases happen daily.

We had few of our customers report this so we wanted to blog about this issue and provide a workaround until the issue is fixed. 

There are 2 issues with this, as you see, we do not have an option to select which day of the week you wanted the backup to happen and the other one is with the code issue. This is currently known issue and we are working to fix this in near future, but until then we can work around the issue and fix it by running the following T-SQL to modify and make the changes using Managed Backup commands:

-- Confirm the days_of_week has all the days selected and also get the information about backup_begin_time, backup_duration and log_backup_freq and update accordingly in below scripts
SELECT db_name, 
       is_managed_backup_enabled, 
       scheduling_option, 
       full_backup_freq_type, 
       days_of_week, 
       backup_begin_time, 
       backup_duration, 
       log_backup_freq
FROM msdb.managed_backup.fn_backup_db_config(NULL)
WHERE is_managed_backup_enabled = 1
      AND full_backup_freq_type = 'WEEKLY';

NOTE: You see System databases Master, Model and MSDB because I had selected “Backup system database” option in earlier screen shot to enable backups for those aswell.

Things you need to note from about is “backup_begin_time”, “backup_duration” and “log_backup_freq” and parameter we are interested in updating is “@days_of_week“.

-- Updating the backup config instance wide so that any new databases created, they already get added with the required info. 
-- We are updating @days_of_week to required day  
EXEC msdb.managed_backup.sp_backup_config_schedule 
     @database_name = NULL, 
     @scheduling_option = 'CUSTOM', 
     @full_backup_freq_type = 'WEEKLY', 
     @days_of_week = 'Monday', 	-- needs updated to your required day
     @backup_begin_time = '00:00',	-- needs updated based on above output
     @backup_duration = '02:00',	-- needs updated based on above output
     @log_backup_freq = '01:00';	-- needs updated based on above output
GO

-- Remember for existing databases this will get applied when you manually modify the values for each of them. So we have to manually update for each existing database

DECLARE @DBNames TABLE
(RowID  INT IDENTITY PRIMARY KEY, 
 DBName VARCHAR(500)
);

DECLARE @rowid INT;
DECLARE @dbname VARCHAR(500);
DECLARE @SQL VARCHAR(2000);

INSERT INTO @DBNames(DBName)
       SELECT db_name
       FROM msdb.managed_backup.fn_backup_db_config(NULL)
       WHERE is_managed_backup_enabled = 1
             AND full_backup_freq_type = 'WEEKLY';

SELECT @rowid = MIN(RowID)
FROM @DBNames;
WHILE @rowID IS NOT NULL
    BEGIN
        SET @dbname =
        (
            SELECT DBName
            FROM @DBNames
            WHERE RowID = @rowid
        );
        BEGIN
            SET @SQL = 'EXEC msdb.managed_backup.sp_backup_config_schedule
                                         @database_name =  ''' + '' + @dbname + '' + '''
                                         ,@scheduling_option = ''CUSTOM''
                                         ,@full_backup_freq_type = ''WEEKLY''
                                         ,@days_of_week = ''Monday''		-- needs updated to your required day
                                         ,@backup_begin_time =  ''00:00''	-- needs updated based on above output
                                         ,@backup_duration = ''02:00''	-- needs updated based on above output
                                         ,@log_backup_freq = ''01:00''';	-- needs updated based on above output
            EXECUTE (@SQL);
        END;
        SELECT @rowid = MIN(RowID)
        FROM @DBNames
        WHERE RowID > @rowid;
    END;

If we now again run the first query above and should see the days_of_week reflect to the day(s) of your choice.

Once done, it should work for any new database created.

Point to note, if you disable and re-enable the Automated backup before the fix is released, we will have to go over the same process again.

Hope this helps!

Regards,

Dinesh

Ref: managed_backup.fn_backup_db_config, sp_backup_config_schedule

COVID-19 data analytics and reporting with Azure Databricks and Azure SQL

by Scott Muniz | Jul 6, 2020 | Azure, Microsoft, Technology, Uncategorized

This article is contributed. See the original author and article here.

With the evolving need to keep up with growing data volumes, increased data dependency on the business decision making process and the evolution of data platforms with Cloud Computing, enterprises have widely adopted Unified Analytics systems, Data Warehouses and Data Lakes to fulfil their data analytics and machine learning requirements. To that end, there are technologies to support a modern data warehouse architecture in Azure using platforms like Azure Synapse which is an analytics service that brings together enterprise data warehousing and Big Data analytics. While the modern data warehouse architecture is well poised to serve as a central data repository and to fulfil advanced analytics requirements for many enterprises, there is also a growing desire to be able to support a hybrid OLTP database / Datamart for a wide variety of reporting and data visualization needs to simply serve an enriched dataset.

Organizations have invested significantly in leveraging the work of data engineers and data scientists to perform complex data transformations and to produce meticulously crafted machine learning models to deliver invaluable business insights. Disparate datasets are put through a process of sophisticated transformation along with batch scoring / predictions derived from Machine Learning models whose resulting datasets are often required to be stored in relational databases (Operational Data Stores, HTAP databases, Fit-for-purpose Reporting Datamarts etc.). These are use cases where data scientists or data engineers want to be able to simply land and store computed data insights in a relational SQL database to make it easily available to downstream applications and data visualization tools. The purpose of these use cases is not to perform complex computations and aggregations that is best suited for Massively Parallel Processing (MPP) based data warehouses but to serve the transformed dataset to business users and applications via Symmetric-Multi Processing (SMP) SQL databases.

The objective of this article is to focus on a use case that demonstrates the integration between Azure Databricks and Azure SQL to deliver insights and data visualizations using a publicly available COVID-19 dataset. While Azure Databricks provides the distributed computing power to process and transform complex datasets, Azure SQL is a fitting recipient of the transformed dataset that surfaces these insights to business users.

Azure SQL is a family of fully managed, secure, and intelligent SQL database services that support a wide range of application patterns, from re-hosting and modernizing existing SQL Server workloads to modern cloud application development. With the SQL Server engine powering the Azure SQL family of database services, Azure SQL offers the best-in-class relational, transactional and hybrid analytics database services that truly meets the varying data demands of our customers.

The architecture diagram below shows the various layers in a typical data analytics scenario and how Azure SQL can meet the requirements of both the storage layer (storing mission critical application transactions) and the serving layer (delivering pre-aggregated, transformed or predictive insights) that organizations need today. There are various technology services in Azure that can be overlayed on top of these generic architectural layers and we focus on one such pattern in this article that focusses on performing data transformation and producing machine learning predictive insights (using Azure Databricks) while serving the transformed datasets via a Datamart for visualizations and reporting (using Azure SQL).

Solution Architecture

Apache Spark Connector for SQL Server and Azure SQL

One of the key requirements of the architectural pattern above is to ensure we are able to read data seamlessly into Spark DataFrames for transformation and to write back the transformed dataset to Azure SQL in a performant manner. We recently open-sourced the Spark connector for SQL Server and Azure SQL that was announced here. The Spark connector for SQL Server and Azure SQL is a high-performance connector that enables you to use transactional data in big data analytics and persists results for ad-hoc queries or reporting.

Key features of this connector include:

• Support for all Spark bindings (Scala, Python, R).
• Basic authentication and Active Directory (AD) keytab support.
• Reordered DataFrame write support.
• Reliable connector support for single instance.

The connector can either be downloaded from this GitHub repo.

Solution Walkthrough

To immerse into a working solution to demonstrate the architectural pattern mentioned above, the solution uses a publicly available COVID-19 dataset and runs a machine learning model in Azure Databricks to predict the fatalities of COVID-19 per day in each country. These predictions are then transformed and written into a DataMart in Azure SQL Managed Instance for visualization and reporting. Azure SQL Managed Instance is just one of the services in the Azure SQL family and the same solution can be modified to use other services in Azure SQL.

The dataset is from COVID-19 Data Lake published on Microsoft Azure Open Datasets. The dataset is the latest available public data on geographic distribution of COVID-19 cases worldwide from the European Center for Disease Prevention and Control (ECDC). More information on the dataset and COVID-19 Data Lake can be found here.

The solution comprises of the following parts as described in the data flow below.

1. The solution extracts the COVID-19 public dataset from the pandemic data lake into Azure Databricks as a Spark DataFrame.
2. The extracted COVID-19 dataset is cleaned, pre-processed, trained and scored using a Gradient Boosted Trees (GBT) Machine Learning model. GBT is chosen to predict the deaths per day in each country purely for demonstration purposes only and should not be considered as the only model for such prediction
3. The resulting dataset with the predicted scores is stored into a staging table in Azure SQL Managed Instance for further downstream transformation.
4. Common data dimension tables and the staging tables from Azure SQL Managed Instance are read into DataFrames in Azure Databricks. Note the two Managed Instances shown in the “Store” and the “Serve” layer are essentially the same instance just depicted in different phases of the data flow. Azure SQL can play the role of both a data storage service and a data serving service for consuming applications / data visualization tools.
5. The DataFrames containing the necessary dimension and staging data are further refined, joined and transformed to produce a denormalized fact table for reporting. Denormalization of data is highly recommended for reporting and data visualization as the data is structured in a way that optimizes performance of reporting queries and enables slicing-and-dicing of data as desired by business decision makers.
6. The resulting denormalized data is written to Azure SQL Managed Instance (using the highly performant Spark connector for SQL Server and Azure SQL) which is ready to serve the data to its consumers.

Azure SQL Managed Instance Business Critical tier provides a readable secondary by default and is a fitting solution to read data off the replica while the primary is busy transacting from the application. The solution works just as good with the General Purpose tier except that there is no readable secondary by default (unless a Geo-Replica is provisioned) . Public endpoint for Azure SQL Managed Instance is securely enabled to allow Azure Databricks to connect to the instance. If there are security restrictions in your organization that prevents the usage of public endpoint, the solution will require Azure Databricks to be injected into a Vet and use one of the connectivity architectures either using VNet Peering or VNet-to-VNet VPN Gateway to establish connectivity between Azure Databricks and SQL MI.

The solution artefacts and a detailed walkthrough of the various steps are published here.

The transformed dataset can be visualized by data visualization tools like Power BI, consumed by APIs in downstream applications or even visualized in a Databricks notebook as shown below.

Data visualization – Map

Note the predictions can be further refined by tuning the hyperparameters, by combining features from supplementary datasets and by leveraging other techniques to improve the accuracy of the model.

Conclusion

With continuous innovation to meet the growing demands of relational database services, Azure SQL offers the flexibility and deployment options to meet a wide variety of customers’ workloads ranging from real-time IoT streaming scenarios to business decision support systems with Hybrid Transactional and Analytical databases. This article touched upon one such scenario for interaction between predictive batch scoring using Azure Databricks and serving the data to consumers using Azure SQL. Find more scenarios and architectural references at the Azure Architecture Center.