Voice of the DBA

Author: way0utwest

The Redgate Hub

It’s live, but we are having a livestream event to launch this officially to the world. On September 6, 2017, I’ll be in Cambridge at the Redgate office to host a live event.

Register today and join us for one of the two times to watch what’s new at Redgate.

The Redgate Hub is the place we’ll have most of the Product information and knowledge customers need, with the goal of making it easier for you to learn about and use our tools.

I’ll do some writing there, and answer questions when I can, but check out our Livestream and visit the Hub.

August 29, 2017
Graphing Performance

We have a lot of different database platforms to choose from when building software. Most of us reading this are SQL Server users, and likely relationally biased. However, key-value stores, document databases, graph databases, and more are out there. If you work with developers that embrace change and new options, likely you’ve been asked about implementing some sort of NoSQL database instead of SQL Server for some project. Maybe you’ve even been asked to migrate away from SQL Server to an Open Source (OSS) NoSQL platform, with the lack of software cost being a factor.

I do think that there are some domains of problems that relational systems don’t handle well. Certainly at scales (data volume or rate), there are better ways to deal with some data sets in a less structured and tightly coupled way. We see that in the large scale web companies like Google, Twitter, Facebook, etc. If these companies had tried to build their entire system on a RDBMS platform, they would have struggled to grow, and maybe not even reached the size they are.

I’ve been reading and playing with the new graph capabilities of SQL Server 2017, trying to determine what I think of the concepts. Certainly large scale many-many relationships don’t seem to be a strength of relational databases and I’ve thought there are certain types of queries or data models that might be better handled by a graph database.

Then I ran across this report from a few researchers that examine how graph database compare to relational ones. After all, we’ve grown accustomed to using RDBMSs in many environments and situations. What better way to evaluate the performance of a specialized database than compare its performance in the problem domain its designed to solve to that of a general database platform.

The results are a little surprising. Even with a sub-optimal query language, I would have expected the graph database to perform better. Instead, relational seems to handle the reference graph workload better. Raw performance isn’t everything. Ease of development and ability to scale are important. There may be other considerations in your system as well, but I did find this to be an interesting paper.

We will see how the world of specialized databases handles real world workloads over time as more companies use them, but for now, I’d be skeptical of replacing an existing, working RDBMS with something unproven. I’d need to see a good POC that shows quite a bit of improvement across a variety of metrics, not just scalability.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

August 28, 2017
The World of MySQL

I had two different people ping me on the same day with MySQL questions. One was a former co-worker, asking for some query help, and one was from the Boy Scouts of America, also asking for some query help for a few reports. Strange that both requests came on the same day.

I didn’t have a MySQL install on the latest incarnation of my desktop or laptop. I did pop MySQL Workbench on a laptop last year, playing with Azure deployments, but no server. I deployed a MySQL instance on an Azure VM, but I had trouble connecting. Either the Ubuntu instance didn’t have the MySQL ports open (should have in the template), or I typed the password wrong. Either way, I just decided to delete that resource group (I love resource groups) and download the MariaDB server.

It’s a standard download from Mariadb.org and it’s not too large. In fact, it took longer to download than install. The install was a standard msi installer, and ran in a minute or two.

From there, I started up MySQL Workbench, which I’d downloaded and installed. I entered my user and password, using the default ports for the server.

And things worked, as they should in 2017.

From there, I selected the data import and chose the file I’d been sent. It was (supposedly) a MariaDB dump, but listed as a .sql file. Looking through it, it has SQL and data inserts, so I ran it with the Data Import.

This ran for less than a minute and things were complete.

And sure enough, some MySQL syntax (googled to remember) works.

Now to debug some queries for a friend.

August 28, 2017
Responsible Log Growth with dbatools

I really like the dbatools project. This is a series of PowerShell cmdlets that are built by the community and incredibly useful for migrations between SQL Servers, but also for various administrative actions. I have a short series on these items.

I love dbatools, and I’ve been trying to explore the various cmdlets over time, both to practice my PoSh and see if there are easier ways of accomplishing some tasks.

Recently I was browsing the dbatools.io site to see what’s changed and noticed a new cmdlet, Expand-DbaLogResponsibly and just had to check this one out.

Often I’ve considered proactive space management a part of my DBA responsibilities that is core to a well run system. I don’t look at storage every day, but I usually try to look monthly, with an eye towards keeping about 3 months worth of data growth as a pad in most systems. That’s a general guideline that varies by system.

However, logs can be different. They are sized based on workload and backup schedule, which usually works. However, sometimes there are issues and I want to grow my log quickly. If you’ve read about growing log space (like this piece), you’ll realize that there are a variety of things to consider. And the rules change by version, or even patch level, of SQL Server. A lot to consider.

Fortunately, this cmdlet wraps those things up into a command. Just call it with the server, database, target size, and optionally log FileID. It will capture the rules for growth and take appropriate actions.

NOTE: This does not look at the current number of VLFs. You need to be sure you’ve managed your log file.

If you wonder how this works, or if the rules are appropriate for your system, check out the code at Github. I’m sure you’ll find that this is the easiest way to manage a log file.

August 25, 2017