15Feb 2016 by Grant Fritchey 11 Comments

Loading Data into Azure SQL Data Warehouse

Azure, Data Science
Ouch. Let's start with the level set. I'm not an ETL expert. In fact, I haven't done any professional ETL work for several years. My skills are, at best, rusty. With this in mind, I knew I'd have a hard time extracting data from a local database in order to move it up to Azure SQL Data Warehouse. I expected to be fumbling and slow and to constantly feel more than a little stupid as I made one mistake after another. All of this came to pass. Yet... OMG! THAT WAS DIFFICULT! Here's how I started. I defined a bcp command for the tables I was interested in. I ensured it was working correctly, then wrote a little PowerShell script so I could supply a list of tables and get…
Read More
25Jan 2016 by Grant Fritchey 7 Comments

Finding Your Query in Query Store

Azure, SQL Server 2016, T-SQL
Query Store is pretty amazing. I'm loving working with it. I think it's likely to change how query tuning will be done in the future. Lots of people are probably going to just use the reports and tools in SQL Server Management Studio. However, a pretty healthy chunk of us will start using the system views in order to programmatically access the information stored in Query Store. One of the first things you're going to want to do is track down your query. The primary views you'll want are sys.query_store_query and sys.query_store_query_text. They join together based on the query_text_id. Let's take four scenarios and see if we can retrieve the correct query. First up, an ad hoc query: SELECT e.NationalIDNumber, p.LastName, p.FirstName, a.City, bea.AddressTypeID FROM HumanResources.Employee AS e JOIN Person.BusinessEntityAddress AS…
Read More
18Jan 2016 by Grant Fritchey 15 Comments

Removing All SQL Server Query Store Data

Azure, SQL Server 2016
While setting up example code for my presentation at SQL Cruise (which is going to be a fantastic event), I realized I wanted to purge all the data from my Query Store, just for testing. I did a series of searches to try to track down the information and it just wasn't there. So, I did what anyone who can phrase a question in less than 140 characters should do, I posted a question to Twitter using the #sqlhelp hash tag. Jamey Johnston (t|b) came through... and it was right there in the documentation that I had been reading, over and over. In fact, it was in the documentation in two different places. Reading is clearly a problem for me today. Just so that you know, it's actually really easy:…
Read More
12Jan 2016 by Grant Fritchey No Comments

“Applies To…” in the MSDN Documentation

Azure, SQL Server 2016, T-SQL
Quick little post. I just wanted to share how happy I am with the new "THIS TOPIC APPLIES TO" infographic. An example here: I think it makes things much more clear when you're attempting to figure out what's up with some T-SQL syntax. Well done Microsoft and thank you. Side note, this only exists in documentation that has been updated recently. I first saw it in some documentation that was updated January 11, 2016. It's not there in another piece of documentation I saw that was updated October 15, 2015. Here's hoping it gets put everywhere. It works.
Read More
11Jan 2016 by Grant Fritchey 13 Comments

Restoring a Database in Azure

Azure
One of the many small things you don't have to worry about any more when working with Azure SQL Database are those pesky backups... Ha-ha-ha! Yeah, I can't keep a straight face when I type it. Microsoft takes on the role of DBA and creates backups and log backups for you when you're working in the Platform as a Service offering of Azure SQL Database. If that doesn't scare you, I'm not sure what will. However, there's good news. There are still ways for you to create your own backup (through the bacpac, I outlined it here, years ago). More good news is, Microsoft's backups actually work. Testing it out is easy. Let's walk through it once. I'm going to assume you have an Azure account on which you already…
Read More
07Dec 2015 by Grant Fritchey No Comments

Learning R: Foundations

Azure, DevOps, Professional Development, SQL Server 2016
Learning a programming language is largely an act of using that language to do stuff. Done. However, the big thing about R is the mathematical and statistical analyses that can be easily run against your data sets. This means, part of learning this language is learning another, that of data science. I'll be posting about how I'm learning R, but I also should tell you how I'm picking up on Data Science. First and foremost, madman he may be, but one of the few sources of information that I simply trust is Buck Woody. He's been running a series on Data Science. Here's an excellent example on how to pick a particular algorithm. These are must reads. Next, I'm starting a book called Data Science for Business: What you need to…
Read More
03Dec 2015 by Grant Fritchey 4 Comments

Changing Course On Learning

Azure, Database Lifecycle Management, DevOps, DocumentDB, Professional Development
With all the new stuff on the Microsoft Data Platform, it's really hard to keep up with it all. I had announced my plans to charge down the DocumentDB road to try to get the basics of that in my head along with learning some JSON so I could get what all the hoopla is about. However, after a lot of thought and some extensive meetings at Redgate, I'm looking to shift my learning in a new direction. First up. Arrrrrrrrr! No, it's not yet "Talk Like a Pirate Day." I'm going to start learning the R language. It's a language for statistical computing and is one of the many underpinnings for what's going to be happening with a lot of the Machine Learning capabilities in the Data Platform. With Azure…
Read More
01Dec 2015 by Grant Fritchey No Comments

It’s Not Too Late

Azure, Professional Development, Redgate Software, SQL Server 2016
You know you want to go on the SQL Cruise. You can. You just have to convince the boss that it's worth doing. It is. I've said it before and I'll repeat it as necessary, SQL Cruise changes peoples lives. I've watched people go on the cruise with a job and come back with a career. People don't just learn on the Cruise. They get energized. They get engaged with the data professional community. How do I profit by promoting SQL Cruise? I don't. Tim Ford is a friend and I'm supporting him. My company, Redgate Software, is a sponsor of the cruise, so I'm supporting them. I could just be doing the bare minimum in support of these parties. However, I'm not getting paid anything special by anyone for doing more.…
Read More
14Oct 2015 by Grant Fritchey 2 Comments

Getting Started With DocumentDB

Azure, DocumentDB
I've put this off for too long. It's time to get my feet wet with some new tech. Step 1 is easy. Go to the Azure portal and start the process for creating a DocumentDB: While that's running, let's see what's on the interwebs about getting started in DocumentDB... Nice. I know I'm going to have write a little code to exercise this thing. Here's a great run-through on exactly how to do it. Actually, the first hit when I searched on "Getting Started With DocumentDB." Microsoft has a start page on DocumentDB, but it was clearly put together by someone from marketing. Scroll down to the bottom. There are a couple of interesting links including SQL Query Within DocumentDB. Now we're talking. Here's a Curah! or Docs.com (which is…
Read More
05Oct 2015 by Grant Fritchey 1 Comment

Trace Flags in Azure SQL Database

Azure, SQL Server 2016, T-SQL
One of the ways that you take more direct control over your SQL Server instances is through the use of trace flags. There are a number that people recommend you enable by default. Prior to Extended Events for example, I'd say you should turn on trace flag 1222 in order to capture deadlock information on your server (now I just recommend you use the system_health session). I absolutely think you should turn on trace flag 2371 to get better behavior out of your automated statistics updates. There are others that I'll leave to all the systems experts to advise you on. What about Azure SQL Database? I doubt you'll be shocked, but if I try this: DBCC TRACEON (2371,-1); I get the following error: Msg 2571, Level 14, State 3,…
Read More