I've been exploring execution plans in Azure SQL Databases a lot lately. I'm getting a presentation together for  some upcoming SQL Saturday events (first one is SQL Saturday #177, Silicon Valley). If you scroll to the bottom of this previous post, I mentioned that there were clearly differences in the optimizer because queries against empty databases were generating different plans. I've loaded up the data in my database, both SQL Server and SQL Database, so I can compare real behaviors. Doing so, I found a fun difference, even though I was running the query and generating the plan from SQL Server Management Studio. Here's the property sheet from the SELECT operator for the query run against SQL Server: And here's the property sheet from the SELECT operator for the query…
In my previous post showing how to get to execution plans in the Database Management Portal, I showed that it's pretty easy to put a query in a query window and get the execution plan. This allows you to understand query behavior in order to tune your T-SQL or your data structures, all through the Azure interface. But, what happens if you want to share an execution plan with a friend, post it to an online forum, save it for later comparisons as part of troubleshooting bad parameter sniffing, track behaviors over time as statistics change, other purposes that I can't think of at the moment? To first answer this question, let me tell you how you would do these things in SQL Server Management Studio (SSMS). First, and most…
If you've been working with the Azure SQL Database portal, you know that you have a query window. Within the query window you can run queries against your server. You'll get back results, timing, everything you need to observe behavior within the system. What's that? You never heard of this? Let's figure it out real quick. Here's my portal to my Azure SQL Databases: [caption id="attachment_2296" align="alignnone" width="150"] Azure SQL Database Portal showing usage overview[/caption] Yes, all sorts of things you can do from here, but we're focused on the bottom of the screen at the Manage button. Clicking this button will open up the Management portal window, part of which you can see below: [caption id="attachment_2298" align="alignnone" width="150"] Part of the Azure SQL Management Portal showing some of what…
When last we left our intrepid hero he had successfully deployed to SQL Azure using the Data-Tier Application Package, a DAC pac. It was easy and I had a database in place in nothing flat. There really weren’t any issues worth mentioning. I wasn’t crazy about the fact that unless I had Visual Studio 2010 I couldn’t edit the Data-Tier apps or get them into source control, but there you. So, assuming this is a real production application, I’ve just realized that I need to get a new procedure into my database. If I just got and run the script to create the procedure then I’ll be breaking the link between my database and the DAC pac (and yes, I still enjoy saying that knowing that blood pressure is rising…
You’ve set up your access to a SQL Azure site so you’re ready to go. Now how do you get your database up there? Well, TSQL works just fine, so you can script it all out to a file or set of files and apply them. Because you can connect with SSMS, you could use the GUI to build out your database. You can also use the Azure development platform and it’s web based GUI to create your database structures. Personally, the scripting method doesn’t seem too bad to me because you can get your code into source control that way. But, Microsoft has something additional in mind. It’s called Data-tier Applications or DAC for short (and yes, there are other things called DAC in SQL Server, what can I…
In case you don't know, I've been writing a series of articles over on Simple-Talk as I learn PostgreSQL. It's all from the point of view of a SQL Server person, expanding into a new technology. In other words, a true story. I thought I'd take a moment here on my own blog to talk about the tools I'm using and why I chose those. AWS RDS Let's establish up front, I'm lazy. Very lazy. So yeah, I'm trying to learn this new technology, but I'm going to find as many ways to use the knowledge, skills & tools I already have as I can. Now, I first started learning PostgreSQL because I wanted to learn more about how Flyway works. Further, as I also needed to learn how to…
If you're attempting to implement automation in and around your deployments, you're going to find there is quite a steep learning curve for DevOps and DevOps-style implementations. Since adopting a DevOps-style release cycle does, at least in theory, speed your ability to deliver better code safely, why would it be hard? Why is there a Learning Curve for DevOps? I recently did a presentation on a simple Continuous Integration process. Here are the tools that I used in the demo: Local Git repositoryVS CodeAzure Data StudioAWS CodeCommitAWS CodePipelineAWS CodeBuildAWS RDS PostgreSQLShell commandsYAMLDockerFlyway Also, I regularly present using Azure DevOps Pipelines too. Here's the list of tools that might be in a given demo: Local Git RepositoryVS CodeAzure Data StudioAzure Git RepositoryAzure DevOps PipelineAzure DevOps AgentRedgate DeployAzure SQL DatabaseYAMLPowerShelltSQLt While there…
If you're working with the Microsoft Data Platform, you should be, at the least, exploring Azure Data Studio as a new tool in your toolbox. One of the big reasons for this is the inclusion of Jupyter Notebooks. For those who don't know, Jupyter Notebooks are an open source documentation tool that lets you combine text and pictures with live code. From this we can talk about runbooks that you can share with people, lessons in combination with videos, presentations, interactive software documentation and lots more. I'm myopically focused at the moment on Azure Data Studio, but there are a lot of other places and ways to create or consume notebooks. However, I'm going to keep my focus. The issue I'm running into, is distributing the notebooks. Where to go…
In April, I said I was going to start learning Jupyter Notebooks. It's November. Let's get going with your first Jupyter Notebook. A quick aside before we start. I think one of the huge strengths that is going to come out of these things is as a runbook. You can share a notebook with someone, they can run the queries on it against their own systems and return the book, with the results to you. That's going to be extremely useful as a troubleshooting tool, but has all sorts of other functionality as well. I strongly suggest you start learning these things, as I am. Azure Data Studio There are a number of ways to create and consume Jupyter Notebooks, but I want to focus on the functionality around data…
Not really, but sort of. The beauty of containers, at least in a dev/test environment, is the ability to spin them up while you need them and then throw them away when you're done. Containers give you a bunch of functionality not otherwise available through a VM. However, once you've spun up a container, they're so dull. Why Are Containers Boring Grant? I'm so glad you asked. Last week I was presenting at SQLIntersection (great show, you should consider attending). I was talking about Query Store in SQL Server 2019. One person in the audience asked, "Can Query Store run inside a container?" I responded, "Great question, let's check." I then switched over to VS code to show this: docker run ` --name DemoSharedVol ` -p 1460:1433 ` -e "ACCEPT_EULA=Y"…