Press "Enter" to skip to content

Category: Cloud

Azure SQL Database Maintenance

Published 2016-08-23 by Kevin Feasel

Jeffrey Verheul mentions that Azure SQL Database databases need regular maintenance, too:

Before I’m going into detail, I want to give full kudos to Ola Hallengren (Website | @olahallengren). He has spend a lot of his time to build a SQL Server Maintenance Solution that is completely free for everyone to use. And he did such an excellent job a lot companies (also huge companies) use his solution to run maintenance tasks on their databases.

None of the scripts below are written by me, but only small changes are made in order to make things more clear when the solution is deployed to an environment. The original scripts can be downloaded via the download page on Ola’s website.

Most of the to-dos are the same between on-premises and Azure SQL DB, but some of the implementation steps are a bit different.  This is worth checking out if you have any Azure SQL Database instances.

Comments closed

Getting Started With Azure SQL Data Warehouse

Published 2016-08-22 by Kevin Feasel

Warner Chaves looks at Azure SQL Data Warehouse:

The first thing to keep in mind is that ASDW was designed to be a cloud based system. As such, it aims to be very flexible for resource allocation and very efficient to scale up or down. To meet those goals, the system allows you too:

  • Increase or decrease compute power represented by Data Warehousing Units.

  • The amount of storage can grow and is charged independently from the compute power.

  • The compute power can be completely paused and only storage is payed at that point.

Warner also has a brand new Pluralsight course on the topic.

Comments closed

Calling Azure ML Web Services Using Data Factory

Published 2016-08-22 by Kevin Feasel

Ginger Grant shows how to call an Azure Machine Learning web service from within Azure Data Factory:

The Linked Service for ML is going to need some information from the Web Service, the URL and the API key. Chances are neither of these have been committed to memory, instead open up Azure ML, go to Web Service and copy them. For the URL, look under the API Help Pagegrid, there are two options, Request/Response and Batch Execution. Clicking on Batch Execution loads a new page Batch Execution API Document. The URL can be found under Request URI. When copying the URL, you do not need to include any text after the word “jobs”. The rest of the URL, “?api-version=2.0”. Copying the entire URL will cause an error. Going back to the web Services page, The API Key appears on the dashboard section of Azure ML and there is a convenient button for copying it. Using these two pieces of information, it is now possible to create the Data Factory Linked Service to make the connection to the web service, which here I called AzureMLLinkedService

Read the whole thing.

Comments closed

Azure SQL Database Alerts

Published 2016-08-18 by Kevin Feasel

Julie Koesmarno shows how to set up Azure SQL Database alerts:

Over the last year, I have been intentionally seeking out to get feedback from the community via various SQL events, particularly those who plan to use or are currently using Azure SQL Database. A lot of questions have come up about managing Azure SQL Database better – i.e. being more proactive and more responsive in managing Azure SQL Database. One of the ways to be more proactive about your SQL Database is by setting up alerts. As an example, you can create an alert in case DTU goes above 95% – say in the last 5 minutes, so that you can either investigate why this might be or upgrade it to a higher SKU.

This article walks through how you can setup an Alert on Azure SQL DB.

I really like the fact that they offer web hooks; that way, I can integrate these alerts with Slack or other messaging systems.

Comments closed

Azure SQL Database Versus SQL Server

Published 2016-08-11 by Kevin Feasel

Kenneth Fisher learns about differences between Microsoft’s Azure SQL Database and their on-premises (or IaaS) SQL Server:

T-SQL Differences in Azure SQL Database

I used to think this was the real difference between SQL Server and SQL Database. I was wrong. Really wrong. But it’s a good place to start. Now from what I can tell everything in Azure is a moving target. There are constant changes so it’s important to know where the documentation is. In this particular case here it is: Azure SQL Database Transact-SQL differences.

Check it out.  The differences are smaller than in the past, but I expect that there will always be some differences—particularly on the administration side—due to the nature of Azure SQL Database as a PaaS offering.

Comments closed

Microsoft R Server On Spark

Published 2016-08-10 by Kevin Feasel

Max Kaznady, et al, discuss using Microsoft R Server on Spark to perform rapid prototyping against the NYC Taxi dataset:

Once the cluster is created, you can connect to the edge node where MRS is already pre-installed by SSHing to r-server.YOURCLUSTERNAME-ssh.azurehdinsight.net with the credentials which you supplied during the cluster creation process. In order to do this in MobaXterm, you can go to Sessions, then New Sessions and then SSH.

The default installation of HDI Spark on Linux cluster does not come with RStudio Server installed on the edge node. RStudio Server is a popular open source integrated development environment (IDE) available for R that provides a browser-based IDE for use by remote clients. This tool allows you to benefit from all the power of R, Spark and Microsoft HDInsight cluster through your browser. In order to install RStudio you can follow the steps detailed in the guide, which reduces to running a script on the edge node.

If you’ve been meaning to get further into Spark & R, this is a great article to follow along with on your own.

Comments closed

Azure SQL Threat Detection

Published 2016-08-10 by Kevin Feasel

Ron Matchoro discusses use cases for Azure SQL Threat Detection:

“Thanks to SQL Threat Detection, we were able to detect and fix code vulnerabilities to SQL injection attacks and prevent potential threats to our database. I was extremely impressed how simple it was to enable threat detection policy using the Azure portal, which required no modifications to our SQL client applications. A while after enabling SQL Threat Detection, we received an email notification about ‘An application error that may indicate a vulnerability to SQL injection attacks’.  The notification provided details of the suspicious activity and recommended concrete actions to further investigate and remediate the threat.  The alert helped me to track down the source my error and pointed me to the Microsoft documentation that thoroughly explained how to fix my code.  As the head of IT for an information technology and services company, I now guide my team to turn on SQL Auditing and Threat Detection on all our projects, because it gives us another layer of protection and is like having a free security expert on our team.”

Anything which helps kill SQL injection for good makes me happy.

Comments closed

Considerations For Azure SQL Database

Published 2016-08-10 by Kevin Feasel

Grant Fritchey discusses whether new database administrators might want to start with Azure SQL Database rather than on-premises SQL Server:

Since you are right at the start of your career, you may as well plan on maximizing the life of the knowledge and skills you’re building. By this, I mean spend your time learning the newest and most advanced software rather than the old approach. Is there still work for people who only know SQL Server 2000? Sure. However, if you’re looking at the future, I strongly advocate for going with online, cloud-based systems. This is because, more and more, you’re going to be working with online, connected, applications. If the app is in the cloud, so should the data be. Azure and the technologies within it are absolutely the cutting edge today. Spending your limited learning time on this technology is an investment in your future.

This answer is a tougher call for me.  Looking at new database developers (or development DBAs or database engineers or whatever…), I think the case is pretty solid:  there’s so much skill overlap that it’s relatively easy to move from Azure SQL Database to on-prem.  With production DBAs, the story’s a little different:  as Grant mentions, this is a Platform as a Service technology, and so the management interface is going to be different.  There are quite a few commonalities (common DMVs, some common functionality), but Grant gives a good example of something which is quite different between the PaaS offering and the on-prem offering:  database backup and restoration.  I think the amount of skills transfer is lower, and so the question becomes whether the marginal value of learning PaaS before IaaS/on-prem is high enough.  Given my (likely biased) discussions of Azure SQL Database implementations at companies, I’d stick with learning on-prem first because you’re much more likely to find a company with an on-prem SQL Server installation than an Azure SQL Database.

Comments closed

Power BI Embedded

Published 2016-08-09 by Kevin Feasel

Reza Rad looks at Power BI Embedded:

Power BI Embedded is the reporting and analysis solution for mobile and web applications. Power BI Embedded is an Azure service that integrates Power BI solution into mobile and web applications. The report still has to be authored and created in Power BI Desktop. After creating the report it can be published into Power BI workspace in Azure, and using API Keys of Power BI workspace and embedding Power BI report frame into the web/mobile application it will be integrated into the application.

Reza walks through the process step-by-step.  The upshot is that you can take this report you created in Power BI and embed it into your own application, where you can apply your own in-app access controls.  There are limitations, which Reza spells out at the end of the post, so check it out.

Comments closed