I’m taking the rest of the week off. I get on a plane today to go spend the week on the East Coast of the US, going to see family. I’m looking forward to the trip, and I’m not worried.
This will be an interesting time, and I’m looking forward to seeing how things go, what might have changed for flying, and how I feel, emotionally, about flying.
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 make a list of things you are looking forward to.
I look forward to life, but a relatively short term list is here:
This post looks at how to re-configure a local Azure DevOps agent when you need to change to a new pool or organization.
I got a note that one of my personal access tokens (PAT) had expired, and I wanted to update the agent. I ran config, but saw this, I need to remove the config first.
However, when I ran “config remove”, which is how you can reconfigure an agent, I got this:
In the image, the agent config asks for the PAT to allow it to authenticate to be removed. Since the agent doesn’t store this, and I don’t have it anymore, I can’t enter this. I can remove the agent from the portal, but there’s an easier way.
If you show hidden files, and if you are in tech, you always ought to do this, you will see a few in the agent folder. I name mine Agent, and inside, you can see there are .agent, .credentials, and .credentials_rsaparams files.
If I remove these three files, the agent doesn’t know where or how to connect. I can then run the config and re-set up the agent.
Do you know the difference between XLS and XLSX? They’re both Excel formats, and many of us might just use one or the other. After all, the latest versions of Excel work with both, and if you’ve been using a spreadsheet for years, perhaps you stick with the older format when exchanging data with others.
As many of you might have seen, Public Health England recently learned there is a difference with large amounts of data. They found data was being chopped off in a spreadsheet because they were using the old XLS format, which only supports 65k rows. The newer XLSX format will support a million rows, but both numbers are far below what SQL Server, MySQL, PostgreSQL, and other platforms support. Those platforms support billions, and most are limited only by the storage available.
I know that pandemic has had many groups scrambling to assemble and analyze data. We have people building dashboards and gathering data together in numerous ways, from paper and pencil to Excel to (hopefully) enterprise databases. An import into a relational store would make more sense than Excel, but I also understand that setting a schema, dealing with ETL and different formats from different sources, and other issues are a pain. There is a reason data professionals get paid a lot of money for these tasks.
To me, this highlights one of the issues of working with SQL Server, MySQL, PostgreSQL, etc., in that they are cumbersome and difficult to get started with. Even if scientists chose Cassandra or MongoDB, there would be issues, because there aren’t easy, simple client tools that facilitate work with data sets coming in disparate text files and formats.
I don’t mean to excuse this, because IT professionals should know better. If you’re using XLS, stop. Data volumes increase and you don’t want to realize you’ve hit the limit after data is lost.
I get the ease and convenience of using Excel, but stop using it for major projects once we realize these are important. Once you realize this is data that needs to be intact, secured, and protected, put it in a real platform. Excel, PowerBI, and most tools can query SQL Server.
Use those tools where you need them and where you can, just don’t use them as your database.
Steve Jones
Listen to the podcast at Libsyn, Stitcher or iTunes.
