Friday, February 16, 2018

What I did for the week ending 2018/02/16


Before I get into the mundane things for this week, I'd like to point out that all support for SQL Server 2008 and SQL Server 2008 R2 is ending on July 9, 2018. Microsoft's page covering this is here.

Some things to consider:

  • If you are subject to data regulations such as HIPAA, PCI and perhaps even SARBOX, running an unsupported version of your relational database manager may put you out of compliance. 
  • Even if this is not the case for your organization, migrating to a supported version may provide significant performance benefits. I've never heard anyone say "My database is too fast". 
  • With data compression now a feature of the Standard Edition, you may be able to realize savings in storage space, I/O bandwidth and related resources. 


If you have SQL Server 2008 or SQL Server 2008 R2 databases that you need to migrate to a supported version, I can help. Contact me. I have experience with nearly every obsolete version of SQL Server, going all the way back to SQL Server 6.5.

Here are my online learning experiences for this past week, in no particular order:

  • Test-Driven Development with Pester, with June Blender on YouTube
  • Running SQL Server Integration Services within a SQL Server Virtual Machine to do More with Less Money with Jimmy Wong at SQL PASS Summit 2016


Other things I've been working on this week:

  • I've written some data-import code in PowerShell. SSIS seemed overkill and the client doesn't have any SSIS resources to support a SSIS project. 
  • I've been trying out sp_blitz. While I knew about sp_blitz, I had never taken the time to look at before. It's actually found a few curiosities that had never noticed in certain old databases.
  • I found one or two "quirks" in dbatools
  • During my "training time" this week, I have been concentrating on reviewing for the 70-463 test which I intend to take this quarter.



Friday, February 9, 2018

What I did for the week ending 2018/02/09

I find that posting these small learning experiences helps keep up by drive to watch them, so I will continue to post them here. I hope that you find them as interesting and/or useful as I did.

Here are the online learning experiences for this past week, in no particular order:

  • Monster Text Manipulation: Regular Expressions for the DBA with Sean McCown at SQL PASS Summit 2016. This might have been the clearest explanation of REGEX that I've ever encountered.
  • Scalable Application Design with Service Broker With Allen White at SQL PASS Summit 2015
  • DBA Mythbusters with Paul Randal at SQL PASS Summit 2015. Paul Randal is always worth listening to.
  • Columnstore Indexes in SQL Server 2014: Flipping the DW Faster Bit with Jimmy May at SQL PASS Summit 2015
  • Emotional Intelligence for Engineers with April Wensel at ngAtlanta, on YouTube.
  • Application Patterns for Azure SQL Database with Tony Petrossian at SQL PASS Summit 2015
  • Foundation Session: Developing Modern Applications on Azure with Asad Khan, John Macintyre, Pablo Castro, Shawn Bice, Tony Petrossian at SQL PASS 2015
  • Install an AlwaysOn Failover Cluster and Availability Group with Ryan Adams at SQL PASS Summit 2015
  • Dimensional Modeling Design Patterns: Beyond the Basics with Jason Horner at SQL PASS Summit 2016
    • Listened to Query Store and Automatic Tuning in SQL 2017 with Erin Stellato on the RunAs Radio podcast
    • SQL Server 2017 What's New on YouTube
    • Cool New SSMS Features for DBAs on YouTube. SSMS has lots of things hidden away in it and, every now and then, someone points out something else that I didn't realize was there.

    I can't provide direct links for the SQL PASS Summit videos because the SQL PASS site doesn't work that way. I apologize for the inconvenience.

    Saturday, February 3, 2018

    What I did for the week ending 2018/02/02

    I find that posting these "what I've done" entries once a week helps me keep my momentum up, so I'm going to keep doing it.

    I watched the following talks: 

    • "How to Build a Virtual Test Lab for SQL Server" with Ed Leighton-Dick & David Klee at SQLPASS Summit 2015 
    • "Analyzing your ETL Solution with PowerShell" with André Kamman at SQL PASS Summit 2015
    • "Change Data Capture Case Study and Checklist" with Uwe Ricken at SQL PASS Summit 2015

    The most stand-out moments from last week's efforts include:
    • I altered a SQL Server partitioning configuration that I had set up a number of years ago. Even though I implemented partitioning as a "Plan B" for this particular application, it has worked surprisingly well. I haven't had to touch the configuration since I implemented it. I wouldn't have to touch the partition configuration now, but application has survived well past it's initially estimated retirement date.
    • I improved my Pester skills. I've got a lot of infrastructure and configuration tests running now. The weak point, I think, is the overall organization of what I've built. Looking at what I've done, it seems haphazard to me, but I am not entirely sure what "better" should look like. Everyone seems to have their own manner of breaking out or categorizing things via file/Describe/Context/It and their own way of looking through items to be tested. The sqlcollective/dbatools folks intend to release a set of best practice recommendations implemented as Pester tests this spring. I intend to raid them for ideas on how to organize dozens of tests of various sorts. I'm also sure that there is some overlap between what they intend to roll out and what I've been doing. I may need to retire some bespoke code in favor of community-supported code.
    • I installed the latest Pester on a couple of my workstations, updating from 3.4.x or so to 4.1.x. I have seen one or two differences. I haven't bothered to read about the improvements that come with the new version yet (though I do like -Show very much).
    • I removed several dependencies on external code from a code base that I maintain.
    • I updated a toy solution of mine, though the SSIS project in the solution proved to be a problem. The take-away is: If your copy of Visual Studio 2017 (which you *know* has SSDT installed ) doesn't seem to support BI projects (SSRS, SSAS, or SSIS), then you probably don't have the latest version of SSDT installed. VS doesn't seem to update SSDT bits as part of the usual "update extensions & tools" process.
    • I managed to get through a debugging session in VSCode without it crashing on me. This is a first for me. Either VS Code is getting better or I've learned something about how to use the debugging modes that VS Code provides.
    I'd also like to promote the next Philly-area SQL Saturday a tiny bit. SQL Saturday 714 will be held at Montgomery County Community College on April 21, 2018. I skipped SQL Saturday last year, so I haven't been since the event was held at Microsoft's facility in Malvern and I'm curious to see how it will work at this particular venue.

    Friday, January 26, 2018

    What I did for the week ending 2018/01/26


    What useful things did I do last week?


    • I caught up with Kalen Delaney's Weekly Webinar, up to and including Episode 11. These cover a variety of technical details on how SQL Server stores data and are probably too gory for newbs. I have been working on these talks for several months, on and off. Much of the material is familiar to me, but it's easy to forget details here and there. I did learn more about the implantation details of row vs. page compression.
    • I watched Advanced PowerShell Module Development with Visual Studio Code with David Wilson, again. I've never gotten PowerShell debugging in VSCode to behave properly and this will motivate me to take another swing at it.
    • I watched "What’s New for Business Analytics in Excel 2016", with Eli Schwartz. I can't find a Twitter handle or a blog URL for Eli.

    I did start another two or three talks but gave up 15 or 20 minutes in because they weren't really what I was looking for or expecting.

    I've also got a project going to "Pester all the scripts", so to speak. This helps me grow my Pester and VSCode skills.

    Saturday, January 20, 2018

    Keeping up with the SQL Server (things)

    I've been a member of PASS for so long that I'm no longer sure when I actually joined. I attended PASS Summit 2004, which was in Orlando, FL. I must have been a member before then. I started with SQL Server in 1998. I feel like someone from the old, long-dead Swynk.com SQL Server email list suggested that I join.

    (For the uninitiated, membership in PASS is free and you get access to talks from past PASS Summit events. Even though the talks are two or three years old, many subjects are still relevant and many people are still dealing with older versions of SQL Server. In short, you can get good information from some of the best-educated SQL Server experts in the world.)

    I don't go to as many of the monthly meetings in Malvern, PA as I used to. The main reason for this is that there are many good online venues that provide good talks on SQL Server. My three favorite venues are:
    • PASS
    • YouTube (many PASS 'Virtual Groups' will publish monthly talks here.)
    • SQLBITS (look towards the right side of page, under "Conference").
    Both PASS and SQLBITS post recordings of old sessions on their web sites.

    Over the last several weeks, I have been going through the backlog of old PASS Summit 2015 videos. Here are a few stand-outs, with links to the Twitter feeds of the speakers, that I've watched over the last few weeks, in no particular order:
    • Kicking and Screaming: Replacing Profiler with Extended Events with Erin Stellato
    • Run Your DBA Morning Checklist with Policy-Based Management with Pieter Vanhove
    • PowerShell and the Art of SQL Server Deployment with Michael Fal
    • Testing SSIS Packages with Tim Mitchel
    • A Few Of My Favourite Plan Operators with Rob Farley
    • What's New in SQL Server 2016 Reporting Services with Riccardo Muti
    The nature of the PASS site does not allow direct links so I can't point you directly at the talks, but it is easy enough to search for a talk after you log in.

    It seems that the PASS Summit 2016 talks are already online. I've got another 25 talks from PASS Summit 2015 that I'd like to go through, then I'll be talking on the talks from 2016.

    Lastly, but not least, I also watched a talk public by the PASS Business Analytics Virtual Group:


    Friday, September 9, 2016

    SQL Sentry's Plan Explorer now free; The other SSMS tools I use

    This is great:

    SQL Sentrys' Plan Explorer(tm) is now free for all users.

    I haven't been doing much plan analysis lately, as I have been working with largely legacy SQL Server 2008 R2 (and earlier) code. I did spend a lot of time with the free version of Plan Explorer about a year and a half ago. I'm unaware of a better tool for it's specific task.

    While I am on the subject, the other things that round out my SSMS extensions are:


    I've been experimenting with the ApexSQL Refactor and Search tools lately, but I haven't committed to them yet. I like the "reformat" that Apex provides. It is much more flexible that the "Poor Man's formatter", but, if I'm honest, I must say that I'm a little bewildered about how to configure it best for the way I like to see code. The Apex Search tool seems like a good replacement for Red-Gate's search, but I can't say that I've really found anything fatally wrong with Red-Gate's search. 


    Friday, May 6, 2016

    Windows Administration and Trends in (PowerShell) Scripting



    Jeffery Snover is the person most responsible for PowerShell, which revolutionized my approach to database administration. He has a background in scripting going back to VMS (IIRC), which is how he came to create PowerShell. His Twitter bio lists "Microsoft Technical Fellow/ Lead Architect for Enterprise Cloud Group/ Azure Stack Architect/ PowerShell Architect / Science fan".

    From my point of view, he directs strategy for Windows administration. I listen to him because the things he talks about are likely to influence my work life and they give insight into how the PowerShell team expect people to use their product.

    The podcast covers a variety of things in a light way. The thing that grabbed my attention the most was that Snover seems to be saying that Windows will be implementing things similar to what linux does with root and sudo. (My linux experience is limited but my two takeaways are: You never log in as root and sudo controls what your 'day to day' login can do.) Imitation is the sincerest form of flattery, as they say.

    Beyond that, it seems that I should be working towards two goals.

    One of those goals should be to get my code into an open repository.

    I have been using version control for many years, but I have always kept "my code" in a closed repository.

    Initially, I used subversion. At first, I ran my own server (on an old Sun workstation) out of my home office. I moved to a cloud-based solution after experiencing a number of power outages. (I just logged into my old Subversion repository for the first time in well over a year. It has over 3,000 check ins.)

    In spring of 2014, Subversion was seeming old-fashioned, so I tried Git for a while. That was during an extremely slow period for changes to my code, so I never got very far into it. I documented the start of that period with this blog posting

    I've been using Microsoft's TFS Online for the last couple of years. This seemed a natural fit because I spend a lot of time in Visual Studio, it provides feature and bug tracking and it a cloud-based solution. Since then, Visual Studio has come to embrace Git.

    TFS Online seemed like a pretty hot technology in 2014, but I feel like I've missed the boat with GitHub. The current trend seems to demand use of GitHub. The work required to move my code from TFS Online to GitHub is large. There are over 170 files of varying complexity, with a few being modules with many functions. I work very hard to keep client-specific things out of "my code", but I would need to vet everything again. 

    I did do a pass with ScriptCop through much of that code in 2015. I fixed most of the things that ScriptCop spotted. Other than that, much of this code hasn't been looked at in years.

    I don't want to split my code between different repositories. I like being able to install one set of tools and I don't want to get into situations where I'm looking for something that is in the other repository.

    The other goal is to start testing operational environments like developers test their code.  In my case, I'd like to test my code like developers test their code. :-/

    I fiddled around with PSUnit when that was the hot thing, but I never integrated it with my day-to-day work. The current hot technology for this is Pester, and I'd like to do more with it.

    Implementing Pester (or any testing framework) "for real" would be a lot of work. My earliest code was written in the PowerShell 1.0 and 2.0 era. Other than Transact-SQL, my coding at that time was mainly in DOS batch and Windows Scripting Host's version of VBScript. PowerShell was a new thing and it was not obvious that it would be as successful as it has been. My PowerShell scripts were not written with testability in mind. The technical debt is enormous. Layers of code are based on that work. Testing nearly anything seems to require reworking layers of code. Changing that code breaks other code. Since there is no testing framework, those bugs aren't noticed until I happen to run them.

    In short, it looks like I can't do anything without doing everything and there is no time budget for that. If I work on the testability of recent code the going seems slow, there isn't much impact, working on that code is not my "day job" and I can't keep the enthusiasm going.