Voice of the DBA

Author: way0utwest

Confusing Networking Behavior

The other day I got on a Skype for Business call and I struggled to hear the other people. I looked at the interface, and saw red where the network connection was represented. Since I was communicating with the UK, and I sometimes see issues, I assumed this was our office over there.

Then it happened the next day. I decided to assume it was my issue, and I ran a speed test.

That’s not great, but that looks OK. I’d think this was enough bandwidth for a video call. No one else was in the house, so there shouldn’t be contention. After contacting both Redgate’s IT group and my provider, I ran a ping test.

If you know anything about networking, you know this is no bueno. The periodic long times will cause stuttering and problems with real time communications. A short, 4 ping test might not show this, but over time, you can see things repeating.

In this case, it seems something is wrong with my connection.

However

That’s not the issue. I called my provider, and they didn’t see issues from their end. We restarted the router (hard start) and things didn’t see to improve. They escalated, which is a callback, so I went on with my day.

This was bothering me and I had more calls, so I decided to restart my machine. After the system came up, I started shutting services down. No Spotify, no TweetDuck, nothing with network access.

In the meantime a call started, which had stuttering and issues, as you might expect if regularly there was a 1-3s delay for packets. In this case, I finally got to my Crashplan backup process. This was backing up files, which is something that has been happening for a few years. However, as soon as I shut this down, my video/audio issues stopped. In fact,

That’s more what I have been experiencing for years.

Since then I’ve restarted that process a few times, just to see, but issues haven’t returned. In hindsight I should have tried video from another machine and made sure this was a networking issue and not a “this machine” issue.

I’ve been good at troubleshooting in my career, but this is one of this times that I didn’t really do a good job of looking at the trees and not the forest. I made assumptions and experienced issues for a few days that were unnecessary.

A good reminder and lesson for me.

May 2, 2018
Auto-Deleting Data

Email has been a part of my life for well over nearly thirty years. It’s kind of amazing to think of a time back in high school when there weren’t electronic communications. That seems foreign now, as we don’t have to send physical objects or converse with voice to communicate. The world of communications have changed dramatically.

These days email is still a preferred method of communication for many people. Even if we use sites like Facebook, NextDoor, or SQLServerCentral to communicate with others, we often get notifications of changes through email. If you’re like me, many of you might keep far too many emails in your mailbox, rarely removing unnecessary ones. In the era where we measure storage in dozens or hundreds of GB, or even in TB, do we bother to even manage text communications?

Some firms do require this, often for legal reasons. With the GDPR, I wouldn’t be surprised to see more organizations starting to set retention policies that ensure that communications don’t live forever. There are some systems that do this now, but the practice isn’t ubiquitous, but maybe it will be soon. Google is redesigning Gmail, which will include Confidential Mode. Not only will there be limits on these emails, but one of the more interesting is the ability to expire an email and have it automatically deleted.

I don’t love the idea of having communications disappearing, but that might be because of the way I’ve grown up. I don’t like using Snapchat with my kids, because I don’t want pictures I take to disappear. However, younger generations don’t feel this way. As I wonder why I try to hold onto old communications and records, I start to wonder if the idea of expiring data is something that we should be embracing as data professionals. Do we really need sales records from a decade ago? Are recordings of web traffic valuable from the early days of SQLServerCentral? Is there really any point to holding onto much of the data we generate?

I know that there are corporations that hold onto decades of paper records. I worked at one that had nearly a 100 years of old records, most of which might never be examined again. Likely plenty of them aren’t even legible or useful at this point. They’re being stored for, well, I’m not sure why. I’m sure there are plenty of writers that might come up with a detective story that requires old paper records, but I’m not sure there’s practical use for this data.

I expect that we’ll start to see organizations changing their record retention policies as we look to avoid more liability and risk from data breaches. Every old record, every piece of PII data that we no longer use probably needs to go. Even records for existing customers might need to be removed. I don’t necessarily need to ever access the record of my first Amazon order from 1998. I’m really sure that Amazon having liability for holding my old address, which potentially could be used to validate identity, is a bad idea for both them and me.

Steve Jones

The Voice of the DBA Podcast

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

May 1, 2018
Learning Goals–April Update

Here is the update: I did better.

Extended Events

I did a little more multi-tasking this month, ensuring that if I rode the stationary bike for exercise, I had my tablet and watched some Pluralsight or read some content.

Here I learned a few things:

Predicates

These short circuit, and this means that we want to write our predicates to end early if possible. We also want to write our predicates to look at local data before global data so that we can stop event code as early as possible.

There is also a 3000 byte limit, so be careful with structuring the event predicate statement.

I also found that u_tables.sql lets me rebuilt the default sessions for an instance. It’s in the install folder. That’s handy as there are times that I’ve removed the default sessions or changed them and I’d like to get back to the beginning.

Event Sessions

The structure for event sessions is like DDL for other objects. These are always created with events, but we can remove all events. I’m not sure why you want the placeholder, but it’s an interesting anomaly. I suspect that this is a hole in the DDL parser.

I also learned you can drop sessions without stopping them. Convenient? Sure. However, this means that I could make a mistake and not get a question about dropping a session that I, or someone else, is using.

Changing event or target configuration means dropping and adding back the item. That’s good to know. No way to alter an event predicate.

Python

I made it a point to do some coding every week, and work on Python. I did some fundamentals, ensuring I understood how modules work and are imported. I spent some time on variables and mutable/immutable items. I wanted to be sure I understood how these worked compared to other variables. It’s good to know that some of the assignments don’t create a new variable, but merely set a pointed to the existing item.

I set up a Jupyter notebook in Azure, which was interesting. I need to use this more, and perhaps do some coding here on the Advent of Code or other projects. I solved a couple more days of puzzles, which was fun. These are challenging, and it took a little time to work through items.

I didn’t do this every week, as I had some travel this month, but I did make an effort to work in python a bit more.

April 30, 2018
Replication Gets Some Love

I really like replication in SQL Server. At least, I like it as a concept. It solves some hard problems and lets me move data around in a way that can handle larger loads, reduce queries to OLTP servers, and more. I’ve been hoping Microsoft would see this feature as critical to the future success of SQL Server and enhance it’s tooling, reliability, and feature set. Each time a new version is completed, I’m hoping that the Release Notes include a lot of replication changes.

I’m usually disappointed, but not always. Recently I was excited by a change in a CU, not a new version. The SQL Server team added the ability to put the distribution database in an Availability Group. This is incredibly useful and helpful for ensuring HADR for replication scenarios. Prior to this, you could put the publication database in an AG, but not the distribution database. This is being added in CU6 for SQL Server 2017 and will be back ported to SQL Server 2016 in a future CU.

There are plenty of restrictions in this first version of the feature, including the fact that local distributors aren’t supported. In fact, with all of the ways you can’t use this, I bet many replication environments can’t implement this.

Yet.

I have hope that future CUs will enhance this feature to remove restrictions and allow more flexibility with replication in AGs. I think that handling naming and networking issues in HADR situations is incredibly complex and replication was built quite some time ago, when we didn’t think so deeply about distributed systems. I slowly see Microsoft adapting parts of SQL Server to the modern world, and I hope that they continue to do so for replication.

Steve Jones

The Voice of the DBA Podcast

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

April 30, 2018