T-SQL Tuesday #155–Using Dynamic SQL for SQLCMD

It’s that time of month, and I’m the host this month. I wrote the invitation last week and now its’ time to answer. I’m actually using an example from the past that I was reminded of. That was the basis for the invitation as well.

SQL Slammer

I don’t know how many of you remember SQL Slammer, but it hit my company, JD Edwards, over a weekend. I was away on holiday, coming home Sunday night and got called into the office that night.

One of the challenges with JD Edwards was that we used MSDE extensively. It was a part of some of our products, and developers had it on various machines, lots of multi-instances, and in all sorts of development servers. The worm crippled our network.

We also had non-standard installs, so when we got a patch from Microsoft, it didn’t work because we weren’t in the c:\Program Files\… that they expected.

I had to some some fancy dynamic stuff to get the patch to work. First, we used some queries in SMS (Systems Management Server) to find all the places where we had MSDE and SQL Server services. This wasn’t too hard, and I had a list of hosts and instances from here. Now the hard part.

I needed to query all these instances and find out where things were installed, as well as get some patch information back. The SQL itself wasn’t too hard, but connecting to and querying all these machines wasn’t simple. This was the pre-PowerShell  era. and VBScript wasn’t as easy, or bulletproof, to write.

Excel to the Rescue

I’d used Excel to help with this type of thing in the past. I would  put in some data in a column in this case, the hosts and instances. Then I’d add the same value in other columns, like SQLCMD. Then I would concat these together to make a string I could run. I also included T-SQL code in here, as I’d be querying various tables inside the engine.

I also had an output part of the command, so that when I copied the contents of my final column, I had hundreds of SQLCMD command calls that would query all our instances and return data in a way that we could use to run the patch.

Double dynamic code!

I put these in a batch file, ran them, and then we had results that could be used in a similar process with the MS patch to patch all machines.

A long 2-3 day of getting systems patched and slowly turning our network back on.

Posted in Blog | Tagged , , | 1 Comment

Daily Coping 11 Oct 2022

Today’s coping tip is to take a small step towards a goal that matters to you.

I’ve been working on a coaching certification. I don’t really need it, but it will help in the future if I end up changing to a new club or role. I’m trying to take time every few days to work through a video and a quiz.

This is a good reminder to keep doing that, so I’ll watch one tonight.

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 11 Oct 2022

Things I’ve Learned About the Cloud

I do a little cloud work, but mostly I end up working with customers that are trying to build and migrate their systems to the cloud. The transition to cloud-first took place quicker than I expected, though certainly the pandemic of the last 3 years has continued to accelerate the moves by many organizations. While not all systems will move, for better or worse, many organizations consider the cloud for everything and then decide to accept or reject the notion for individual cases. That’s the cloud-first world I see.

At a recent event, I had numerous people telling me that their groups were mandated to move many systems to the cloud in a lift-and-shift fashion, and many of the technologists weren’t happy. That’s a conversation for another day, but listening to them and others has taught me a few things. Some of these are experiences I’ve learned myself, and some are things from others, nuggets gained from their hard experiences.

First, lift-and-shift appears to be a way to get started in the cloud. Some people find this to be an end-goal when they can get systems running without ever worrying about hardware or facilities. While costs can be higher, flexibility and tax considerations can make this worth the effort. Others see this as a first step to moving towards Platform-as-a-Service (PaaS) products and rewriting software, and life-and-shift is a good way to approach this process, but that leads me to the second nugget.

Everything takes longer to get settled in the cloud than you expect. Not that the cloud is slower to build and use resources. The opposite is certainly true as provisioning anything is fast, and upgrades (or downgrades) are incredibly flexible and quick. However, making the move, getting systems running to your satisfaction, decommissioning old systems, and more take longer. It seems most life-and-shift projects end up taking much longer than estimated, and not all of this is the massive data transfers needed to move data assets. Often it’s humans that can slow the process, whether through debate on decisions or just getting comfortable with cloud resources through testing. Things take longer.

Maybe the last big thing that I’ve seen affect many companies is that we have to put better software development techniques and infrastructure configuration in place. The connection to resources is more complex and tenuous than it is on a LAN. We need to better architect software, really architect it as we were told to do so early in our careers with better error handling and retry logic. The cloud is very secure when you set it up right, but you no longer have the castle with high walls that you grew used to in a data center. Instead, you have a series of people and systems that are disparately connected and you need to ensure you protect each one along with the connections between them. That requires lots of configuration work and standards, and likely Infrastructure-as-code. Plus there are new tools to learn and the habit to build of using command line interfaces.

I love the cloud. In many ways, I prefer it over building and managing things on premise. Not for everything, and not for things where I might lose productivity with slow connections. I certainly don’t want developer laptops or workstations in the cloud, but having other systems there can remove a lot of the hassles of managing assets that we purchase, track, upgrade, etc. I don’t know I’d ever want to buy another physical server in a company I owned, but I also know that at some scales, it might still make sense.

The other thing I’ve learned about the cloud is there are a lot of unknown unknowns, as well as plenty of known unknowns. Tackling the cloud require a staff that wants to grow and learn with the platform, as well as one that knows when to use the resources they know best and don’t add complexity or novelty just because someone wants to try something new. The cloud is worth considering, especially as it seems physical offices and data centers are becoming rarer and rarer in many organizations.

Steve Jones

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

Posted in Editorial | Tagged | Comments Off on Things I’ve Learned About the Cloud

Daily Coping 10 Oct 2022

Today’s coping tip is to write down three things you can look forward to in the next month.

Easy.

  1. Visiting my daughter. I’m heading up this weekend to watch her play and hang out
  2. SQL Saturdays – Boston and Toronto, exciting to get back to a few events
  3. Open Gyms – Volleyball won’t start for another month, but I have a few gyms I’m hosting for kids that aren’t playing now. Looking forward to seeing them out on the court.

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 10 Oct 2022