Daily Coping 2 Mar 2023

Today’s coping tip is to make plans with a friend or loved one.

I don’t like making plans, preferring to somewhat flow with life and make decisions close to the time when I’m ready to do something. However, that isn’t always convenient.

Recently my wife and I both got a mailing for a comedy club in the area, with some special deals. We chatted and decided to book a show, getting a date night set up together. We don’t do that often enough, but we took the opportunity this time to make plans with each other.

I started to add a daily coping tip to the SQL Server Central 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. All my coping tips are under this tag.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 2 Mar 2023

Shutting Down Coping Tips

It’s been nearly 3 years of Daily Coping Tips here at the Voice of the DBA. I started these when the pandemic hit and the world shut down. I’ve enjoyed them and they have helped me deal with life, and more importantly, think more about life.

However, they do eat up some time, and I am planning on stopping on Mar 24, 2023. That gives me 3 years of daily workday tips, ending on that Friday.

Hopefully these have helped you, and if you miss them, feel free to flip through the past ones and see what I’ve suggested and how I’ve implemented the tips.

If you want to set up your own calendar moving forward, looking for ways to make life better, I’ve been inspired by the Action for Happiness calendar, which comes out each month. I’ve made this one of my wallpapers for the last three years.

Posted in Blog | Tagged , , | 6 Comments

Adding a Computed Column–#SQLNewBlogger

Recently I needed to add a computed column to a table and realized that I didn’t remember the syntax. This short post show how to do this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Adding the Column

I had a table, OrderHeader, and wanted to add a new column, OrderedByDate. I can do this with a simple ALTER statement and an ADD. The inserting part is the computation. You use as AS clause with the computation instead of a datatype. For example, my code was:

ALTER TABLE dbo.OrderHeader 
   ADD OrderedBy AS OrderDate;
GO

This added a copy of my OrderDate column with a new name. This is useful for zero downtime deployments in some cases, and in this case, I wanted to just have a copy. However, if I wanted some calculation, I could easily do that by specifying this as I would in a SELECT statement. For example, if I wanted the computed column to be a week later, I could do this:

ALTER TABLE dbo.OrderHeader 
   ADD OrderedBy AS DATEADDD(DAY, 7, OrderDate);
GO

This would add a week to the original value and return that. I can likewise do any sort of string or numeric manipulation I want. A common one is adding or multiplying two columns together for a new value. For example, adding various charges for a total in an order.

When you do this as a computed column that is not persisted, no space is taken in the actual table rows. If you persist this, then space is used.

More information on Microsoft Learn.

SQL New Blogger

I realized that I needed to double check the syntax in the docs, and when I did, I took this as an opportunity to write a short blog post. I grabbed a link, wrote some code, and then spent 10 minues knocking out this post.

If some employer does this a lot, they might search your blog to see if you can do this. A nice few posts on how to do this, what persisted does, how you index this, etc. Take a few minutes and start blogging on topics like this throughout your week.

Posted in Blog | Tagged , , | Comments Off on Adding a Computed Column–#SQLNewBlogger

Stale Data Causes Security Issues

Security has become better and better in many organizations. At the same time, hackers and malicious actors are doing a better and better job of finding new ways to attack systems. Some work to target specific individuals, often because of government or industrial espionage. Most of us aren’t likely to deal with those issues, unless we work with (or are) someone that is very important in a particular situation.

Instead, many of us deal with wider spread attacks that look to exploit vulnerabilities in technology or humans at scale, hoping to find the weak links. Lots of people I know have dealt with viruses in the past that shut down systems, and more recently, had to rebuild systems crippled by ransomware.  Despite their best efforts, this often means lots of extra unexpected work, combined with the stress of falling behind on our commitments. We have plenty of other work to do.

Windows has been vulnerable throughout its history, and it appears, lately through a data problem. There is a class of attacks that look to use approved, though old and unpatched, drivers as a vehicle for gaining a foothold inside a network. This was a problem (called the BYOVD issue) and Microsoft addressed this with a block list that was used to prevent the loading of vulnerable drivers. They updated this list through Windows Update.

Except they didn’t. In database terms, we had an eventually consistent set of data, which was being updated at Microsoft, but not being sent to client workstations. There were over 3 years of the list not being updated, despite assurances from Microsoft that Windows 10 PCs were protected. There are instructions for manually updating your machine.

I don’t envy this being a process I’d want to build. Getting data from security researchers or elsewhere, putting it in a database (I hope), then exporting this into a text format and getting that loaded into the Windows Update process, all while trying to ensure the process is secure along the way. That can’t be easy inside a large company like Microsoft. At the same time, not noticing this wasn’t working isn’t excusable. Likely there were issues, but my guess is someone didn’t want to admit a failure and get a bad annual review.

Data sync issues are nothing new, and many of us struggle with these on a weekly basis. However, these are important issues. Replication can fill log files (and disks), broken ETL processes can cause execs to make poor decisions, and in the security space, not updating drivers and block lists leave us vulnerable.

This situation isn’t excusable and Microsoft ought to be ashamed. Some of these execs ought to lose bonuses, at the very least. It’s also not excusable in our orgs. We ought to be sure we’re patching on a regular basis and minimizing the attack surface area we present. It’s the least we can do as IT professionals.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

Posted in Editorial | Tagged , | Comments Off on Stale Data Causes Security Issues