I’m heading to Boston next week for SQL Saturday Boston 2023. I went to this event last year and I’m glad to be going again. It’s a great event, well run, and I have a lot of friends in the area.
I was honored to be selected, and I’ll be delivering my Architecting Zero Downtime Deployments, so if you’re in the area, register and come see me and others.
We’ve been doing some events as part of the Redgate Roadshow, and at one of the events, we had a customer ask about something that we demo’d. This post looks at quick access to snippets.
This is part of a series on SQL Prompt that I’ve written. If you’ve never tried SQL Prompt, it’s amazing. Download an eval today.
When typing a query in SSMS, I can hit the CTRL button and get the palette on the side. Here’s a basic query:
![2023-09-04 04_39_35-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]](https://voiceofthedba.com/wp-content/uploads/2023/09/2023-09-04-04_39_35-sqlquery3.sql-.dlm_1_dev-way0utwesthp_way0u-89_-microsoft-sql-server-ma_thumb1.png "2023-09-04 04_39_35-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]")
I might want to add the begin and end around my code in the proc. If I highlight some code and hit CTLR, I see this:
![2023-09-04 04_40_25-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]](https://voiceofthedba.com/wp-content/uploads/2023/09/2023-09-04-04_40_25-sqlquery3.sql-.dlm_1_dev-way0utwesthp_way0u-89_-microsoft-sql-server-ma_thumb1.png "2023-09-04 04_40_25-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]")
On the left, I have opened the palette where I can down arrow to pick something relevant or search for a command that helps me. As you can see, the first one is the very common “add begin end” to the code. If I pick this, it will surround my code with begin end, as seen here:
![2023-09-04 04_43_07-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]](https://voiceofthedba.com/wp-content/uploads/2023/09/2023-09-04-04_43_07-sqlquery3.sql-.dlm_1_dev-way0utwesthp_way0u-89_-microsoft-sql-server-ma_thumb1.png "2023-09-04 04_43_07-SQLQuery3.sql - ..dlm_1_dev (WAY0UTWESTHP_way0u (89))_ - Microsoft SQL Server Ma_thumb[1]")
I can also get other helper code, like the CTE outline, as you can see below:
SQL Prompt is amazing, as this video shows. Download an eval today.
One of the things that happens with many companies that start adopting DevOps is that they release new features constantly. They publish their lists of changes, and they try to attract customers and grow their businesses. They may make some mistakes, but they fix those quickly and keep pushing forward. That’s the idea, and it works well.
However, many of the developers (and most managers), don’t think about the security side of their changes. This piece looks at the way hackers and criminals view DevOps, often using release notes and feature changes as a target to focus their efforts. In this way, they exploit holes and vulnerabilities in software to attack data storage. The examples include S3 buckets of storage and Elasticsearch, which is notoriously poorly secured by many people.
I’m sure there are hacks that also expose relational data stores and NoSQL stores, but those are often more secure and harder to directly attack. Certainly, hackers do get credentials and can query data, it’s more often I hear about data breaches from other sources than direct relational database access. SQL Injection is definitely still an issue, and I hope that more and more developers are learning patterns that avoid these vulnerabilities.
DevOps works. I think it’s great. However, it’s not enough to trust developers to build features without including static code analysis, pen testing, and other security evaluations before you release code. Often developers can build features just as fast with good patterns as bad. Use automation to catch bad patterns and force developers to learn new ones.
Also, avoid letting developers implement data stores out of convenience and speed. Ensure that strong security practices, long passwords, service accounts, secrets, and more are implemented from the start. It’s easy to shortcut these, but it’s also harder to explain to customers and investors why we didn’t do better.
If you’re a developer, the main thing to keep in mind is that you will often be the scapegoat for these issues. Upper management might not support you and pressure you to move faster, but when there are issues, they’ll also be quick to blame you and let you go first. Push back and ensure that you have the tools and the process to evaluate if you are building problematic code. Always use long passwords, and document what you do for others to follow.
And if your boss insists on cutting corners, get that in writing. It might not save your job, but that documentation has served me well in the past when issues come to light.
Steve Jones
Listen to the podcast at Libsyn, Spotify, or iTunes.
There was a failure recently at an Azure data center in Australia when a utility power sag caused equipment to trip offline at one of the Azure data centers in Australia. You can read about it here, but essentially the headline is that there were only three people on site when the incident occurred, and that caused them to be unable to restart the equipment in time before an outage occurred.
In a little more detail, there weren’t enough people to quickly restart the equipment chillers after the incident. The staff had to access the equipment on a roof when 13 of the units didn’t restart. They were able to get to 8, but when they got to the last 5, the temperature of the water had risen to a level that wouldn’t allow a restart. So they had to power down some computer equipment and go through a more lengthy process to get everything running.
This sounds bad, but in reality, this is exactly the type of thing I’ve seen in private data centers, who almost never have all the staff they need, or the knowledge necessary, to deal with large-scale failures. While I haven’t seen the chillers, I have seen people trip electrical systems and be unable to restart or reset UPS’s or generators for hours until qualified staff could come in. If you read the incident history, there is a good retroactive of what happened, and then some actions taken to try and prevent this in the future. They increased staff levels but also identified some places where the previous staffing level would have been fine with some equipment and protocol updates.
I wish more organizations would review incidents and examine them with an eye towards not only what happened and where there were failures, but how to prevent issues in the future. Too often I see people going through this exercise in order to blame someone and “prevent this from ever happening again”, which usually means we fire someone and don’t change anything else. We need psychological safety in reviews of actions to get better.
As we build more complex systems, or even more complex organizations with lots of teams, people, equipment, procedures, etc., it’s easy to build in lots of points of failure without realizing there will be problems in the future. My goal often these days is to assume I’ll have some inexperienced or less capable staff and design processes and systems to survive issues. To keep things simple, and not get too cute with engineering. I like robust, resilient systems that anyone can operate, not those that require me to ensure my senior superstars are always on call.
Of course, it often takes the senior superstars to design and test these systems and protocols, which is a good use of their time.
Many businesses struggle with staffing, in many areas. Technology groups are no different, and we have to learn to work smarter, not assume we will just get more staff and solve our problems.
Steve Jones
Listen to the podcast at Libsyn, Spotify, or iTunes.
Voice of the DBA 