Voice of the DBA

Author: way0utwest

  • Careful with Session_Context()–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    A quick note, which is more of a reminder to myself. I find writing things down helps me remember, and I need to remember this.

    I was working with session context, specifically the SESSION_CONTEXT() function. When using this function, you give it a key that contains a value, like this:

    2019-01-15 13_45_34-RLS_Testing.sql - Plato_SQL2016.WideWorldImporters (PLATO_Steve (61))_ - Microso

    I get a value back that I can use. Everything is great.  I was using this to allow one process to set a value and another to get it, and I was happy.

    Until things stopped working. While trying to debug this, I ran this code:

    2019-01-15 13_46_46-RLS_Testing.sql - Plato_SQL2016.WideWorldImporters (PLATO_Steve (61))_ - Microso

    Notice a difference? In the first query, I have SupplierID, but the second is SupplierId, with a lower case “d”. These keys are determined when you use sp_set_session_context, which takes a sysname value for the key. These are going to be case sensitive, as each one is a different identifier.

    It’s not likely that this will cause lots of problems, but when you are setting keys, be careful and ensure you use the same value for writing and reading.

    SQLNewBlogger

    This was a quick mistake I made and it took me 5 minutes to write up. It’s helpful to get me to remember to avoid this, but this also shows I can fix my mistakes.

    What’s a simple thing you learned that makes you write better code? Write your own SQLNewBlogger post today.

  • The Heart of DevOps Webinar

    One of the things that I think about DevOps is that it’s really hard to focus on the hard things that create friction in your process. Most of us want to move faster, and tackle the easy things to automate, the easy things to fix. We want to trust developers to move quickly and pick those items that get in their way.

    They do, but they often stop before the database. Or they start to try and find ways to use a different database (ugh). Or they just start putting strange data into existing columns to get things done.

    The database matters, and I’ll be talking about why.

    Webinar social_Heart of DevOps

    I’m doing a webinar on Feb 6 for 30 minutes to talk about the database in DevOps. You can register here: https://attendee.gotowebinar.com/register/3005019550714731265?source=KB

  • Faster Cloud Warehouses

    I think the cloud is a perfect place for a data warehouse. In many organizations, I’ve found that a data warehouse system is often the largest SQL Server database, both in size of database and also in terms of resources allocated. These systems often handle many complex queries for business users and are allocated a large number of CPUs as well as lots of RAM. Even then, many ad hoc BI tools or lots of “what-if” queries can bring the system to its knees, often causing lots of stress for database administrators during the periods of time when the system is in heavy use.

    Fortunately, many of these systems aren’t in use all the time. Often these are systems used by financial departments to “close the books” at month, quarter, or year end. It’s at these times when lots of resources are needed. Outside of these times, the data warehouse might be one of the least used systems, which makes it a perfect choice for a cloud, scale on demand, environment. Scale up when needed, down when not, limit your costs to the resources you need, when you need them.

    Microsoft has increased the capabilities of the Azure SQL Data Warehouse quite a few times across the last few years. I was thrilled to see ASDW separate out storage from compute, allowing customers to scale up the query, or compute, nodes independently of the storage used. Changes last year improved the ability to move data around between compute nodes as well as increased the number of concurrent queries.

    These improvements are perfect for data warehouses, and if you are looking to build a more responsive SQL Server based warehouse, you ought to take a look at ASDW. More and more customers are finding it valuable and cost effective in their businesses. What seemed to once be a niche idea has grown into a business that quite a few customers are using, with the demand growing.

    What’s more, the move to Big Data Clusters in SQL Server 2019 seems to have adapting some of this technology to the regular SQL Server product many of us use. These will separate out the storage from compute, something that should help many of us scale our systems to meet the demand of our workloads. I haven’t tried a big data cluster yet, but I’m looking forward to seeing how well one works and if it truly scales SQL Server further than I would have dreamed.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Lost Time

    Last year when Daylight Savings Time moved clocks back, I was wondering how the time change might affect different devices and situations in the world. One area that I wondered about was medicate and health care devices. While I’m sure most (hopefully all) medical device software developers have thought about this, it was something that struck me this year as I was fighting a cold.

    I was taking a medicine designed to help reduce the length of illness and was supposed to take this every four hours. That meant if I took this at 10pm, the next dose should be 2am. However, if I woke up at 2:10am, it would actually be 1:10am since the clocks would have moved back an hour. If my clock adjusted itself, then it would be 3:10am. Not a big deal for my dosage, but for some people, this might really matter.

    Certainly software can adjust for this, but can we humans? If I’m dosed every four or six hours, common time frames, both me and my caregiver might get used to a schedule. We might assume that 7am, 11am, 3pm, 7pm is the schedule. We know that 7, 11, 3 repeat throughout the day. Daylight savings time changes that. Once the clock resets, I need to move to 2, 6, 10 the next day. Or to 1, 5, 9 depending on the time of year. It would be easy for someone to think they’d missed a dose and double dose, or assume that medicine was given last out and skip a dose.

    What about sales transactions? I’m sure this is a slow time of the day and year, but still, I’ll get a skewed view of that hour. Either zero sales or double sales. What about auditing? Is this the perfect time to break into a system or perhaps game the tracking of events? If I came and went from a facility during the hour in question, could I leave before I arrived? How could we actually capture a true audit record here?

    Daylight Savings Time might have been a good idea at some point, but these days I think it’s silly and causes more potential issues than it solves problems. I’d like to see it go away and have us stick to some consistent schedule throughout the year. Standard time, savings time, split the difference. I’m fine with anything, just don’t arbitrarily move my clock during the year.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.