14Aug 2017 by Grant Fritchey 1 Comment

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
10May 2017 by Grant Fritchey 4 Comments

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
08May 2017 by Grant Fritchey 9 Comments

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
04Apr 2017 by Grant Fritchey 4 Comments

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
03Apr 2017 by Grant Fritchey 3 Comments

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
18Jan 2017 by Grant Fritchey 7 Comments

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
20Dec 2016 by Grant Fritchey 1 Comment

Database Configuration

Azure, SQL Server 2016
It's amazing just how much the landscape changed with the release of SQL Server 2016 SP1. For example, I just found out that you can disable parameter sniffing at the database level using the database configuration. Not only does this work for SQL Server 2016 SP1, but it's enabled for Azure SQL Database. How Database Configuration Works The syntax is very simple and documented here. So, if I want to disable parameter sniffing for a single database, I can do this: ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; That's it. Done. It works from within the database and doesn't require rebooting or anything else. Changing this setting does flush the cache of all the execution plans for that database. No other actions are necessary. You can control parameter sniffing at the…
Read More
12Dec 2016 by Grant Fritchey 7 Comments

OPTIMIZE FOR Hints When Parameter Sniffing is Turned Off

Azure, SQL Server, SQL Server 2016, T-SQL
While presenting recently and talking about dealing with bad Parameter Sniffing, I got the question; what happens to OPTIMIZE FOR hints when parameter sniffing is disabled? This is my favorite kind of question because the answer is simple: I don't know. Parameter Sniffing For those who don't know, parameter sniffing is when SQL Server uses the precise values passed into a query as a parameter (this means stored procedures or prepared statements) to generate an execution plan from the statistics using the value from the parameter. Most of the time, parameter sniffing is either helping you, or is not hurting you. Sometimes, parameter sniffing turns bad and hurts you quite severely. Usually, but not always, this is because you either have severely skewed data (some data is very different than the rest, lots…
Read More
05Dec 2016 by Grant Fritchey 3 Comments

Presentations for SQL Server Beginners

Azure, PASS, PowerShell, Professional Development, SQL Server, SQL Server 2016
[caption id="attachment_2548" align="alignleft" width="300"] Tired from all those blog posts[/caption] For my final blog post in the #gettingstarted, #iwanttohelp series, I decided to curate a set of presentations from the PASS Virtual Chapters. This content is available online. It's free. Most importantly for the person just getting started as a SQL Server data pro, it's good. I'm going to marry each of the presentations with my eleven blog posts in this series. The Importance of a Full Backup in SQL Server For this one I'm going to recommend Tim Radney's session Understanding SQL Server Backup and Restore. I know Tim personally and guarantee this is a good session. Why Is The Server Slow Jes Borland is a very close personal friend and an absolutely outstanding presenter (and person). She has…
Read More
09Nov 2016 by Grant Fritchey 1 Comment

Azure Data Platform Learning Resources

Azure
I know that more and more of you are moving more and more resources into the Azure Data Platform. In an effort to help you learn about all that Azure offers I created a GitHub repository of blogs, teachers, courses, etc. It's open to the community to update. New material has been recently added and there will be more and more all the time. Check it out. If you know of resources that should be listed there, please issue a branch pull and add them right in. This is your resource.
Read More