What I did for the week ending 2018/08/24

The highlights for this week have been a few videos from the free-to-view PASS Summit 2016 archive, available at PASS.org.

Managing SaaS Application Databases with Elastic Jobs: SQL Agent and More for Azure SQL Database, presented by Deborah Dove

• Essentially, "Elastic Jobs" is SQL Agent for Azure SQL. She also talks about "Elastic Query", which allows you to run queries on all of your databases, or a specific subset of them.

Azure Data Services: Spotlight on Azure SQL Database, presented by Debora Dove

• I think that this is a good introduction to Azure SQL and it covers Elastic Pools. I'm sure that I learned a few things. One thing that bothers ms is the low query/minute ceilings for the various offerings.

Design Patterns for SaaS Applications with Azure SQL Database, presented by Bill Gibson

• If you are going multi-tenant, sharding and employing Elastic Pools seems to be the only sane choice.
• "Most of our customers are compute-bound, not storage-bound."
• There are various clever things you can do with pools and tagging.

What I did for the week ending 2018/08/17

The summer has been busy and my Azure training schedule has suffered. Here are a few highlights from the last few weeks of study.

The following are presentations that occurred during SQLPASS Summit 2016. I can't  link to these presentations directly because they are behind a paywall (though it is free to subscribe):

Backup and Restore SQL Server Databases in Microsoft Azure, presented by Andy McDermid and Pinal Dave

This presentation is about Virtual Machines, not SQL Azure. My takeaways were:

• Segregating data, log, tempdb and backup locations is still recommended practice.
• The "backup to URL" feature can be useful because frees up a disk because it is not needed to hold backup files and writing to a URL doesn't count as bandwidth usage against your VM disks. "Backup to URL" can stripe the data just like writing "regular files" can. You need to ensure that the costs associated with the (required) Azure Storage Account make sense.
• You can stripe VM disks for performance, but remember that VM sizing has rate-limits on the disk performance. If striping provides capacity that exceeds those rate-limits, you won't get the performance benefit that you expect.

Virtual SQL Servers. Actual Performance, presented by David Klee

Klee is always worth listening to, though his client's systems are much bigger than anything I've seen. My takeaways were:

• Your virtual sockets should look like your physical sockets. Put everything inside of a NUMA node, if you can.
• Always consider: "What if my VM gets moved to a host with a different processor configuration?"
• Paraphrased: "Changed from 1x16 (socket x core) to 2x8 and the performance went up 25%"
• Paraphrased: "A client had every VM set to 64 vCPU. Changing from 64 vCPUs to 4 vCPUs got a throughput 3.5 times higher than anything the client had seen up to that point".
• Turn off "hot-add CPU" because it disables proper NUMA configuration.
• Hypervisors generally ignore hyper-thread "cores" until the server is really loaded up. That level of load should not happen often. Therefore, there is little use in turning HT off in the BIOS.
• If your storage does compression, rely on that and turn off SQL Server's data compression.
• Virtual Disk Controllers (VDCs) are still a problem. Creating additional (non-default) VDCs and spreading your IO load around is recommended.
• As always, test any change to make sure that you get what you expect.

SQL Server in Azure VM: Best Practices, Latest Features, and Roadmap, presented by Luis Vargas

and 

SQL Server High Availability & Disaster Recovery in Windows Azure, presented by Lous Vargas, Sanjay Misra, et al

Both of these were very good, with some overlap. I liked the first one so much (which Vargas presents "solo"), I watched the second one when I noticed that he was one of the presenters. My takeaways were:

• You are better off using storage pools, not MDF/NDF files, for striping and performance.
• Licensing seems to only get more complex as time goes by.

What I did for the week ending 2018/07/21

I found a version of my favorite SSDT presentation (from PASS Summit 2016) on YouTube. On YouTube, it is named "Continuous Integration with SQL Server Data Tools", presented by Jon Boulineau for the Nashville SQL Server Users Group.

This seems to be an earlier (?) version of a presentation called "Agile Development Fundamentals: Continuous Integration with SSDT", given at a SQL Saturday. In an earlier blog post, I named the PASS version of the presentation as "my new favorite SSDT video". Both presentations were done by the same person and seem to be the same content. The sound on the YouTube version isn't as good as the PASS Summit version, but you do not have to go through the PASS sign-up process to see it. (Though I do recommend PASS for anyone who wants to know how SQL Server works or how Microsoft expects you to use it.)

Jon goes over several things, including an introduction to SSDT, the test project feature and deploying builds. It's just over an hour long.

According to Microsoft's Certification Planning web site, I am officially a "Microsoft Certified Professional", having passed the following exams:

• Querying Microsoft SQL Server 2012/2014
• Administering Microsoft SQL Server 2012/2014 Databases
• Implementing a Data Warehouse

I'm going to need to evaluate what exam(s) to take next. The versions of SQL Server that I'm tested on are getting old and dusty but I need more Azure in my (professional) life.

I have started the edX.org "Azure 215x: Cloud Administration" course. Partly, I am interested in how edX runs it's online courses. I've taken a few other thing online over the years. I tend to do better with self-directed learning. The course is not very long, just a few hours, but I'm going to drag my feet until August starts in order to maximize the free time I have to use Azure tools. (There is a free account level for 12 months, but the minimum granularity seems to be "one month", so I'll dilly-dally for a week.)

Microsoft is now saying that you can get security patches for SQL Server 2008 for free after extended support ends on 7/9/2019. The catch is that you have to move your SQL Server 2008 instance to Azure. IOW, they are dangling extended lifetime for existing SQL Server 2008 applications in front of organizations in order to get more people onto their cloud systems.

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

I do not have much to report this week.

I've started the Microsoft Professional Cloud Administration course over at edX.org. I've only done the very first units and have yet to form an opinion.

I'll be looking at learning a few things about Confluence in the upcoming weeks,as well.

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

Over the last two weeks, I've been watching PASS Summit 2016 videos. No particular presentation really stands out and I've been watching presentations that cover topics outside of my core competencies, so I haven't been keeping an inventory.

I've signed up for an edX course on Azure. I expect this to take up all of my training time budget for the next few months. I've taken short online courses before, including Rhetoric, Learning and Spanish, but this will be my first time taking an edX course. Those low-intensity courses and required only a few hours a week, while (IIRC) the edX course materials state that I should budget 12 hours a week for the next three months. I'm excited to see how a more demanding course will work out.

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

I have finished watching the PowerShell + DevOps Global Summit 2018 presentations on YouTube that caught my interest. If you have any interest in applied PowerShell, I suggest that you have a look at their playlist. there are nearly 60 presentations and I'm sure that something in there will interest you. If not, PowerShell.org has many other presentations.

After finishing up the PowerShell presentations, I went looking for some good tutorials on Jira. I found a lot of marketing, but I didn't find much on useful, implementable details. Some of that seems to be due to Jira being flexible enough to do whatever you want. That's great, but my problem is that I'm not at the point where I know what I want. I will just endeavor to persevere on this front. 

For the past week or two, I've been spending more time with Trello than I had been. I had a ten minute look at Kanbanflow, which seems like a worthy competitor to Trello. All of this activity is the fault of my reading The Phoenix Project, which re-ignited my interest in thinking about work strategically.

The fact of the matter is that you can turn many information tools into a rough Kanban-style task manager. I use OneNote to do task management. I have been using methods based on GTD since I read David Allen's book many years ago, but I've been moving towards Kanban. (A benefit of using OneNote is that my notes on my tasks become part of what I search when I say to myself "Didn't I do this before?". If something is buried in Trello, I might not find it so easily. I might not even think to search Trello.) You could use text files in some folders for a crude task list. The web sites start to shine when you need to work as part of a team or need better reporting.

I also did a little DFS research, because I am curious as to how that works even though it isn't really my bread-and-butter. One of the things I learned was that DFS was introduced in Windows Server 2003. I thought that DFS was a more recent innovation and (maybe Windows Server 2008). It is good to know things. 

I've started looking through the SQLPASS Summit 2016 presentations. I identified about 60 presentations that looked interesting. I've started going through them. The stand-out presentation so far is Agile Development Fundamentals: Continuous Integration with SSDT, which was presented by Jon Boulineau. This is my new favorite "How to do SSDT" presentation. It covers SSDT basics, testing and deployment. I have been using SSDT since the days of "Data Dude" back around 2008 (or earlier) and I need to improve my use of SSDT to match Jon's.

(I can't publish direct links to the SQLPASS site because of the way their site is built. You will just have to log into the SQLPASS site and search for Jon's work.)

I have said this before, but I'll say it again: If you have any interest in SQL Server, you should join SQLPASS. They don't bother you very much and you will get access to hours and hours of free SQL presentations. Yes, much of the content is "old", but SQL Server doesn't move that fast and "older" doesn't mean "obsolete". I would avoid anything earlier than SQL Server 2014, unless you stuck on an older platform.The quality and focus of the SQLPASS presentations tends to be better than what you find randomly searching around on YouTube. Additionally, you don't have to weed out as many introductory presentations for newbies. Even if you are "just an analyst", there are enough presentations on reporting technologies for you to find something that you can use.

I am still finishing up Nate Silver's The Signal and the Noise. I found the chapter discussing the 1976 Swine Flu incident interesting as I kind-of-just-barely remember that time.

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

I've watched so many of the Powershell + Devops Summit 2018 videos in the last week that I've lost track of the best ones. I won't bore you with a list. I'll just point you to the official Ashdar Partners twitter feed. The feed shows all of the videos that I've liked.

I have three more videos from the Summit that I want to watch. After that, I'll be switching focus to Jira for a while because one of my clients has adopted Jira and I feel a little lost.

After Jira, I'll be going back to the SQL PASS 2016 videos that I put aside a few months back. With my recent certification and the way that the IT universe seems to be going, I am starting to believe that Azure is the future.

I'm also on a kick to read more. I have a good local library and I should take more advantage of it. I have three branches within easy driving distance, including the main branch, and I have easy access to any book at any branch through their inter-branch loan system. After finishing "The Phoenix Project" last week, I am reading Nate Silver's "The Signal and the Noise". It is a lighter read than the book by Nassim Taleb that I read last year.

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

I took advantage of the following learning opportunities:

• PowerShell Team: Using PowerShell From a Browser to Manage Cloud Resources by Danny Maertens on YouTube
• CSV, JSON and XML (Oh My!) by Jeff Hicks on YouTube
• WebJEA: PowerShell driven Web Forms for Secure Self-Service by Mark Domansky on YouTube

Perhaps more interestingly...

Last week, the library didn't have the books I was actually looking for, so I picked up The Phoenix Project by Kim, et al. I should have read this book a couple of years ago. I've seen this book recommended as required reading for greater DevOps understanding a few times.

I've been following the DevOps movement for a while, at a distance. I haven't paid much attention to DevOps's underpinnings or the scope of it's ideas. People don't hire me to re-engineer their business processes. As I did with TDD over ten years ago, I have adopted what I can of DevOps, according to my understanding of it.

"DevOps" is usually sold to technologists as tools (open-source, closed-source or roll-your-own), or perhaps some tactical approaches to things. Tools are easy to sell. (If we are talking about open source tools, "sell" is metaphorical but someone still needs to convince you that you need that tool.). You put up a Kan-ban board, you use git, you install a CI/CD system and you are done. Frankly, I've seen tools come and go (ah...Borland...) and I'm jaded. Most tools do not last more than a few years. I've seen waterfall projects outlast the tools they were based on. Many tools are just old wine in new bottles. (A new text editor? Sure, I'll give it a go. Is it better than the old one? Yes. Is it revolutionary? No.)

Concepts are not easy to sell. The book pushes the DevOps concept well beyond my previous understanding. I feel that my understanding of what the DevOps folks are trying to do has been radically upgraded. It encompasses tools, tactics and corporate strategy. IT isn't just something that "the IT department does". IT involves much of, if not all of, a company. whether the company understands it or not. IT should be seen as a competitive asset and data is as much the life blood of the company as cash flow is, or chargebacks between departments are. I've read that "going DevOps" requires a change in mindset, but I'd never understood the true breadth of what would be required, including from people who traditionally have as little as possible to do with IT.

All of the book's stories about WIP, queued work and rework all ring true. My first job out of school was in a factory. I worked on testing programs and hardware. I didn't really see the factory's products as my worry, but my boss did. He worried about WIP all of the time. I would see him out on the manufacturing floors, counting the pallets of unfinished goods that were stacked all over the building and frowning. Just figuring out how much product was in the pipeline was a problem, they would conduct "inventory counts" a few times a year, when nearly all other plant work was frozen for a day or more. I saw firsthand the chaos that happened when "finished" goods started failing tests and had to be reworked. Those tests were run by the programs that I wrote.

I've made note of many of the books mentioned after the end of the novel. I'll be following up on them at my local library.

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

I am slowly going through all of the PowerShell + DevOps Global Summit 2018 talks that I find interesting.

I took advantage of the following learning opportunities:

• Whip Your Scripts into Shape: Optimizing PowerShell for Speed by Joshua King on YouTube
• Application Provisioning with DSC and Octopus Deploy by Josh Duffney on YouTube
• PlatyPS: PowerShell Help meets Markdown by Sergei Vorobev on YouTube
• Defending against PowerShell Attacks-In theory and in practice by Lee Holmes on YouTube

Certified, as opposed to merely Certifiable

I passed the 70-767 Implementing a Data Warehouse exam this week.

I first read Kimball's Data Warehouse Lifecycle Toolkit and The Data Warehouse Toolkit over fifteen years ago, so it's about time. I still have those books, although many other books have hit the recycling bin. A lot of the underlying technology has changed in the data warehousing area, but a grounding the basics of facts and dimensions is still important.

I passed the exams for Querying Microsoft Server 2012/2014 and Administering Microsoft SQL Server 2012/2014 Databases, a while back. My Microsoft Learning dashboard points tout that this third success qualifies me as a "Microsoft Certified Professional". The last time I was Microsoft-certified in anything was in 1998, when I had a certification in Windows 98 desktop support, or some such thing. I don't think I ever did anything with that.

Co-incidentally, I used the last of my last business cards while attending a talk on Machine Learning at Jacquette Consulting on Wednesday. It must be time for new cards.

What I did for the week ending 2018/05/19

Here are the learning opportunities that I took advantage of last week:

• The Build Release Pipeline Model for Mere Mortals by Ryan Coates on YouTube
• PowerShell Team: Inventory Your Server Environment and Detect Change at Scale by Jenny Hunter on YouTube

What I did for the week ending 2018/05/12

Here are the learning opportunities that I took advantage of last week.

Due to my interest in and use of DbaChecks, I need to get a better handle on PowerBI:

• Introduction to Power BI Desktop with Dustin Ryan on YouTube

PowerBI seems to be the only reasonable way to visualize the results of the tens of thousands of tests I'm running.

I could not resist watching a several things from the PowerShell + DevOps Global Summit 2018. The following are all from the Global Summit:

• Beyond Syntax: Pester Testing with Dave Wyatt on YouTube
• Keynote: State of the Community with Don Jones on YouTube
• PowerShell 2018: State of the Art with Jeffrey Snover on YouTube
• A Historical Architectural Tour of PowerShell with Bruce Payette on YouTube
• Become a PowerShell Debugging Ninja with Kirk Munro on YouTube

I don't usually listen to things like keynotes, but I find that Don Jones and Jeff Snover always have something valuable to share.

I am surprised with the renaming of PowerShell Core to PowerShell. I am sure that won't be confusing to casual scripters. /s

If you'd like to keep tabs on what I am watching more closely, you can head over to the official Ashdar Partners twitter feed. I am not big on chitchat. It's mainly YouTube sharing what I watch and (what I hope are mostly) quality retweets.

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

I took advantage of these learning opportunities:

• Building Your T-SQL Tool Kit: Window Function Fundamentals with Christina E. Leo on YouTube
• SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headaches with Adam Machanic on YouTube
•  Beyond Pester 101: Applying testing principles to PowerShell by Glenn Sarti on YouTube.

The PowerShell + DevOps Global Summit has released their 2018 videos (62 in all!) and all I want to do is watch PowerShell videos all day. Life intervenes, however...

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

I attended SQL Saturday in Blue Bell, PA. Specifically, I attended these sessions:

• Effective DW Storage Patterns with Miner
• Azure SQL DW Migrate On-Premise to Cloud, with Zhang
• Extending DevOps to SQL Server with Fritchey
• Data Governance and Master Data Management, with Napoli
• Successfully Running SQL in AWS, with Carrig

I took advantage of these additional learning opportunities:

• I watched Creative Uses of the APPLY Operator, session 1, with Itzik Ben-Gan at SQLug.se on YouTube
• I watched Creative Uses of the APPLY Operator, session 2, with Itzik Ben-Gan at SQLug.se on YouTube

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

The big thing for this week is SQL Saturday #714, which will be held at Montgomery County Community College on 4/21. I'm writing this post a little early, as I won't have time to write over the weekend.

The time I had planned to spend on finishing up 70-767 has been taken up by finishing up the annual and quarterly book keeping and a strange issue with my backup laptop (an old Core Duo ThinkPad T500) refusing to update to a recent version of Windows 10. If I get the laptop working, I will post a more detailed entry. Unfortunately, I'm starting to think that it's time to retire the old workhorse or put Ubuntu on it.

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.

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

Here is the progress for the week:

• I watched the "Columnstore Indexes", episode 12 of Kalen Delaney's Weekly Webinar series on YouTube. Kalen references Niko Neugebauer's blog several times in her talk. She also points to the SQL Server Central "Stairway to SQL Server Columnstore Indexes" series of blog postings.

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.

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.

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.

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

In no particular order, here are the learning opportunities I took advantage of in the last week:

• I listened to ReverseDSC with Nik Charlebois on RunAs Radio. 
• Opinion: The unspoken truth about managing geeks by Jeff Ello. I read this once a year and I recommend it to anyone who interacts with IT people.
• I watched the "Implementing a Data Warehouse" by "Free Training on YouTube. 
• I am reviewing what I need to know for the 70-463 exam. I took the 70-461 and 70-462 exams ages ago, then got busy and never got around to taking the 70-463 exam. 
• The videos are a little dated, since they are from the SQL Server 2012 era. The same firm has videos for 70-462.
• There are also old videos for 70-463 at the Microsoft Virtual Academy (MVA) site. I've watched those before, and I should probably sit through them one more time.
• Why am I reviewing? I never feel "comfortable enough" with SSIS, even though my experience goes back 18 years to DTS, because I use SSIS so sporadically.
• I started a Pester class at MVA. Apparently, reviewing for 70-463 doesn't keep me busy enough :-/. More seriously, I think Pester is really great and one of the cleverest things that's come out of PowerShell (which I also think is really great). I want to exploit Pester as much as I can, as a DevOps "thing".

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.

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.

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: 

• Hadoop Primer for SQL Server DBA by Konstantin Korobkov at SQLBITs XIV (The sound quality for this one isn't great.)
• Python and R for SQL and Business Intelligence Professionals  with Jennifer Stirrup  at SQLBits XIV.

• Shiny: dashboards in R by Steff Locke (@SteffLocke) SQLBITS XIV 

• "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.

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

What useful things did I do last week?

• I listened to a RunAs Radio podcast on "ReverseDSC" with Richard Campbell and Nik Charlebois
• I listened to a RunAs Radio podcast on "Making Work Visible" with Richard Campbell and Dominica DeGrandis
• I watched the SQLPASS Summit 2015 talk "Enterprise Auditing with SQL Server Audit" with Colleen Morrow.

• 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 watched Best Practices for multi-environment database development with Lonny Bastien and Erick Ellis, which is a SQLBits XIV talk.

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.

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:

• What's new SQL Server 2017 Reporting Services with Anupama Natarajan. [YouTube]