Voice of the DBA

Author: way0utwest

  • Rewrite Your Code Tomorrow

    One of the things that many developers learn over time is that they write better code with practice. In fact, some of the time a developer will find that if they write a bit of code to solve a problem, they’ll come up with a better solution a day or two later, as they’ve had a chance to think about the problem. Even while moving on to other code, their brain will continue to examine the previous solution, inherently knowing the approach wasn’t the best.

    Many of us see this with our code over time. If we look back at something we wrote a year or two ago, many of us cringe. At least, most of us cringe, especially if we constantly look to improve our skills. We see that old code as poorly written, in a way that we might never want to repeat in our career. I think that’s a good sign that we are moving forward and learning better patterns over time. If you don’t view any of your old code as poorly written, I’d stop and get a second opinion. You certainly might have written some great routines that stand the test of time, but more often than not, I find places where I’d improve code. Having someone else review my work might help me build better skills.

    One of the things I heard recommended recently was that developers solve some problem, commit the code in their VCS, and then tomorrow, redo the work. Start the day by deleting that section of code (that proc, method, class, etc.). Then move forward and rewrite the solution, not trying to remember what was there, but by resolving the issue. Plenty of application developers find they’ll write similar logic, but better structured code. Even if you don’t believe this, conduct an experiment on a small section of your code.

    I don’t know if we’d come up with the same results with T-SQL as many of us tend to solve the problems the same way across a short period of time. However, maybe you’d like to post a mock up of your challenge and solution, and ask for some opinions. We have great forums at SQLServerCentral and some really sharp SQL developers. You might get the validation that you built a great solution, or maybe you’d get some ideas on how to improve both your code and your skills.

    Steve Jones

    The Voice of the DBA Podcast

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

  • No More Service Packs

    The new servicing model for SQL Server is out, with a great explanation from Glenn Berry (there’s also a webcast). The summary is that going forward with SQL Server 2017, we won’t get Service Packs anymore. I’m somewhat sad, as I was always looking for SPs as a once a year patch if I didn’t have any major issues with the platform. This allowed me to keep up to date, but not constantly test smaller patches. I liked that, partly because I’ve been worried about the quality of CUs in the past.

    We’ve had Cumulative Updates (CU) for quite some time. While I’ve seen a few service packs pulled and re-released, that’s been very rare with CUs. I do find that SPs might have more issues because building one is an out of process, disruptive project, whereas CUs have become a regular, repeatable part of the development process. That’s not to say that the CUs are bug free. A few of them have caused issues, and that’s a problem that I hope improves over time. However, I also can’t imagine what it’s like working on such a large codebase, for installation in a large number of environments. It has to be a tremendous challenge to test all cases and remove bugs. Not that there shouldn’t be fewer issues, especially in some areas, but I’m not sure there will ever be a complete absence of bugs.

    I’ve changed my mind on CUs, and I like the new servicing model for it’s simplicity as well as the automation and deep testing that CUs get. There is no confusion, no resources at Microsoft maintaining an RTM and SP1 branch with patches. I dislike code merges, and I suspect they’re often a source of unintended bugs, so the fewer that are done the better. Now we’ll have SQL Server 2017 essentially with one branch of code and patches. We’ll have CU1-11 in the first year and then CU 12-15 the next year. Across fix years, I’d expect that we’d go from roughly 16 CUs per SP + SP to potentially 28 CUs in a single stream across the five year mainstream support of the product.

    I know there are potentially more issues discovered early in a version’s life, and hopefully fewer over time as developers write more tests and learn to cover edge cases better. I suspect that we’ll see fewer and fewer items in each patch over time. Maybe not right away in SQL 2017, but it seems as though the number of issues corrected in each CU declines over time, and I’d expect that testing and better coverage of feature corner cases will come in the future. Perhaps SQL 2019 or 2020 will have fewer issues in the first year with even more testing.

    I know some people see this as a message that the product isn’t well tested. That’s a fair view, after all, doubling the number of patches in the first year could be taken either way. I prefer to view this as the new way software is being developed. Build a pipeline, including lots of testing and feedback, and be ready to respond quickly if things are broken. I hope that’s what Microsoft is thinking, and based on their push for better security and quality, it’s the view I’ll adopt.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The New CU Schedule

    Is my math right? The new CU servicing schedule for SQL Server 2017 is:

    • 1 CU per month for the first year
    • 1 CU per quarter through mainstream support (5 years)

    That means by my count, we get this:

    WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3)) b(n)
)
   SELECT dt = CAST('20171002' AS DATE)
   , patch = 'RTM'
   UNION
SELECT dt = DATEADD( MONTH, n, CAST('2017-10-02' AS date))
    , patch = 'CU' + CAST(n AS VARCHAR(2))
FROM myTally
WHERE n < 12
UNION
SELECT dt = DATEADD( QUARTER, n-13, CAST('2018-10-02' AS date))
    , patch = 'CU' + CAST((n -1) AS VARCHAR(2))
FROM myTally
WHERE n >12
AND n < 30
ORDER BY dt

    Or, for those of you want want it spelled out:

    2017-10-02 RTM
    2017-11-02 CU1
    2017-12-02 CU2
    2018-01-02 CU3
    2018-02-02 CU4
    2018-03-02 CU5
    2018-04-02 CU6
    2018-05-02 CU7
    2018-06-02 CU8
    2018-07-02 CU9
    2018-08-02 CU10
    2018-09-02 CU11
    2018-10-02 CU12
    2019-01-02 CU13
    2019-04-02 CU14
    2019-07-02 CU15
    2019-10-02 CU16
    2020-01-02 CU17
    2020-04-02 CU18
    2020-07-02 CU19
    2020-10-02 CU20
    2021-01-02 CU21
    2021-04-02 CU22
    2021-07-02 CU23
    2021-10-02 CU24
    2022-01-02 CU25
    2022-04-02 CU26
    2022-07-02 CU27
    2022-10-02 CU28

    There will be slippage, and potentially other issues, so I don’t know we’ll see 28 CUs, but having a schedule is something I’m glad about.

  • Get-DbaPowerShell

    When I first saw PowerShell, at TechEd 2006 (ish) and I was enamored. This was a much better environment than VBScript for working in a shell. Then I realized I needed -eq, -gt, etc. and was less excited. These language elements felt like a step back. Then I tried to build a restore script for SQL Server databases early on and was much less excited.

    Across the years, I’ve played with various tasks in PoSh as opposed to T-SQL or simple command scripts in various languages, and I find myself going back and forth. PoSh is very useful in some ways, more cumbersome in others, and I still am not sure exactly how I feel about it as a go-to tool. I am coming around to use more PoSh because of the dbatools project, and I find myself considering PoSh instead of T-SQL, especially when I think I may want to work across instances and string together commands.

    I was quite excited recently by T-SQL Tuesday #94, hosted by Rob Sewell. The topic was PoSh, and quite a few people participated, as you can see from the Get-PostRoundup. There are some creative uses of PoSh, such as importing Excel data, administering SSRS, turning Azure VMs off, cleaning up orphaned files, and more. Personally, once you start to use Azure more, PoSh makes the portal seem cumbersome and slow, and I find that for recurring tasks, it’s a much better tool.

    Recently I saw the DBAfromtheCold write a short piece on why you should learn PoSh. It’s a good look at some of the tasks that are easy through PowerShell. Some of these could be done in T-SQL, some with other scripts, but PoSh certainly is a possibility and an easy way to manage some of the tasks you might need to do at scale.

    I don’t think PoSh is the best way to do everything. In fact, I often still lean on T-SQL for many admin things, especially if I already have a connection open in SSMS. However, as I learn more about PoSh and how to use it, I can make those judgment calls about when it’s a better fit. That knowledge helps me become a more capable and stronger data professional, because I have choices about how to work with systems, and I have some knowledge or basis for the decisions I make.

    I advocate continuing to learn throughout your career, and certainly including PoSh as a topic if you work on the Microsoft platforms.

    Steve Jones

    The Voice of the DBA Podcast

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