Monday, April 16, 2018

What I did for the week ending 2018/04/14

I don't know what happened with the date in the title of the last blog entry. I don't think that it will cause any harm to anyone, so I'm going to leave it as-is.

Here are the main things I worked on last week (in addition to my 'normal' work for clients, which I don't detail here):

  • I watched several videos from the "SQL Server tutorial for Beginners" playlist on YouTube, which is produced by kudvenkat. As you know, I am not a beginner with SQL Server. Even so, I don't have much practical experience with the LEAD(), LAG() and windowing functions. I've been working on a project where these functions are beneficial, so I am learning more about them. I found the ten or so videos that I watched up around the "Part 110" area to be well-explained.
  • I am up to page 145 in my "Implementing a SQL Data Warehouse" review. 
  • My progress was impeded by having to catch up with the quarterly and annual bookkeeping.




Saturday, April 7, 2018

What I did for the week ending 2018/04/10


Here is the progress for the week:



I've spent quite a bit of time on a couple of dbatools issues, which has negatively impacted the amount of time I have had to read my 70-767 book. I am only 34 pages into the 70-767 book.

Monday, April 2, 2018

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

Last week was a very light week for learning.

I watched:
  1. DSC in a vSphere Environment by Luc Dekens on YouTube. Mainly, I am interested in DSC as I have moved on from VMWare in my home lab.
  2. Test your Powershell code with AppVeyor for ITPros with André Kamman & Rob Sewell on YouTube.

Saturday, March 24, 2018

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:



Saturday, March 17, 2018

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.


Saturday, March 10, 2018

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. 

Wednesday, March 7, 2018

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.