Tag: syndicated

  • You Need to Manage Passwords

    I saw a note this week from CNet about a system built to crack passwords (also on ArsTechnica). It reminded me of the story of the guy that cracked Googles DKIM key at 512bits. Not insignificant, until you get to the point of renting that power from AWS for tens of dollars.

    Here’s a great comic on the subject of passwords: Password Strength. It’s got some good advice, but there’s more to it than just having a good strong password. You need to manage your passwords, as in you need to have lots of them.

    Doubt that? Here’s a good piece from Troy Hunt.

    You need a password manager. Whether you use 1Password, KeePass, or PasswordSafe (my choice), choose one and set the defaults to something long. I’ve been using 12characers, but I’ve moved to 16 for my passwords. All of these work cross platform, and you can sync your files between devices.

    One more thing: you need to rotate passwords. Not just on your password manager, but on your various sites. If someone gets a copy of your password manager file, then it’s just a matter of time before they can crack it. Within months, they could have all the passwords in your file if they were determined.

    Lots of passwords I’m not overly worried about, but some I am. Banks, mail, a few of my profiles, these are important to me, and so I rotate the password periodically on them, using new passwords from my manager.

    Security is hard, and passwords aren’t going away anytime soon. Tell your friends, family, and make sure they all consider using some type of password manager and improving their security.

  • T-SQL Tuesday #37 – A Month of Joins

    tsqltuesdayIt’s time once again for T-SQL Tuesday, and this month is hosted by SQLity.net, Sebastian Meine.

    If you want to know more or participate, read the invitation and write your own blog post.

    The topic this month is joins, in honor of Sebastian’s a-join-a-day series. He’s writing about various aspects of joins, and invites us all to do the same thing on this Tuesday.

    Writing Better Joins

    I’m not a T-SQL expert. I can write code, and understand many type of queries, but I’m not one to dazzle others with their code, like Jeff Moden can. Instead, I want to talk about how I’ve learned to ensure that my code makes sense, is understandable, and most importantly, easy to find the mistakes inside.

    I mainly do this by paying attention to the formatting of the code. I would say that once I started to get away from writing code like this, I found bugs easier, and understood the code better:

    WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
    AS (
    SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
    FROM [HumanResources].[Employee] e INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
    WHERE e.[BusinessEntityID] = @BusinessEntityID
    UNION ALL SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
    FROM [HumanResources].[Employee] e INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
    INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
    )
    SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
    [EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
    FROM [EMP_cte] INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
    INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25) 

    I often find code in forums, or sent to me and I need to reformat it so that it looks better. I prefer something like this:

    WITH    [EMP_cte] ( [BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel] )
              -- CTE name and columns
              AS (
                   SELECT
                    e.[BusinessEntityID]
                   ,e.[OrganizationNode]
                   ,p.[FirstName]
                   ,p.[LastName]
                   ,0 -- Get the initial list of Employees for Manager n
                   FROM
                    [HumanResources].[Employee] e
                    INNER JOIN [Person].[Person] p
                        ON p.[BusinessEntityID] = e.[BusinessEntityID]
                   WHERE
                    e.[BusinessEntityID] = @BusinessEntityID
                   UNION ALL
                   SELECT
                    e.[BusinessEntityID]
                   ,e.[OrganizationNode]
                   ,p.[FirstName]
                   ,p.[LastName]
                   ,[RecursionLevel] + 1 -- Join recursive member to anchor
                   FROM
                    [HumanResources].[Employee] e
                    INNER JOIN [EMP_cte]
                        ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
                    INNER JOIN [Person].[Person] p
                        ON p.[BusinessEntityID] = e.[BusinessEntityID]
                 )
        SELECT
            [EMP_cte].[RecursionLevel]
        ,   [EMP_cte].[OrganizationNode].ToString() AS [OrganizationNode]
        ,   p.[FirstName] AS 'ManagerFirstName'
        ,   p.[LastName] AS 'ManagerLastName'
        ,   [EMP_cte].[BusinessEntityID]
        ,   [EMP_cte].[FirstName]
        ,   [EMP_cte].[LastName] -- Outer select from the CTE
        FROM
            [EMP_cte]
            INNER JOIN [HumanResources].[Employee] e
                ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
            INNER JOIN [Person].[Person] p
                ON p.[BusinessEntityID] = e.[BusinessEntityID]
        ORDER BY
            [RecursionLevel]
        ,   [EMP_cte].[OrganizationNode].ToString()
    OPTION
            ( MAXRECURSION 25 ) 

    That actually came from reformatting the code using SQL Prompt, a product from my employer, Red Gate Software. I’m lucky in that SQL Prompt formats things as I’d prefer them, indenting and getting the JOIN and ON clauses onto separate lines.

    Having code with structure, where you can clearly see the tables being joined, the clauses in use, and not miss any of the columns being selected at a glance is important. When you’re under stress and trying to debug or develop something, it’s easy to miss something that’s happening in the code if it’s not formatted correctly.

    Whether you like commas before or after columns, or you want things indented so that the names of objects line up doesn’t really matter. What’s important is that you and your team agree on a set of formatting, or have tools that reformat things for each developer in a consistent way. You’ll spend less time trying to understand the code and more time building or fixing it, if it has a consistent layout.

  • Speaking at Oracle Training Days

    RMOUGSpeakerA first for me. I’m attending a non-SQL Server event, trying to bring a little knowledge of my platform to those that typically work on the Oracle side of things.

    The Rocky Mountain Oracle Users Group is holding their Training Days 2013 event on Feb 11-13 in Denver and a few of us SQL Server people were invited to speak.

    I’ll be talking about Database Maintenance one of the days, hopefully ensuring that some of the “accidental” SQL Server DBAs that also manage Oracle understand the basics of maintenance in SQL Server.

  • SQL Server Thumbnail Metrics – OS Memory

    I’m gathering a few metrics around the Internet for SQL Server from people that I think really know how to run a SQL Server. This is a series of posts that I’m making to gather these metrics up for easy access later.

    Host Memory

    The short metric is:

    Leave 4GB for the OS, or 10% of physical (or guest) memory, whichever is greater.

    Examples: If I have the following amounts of RAM, I leave this for the OS.

    • 16GB of RAM – leave 4GB since 4 > (16 * 10%)
    • 32GB – leave 4GB since 4 > (32 * 10%)
    • 64GB – leave 6.4GB since (64 * 10%) > 4

    Essentially leave 4GB until you get to 48GB of RAM, then go to 10%.

    References: Mr. Brent Ozar of Brent Ozar, PLF who’s presented and written this a few times, but the reference I’m using is: How to Set SQL Server Max Memory for VMware

    Explanation: In the older x86 days there were all sorts of rules for setting memory for the OS and SQL Server. I won’t go into those, and for the time being we’re working in the x64 world, not x128. For that, you need to be sure the OS isn’t starved by SQL Server and unable to manage the host processes. These days 32GB isn’t expensive, and 4GB gives you a nice cushion at lower levels. At higher ones, you need