What I did for the week ending 2018/03/23

Another snowpocalypse has come and melted.

I've been busy, but I haven't made much progress on my training this week. Just about the only thing I have done is this:

• I listened to a RunAs Radio podcast on "Be the Master" with Don Jones.

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

This week was very busy (work can be so inconvenient :-)) and I didn't get very far in my studies. 

• I went through the MVA course on SQL Server on Linux. This is several videos and takes a couple of hours. This required dusting off my aging Linux knowledge. In short, most SQL things are the same. I'm not sure that I would be in a hurry to build a complex cluster just yet, but small, uncomplicated single-instance applications should be fine. 
• I watched Introduction to Jira & Agile Project Management with Dan Cuparkoff on YouTube. I'm not unfamiliar with Agile tactics and strategies, but I have not had a chance to work with Jira. 
• My exam reference book for 70-767 arrived but I haven't had a chance to crack it open.

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

Here in southeastern Pennsylvania, another Snowpocalypse petered out this week (7 to 13 inches of snow turned out to be 2 to 3 inches of slush) and I took advantage of the following learning opportunities:

• Performance Troubleshooting made easier New features in Management Studio by That DBAGuy on YouTube
• PowerShell and KeePass - A poor person's credential database with Christian Lehrer on YouTube
• Advanced Components in SSIS MS SQL Server Integration Services 2016 with Tim Mitchell on YourTube. This is labelled as "SSIS 2016" but was recorded in 2011. That doesn't matter very much, I've watch Tim present before and it's always illuminating in some way.
• SSIS Best Practices on YouTube
• Demystifying $THIS, $_, $PSITEM, $$, $Whatever… with Jason Yoder on YouTube.

I realized that the 70-463 exam has been retired and that I should be targeting 70-767 exam. This is what happens when you let things lie fallow for to long.

There seems to be a dearth of SQL Server-specific open learning materials for data warehouse topics, so I bought the Microsoft Press book for 70-767 and I'll be going through that once before taking the exam. This will push me back a couple of weeks, but my knowledge will be more up-to-date. 

Things I had to do to test dbatools, Part 2

My last entry chronicled some of the work I did to get a 'development environment' up and going for the dbatools project. Here are the rest of the details, including the issue with the "BatchParser" DLL error.

What I did to get the dbatools Pester tests going on my VM:

• I checked out a copy of the appveyor project that sqlcollaborative has. This seems to be a requirement for testing with the 'official' dbatools tests, even if you aren't going with a full-blown appveyor workflow. If you want to write your own Pester tests, you can do anything you want to.
• I created a custom version of configure.ps1 and put it in c:\temp. This informs the Pester tests about my specific environment.
• I set my SQL Server 2008 R2 and SQL Server 2016 test instances to allow SQL Logins. I do not allow SQL logins on my instances if I can avoid it, but it makes sense to test dbatools features that support SQL logins.

Those changes let me run all of the tests without any critical "Pester is broken" problems. Unfortunately, there were still over 200 failures. All of them, or at least the vast majority of them, seem to be issues with my environment. Can't blame dbatools or Pester for that.

Since my immediate interest is in Restore-DbaDatabase, I needed to get that Pester test working before I started changing things. So, I switched focus to just getting that one test working perfectly. Here are the two important things I did to get Restore-DbaDatabase.Tests.ps1 working:

• I changed permissions on certain file system folders so that my "normal" login can see files in those folders. Prior to my doing this, the Restore-DbaDatabase test would fail. This is a development machine and this should be harmless (until I move to another machine and forget that I have to do this.) Those folders should be pretty familiar to any DBA. They were
• C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA
• C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\Log
• Here's the show-stopper: the test for "page restore" gave me an error:

WARNING: [Invoke-DbaAdvancedRestore][11:52:21] Failed to restore db PageRestore, stopping | Exception calling "ExecuteNonQuery" with "1" argument(s): "Could not load file or assembly 'Microsoft.SqlServer.BatchParser,Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified." 

This lead to the failure of the "page restore" test.

This error took me a while to figure out. The file does exist on my system, it is part of the dbatools distribution. The file also in my GAC, but it's a much older version. 

After a couple of hours of stepping through code, googling, swearing, wondering why some of my breakpoints weren't being hit, fiddling with Add-Type, fiddling with LoadWithPartialName() and wondering why the DLL was unloadable, I found a guy with the same problem. He had a fix, which is to install the Visual C++ Redistributable Packages for VS 2013. 

I installed the 32 bit and 64 bit versions of the redistributable package, just to be safe. One reboot later, and everything seems hunky-dory now. I can run Restore-DbaDatabase.Tests.ps1 without any problems.

So: thanks, guy! 

I presume that most people are working with Windows 10 or maybe they've got full-blown visual Studio installs on their machines and already have those VC++ packages on their systems, making me an oddball. I'm documenting this, here, in case some other oddball has the same issue.

Things I had to do to test dbatools, Part 1

I spent a good amount of time this week getting a system up so that I could actually maybe start contributing code/tests/something to the dbatools project. I've been wanting to do this for a while, but there is a large amount of "stuff" to get working before being able to do "real work" and getting it all done eluded me. 

Over the last year or so, I've spent a couple of hours getting up to speed, getting things installed and then run out of time just as I get to the point where I can start working with the code. (You know, the fun part.) 

A few months later, I'd give it another go, only to have to spend time fetching my remotes (since I fly solo so often, I'm a perennial newb at some things that I wish I weren't a newb at) and addressing other bit rot issues. By the time I got caught up again, I'd be out of time. Wash, repeat, et cetera and so forth.

I still support several SQL 2008 and SQL 2008 R2 instances. I've noticed a dbatools "behavior" that might need remedying. Now I have (much) more reason to get off my comfy couch and do something useful. The first thing is to get my work environment completely set up.

How I got my work environment tuned up to work with dbatools:

• I already had VS Code working on my development laptop, with git and a smattering of extensions. I have basic git knowledge. I have relied on a dozen or more online guides for getting all of that configured and working. I've got nothing to add to them, so I won't try here.
• The dbatools Pester tests seem to like to remove all of the user databases on an test instance when they run, so you need to give it an instance that doesn't have databases that you need to keep. Additionally, I needed a SQL Server 2008 R2 instance that I can work with. I have a smattering of development instances around my homelab. My main development laptop does not have anything as old as SQL Server 2008 R2 loaded onto it, and I don't want to have to worry about accidentally dropping databases on it. My development cluster only has SQL Server 2012, SQL Server 2014 and SQL Server 2016 configured and that won't help either. I have an old Windows 7 development VM that had fallen into disuse. It had SQL 2008 and 2008 R2 configured. That seemed ideal, so I spent time opening up the firewall so that I could run my fast development laptop against that VM. I had forgotten how much of a PITA this is, between port assignments and firewall rules. Worse, I realized belatedly that dbatools tests are written in a way that seems to force running locally to the instance. All of that work on the port assignments and firewall rules was wasted. So, Plan "A" was a failure.
• Plan "B" was to set up my entire development environment on that old VM. First, I had to do a few rounds of Windows Updates. Then, I had to install and configure VS Code and Git. There is an old SQL Server 2008 R2-era SSMS installed, which seems good enough for me to put off installing a modern version of SSMS for a while. I discovered that the VS Code Settings Sync extension I have configured doesn't seem to be working, or doesn't work the way I thought it did. That cost me some time as well.
• To save time, I just copied the source code files over my network from my main development laptop to my newly-tuned-up development VM. Git never missed a beat, though I suspect that doing this to a TFS installation would have driven it nuts.

At the end of the evening, I had all of that working OK. The next night, I focused on getting the tests to actually run. That will be my next blog entry.

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

I spent some time looking at dbachecks. It's got a lot of promise. I need to see how I can integrate it into my existing tests or integrate my existing tests into dbachecks.

I've spent some time getting an environment together so I could contribute to dbatools.

I'm going through the MVA Jump Start videos for 70-463 again.

I watched Auto-generated User Interfaces with Phosphor with David Wilson at PSCONF.EU 2017.