20May 2019 by Grant Fritchey 11 Comments

Learning Containers

DevOps
I find that I'm using containers more and more to get things done with SQL Server. They're so easy to set up for testing, spin 'em up, do stuff, turn 'em off, done. So, as I learn more and more about them, I figured it was time to start to share that learning here on the blog. First up, I'm NOT an expert on this topic. The two best people I know currently on this are Anthony Nocentino and Andrew Pruski. Those are the people you really should be learning the details from. I'm going to try to start to cover the introductory level of containers, Docker, and, at some point in the future, Kubernetes (maybe) and other orchestrators. However, I know that as my knowledge of how to work…
Read More
06May 2019 by Grant Fritchey 2 Comments

Query Store, Plan Forcing and Table Variables

SQL Server, T-SQL
This weekend I was in Stockholm in Sweden, talking Query Store and plan forcing with Steinar Anderson, when he mentioned the problems he had while forcing plans that had table variables in them. Don't panic. Of course you can force a plan with a table variable, most of the time. Steinar had a fairly focused problem. Before I go on to explain the issue, let me be really clear, Steinar figured out the issue all on his own. When he outlined the problem, I saw immediately what his conclusion was going to be. What's spurring this blog post is that Steinar said he'd searched on the internet and no one had talked about the issue yet. So, let's talk about it. Plan Forcing With Table Variables First up, let's show…
Read More
15Apr 2019 by Grant Fritchey 2 Comments

Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

Database Fundamentals, SQL Server
The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you have to include whether or not you’re dealing with a RIGHT or a LEFT JOIN. The OUTER word, just like the INNER key word, is not required. OUTER JOIN Imagine a situation where you have a list of people. Some of those people have financial transactions, but some do not. If you want a query that lists all people in the system, including those with financial transactions, the query might look like this: SELECT p.LastName, ft.TransactionAmount, ft.TransactionDate, ft.TransactionTime FROM Personnel.Person AS p LEFT JOIN Finance.FinancialTransaction AS ft ON p.PersonID = ft.PersonID; Except for the addition of the LEFT key word, this…
Read More
08Apr 2019 by Grant Fritchey 5 Comments

Learning Jupyter Notebooks

Azure, Professional Development, Tools
I'm starting the process of learning how to use Jupyter Notebooks. Notebooks are documents that contain live code, commentary, results, pictures and more. Jupyter Notebooks are used for presentations, documentation, run books, troubleshooting guides and lots more. Their support within Azure Data Studio opens up lots of opportunities. Azure Data Studio If you're interested in learning about notebooks yourself, or, as I publish the notebooks that I put together and you want to consume them, you need to have a mechanism. There are any number of third party or open source solutions to read notebooks. However, since I'm focused primarily on the Microsoft data platform, I'm using Azure Data Studio to do this work. I've written in the past about using Azure Data Studio (ADS). I also have a bunch…
Read More
04Mar 2019 by Grant Fritchey 4 Comments

system_health Extended Events in Azure SQL Database

Azure, SQL Server
The system_health Extended Events session is incredibly useful. Further, it's running, by default, in every server you have under management that is 2008 or greater. Things are not the same in Azure though. system_health in Azure SQL Database If you look at the documentation for system_health, it shows that it's applicable to Azure SQL Database. However, if you try to run the example query, it won't work. This is because the implementation of Extended Events inside Azure SQL Database is a little different. Instead, you need to use the Azure SQL Database equivalent system views to create the same query like this: SELECT CAST(dxdst.target_data AS XML) FROM sys.dm_xe_database_session_targets AS dxdst JOIN sys.dm_xe_database_sessions AS dxds ON dxds.address = dxdst.event_session_address WHERE dxds.name = 'system_health'; Now, running this in Azure, prepare to be…
Read More
25Feb 2019 by Grant Fritchey 13 Comments

How Do You Export A Database in Azure Data Studio

SQL Server, SQL Server 2016, SQL Server 2017
I've been writing a bunch about Azure Data Studio. I've also been recording videos on the topic. A comment I received recently asked how to export a database from Azure Data Studio. It made me want to explore the topic of exporting a database as it relates to Azure Data Studio. Export? When we say export, what exactly do we mean. It could be as simple as exporting data to a flat file for consumption in Excel or something. It could be creating a backup. Maybe we mean creating a bacpac file. We could also be looking at creating individual scripts for objects within the database. Finally, what about a full export of the database object definitions? Any or all of these could be what the question was about. So,…
Read More
18Feb 2019 by Grant Fritchey 6 Comments

Installing Extensions to Azure Data Studio

Azure, SQL Server 2016, SQL Server 2017, T-SQL
If you're even thinking about experimenting with, let alone actively using, Azure Data Studio, you need to plan on installing a few extensions. Buck Woody has a great list that you should look through in this blog post. If you're just getting started with Azure Data Studio, I have an introduction here. Depending on the extension, this could be a simple as a mouse click. However, not all the extensions are that easy. Let's explore this just a little so when you do start using Azure Data Studio, things are easy. Extension From a Mouse Click For this bit of the blog post, we'll stick to nothing but mouse clicks, but, if you really want to bring the power within Azure Data Studio, you really need to learn keyboard shortcuts…
Read More
11Feb 2019 by Grant Fritchey 2 Comments

Query Store and a READ_ONLY Database

T-SQL, Tools
What happens in Query Store when the database itself is READ_ONLY? Yeah, I don't know. Let's find out. READ_ONLY The only way to find out how this works is to test it. So, let's write some code: CREATE DATABASE testquerystore; GO ALTER DATABASE testquerystore SET READ_ONLY; GO ALTER DATABASE testquerystore SET QUERY_STORE = ON; Executing that resulted in a small glitch in the Matrix: 8:00:54 AMStarted executing query at Line 1Commands completed successfully.8:00:54 AMStarted executing query at Line 2Commands completed successfully.8:00:54 AMStarted executing query at Line 5Msg 5004, Level 16, State 6, Line 5To use ALTER DATABASE, the database must be in a writable state in which a checkpoint can be executed.Msg 5069, Level 16, State 1, Line 5ALTER DATABASE statement failed.Total execution time: 00:00:01.448 Well that's not going to…
Read More
04Feb 2019 by Grant Fritchey 3 Comments

Combine Extended Events and TagWith to Monitor Entity Framework

SQL Server 2016, SQL Server 2017, T-SQL
I'm going to start with a sentence that makes a lot of people crazy; As a DBA and database developer, I love Entity Framework. That's right. Entity Framework is the bomb. It's amazing. It does wonderful stuff. Entity Framework makes the developers lives and jobs easier. It makes my life and job easier. Yes, Entity Framework will improve your job quality and reduce stress in your life. With one caveat, it gets used correctly. That's the hard part right? There is tons of technology that makes things better, if used correctly. There are all sorts of programs that make your life easier, if used correctly. Yet, all of these, used incorrectly, can make your life a hell. One nit that I've always had with Entity Framework is that it's very…
Read More
28Jan 2019 by Grant Fritchey 1 Comment

Input Into Azure Data Studio

Azure
I see more and more people starting to use Azure Data Studio. As it keeps growing and expanding, it's going to become more and more a go-to tool for everyone that has to work with the Microsoft Data Platform. Wouldn't it be good to be able to provide direct feedback to the development team? Why not do that? GitHub Microsoft does a heck of a lot work with GitHub. It just so happens that the Azure Data Studio team is using it as well. In fact, they have a public Issues resource within GitHub. You can go there yourself, search out the existing issues, submit a problem, or, better still, submit a suggestion. The people working on this are clearly busting their behinds to add functionality and improve functionality. You…
Read More