In my Simple-Talk article Azure SQL Data Warehouse, I introduced you to SQL Data Warehouse and gave you an overview of the architecture and technologies that drive the service and make it all work. In this article, I go a step further and provide details about getting started with SQL Data Warehouse, demonstrating how to add a sample database and then accessing the server and database settings.
If you want to follow along with my examples and try out SQL Data Warehouse for yourself, you must have an active Azure subscription, even if it’s just the free trial. For those who have already used up their free trial, be aware that SQL Data Warehouse is a pay-as-you-go service, even though it’s still in preview, so unless you’re on an unlimited company budget or happen to have accrued MSDN credits, you’ll want to be judicious in how you try out the service. Fortunately, as you’ll see in this article, you can pause the compute resources when not in use, helping to minimize the costs associated with learning about the service.
This article is all about initial installation and configuration.
So I set about looking for a workaround. This week I think I’ve finally managed to get something working that approximates the number I need from that view, ms_ticks.
Attached is sp_whoisactive v11.112 — Azure Special Edition v2. Please give it a shot, and I am especially interested in feedback if you use the @get_task_info = 2 option when running sp_whoisactive. That is the main use case that’s impacted by the lack of ms_ticks information and my attempt at a workaround.
If you’re using on-prem SQL Server, this doesn’t add anything new, but if you’re on Azure SQL Database, give it a try.
I do have alerts set up on the Azure portal and in Application Insights to notify me when availability or performance thresholds are violated but I also need to know if there is a global or regional issue that might affect our app so that I can respond and notify the staff when appropriate. Azure status changes are reported on the Azure Status web page.
The following will describe how to use the Azure Status page RSS feeds and Outlook rules for notification if things go sideways in Microsoft Azure.
This is a good use of Outlook’s built-in RSS reader.
In the field, I see a lot of people using Availability Groups to have a near real-time replica for reporting. I talked a little bit about this above. What isn’t mentioned here is you have to maintain a Windows Failover Cluster, Quorum, Active Directory (Unless using Windows 2016 Preview) and more. This gets you a replica that is just a copy of the database. What does this mean? You cannot change database objects like security, indexes, etc. Also, what if you don’t need the whole database(s) for reporting? If not, you can replicate only the data you truly need.
So, let’s recap here. You only have to replicate the data that you need. You can have different security and indexes on your reporting subscriber database(s). The reporting subscriber database can be scaled up or down as needed based on your needs. The reporting database can now be an Azure Database. Folks, I call this a huge win!
There’s a lot more replication love out there than I’d expect. John promises to follow up with a guide on how to implement this, so keep an eye out for that.
As I’m currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?
To study that, I’ve put some diagrams together to show the possibilities of moving BI to the cloud. First, I’ll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).
One man’s opinion: there will be SSAS for Azure. I have no proof of this, and the nice part about having no proof is that I can throw out wild speculation without fear of violating NDA…. But to me, Power BI solves a different problem and acts in conjunction with SSAS rather than as its replacement. I also don’t see any technical reasons why SSAS couldn’t live in the cloud, and so that leads me to believe that it will be there eventually. But hey, it turns out wild speculation is occasionally wrong…
Extended Event (XEvent) feature is available as public preview in Azure SQL Database as announcedhere. XEvent supports 3 types of targets – File Target (writes to Azure Blob Storage), Ring Buffer and Event Counter. Once we’ve created an event session, how do we inspect the event session target properties? This blog post describes how to do this in 2 ways: using the User Interface in SSMS and using T-SQL.
It’s nice to see Extended Events making their way into Azure SQL Database.
There are two types of indicators for linear correlation, positive and negative as shown on the following charts. The Y axis represents Grades, and the x axis is changed to show positive and negative correlation of the amount of X on grades. When X is the amount of study hours, there is a positive correlation and the line goes up. When X is changed to watching cat videos, there is a negative correlation. If you can’t draw a line around the points there is no correlation. If I were to create a graph where X indicated the quantity of the bags of Cheese Doodles consumed on grades, it would not be possible to draw a straight line, where the data points cluster around it. Since this is Line-ar regression, if that line doesn’t exist there is no correlation. Knowing there is no correlation is also useful.
Simple linear regression is a powerful tool and gets you to “good enough” more frequently than you’d think.
As more and more customers are interested in moving some portion of their BI/analytics workloads to cloud services, one question that comes up occasionally is whether or not you should start with a marketplace image that has SQL Server already installed. So far I’ve noted a few key considerations for this decision:
Do you want to pay for the SQL Server license as part of the VM pricing?
Do you want to configure SQL Server in a specific way (i.e., following best practices)?
Do you want Azure to handle things like automated patching by default?
My rule of thumb is if it’s Express Edition or just for me to mess around with, I’m typically happy with an image. But for a production setup, I’d want the fine-grained control at installation time that you just won’t get.
Jupyter is an easy to use and convenient way of mixing code and text in the same document.
Unlike other reporting systems like RMarkdown and LaTex, Jupyter notebooks are interactive – you can run the code snippets directly in the document
This makes it easy to share and publish code samples as well as reports.
Jupyter Notebooks is a fine application, but up until now, you could only integrate it with Azure Machine Learning if you were writing Python code. This move is a big step forward for Azure ML.
First off, the word itself. The Cloud. What is The Cloud? It’s a server that you don’t own. You can’t touch it, it’s in someone else’s data center. It may or may not be virtual. Amazon’s Cloud or Microsoft’s or Google’s are several data centers with racks and racks of servers. They are physical, just not at your location. And they’re accessed across the Internet. This is something that we’ve been doing for 30 years, it’s called a Wide-Area Network, just scaled up bigger. We had bi-coastal WANs before the World Wide Web came along.
Four or five years ago, I was absolutely in agreement. Today, I’m 50/50, being near 100% for many types of servers (web servers, etc.) and closer to 25-30% for databases. My expectation is that those numbers will continue to shift upward as time goes on, but there will always be reasons not to migrate certain servers to someone else’s data center.