Books I'm Reading (or About to Read)

Since leaving the classroom and returning to the world of software development, I've tried to spend at least an hour a day reading about the changes that have taken place in technology since 2010. In July I started to dedicate my in-bed-before-sleep time to this task, and it's resulted in a lot of books being read and a bunch of new skills being acquired or refined. While it's impossible for any one person to know everything about a given subject, it should not be impossible for one person to know a healthy amount about a number of different subjects. This has certainly been the case with me while I learn more about data modelling, database design, and data warehousing. In fact, looking back at the technical books I've read in the last six months, it's easy to see that the vast majority are all related to databases in one way or another, and the four I have dedicated for January, February, and March are all on SQL Server.

I think I may have a little bit of a database fetish.

SQL Server Books

Last week I finished Stacia Varga's Developing SQL Data Models exam reference and I'm currently going through Victor Isakov's Administering a SQL Database Infrastructure reference in preparation for an upcoming Microsoft certification exam. On deck is Jose Chinchilla's Implementing a SQL Data Warehouse as data warehousing is a topic that has recently piqued my interest. Randolph's SQL Server 2017 Administration Inside Out is expected to be released by the end of next month, and I'll likely set aside some additional time to ingest the wisdom contained in the book. There's just so much to learn and explore!

A few people have asked why I read so much. They want to know specifically why I read so many technical books. When I think about it, though, the answer is not so cut and dry. Sure, I'd like to learn more about these tools so that I can make better use of the technology, but this isn't the only reason. Buried deep in the curiosity is the desire to discover what I do not yet know. As Bart Simpson so eloquently said to Mrs. Lovejoy all those years ago, "what you don't know could fill a warehouse."

It's true. We generally do not know what we do not know, and it's because of this ignorance that incomplete or inefficient decisions can be made to solve problems that other, smarter people may have resolved years before. Not a day goes by where I don't learn something new about the tools I use, and I hope this does not change anytime soon.

There's a certain excitement that comes from reading about an interesting feature or function, then trying it out and thinking about how it might be used to solve a real problem elsewhere. Back in November, I said that 10C would switch from MySQL to SQL Server just because I wanted to gain some experience with the platform on Linux. The conversion was finished mid-December, and nobody has reported any issues with the service since the switch. It was, by all accounts, one of the smoothest migrations of 10C I've ever performed. Learning more about SQL Server will (hopefully) allow me to do even more with the database going forward. More than this, I'd like to better understand how complex business problems could be solved with better use of this powerful tool.

While 10C is a personal project that I take very seriously and put a lot of care into, there is simply not enough "hard" work for me to do with the database. Businesses, however, ask a lot of tough questions. Depending on the quality of those questions, businesses may ask the question again and again in the form of reports. Being able to build the SQL queries to quickly and accurately return the answers is certainly a worthy place to use the skills I'm working so hard to acquire.

Who Will Use SQL Server on Linux?

This past weekend I was having a discussion on IRC with someone about the role of SQL Server on Linux, and who might actually use the database engine over many of the more traditional, open alternatives like PostreSQL and MySQL. The argument essentially boiled down to this: my opponent said that "nobody in their right mind who wants to use SQL Server would migrate from Windows to Linux", and I said that "SQL Server on Linux isn't for people who already have systems built to use SQL Server installations on Windows, but for people who haven't even started their projects yet". Both of us are likely correct to a certain degree, and both of us are likely over-generalising by using terms like "everyone", "most", and "developers".

I've been using Microsoft's SQL Server on an Ubuntu Server installation since it was first made available this past summer, and I've got to admit that it's a solid little engine. SQL Server has long been my favourite database, and I tend to use others primarily because my servers are all Linux-based. MySQL is the go-to, but does it have to be for smaller projects?

I don't think so.

So with this in mind, I plan on proving to my weekend opponent that SQL Server on Linux is a viable solution by migrating 10Centuries from MariaDB 10.1 to SQL Server 2017. This won't be a simple drop-in replacement, as the two systems do things differently enough that it makes zero sense to hammer a square peg into a round hole. Instead, I plan on taking the examining system and designing a proper replacement that'll play to SQL Server's strengths and finally move all of the data rules from the API layer to the database, where it actually makes sense.

10Centuries is very much a relational system, and it should be perfectly reliable on SQL Server Web despite all the licensing limitations that Microsoft has in place for that edition of the engine. If there's any change at all, it will likely be that 10C is faster after the database change. There will certainly be some performance reports after the fact. If I can drop API response time down by 500ms without spending a small fortune on more hardware (or software licenses), I'll be very, very happy.

Umm … Why?

A little over ten years ago I had an MCDBA certification from Microsoft that revolved around SQL Server 2005. Those skills are now well over a decade out of date, and I'd really like to get back into using this more powerful tool. By using SQL Server on Linux, I can gain experience with the system and also begin to understand the various things that the tool can and cannot do. I'm years away from becoming an expert, this should be a good first step down that road.

SQL Server can certainly be a great tool for people who prefer to use Linux and want to build or update their software. Just because it may not fit every use case doesn't mean it can't fill others.

Master of None

Over the last year or so I've been investing a great deal of time to improve my knowledge of both Microsoft SQL Server and MySQL. One is the greatest database engine that's ever existed1, and the other is certainly adequate for many tasks with some caveats. As with many things in life, the more we learn about a specific subject, the less we feel we know about that subject. The vast absence of knowledge stretching before me has me wondering whether I'll ever truly know either of these tools well enough to master them.

The chasm by Kevin Carden

In just 11 months I've read 8 SQL Server and 4 MySQL books that have really opened my eyes to how these tools have evolved since I was last working with databases in any meaningful way. Sure, I've used these tools a lot over the last decade, but I've never really had to deep-dive into the subjects in order to provide better support for the engines. With some recent issues at the day job, I feel there is no other option than to absorb as much knowledge in order to take on the role of DBA, as there hasn't been one inside the organization for quite some time. What I've come to learn through this activity is that a lot of what I've previously been taught as "best practice" is now anything but, and it has me second-guessing a lot of the software I've built over the years. This has me wondering if any of the other "best practices" that I've been following are outdated, outmoded, or just plain wrong.

For almost ten years I set aside software development and database work in order to earn a paycheck here in Japan, where my personality type is generally not welcome within a typical Japanese company. A great deal has changed in that time. The question I need to answer now is how to apply this newfound knowledge without essentially scrapping the work that's been done up to now on both personal and professional projects.

  1. in my opinion, anyways

One Thousand Eight Hundred

Hmm … it seems that a nice round number occurred on this site, and I had almost completely missed the milestone! Today is the day that the 1800th comment was left on my site, and the author was none other than my direct supervisor! So, to mark the occasion, I thought that I'd share a little link love with 25 of the most frequent authors, who made this post possible, and share a little SQL code with anyone that wants to know how to query their WordPress database to do the same.

Here are the most active people on this site since its inception:

  1. Nick Ramsay left 232 comments

  2. Nick left 98 comments

  3. Brian left 41 comments

  4. Mike left 40 comments

  5. msdanielle left 38 comments

  6. Contamination left 30 comments

  7. billywest left 25 comments

  8. tornadoes28 left 24 comments

  9. Thomas ( left 19 comments

  10. Brian Dunbar left 15 comments

  11. Nick Phillips left 14 comments

  12. yuni left 9 comments

  13. Drew left 9 comments

  14. Frank C left 8 comments

  15. Jon Lee left 8 comments

  16. freedomwv left 8 comments

  17. Rob left 7 comments

  18. prija left 6 comments

  19. prija left 6 comments

  20. Tony left 6 comments

  21. David left 6 comments

  22. Nihal left 5 comments

  23. Thomas ( left 5 comments

  24. SpaceElevatorGuy (Michael Laine) left 5 comments

  25. Living Off Dividends left 4 comments

The query I used linked people based on their website, rather than name. For that reason, there are a few people in the list that could be combined … but that's not something I plan on writing into the SQL query. For anyone that's interested in knowing how to query their database to get the same result, just put this into your phpMySQL Query window:

SELECT CONCAT( '<a href="', comment_author_url, '" title="', max( comment_author ) , '">', max( comment_author ) , '</a> left ', count( comment_ID ) , ' comments' ) AS Result
FROM wp_comments
WHERE comment_author_email != (SELECT user_email FROM wp_users)
AND comment_author_url != ""
GROUP BY comment_author_url
ORDER BY count( comment_ID ) DESC

As you can see from the code, anyone that's registered on the site will not be included in the results. If this is a slight problem for you, you can always change the sub-query to your own email and run it. This way, no commenters with your email address (which should just be you) will appear in the result list.

Want to limit this by date? No problem. Just add and comment_date BETWEEN '2008-01-01' AND '2009-01-01' before the GROUP BY line in the SQL query, and change the dates to whatever you'd like them to be.

Fun, right?

Want to know some SQL?

Over the last few weeks, I've been helping quite a few people with direct queries against their WordPress databases. Is there something you'd like to ask your database, but aren't sure how? Just leave me a comment letting me know what information you're seeking, and I'll be happy to share the code with you :)