Do You Have Big Data?

Posted on September 9, 2020 by way0utwest

Data sizes are always growing. Stats on world data are astounding, as are the stats many of us experience in our lives. Plenty of us have moved from MB management to GBs, and I see plenty of people dealing with TB storage at home. Most of that data is likely from images and video, but I wouldn’t be surprised to find some people capturing lots of IoT and other random data about their lives.

As data professionals, likely we don’t quite have the explosion of data inside our organizations, but we certainly do have increasing database sizes. I see this with customers all the time, often with continued or expanded collection of data and no archival plans. The number of customers with > 1TB databases increases every year.

Overall data volumes are growing at a faster rate than our growth in processing capabilities. That is nothing new, as we’ve always collected more data than we can easily process. Even if you avoid image, video, and audio data, likely you have more CSVs, extracts, and export files than you know what to do with. Maintaining this data is hard, and dealing with all the associated systems is a challenge.

I wonder today how many people think you have big data issues. No matter whether you deal with GBs, TBs, or something larger, do you feel that managing all your data is a big part of someone’s job? Can you analyze all your data, at least at the rate your customers would like? Can you keep up with queries and extracts?

This article talks about some of the challenges, and how the problems with big data are going to to continue to plague us for some time. Does the article resonate with you? Or perhaps you feel that you have everything under control. I’d be curious in either case. Leave me a comment and let me know.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Posted in Uncategorized | Tagged | Comments Off on Do You Have Big Data?

Daily Coping 9 Sep 2020

Posted on September 9, 2020 by way0utwest

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here.

Today’s tip is to forgive yourself when things go wrong. We all make mistakes.

Things go wrong all the time. Especially at the ranch, where many things break, or get broken.

Recently I took a day off to work on some baseboard trim in the house. We had some work done in a couple rooms, and the old trim was taken out and tossed. It didn’t match the rest of the house, and it’s been something I wanted to replace. At $6 a foot for a contractor, who wasn’t thrilled with a 2 room job, and a material cost of $1.42 a foot for about 120ft, I decided to do this myself.

If you’ve done this, you know it’s not hard, but it’s not a piece of cake. Some attention to detail is needed, and some care. As I worked my way around the two rooms, I find the flor wasn’t quite level, and so sometimes the trim needed to bend over a slight rise or meet a dip. This resulted in a few joints not matching perfectly. Add to this that I cut a few pieces incorrectly as I got them down.

I know some imperfections get covered with paint or caulk, but as I got close to finished, I wasn’t happy with two pieces. Rather than get upset, I just pulled them out, cut new pieces, one I cut 2 or 3 times, and made things slightly better.

A small thing, but it’s a lesson I’ve learned from work and life that I will make mistakes. I should aim for, and work to, limit mistakes and try not to repeat them, but sometimes I do, and it’s not the end of the world. Move on, correct things as best I can, and forget about them.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 9 Sep 2020

The Best Security for Database Administrators

Posted on September 8, 2020 by way0utwest

A short while ago, Twitter got hacked. I wrote about this, as did Denny Cherry. I think Denny’s piece was more interesting, as he speculates about the security measures that Twitter may, or may not, have had in place.

One of the things Denny brings up is air-gapping administrative machines. I’ve rarely seen this in organizations, and perhaps see this less and less in the pandemic world. There are some high security places that do this, but could your organization do this? How many of us use cloud or co-location facilities where we can’t even physically enter the premises?

I suppose we can use some strong network security controls, perhaps even requiring static IP addresses for people at home and specific routes for certain administrators. I do know some companies that do require specific laptops for access, with limited software, but this certainly isn’t the norm. Too often a general laptop used for most work performs double duty as an administrative workstation with access to production data.

Another thing Denny mentions is jump hosts, without any cut/copy/paste functionality from the remote machine to the host. This is something I am starting to see from customers, even smaller ones, as a way of limiting the chance of ransomware or some security breach. Multi-factor authentication gets an administrator onto a remote desktop session on a jump host, from which they can access production systems with limited tools. This certainly isn’t perfect, and it is annoying for administrators, but it is a good security layer, and it forces organizations to use good, compliant, database DevOps practices to deploy changes.

Perhaps the best part of Denny’s article is the title of the last section: good security shouldn’t be user friendly. It shouldn’t be for administrators. While we might make things slightly easier for average users, anyone that can access bulk amounts of data, especially in a privileged fashion, should have strong security, which is a bit cumbersome. I think the hassles of strong security would be a good thing for more of us to have to deal with. Hopefully more organizations will start taking better precautions and reduce the chance of attack.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

Posted in Editorial | Tagged | Comments Off on The Best Security for Database Administrators

Automation Ideas for T-SQL Tuesday #130

Posted on September 8, 2020 by way0utwest

tsqltuesdayThis month we have another great T-SQL Tuesday topic, and again, a host that I pressured into writing the invitation. Elizabeth Noble (@SQLZelda) and I were talking DevOps last year at a SQL Saturday, just as she was effecting some change at her employer. At an event later in the year, I challenged her to host, and here we are, with a great topic, Automation.

That’s this month’s invitation. Elizabeth describes the process of using automation to smooth out their deployments to SQL Servers. They slowly built a CICD pipeline and migrated projects over and over to save her time.

This month, what have we done to automate things? I have a few stories.

Automating Data Collection

One of the things that I am passionate about is the SQL Saturday events. I loved that PASS maintained a site, and made a feed of event data available, but over time, we’ve lost some data and I don’t think there is much impetus to maintain this over time.

As a result, I build an automated process to grab data from the feeds and save it as an XML file on my local machine. I have the basic code up at GitHub, though I need to improve and refactor it a bit. These files change as the organizers update them, so I need a good merge process. Right now I tend to delete all 1-2KB files periodically, as the file is built once the event is approved. However, until all the speakers and sponsors are scheduled the file size is just a few KB. Most events are > 100KB once they are set.

This is a basic way of grabbing some data, and I’m looking to build a few more data collection processes to grab data in my life and keep it around, just in case some service I use goes kaput.

Automatic Databases

I work at Redgate, and one of the things I’ve been spending more time doing is the automation of databases for development with the latest code and test data. This is a challenge, but a few of the things that I’ve done with Redgate that help are as follows:

  1. Use Powershell to automate the creation of SQL Clone images.
  2. Use PowerShell to update SQL Monitor alert values and add instances
  3. Use Flyway in a container to deploy changes
  4. Use our SQL Change Automation cmdlets to deploy changes to databases

All of these items can be automated so that a user just needs to run a script, or sometimes just click a button.

I’m slowly getting the PoSh code up on GitHub as I build demos and help customers get things done. Right now, getting organized is the biggest part of my job, because I find these questions coming over and over.

I’m a bit proponent of DevOps and automation. Use tools and computers to do the tedious, repetitive work. Keep your day focused on solving problems and building/improving scripts.

Posted in Blog | Tagged , , , | 1 Comment