Voice of the DBA

Category: Blog

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
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
Securing Test and Dev Environments at the SQL Privacy Summit
Redgate Software is putting on a one day SQL Privacy Summit on May 18 in London at the Grange Tower Bridge. This is a chance to see how you can ensure compliance with the GDPR while keeping up the modern pace of DevOps software development. Our goal is to educate you one the ways of protecting data and minimizing impact to business processes.

Use the code “Steve” when you register to save 25% off the standard rate tickets. I’ll be there, along with some of the sharp minds from Redgate and some local consultancies to talk about the topics of data privacy and protection.

We’ve designed the Summit to be for both technical and managerial staff that are working to ensure compliance with the GDPR. Our schedule is designed to help ensure that you lean from our research and experience to implement three key principles:
- Map and maintain a living data catalogue of your data estate
- Reduce your surface attack area by protecting data in dev and test environments
- Implement ongoing monitoring to ensure your data is protected.
It’s a great schedule of sessions delivered by some experienced speakers and a panel that will cover these topics and more. We even have a couple workshops to help you discuss concepts and best practices with peers.

Register today (use the code “Steve”) and I’ll see you in London next month.
April 26, 2018
Watching the Sands of Suggestion in #SQLPrompt

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about Code Snippet Code recently, and this post continues to move across the map.

Incredible Suggestions

The first thing that most people notice after they install SQL Prompt is the suggestions that pop up as you type SQL code. This was one of the first things that captivated my interest after I started using the product. Seeing lists of tables, of columns, of valid syntax, pop up as I type is so useful that I struggle when I don’t get the suggestion box. By default, this appears quickly, and one of my customizations is that I have slowed it slightly so that quick typos don’t pop the box if I get rid of them immediately.

However, as the Treasure Map shows, it’s not just suggestions, but also the fact that I can hover over a suggestion and get more data, like the code that defines a table, view, or procedure. This is especially handy when using views, as I’ll look to see if I’m starting to nest views in queries.

I do find that CTRL+Shift+D is one of the shortcuts that I often need. I may create a table or run some code in another tab, and I don’t get refreshed suggestions automatically. There is an experimental feature to auto refresh suggestions, but this means more polling of the database, and I try to avoid adding more load to processes that are running. The shortcut works well for me.

The Dependencies Tip

SQL Prompt has lots of features, and plenty that I don’t use. A few I don’t use because I don’t know about them. The dependencies tip is one of these. I didn’t know about it until I read the Treasure Map post, but now I think that’s a really cool features. If I’m looking to alter my schema, one of the important things to know is what dependencies I have. Certainly I could use SQL Search, but being able to quickly decide what objects I need to consider, or where I need to make other changes, is great.

The Treasure Map describes this, but I had to experiment a bit to understand how this works. I’ve added a short animation to show this.

Now that I know this, I’ll get the list, copy it, and use it as a TODO list of things to alter in this same commit. I’ll also know where to test changes before I actually commit this code to a shared repo where others will see it.

There are plenty of other small features in this list, and you ought to experiment with these if you write a lot of T-SQL. You’ll find them to be helpful and handy.

April 24, 2018