Voice of the DBA

Author: way0utwest

  • Scary Data Collection

    Most of us would feel fairly creeped out by finding out an AirBnb or hotel had security cameras watching us. I’m not a woman, and I’m sure ladies are especially bothered by this, but there was an AirBnB rental where a guest found a camera using a little technology scanning. While cameras are allowed, they have to be disclosed.
    While many of us would prefer not to be surveilled, we are on a regular basis. Governments are watching our vehicles, all commercial activity is tracked in multiple ways, our locations are captured and sold to anyone. And it’s not even the carriers, it could be software that we think is innocuous and helpful. I would think most people reading this know that everything you do online is tracked, and often tracked from site to site with Facebook, Google, and other APIs, even if you don’t use those companies’ services. What’s disconcerting to me is how extensive data gathering and tracking has become and most people aren’t aware how comprehensive it has become.
    And in a wonderful set of timing. As I was writing this, I got a great article about how Google apparently isn’t perceived as invading privacy to the extent that they are. We likely trust them more than we should.
    The capture and misuse of data continues to grow. Whether this is by criminals, governments, or commercial businesses, it’s something we have to deal with. This isn’t necessarily any particular organization or situation that stands out, though the larger organizations likely have an out-sized impact and benefit from this. This is one of the reasons why the GDPR and similar legislation was passed. It’s a first attempt, and arguably weak attempt, to limit the use of data by organizations in ways that might be contrary to the wishes of the human that generated the data.
    Personally I like the GDPR, and while it might need alteration over time, it does start to to examine the idea that humans ought to be in control of data about them, just as we are often in (some) control of many of the physical items in the world we own. There are rules and regulations, restrictions, and even legal processes that provide recourse over our possessions. Those ought to be extended, and certainly adapted, to digital data, with the corresponding rights that we currently have and perhaps even new ones.
    I think this is going to impact our jobs as data professionals in the future. While we will have more requirements, more hassles from security, and more restrictions, this is also going to ensure that organizations need data professionals for a long time.
    Steve Jones

  • Securing Code Early

    Last year I started to get alerts from Microsoft Repos that someone had put a piece of security information in their code that pertained to one of my Azure services. At first I was worried, but then I realized this was the public version of AdventureWorks we maintain in Azure. We’ve published the login so people can test code against this if they want, and I started ignoring the warnings. Well, not ignoring. I still glance over them to verify the issue, but I’m less concerned.
    That doesn’t mean that you shouldn’t be concerned about sensitive information in repos. I saw this quote: ” Bots are crawling all over GitHub seeking secret keys, a developer served with a $2,375 Bitcoin mining bill found.” This follows a sentence that says “It once caused Uber to leak the contact details of 75m users“. These are from an interesting look at a way to secure code that might leak API keys. The idea is that you secure code on local commits and prevent secrets from being stored in your VCS.
    That’s a great idea. Can we prevent passwords in SQL code or ASP.NET config files? Can we actually start to teach developers to use secrets and other run-time mechanisms and prevent them from hard coding anything into a VCS? Perhaps, but we have a lot way to go and certainly more tool chains need to be updated to prevent what is a simple, but common, mistake.
    We need to get better at security, and we are. I see more Static Code Analysis tools being used in all sorts of companies, and I’m glad when they start to impact developers. While I get that changing your favorite method of writing a query or procedure is a pain, often we can reduce potential problems by enforcing some standards and avoiding poor coding practices. We do need to have exceptions since a rule for code might really be a general guideline 99% of the time with a few edge cases.
    Catching issues early in development is one of the goals. Having things like inline SCA in SQL Prompt (live demo), or continuous testing in Visual Studio, scanning with Sonarqube, and other tools are improving our code, while allowing us to build applications faster. There is plenty of other work to be done, especially for database code, but we are improving as an industry.
    If you haven’t used any of these tools and you write code, start learning a bit about them. You’ll likely appreciate their benefits once you get over the learning curve. You’ll also start writing better code.
    Steve Jones

  • Lengthen a Primary Key–#SQLNewBlogger

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

    I saw a post recently where someone needed to increase the size of a PK and was getting a table rebuild message in SSMS. This is short post to show that isn’t required.

    First, let’s create a table and give it some data. Note that the PK is set to a specific size.

    CREATE TABLE dbo.Document
    
(DocumentKey NVARCHAR(5) NOT NULL CONSTRAINT DocumentPK PRIMARY KEY
    
, DocumentName NVARCHAR(200)
    
, DocumentDate DATETIME2
    
)
    
GO
    
INSERT dbo.Document
         (
             DocumentKey
           , DocumentName
           , DocumentDate
         )
    
VALUES
         (N'ABC23', N'Something very interesting', '2019-01-02'),
         (N'QNI43', N'An adventure you admire', '2019-02-02'),
         (N'MNT33', N'Magnets describing life', '2019-03-04'),
         (N'DEF25', N'Time for nothing', '2019-03-12'),
         (N'HIJ54', N'Dreams of the dark', '2019-04-17')
    
GO
    
SELECT top 10
      *
      FROM dbo.Document AS d
    
GO

    If I try to insert data that’s larger, I’ll get this message:

    2019-04-16 08_58_43-SQLQuery10.sql - Plato_SQL2017.sandbox (PLATO_Steve (53))_ - Microsoft SQL Serve

    In SQL 2019, I’ll get a better error, but for now, this shows me a limitation of my key.

    Now I’ll increase the size of the key. I use the ALTER TABLE … ALTER COLUMN statement.

    ALTER TABLE dbo.Document ALTER COLUMN DocumentKey NVARCHAR(7) NOT NULL
    
GO

    Now, I’ll run my failed insert again:

    2019-04-16 09_00_00-SQLQuery10.sql - Plato_SQL2017.sandbox (PLATO_Steve (53))_ - Microsoft SQL Serve

    As you can see, I can increase the size of the PK without rebuilding the table. Making it smaller is a post for another day.

    SQLNewBlogger

    This was a quick repro I set up to answer the question for myself and others. I thought I could do this and spent five minutes proving it.

    The longest part of this post was the test data. You could do the same thing, maybe showing how this relates to a child table as well. In fact, start today and you might beat me to creating that post.

  • Capturing the Application Time

    I’ve always thought that any times we recorded in a row were related to the actual data itself. Almost all of the time I’ve built software, we’ve assumed that the time a row is written or updated is the time we need to record as a timestamp. This works great for many applications and it certainly simplifies programming when the database can capture the time. System versioned tables are a great example of where we use time in the database as the time of record.
    Over the years we’ve started to build more distributed applications that reach a widely dispersed audience. With the Internet today, it’s entirely possible that a row inserted by one user takes place in time zones far away from the next row inserted by a different user. What may be more confusing is that I may insert a row in GMT/UTC, but then update that row a day later in the Mountain Standard Time time zone. In fact, that’s something I’ve done.
    The complexity of time in an application means that we can’t assume we know how best to capture or store time. While I think system versioned tables are a great addition to SQL Server, they are an incomplete one. We need the ability to use these tables with some sort of application time marker, not just the database server time.
    This week I’m wondering how many of you might feel the same way. How many of you need to capture application time separately from the system time? Do you do that now, with some bespoke system? Perhaps you wish you could and this is a feature your system is missing.
    There is a feedback request for Microsoft that asks if application time can be captured as a part of the system versioned timestamps. Regardless of whether or not you have the need, this is an important enhancement that would greatly improve the system versioned table feature. Cast a vote today, and let us know if you would use application time in addition to the time in your database server.
    Steve Jones