Tuesday, May 19, 2020

What I'm working on now

I hope that you are all safe in this time of COVID.

I am (slowly) working through the "Automate the Boring Things (with Python)" class over at Udemy.

I am keeping my code/notes/experiments in an open GitHub repository. You can read my raw files and see how I think when I write programs in a language I am not at all familiar with.

I have also been (slowly) updating my old "Tools I use" wiki at github. This includes things like programmer's editors and some of the languages I have used over the decades.

Monday, June 24, 2019

News Flash: Old dog learns new tricks (or tries to)

Lately, I have been trying to up my game a bit and one of the things I am doing is using some of the latest tools and bits of things.

(Partly, this is because I have been working with some old technology lately and I am feeling "dated". Ask me about Fox Pro, if you dare.)

PowerShell
After many years of not seeing value in updating, I am moving to PowerShell 6. (Version 6.2, specifically.) Frankly, more things work than I expected. I haven't seen any performance regressions and things have gone smoothly. I think I spent more time incorporating the PowerShell version into my Prompt function than anything else that I had to do because of the upgrade.

SSMS
I am running the absolutely-latest build of SSMS. I skipped some SSMS builds when the removed the diagramming functionality. Now that it has been put back into the product, I am on the update train again.

Aside: The diagramming feature isn't awesome but it is easy to use, requires no installation, doesn't cost anything and is more than adequate for 95% of the things I need such a tool for. I know that it isn't a real "enterprisey" ERD tool, but I don't need one. In fact, if they took out all of the "modify the tables in the database" features, I would like it more. The main thing is that, sometimes, you just want to look at a diagram of a subset of the tables in a database.

Windows 10 1903 Update
After not getting any update action for weeks after it was released, I bit the bullet and forced this latest Windows release onto my main laptop. (Carpe Diem, right?) It's been fine so far. The blurred-out login display was a little jarring at first. Multi-monitor has always been the shakiest feature of my laptop, but it has not been any worse after updating.

DbaTools
After several years of "beta", DbaTools has had it's first release that is judged as production-ready. I've updated and I'm slowly looking through my code for issues. I've been using this as a production tool for years. I'm excited for this release because it validates all of the work that has been done on the tool and the names of functions should stop jumping around so much.

Learnings
I spent a lot of time watching things on YouTube (or the SQL PASS site) in order to learn new things and see how other people approach the problems that I probably have. Lately, I have been focusing on the PowerShell + DevOps Global Summit 2019 videos.

Just For Fun
I am trying out the new "Windows Terminal", which is in preview and available in the Windows Store. I found this through a post on Hanselman's site. It found my Windows Subsystem for Linux (WSL) and gave me bash, it found my new and old PowerShell installations and my old, trusty cmd shell (which I still sometimes use for chocolatey). I haven't put it into heavy use yet, but nearly everything seems OK so far. (The settings menu pick doesn't seem to be working properly for me.)


Saturday, June 22, 2019

DbaTools version 1.0 has been released

(Actually, they are up to version 1.0.2 already.)

I've been using DbaTools for a number of years. I have come to rely on it more and more. Now, I find it at least as deeply entwined in my PowerShell code as the SqlServer module is.

All right-thinking people use DbaTools. And you should, too.

More seriously, DbaTools is one of the few PowerShell modules I absolutely rely on, on a daily basis. I use DbaTools so frequently that I can't easily categorize it all. In short, I can:

  • Check servers, instances, databases, disks, security and many other things for issues on a daily basis
  • Check business rules on a daily basis
  • Restore databases to test environments on an ad-hoc basis. I have this working so smoothly that I forget that I have Ola Hallengren's backup scripts actually running the database backups. 
  • Load data into databases
  • Extract data from databases
I've never actually used DbaTools for it's original purpose, which was migrating databases and instances onto new computers. It can still do that, but it has added so much more goodness over the years that you should install it anyway. There really is something for everyone in DbaTools.



Saturday, January 19, 2019


If you want to see a cool ten-minute, "lightning" demo of how you can use PowerShell and Excel together to analyze data or produce nice reports, watch this YouTube video, from 7:47 to 18:22. This is presented by Doug Finke, the author of the Import-Excel PowerShell module.

Sunday, August 26, 2018

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.

Sunday, August 19, 2018

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.





Sunday, July 22, 2018

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.