Voice of the DBA

Tag: syndicated

  • Adding the Constraint Name to the PK at the End of Create Table–#SQLNewBlogger

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

    A good habit to get into is to explicitly name your constraints. I try to do this when I create tables to be sure that a) I have a PK and b) it’s named the same for all environments.

    I can create a PK inline, with a simple table like this:

    CREATE TABLE Batting
       (
            BattingKey INT NOT NULL CONSTRAINT BattingPK PRIMARY KEY
            , PlayerID INT
            , BattingDate DATETIME
            , AB TINYINT
            , H TINYINT
            , HR tinyint
       )
    
;

    This gives a primary key, named “BattingPK, that I can easily see inline with the column.

    Not everyone likes this, and I do run into clients and customers that want the keys separated from the column. This is fine, and I understand that this explicitly calls out the keys separately from the column.

    This is an easy change to my code.  I move the CONSTRAINT part to the end, as a separate item in the column list, and add the column(s) that I want to use in the constraint.

    CREATE TABLE Batting
       (
            BattingKey INT NOT NULL 
            , PlayerID INT
            , BattingDate DATETIME
            , AB TINYINT
            , H TINYINT
            , HR TINYINT
            , CONSTRAINT BattingPK PRIMARY KEY (BattingKey)
       )
    
;

    As you can see, inlining names for constraints is pretty easy, and it’s a good practice to get in the habit of adopting.

    If I didn’t do this, I’d get a system generated name, which is fine, but the constraint name would then be different on every system where I deployed this object. Since I often want to test something on one system and deploy on another, future coding gets much more complex than it is by just doing this from the start.

    SQLNewBlogger

    This was a quick 5 minute post for me, following a short session teaching a client how to add the constraint to their table code.

  • Early Bird SQL Bits Pricing

    You can register today and save for the next few days. It’s only £799 for the full SQL Bits 2019 conference until Saturday. If you’ve ever  been, you know this is the best SQL Server conference out there, at least, that’s how I feel, and I go to a lot of conferences.

    I won’t be there next year, as I have a conflict. I’m sad, because I really look forward to this event every year. Fingers are crossed for next year, as I’m hoping to make a vacation of it with my wife.

    Go ask the boss and register today. The savings alone pay for some of your hotel. You know you want to go, and you’ll learn a lot. Microsoft has a large presence, so you can get some tech support for those pressing issues.

    There are always great sessions, from some of the top SQL Server experts in the world. You can see the submissions, and those alone should convince you this is worth the cost. Nowhere else can you get a four day conference for this price.

    Register for SQL Bits and then you can get to work on the really important stuff: picking a costume for the party Winking smile

  • TDE and DDM

    Someone asked a question about TDE (Transparent Data Encryption) and DDM (Dynamic Data Masking), which are two different technologies that are in the security area. As I’ve mentioned in the Stairway to Dynamic Data Masking, DDM is not a security technology. It makes programming some obfuscation easy, but it’s not really security. It can be easily bypassed.

    In any case, how do these work together? Or do they work together? Good questions, and I’ll answer them.

    First, TDE is a technology that encrypts your data at rest, meaning when on storage devices. This handles the encryption and decryption as data is read or written from storage, without coding for it or the user having to do anything.

    DDM is a technology that takes results from a query and replaces some of the data with masked values. This works in memory, after a query runs. Query processing, with WHERE, GROUP BY, etc. clauses is not affected by DDM.

    These work together since data is encrypted on disk. It is read into RAM and decrypted. The query processor then assembles some of this data into a result set. Once this is done, before the results are sent to the client, DDM will make data.

    Demo

    I’ll setup TDE and then add DDM and run a query. Here’s a quick table and enabling TDE:

    CREATE DATABASE TDE_Primer;
    
GO
    
-- create and populate a table
    
USE TDE_Primer
    
go
    
CREATE TABLE MyTable
    
( myid INT
    
, myname VARCHAR(20)
    
, mychar VARCHAR(200)  
    
)
;
    
go
    
DECLARE @i INT = 65;
    
WHILE @i < 92
      begin
       INSERT mytable SELECT @i, 'Steve Jones', REPLICATE(CHAR(@i), 200);
       SELECT @i = @i + 1;
      END;
    
GO

    From here, let’s enable TDE. If you have a master key in the master database, you’ll get an error, but it won’t really affect things.

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'AlwaysU$eaStr0ngP@ssword4This';
    
go

    -- create certificate to secure TDE
    
CREATE CERTIFICATE TDEPRimer_CertSecurity WITH SUBJECT = 'TDE_Primer DEK Certificate';
    
go
    

    
USE TDE_Primer;
    
GO
    
-- Create DEK
    
CREATE DATABASE ENCRYPTION KEY
    
WITH ALGORITHM = AES_128
    
ENCRYPTION BY SERVER CERTIFICATE TDEPRimer_CertSecurity;
    
GO
    ALTER DATABASE TDE_Primer
       SET ENCRYPTION ON;
    
GO

    Once this is done, let’s alter our table. We also need a user that isn’t privileged.

    ALTER TABLE dbo.MyTable ALTER COLUMN myname ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",0)')
    
GO
    CREATE USER JoeDBA FROM LOGIN JoeDBA
    

    GRANT SELECT ON dbo.mytable TO JoeDBA

    Now we can check the execution of a query.

    2018-11-14 10_37_40-Microsoft Edge

    Masked data with TDE.

  • Learning from PASS Summit Feedback

    I got my feedback the other day, and was a bit surprised by a comment. I wasn’t having a great day and posted a note on Twitter. Not the best idea, but I did get some validation back that others support me and appreciate (and enjoy) when I speak. That’s always good, so maybe not the worst idea either.

    Minimize the Impact of Data Breaches in Dev and Test Databases

    This was the last session on Friday, and I wasn’t sure how many people would be interested in the topic. I was hoping for a few, since I think too many of us do a poor job protecting sensitive data.

    The ratings:

    • Rate the value of the session content. – 4.33
    • How useful and relevant is the session content to your job/career? – 4.33
    • How well did the session’s track, audience, title, abstract, and level align with what was presented? – 4.00
    • Rate the speaker’s knowledge of the subject matter. – 5.00
    • Rate the overall presentation and delivery of the session content. – 4.67
    • Rate the balance of educational content versus that of sales, marketing, and promotional subject matter. – 4.67

    Glad to see that last one because although my company (Redgate Software) has products and solutions in this area, I want people to think generally about the problem and challenges. If we’re a good fit, fine but there are other solutions.

    A couple interesting comments:

    “Good content, its very abstract and everything depends on individual scenario. That said it was a great way to explain baby steps and things to get thinking about”

    “Very motivational. I must go fix my systems now.”

    That last one is meaningful to me. I inspired someone.

    “A lot more about devops than I thought it would be considering only one mention of devops in the session description.”

    One that struck me as strange. I don’t think I spent too much time on DevOps, but DevOps is a driver here as management puts pressure to get more done and companies build things quicker, including dev/test environments. The abstract was:

    According to Gartner, more than 80% of companies use sensitive data for non-production in development and for more reliable testing. Hackers view non-production environments as a tempting target since they often have less rigorous security controls. In this session you’ll learn about securing your work in a compliant DevOps fashion with strategies for reducing your attack surface area, and why data masking, anonymization, and data minimization are fast becoming best practices for securing sensitive data in test and development environments.

    I did spend a bit of time describing DevOps and what this means for people working in dev/test areas. I’m also not sure that the description means there will be little mention of a topic like DevOps, but I will think about this for future abstracts.

    I’m also surprised I didn’t get a ding on data masking as I talked relatively little about this.

    Branding Yourself for a Dream Job

    I’ve done this session in many places, including the Summit in the past. I was surprised this was picked this year, but it was. It was crazy that I tweeted this picture, and ended up with over 100 people.

    IMG_20181108_123916

    Ratings:

    • Rate the value of the session content. – 4.80
    • How useful and relevant is the session content to your job/career? – 4.67
    • How well did the session’s track, audience, title, abstract, and level align with what was presented? – 4.87
    • Rate the speaker’s knowledge of the subject matter. – 4.80
    • Rate the overall presentation and delivery of the session content. – 4.93
    • Rate the balance of educational content versus that of sales, marketing, and promotional subject matter. – 4.93

    The comments were mostly good. A few people mentioned they’d seen it before and still came. That’s very exciting as a speaker. This comment really made me smile:

    “I chose this session as filler and, thus, it was the session I was least expectant about. Thus far, it’s been the most impactful session I’ve attended. Positively inspiring!”

    This one makes me think.

    “Your slides could be a little more jazzed up. I think what your saying is awesome and I got some great tips but it was hard to stay engaged with the slide deck.”

    I do try to keep things interesting with pictures and used to have more. I got complaints that not enough info was on slides that would help later, so I may have tipped more to the other side. I’ll have to review this for next year.

    Adopting a Compliant Database DevOps Process

    This is another one I’ve done at the Summit. I actually did it last year to a huge crowd (250+), and wasn’t sure how well this would be attended this year. I was up against some other great speakers and sessions, so I was happy to have 100+ people.

    Ratings:

    • Rate the value of the session content. – 4.53
    • How useful and relevant is the session content to your job/career? – 4.47
    • How well did the session’s track, audience, title, abstract, and level align with what was presented? – 4.65
    • Rate the speaker’s knowledge of the subject matter. – 4.82
    • Rate the overall presentation and delivery of the session content. – 4.53
    • Rate the balance of educational content versus that of sales, marketing, and promotional subject matter. – 4.35

    That last one is interesting. I try not to talk products specifically, even though I use a few Redgate tools, I don’t think I try to sell them. I usually refer people to the booth or sales, but I got this one:

    “I know they probably want the sessions to be free from non-Microsoft product endorsements, but would have loved to see you be able to go deeper with the redgate products. I did get a lot out of the session as it was, and got some ideas to improve our processes in our shop. Thanks!”

    Contrasted with this one:

    “I know you were trying to be vendor agnostic in the talk but I think because of this it didn’t go as deep as you could of”

    This is a tough topic. I’ve spent a few years trying to refine the message to one that’s understandable, and exciting, but not too deep. This isn’t designed to teach you how to do DevOps. I can’t do that in an hour. However, I did get a suggestion:

    “This was great. Maybe stretch it to pre-con?”

    And this one made me smile:

    “I love you and hate you. Great advice but now I despair at my ineptitude ”

    However, this was the Twitter one that caught me off guard.

    “Good session. To me speaker came across as somewhat arrogant which to me reduced the value of the session. I would not come to another session by this presenter. He seems knowledgeable but not a good presenter ”

    I know I can’t please everyone and not everyone will like my style or my talks, but I haven’t ever been told I’m too arrogant. Or that my style turns someone off that much, even though I’m knowledgable.

    Not sure I’ll change anything, but I’ll keep an eye on whether I might be doing something that offends people.

    A Busy Couple Days

    I was only in Seattle for 31 hours, and it was a whirlwhind. I had a half day Thursday, giving a session and chatting with friends. Then a busy Friday, with a few meetings, two talks, and lots of shaking hands.

    I think I did a good job of presenting sessions this year, with good ratings. Not a lot of things to change, but as I’ll deliver new sessions next year, I need to be sure to continue to build and practice high quality sessions. A few comments will make me think and re-examine parts of what I do.

    Like other aspects of my job, and DevOps, continuous improvement along with some experimentation is important.