Voice of the DBA

Tag: T-SQL Tuesday

  • T-SQL Tuesday #139–A Hybrid World

    tsqltuesdayIt’s time for T-SQL Tuesday in June 2021, with a new host this time. Ben Wiessman (b | t), who graciously responded to my request to host, and to whom I want to congratulate on an addition to his family.

    In any case, for #139, Ben asks us about hybrid data. He’s a cloud advocate and someone that has been working with Big Data Clusters and Azure Arc. In fact, he’s written books on those topics(Azure Arc-Enabled Data Services Revealed and SQL Server Big Data Clusters), Bid , so I expected a topic related to those.

    Moving to Hybrid

    I haven’t dealt with hybrid in production at all. My work with SQLServerCentral, and much with Redgate and customers, is all either on-premises or in the cloud. All the SSC stuff runs as IaaS, and most of Redgate does. While we have a lot of local workstations and environments for dev, much of the infrastructure is in the cloud, without much hybrid stuff.

    I do see customers that developer locally and deploy in the cloud, which is something I’ve done, but not too often. Most want to do everything in one place. I don’t really consider IaaS as a “hybrid” despite it being in the cloud, because these are just VMs that could be anywhere, and there isn’t any real change if they live in Azure, AWS, or a remote data center my company owns.

    Experimenting in the Cloud

    The exception for me is Spawn. This is Redgate’s research project on hosted cloud databases that you might use for local development. I started working with this when it was early in the lifecycle, and now it’s something that I use regularly.

    Essentially you create a database, and it’s hosted in a container somewhere. You get a connection string back, but the database lives in a cloud service, and this is a hybrid environment.

    It’s an interesting idea, and while I haven’t made any large databases, I do find that the ability to programmatically start a database to be quite fascinating and simple. A few times when I’ve looked to update a demo project, the ease with which I can set up an environment on a new machine is fascinating.

    The team that manages our main demo made a number of changes in 2020. I had scheduled a talk on the platform and realized a week before that the old demo I had was broken. When I contacted them, they gave me these instructions.

    • Update the CLI tool
    • Update my repo from the “demo” branch of the repo
    • Open the folder in VS Code
    • Hit F5

    That’s it and I had a running environment right away. I did a talk on this at ConFoo 2021, and it went well. The ease of getting things running even impressed me, as I hadn’t dealt with the front end side of things as often. Amazing.

    To me, this is where the hybrid cloud gets interesting, when I easily access resources either locally or remotely.

  • T-SQL Tuesday #138–Technology Changes

    tsqltuesdayThis month the topic for T-SQL Tuesday comes from Andy Leonard, whom I reached out to be a host. I was running low on hosts last year, and Andy agreed to help me out. I had expected something on ETL, but I was surprised with this one.

    Andy asks us about managing technology change. He does mention how SSIS changed as he was writing a book, meaning that things he’d learned while a project was underway no longer applied. That’s a scary place to be, and it’s one I’ve rarely experienced.

    Avoiding Shifts

    One reason I don’t like to test beta software, especially from Microsoft, is that the APIs, the functionality, the effects can change. I prefer to have something fairly well settled before I spend time on it.

    I took a chance early on with Windows 7, when they had the “skydrive”, which become OneDrive. I could sync files from my desktop to laptop, and for someone that travels a lot, this was fantastic. It worked through the beta and until a few years later. At some point it stopped and I moved to Dropbox. I still use DropBox, even though this was reborn as OneDrive and enhanced to allow me to have  personal and business “OneDrive” running at the same time.

    Moving on from Immature Software

    I don’t like to abandon software, and sometimes I can’t. For me, I’ve been involved with some of the products that Redgate Software has built. I often see things early and test them to give feedback to developers. This means I sometimes see things before they work well, or even before the development teams might know what they want to build.

    This has happened a few times, but a couple years ago we were evolving one of our products to expand capabilities for customers. I thought we were moving in a good direction and spend quite a bit of time evaluating how the software worked. I was even starting to incorporate this into some automation pipelines and build demos, when the product direction shifted.

    A fair amount of code I’d written had to be abandoned. I felt as though I’d wasted some time, but as I stopped to think, I realized that I’d been learning and growing in this area. I’d understood how things worked, and while I had to write new code, I was able to write it quicker because I understand the general problem space already. Even though I had to expend other effort, I’m not sure my early work was wasted.

    Fortunately, I didn’t have deadlines for a deliverable. If I did, I might have been more upset. If it happened in the future, while needing to work more to get things done might annoy me, I’d likely understand that the growth and knowledge mean I don’t have to double my investment, and likely I’d be better positioned to write better code the second time around.

  • T-SQL Tuesday #137–Wrap Up

    I hosted the blog party this month, with the invite to write about notebooks. These are a neat technology, and I’ve written about them at SQLServerCentral.

    This post is a wrap-up of the various responses to my invitation. First, quite a few people give credit to either Aaron Nelson or Rob Sewell for their writings and work with notebooks, so check out their blogs.

    With that, Rob’s post this month has a number of links to his notebook work and a list of how he has used them with clients.

    Aaron has kids, lots of them, and they keep him busy. However, he asked his entry this month to be a video where he discusses parameterizing notebooks.

    Rob Farley talks about executing a notebook from Powershell, which to me is one of the key components that makes notebooks usable for something besides teaching.

    Glenn Berry has a fantastic set of diagnostic DMV queries that are incredibly useful for anyone administering a SQL Server instance. He writes about his queries being bundled up in a notebook.

    Chris Johnson was disappointed his organization didn’t adopt notebooks, but found another use. He makes notes to help learn new T-SQL features in notebooks and has a Github repo with them in it.

    Benni De Jagere is rewriting some scripts into notebooks (with the help of PoSh and Bing), starting with extracting Power BI logs into csv files. Hopefully we’ll see him give a presentation on this process sometime in the future.

    Deepthi Goguri writes about how notebooks work, including giving us some magical commands that you might consider adding to your notebooks.

    Barney Lawrence uses notebooks often at work on a regular basis. One of his tasks is a notebook to run git commands for branches. He has also converted a series of blog posts on XML into notebooks that you can use to follow along with his posts.

    Andy Cutler writes about testing the flow of presentations in a notebook. He embeds images near the code to help walk through the process of setting up Synapse.

    Deborah Melkin writes about her challenges getting started, similar to mine. She is using these for some PoSh scripts, but the challenge of running some cells to set up variables and then other ones to do some work. The big win is keeping similar scripts together and documented with text around the code cells. She would like to see some things like execution plans, and I agree.

    Justin Bird has played with them, but not much use in practice. He did some research and learning after the invite and shares some thoughts.

    Julie Koesmarno tells us how she challenged herself to work on Python and microeconomics to build a chart that displays some data. Notebooks help her quickly get hands on and become a better learner. A great use of notebooks.

    Martha Clancy has a few ideas to make notebooks a part of your daily work. Some good tips in there in her humorous flow, which doesn’t sound great, but it shows there is some value in thrashing around a bit.

    Chris Voss tells us the best use he’s found. He puts a number of scripts together with documentation, even  when he can’t run the the code. A colleague can. He also would like to see execution plans in notebooks.

    Mikey Bronowski gives a few scenarios where he has found notebooks to be useful, notably learning and improving processes.

    Hugo Kornelis takes the approach that Microsoft is pushing notebooks a little too much, even in places they may not be appropriate, but he has a few use case: production support, data analysis, and presentations. Another vote for execution plans in notebooks.

    VR expert Todd Kleinhans uses notebooks to work with GPUs, SQL Server on Linux, containers, and RAPIDS. Quite a technical list.

    Linda Kovacheva discovered how powerful notebooks can be with Azure Machine Learning.

    Kevin Chant has been using notebooks for a number of things in the Azure Data Engineering Services at Microsoft.

    That’s the wrap. If I’ve missed anyone, let me know and I’ll update the post.

  • T-SQL Tuesday #137–Notebook Uses

    It’s actually my month to host T-SQL Tuesday, and I came up with the notebook idea last year. Aaron Nelson (b | t) sent me a link to a talk he did at a Meetup. It contained some interesting things, showing how you can use notebooks. Worth a watch.

    For me, I don’t use notebooks a lot in my work. I had thought about using them to show clients and customers how to use our products, but I hadn’t moved far in that direction. I think too many customers are still using SSMS primarily, and haven’t moved to ADS.

    The one place I’ve found them interesting is with Python. When I am sometimes trying to work through an issue, I think it’s easier to run a cell of code and get some results to work through an issue. If I use the REPL, I lose things. In VS Code, which I prefer, I don’t like the split code/terminal. I find that slightly annoying. Notebooks make things easier.

    I was doing this recently, as I tried to work on the Advent of Code. I tried different parts of an algorithm in different cells, just to see what the results were. This helped me to work out some logic.

    2021-04-13 10_02_06-● Day5.ipynb - Data Analysis - Azure Data Studio

    That’s not a great algorithm, and I got better over time, but this was really a good way for me to think about the sections of the problem, putting each on in a separate area.

    It’s a little of “teaching myself” within the construct of a notebook. After all, that’s what a lot of data scientists are doing when they start to go through different code items in a notebook.  They can share the notebook with others, which helps to teach.

    For me, it’s teaching myself.