Voice of the DBA

Author: way0utwest

  • Daily Coping 22 Mar 2022

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to get really absorbed in an interesting or creative activity.

    I try to engage in some creative pastimes in my life, but often I don’t have the time to work on them. That’s been especially true lately.

    A couple of nights last week my wife was busy. Rather than read or watch TV, I got my guitar, opened up YouTube, and spent an hour working through learning a song. At least until my fingers were hurting.

    Didn’t completely learn anything, but I did improve some skills and enjoyed the time doing so.

  • Is CosmosDB the One?

    The question asked of Keanu Reeves in the first Matrix by many of the characters is whether he is the one. THe one that can save the world, and make a difference in their war. It’s a fun movie, and one I’ve enjoyed many times. Keanu doesn’t really believe he’s that special until the end. That’s in contrast to the products that most vendors build. Most of the times I think developers, marketers, salespeople, or all three think that whatever they’ve recently released is the best thing ever.

    CosmosDB is mentioned in a piece as the potential one database to rule them all. The article talks about some of the basis and value that come from the platform, which Microsoft continues to push as the database that can store any kind of data and meet the needs of any application. Whether you believe that or not, I find many articles and blogs from developers that are experimenting and building systems on top of CosmosDB.

    I do think the consistency models for CosmosDB are interesting, and having a variety of these models across a distributed RDBMS table would be great. I’d love to be able to insert data into a table, have it not available as a live value until committed on other nodes, and avoid locking my table. I realize I’m asking for something that seems like a bit of magic, but I bet Microsoft could add this to the SQL Server relational engine.

    CosmosDB is very interesting to me, and it’s an item on my list that I want to learn more about. I think there are certainly domains of problems where CosmosDB would fit very well. If any of you are working with CosmosDB, I’d be interested in knowing why and how it fits for your particular environment.

    There was a quote in the article that I thought was interesting. A Microsoft Technical Fellow said, “No data is born relational. In the real world, nobody thinks in terms of schemas — they think graphs or maybe JSON document if you’re an IOT device…”

    That’s interesting. If I produce a JSON document, am I not thinking relational? Do I not expect that most strings, values or arrays will exist in most of the documents? Am I not thinking schema, even ragged schema, from the beginning? I’d think most data relational. It’s just not easy to get it into that format when you deal with many instances of data.

    Of course, dealing with many instances of data is hard for most humans. That’s why we built relational databases.

    Steve Jones

  • Flyway Mistakes

    I have been doing some testing with Redgate’s Flyway Desktop as a new way of managing code for databases. However, just like Git, I appreciate clients, but I want to know how the CLI (command line interface) works. I spent time learning git add, git push, git checkout and more. Now I have more comfort understanding how SourceTree or GitKraken work.

    I wanted to do the same thing with Flyway, just to be sure that I know what the options, switches, and behavior for Flyway operations would be.

    The Scenario

    I had an existing database, and I wanted to play around with adding this to a DevOps flow. I was looking for a basic experiment, and decided to create a new repo. I copied the default flyway.conf file into this folder and changed it.

    The only thing I did was alter the Flyway conf file in my folder to work with SQL Server. I copied the connection string into the flyway.url parameter and set it as follows:

    flyway.url=jdbc:sqlserver://aristotle:1433;instanceName=SQL2017;databaseName=AdventureWorks2017;integratedSecurity=true

    When I ran the info command, it failed.

    fw_fail

    When I ran the same command with a different database, it worked:fw_succeed

    I was highly confused. I tried a number of different databases, and some of them worked, not I couldn’t see a pattern.

    I checked a number of things, including the database owners, a few of which I changed. I thought it might be some permissions and dropped my sysadmin account and added it back.

    I tried connecting with SSMS and with sqlcmd. Both of those tools seemed to work.

    I was really stumped.

    A Small Conflict

    Finally, after a bit of back and forth with a few developers, someone noted that I shouldn’t need the port included in the string. Sure enough, when I removed it, things started working.

    Apparently, the JDBC documentation notes the issue. I kept looking at Flyway docs, but they just pass things along to the JDBC driver from the various parameters and environment variables.

    There is a note that says provide the port number to stop a round trip to the browser to determine the port number for a named instance. If the port number and name are included, the port takes precedence.

    I have two instances, some of which have the same databases on each. The databases that worked were on a different instance (which responds to 1433). The ones that didn’t, weren’t on that instance. I kept examining the \SQL2017 instance, but that wasn’t the one I was logging into with my string.

    A silly mistake, but a good one to note. The port is a higher priority than the instance in a Java connection string.

    I can’t find a priority in the docs for OLEDB or the native client, but they do all say the address takes precedence over the address parameter.

  • Daily Coping 21 Mar 2022

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to stop. Breath. Notice. Repeat regularly.

    Yoga has helped me to appreciate this. I will stop and breathe while coaching, especially at tournaments. I’ve used this as I prep to deliver a session as well. Stop, breathe deep, often raise my arms and stretch.

    I’ve also found this good for focus, or for trying to relax. I’ve written about box breathing, but today I’m taking a few minutes to breathe and look, rather than think about something specific.

    Just breathe, sit, and look out at the world.