PASS Summit 2017

Azure, PowerShell
Don't you want to go to the single largest collection of Microsoft Data Platform professionals and developers on the planet? Sure you do. PASS Summit 2017 is coming up. Now is the time to register for this unique event. This year I've been honored to be able to present a session at the PASS Summit. It's on three subjects that are very near and dear to my heart, automation, PowerShell, and the Azure data platform. The session is called Using PowerShell to Automate Your Azure Databases. It covers a lot of material from controlling your Azure SQL Databases to creating Azure SQL Data Warehouse databases to automating maintenance. We'll even get your Azure PowerShell installed & working locally. I'm very excited about this and I sure hope to see you…
Read More

MySQL and Backups As A Service

Azure
With today's announcement that MySQL is available as a Platform as a Service (PaaS) offering through Azure, a lot more exciting opportunities have presented themselves for companies to build and manage their information. According to the DB-Engines Ranking, MySQL is the second most popular data management system out there. At last, you get to incorporate it directly into your Azure eco-system. While there are tons of reasons this is exciting, I'm going to focus on one very particular issue, backups. Why Are Backups Important? I'm not going to answer that question. Everyone knows that backups are important. Everyone knows that they need to have backups. Yet... There is example after example where people either haven't bothered to set up backups or didn't know what a real backup entails, or even…
Read More

General Notes on Security in Azure SQL Data Warehouse

Azure
I'm still learning about Azure SQL Data Warehouse (ADW, cause I'm lazy). ADW is a deceptively deep topic. Initially you think that it's just SQL Server, what's the big deal. Then you start to understand the underlying architecture and things get complicated. Then you begin to understand the implications of the architecture and things get down right arcane. However, I'd like to talk about some relatively easy concepts around security in your Azure SQL Data Warehouse. For lots more detail, see the excellent documentation provided by Microsoft. Firewall Security The single most important aspect of security in and around Azure is the fact that for the public facing aspects (and the database stuff is public facing), there is a built-in firewall. This firewall is enabled by default and actually can't…
Read More

Alerts From Azure Automation

Azure
In a previous post, I showed how to set up statistics maintenance for your Azure databases using Azure Automation. However, what I didn't show was how to generate an alert when things go south. Let's do that now. An Error Needing an Alert First, I need to generate an error. I'm going to modify the code just slightly from the previous example so that it will fail: $Cmd=new-object system.Data.SqlClient.SqlCommand("UPDATE STATISTICS dbo.TableNotInDB", $Conn) If I modify my Runbook with the code above and then run it, I will get an error: This is from the test pane. I strongly recommend you use tests on your PowerShell scripts when writing your own automation. It'll save you a lot of pain trying to troubleshoot things later. Alert on Error Believe it or not,…
Read More

Learning Azure From The Web

Azure
I'm working on a technical blog post that I hope to be putting up soon. I've run into a number of configuration issues that I'm working through. However, it's these issues that sparked this blog post. See, we're learning Azure all wrong. Use Google/Bing What's the first thing you do when you hit a problem on your computer, regardless of the language, the technology, etc.. Wrong! You don't call your son-in-law who "works in computers." That's what my mother-in-law does. We already "work in computers" so we have another resource. Bingle/Ging/Boogle. We run a search. So, let's say for example I'm interested in learning about an Azure Powershell command: Set-AzureRMDiagnosticSetting. Here are the first two results from Google (as of this date, 4/3/2017, or for all my UK friends, 3/4/2017,…
Read More

Scheduling Statistics Maintenance in Azure SQL Data Warehouse

Azure
The power and capabilities of Azure SQL Data Warehouse are pretty amazing. However, it's still basically SQL Server under the covers. SQL Server still needs maintenance and one type of maintenance is keeping statistics up to date. Statistics maintenance is doubly important within Azure SQL Data Warehouse because statistics are not created automatically, nor are they maintained automatically. It's up to you to ensure your statistics are up to date. Statistics in Azure SQL Data Warehouse It's worth re-emphasizing the point that there is no automatic creation of statistics in Azure SQL Data Warehouse. This means you need to plan to add statistics to your tables. Microsoft recommends an easy method is to add them to every column. While that does make things easy, it's not like statistics are free. If you…
Read More

Azure Data Platform Resources

Azure
A few months ago I created a GitHub repository for the purpose of building and maintaining a list of resources for Azure Data Platform training. You can see it here. My goal in putting this into GitHub instead of just running it on my blog is to make it a community resource. I want all of you to maintain it. If you're teaching a class (one hour or one week, I don't care), I'd like you to add yourself to the instructors list. If you have a blog where you post Azure content, please, add your blog. Are you a PowerBI monster? Get on the list. Please, help me create and grow this list so that people have a central, public, resource for this information. More and more of you are…
Read More

Azure SQL Data Warehouse Execution Plans

Azure
Azure SQL Data Warehouse can sometimes feel like it's completely different from SQL Server, but under the covers, it is still (mostly) SQL Server and it does have execution plans. Let's take a look at one. I've created a new SQL Data Warehouse using the sample database available on the portal, AdventureWorksDW. Here's a query against that database: SELECT dd.FullDateAlternateKey AS OrderDate, dc.LastName, SUM(fis.SalesAmount) AS SumSalesAmount FROM dbo.FactInternetSales AS fis JOIN dbo.DimDate AS dd ON fis.OrderDateKey = dd.DateKey JOIN dbo.DimCustomer AS dc ON dc.CustomerKey = fis.CustomerKey GROUP BY dd.FullDateAlternateKey, dc.LastName HAVING SUM(fis.SalesAmount) > 5000.0 ORDER BY OrderDate DESC; If I attempt to capture an execution plan using the SQL Server Management Studio GUI, nothing happens. If I try to use T-SQL commands, I get an error that those commands are…
Read More

Updates to Azure Data Platform Learning Resources

Azure
I'm acting as the curator to a list of Azure Data Platform Learning Resources. It's speakers and teachers, blogs, articles and more, all focused around the Azure Data Platform. I have it hosted on GitHub so that anyone can contribute. There have been several updates recently that you may want to take a look at. If you're looking for where and how to get started in the Azure Data Platform, this is a great resource to get you going. If you're teaching the Azure Data Platform, anything from PowerBI to DocumentDB to Azure SQL Data Warehouse and all the stuff in between, please add your information so that this list is up to date.
Read More

Query Store, Force Plan and Dropped Objects

Azure, SQL Server 2016
I love the Query Store. Seriously. It’s a huge leap forward in the capabilities of Azure SQL Database and SQL Server in support of performance monitoring and query optimization. One of my favorite aspects of the Query Store is the ability to force plans. Frankly though, it’s also the scariest part of the Query Store. I do believe that plan forcing will be one of the most ill-used functions in SQL Server since the multi-statement table-valued user-defined function (don’t get me started). However, unlike the UDF, this ill-use will be because of poor understanding on the part of the user, not a fundamental design issue. No, plan forcing and the Query Store are very well constructed. Let me give you an example of just how well constructed they are. Let’s…
Read More