Voice of the DBA

Author: way0utwest

Getting a Day Difference in PowerShell–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

SSMS froze on me the other day. Actually, it lightly responded to some things, but the window wouldn’t redraw and I couldn’t see the query window. I could see the results pane, but couldn’t get the app to respond.

I wanted to get the difference between two dates, and wasn’t sure, so I quickly searched. I tried assigning the date to a variable, but this creates a string:
```
$start = “2020-03-11”
```
With a couple searches, I learned I can use Get-Date to get a date variable. In this case, I’d do this:
```
$start = Get-Date -Date "2020-03-11"
```
If I did that with two dates, I could get the difference. Here’s a screen show that shows I get the result in a variety of different time slices.

If I wanted just days, I could do this:
```
($end - $start).Days
```
That returns just the 712.

I also learned I could shortcut this with a TimeSpan type.

New-TimeSpan -Start “2020-03-11” -End (Get-Date)

I get the same spread of time parts as the image above, or I can enclose this all in parenthesis and then call the “Days” property to get that value.

SQL NewBlogger

I hadn’t done much with date and time in PoSh, and after seeing an article from an author, I investigated a bit more. This was a part of what I tried to do, albeit as a response to something not working as expected.

Good to know how to work with dates, as I can see this being a part of many PoSh scripts that might clean up old files or otherwise take action based on time values.

You could write this post in about 10-15 minutes and show how you use PoSh to work with date and times.
February 21, 2022
Backup Architecture

I saw a question posted recently on what data is included in a full SQL Server database backup. I hadn’t seen that question in some time, but the post was a good reminder that this is not an intuitive concept, and new data professionals might not understand how a full backup works. If you don’t know, you should do a little research (and write a #SQLNewBlogger post for yourself).

The way a SQL Server backup works, either with an on-premises install or the Azure SQL Database version, is well known and documented. Even if you can’t make a “normal full backup” in Azure SQL Database, the process is the same. You don’t have to run the backup, as Azure does that for you, but you can specify a restore and understand which data will be available in your restored database.

Cosmos DB is a different type of data store, existing only in Azure and storing non-relational data. The service has been promoted quite a bit, and some of you might even be using it. Do you understand how backups, and more importantly, how restores work?

I ran across an article that discusses the way Cosmos DB continuous backup works. This process isn’t quite what I’d expect. Changes are backed up locally (either LRS or ZRS), which makes sense. However, all changes (called mutations for some reason) are backed up within 100s, asynchronously. That’s good, and it’s not perfect, but it’s pretty good. What’s more, you can restore a container, a database, or the entire account. That matches up closely with what I expect in Azure SQL, including the need to restore into a new account. What isn’t great is that stored procedures, triggers, and UDFs aren’t restored.

As with any sort of backup and restore operation, you should be sure you understand the way operations occur, the impact of restores, and the costs involved. You get charged for backup space and restores. Maybe the most important thing to know is how to perform a restore. If you have a problem, you want to be sure that you not only know the mechanics of restoring data but how to reconcile any potential changes between the old and new database, as well as how to ensure all other objects (stored procs, etc.) are put back in place and clients are directed to the correct database.

This process might not be as simple as MS Docs describes, and certainly, I’ve found SQL Server restores are not always as simple as we might like. Practice ahead of time and be sure you can recover a system in a way that meets your clients’ needs.

Steve Jones

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

February 21, 2022
Daily Coping 21 Feb 2022

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. All my coping tips are under this tag.

Today’s tip is to look back at the pandemic; what are the strangest memories you have?

I wrote this tip because I ran across some old pictures from the last two years. Here are a few things that struck me as strange.

I found myself doing yoga on the front lawn because the gym was closed.

I had really taken it for granted that I could go to the gym every week. Maybe not every day, but I’d get there and be able to take a class or use weights. While I could do yoga alone, and I had some videos out in the yard, I was surprised that a) I didn’t have other options, and b) it worked out pretty well. I enjoyed being out there in the spring and am looking forward to it periodically this year.

I’ve traveled a lot for years, and I’ve seen plenty of people in masks in airports and on planes, albeit a minority. I still can’t believe that we got so many people to wear masks, we had regulations, and I coached in one with kids playing sports in one.

I’ve enjoyed some online gatherings, but I can’t believe that I was a part of multiple online classes for cooking or cocktail making. If you had asked me my interest level in 2019 for this, I would have said zero. The last two years I’ve looked forward to a few.

I’ve lived in America for most of my life. This is the land of plenty, and often, excess. I’ve lived in places with hurricanes and blizzards, and seen shops quite empty at times because the demand far exceeds the supply, at least for some items.

I was surprised to see so many empty shelves, especially a lack of toilet paper, early in the pandemic. A very strange time for me.

In line with that, I remember coming out of volleyball practice one night and going to the store to get things for the next day, only to find it closed. All stores were closed around 8 or 9pm. Quite an experience for me.

Perhaps one really strange thing for me was a lot of time with horses. Not just around them, as that’s normal, but on them. I never look to ride very often, but I will if my wife asks. We plan some trips, but around the house I’m usually busy or traveling myself.

In 2020, I ended up taking more rides with my wife around our property and the local area because there wasn’t much else to do. No distractions, nothing open, no work trips, so I spent a lot of time on horseback.

Well, not a lot, but a lot for me.

Overall the pandemic was not that hard for me. In some ways, it was enjoyable as life slowed down, more time with family, I had all my kids some again for a period of time, and I used all my vacation.

Maybe that was the best part. Usually work gets in the way of too many personal trips, but in I managed to get out, camp and travel a bit, using up all my vacation with family.

February 21, 2022
Getting Started with TeslaMate

This is part of a series that covers my experience with a Tesla Model Y.

One of the things that a Tesla car does is make a lot of data available. From the mobile app I can not only control the car, but I get see where it is an how fast it’s going. There is more, but it’s a neat idea that I saw lightly implemented in my BMW X5, but in a very crude, 2000-era way.

Tesla is gathering a lot of data, and they’ve made it available to owners. You can log in with your Tesla account and get access to data. In fact, quite a few people are offering services, like Stats, Tezlab, and TeslaFi. I tried a couple, but really I didn’t like the idea of my data flowing out to third party services. There’s already been a hack with one.

Then I found TeslaMate, a project that a Tesla owner built, allowing you to run your own service, capture data in a PostgreSQL database, and run your own dashboards in Grafana. There’s a lot of setup here, but I decided to run it in Docker, which was easier. My plan is to move this to the cloud at some point, but for now it’s on my home machine.

This is what I did.

Setup

I have Docker Desktop running, so pulling the container down was easy enough to do. Then the instructions have you set up a docker-compose file. If you’ve never done this, it’s basically a YAML description of the containers and options you want to run. In this case, I’m configuring four containers: the teslamate service, the mosquito message broker, the granada site, and one for postgres.

I set up a folder on my machine and put the docker file inside of it. I changed the config with my own passwords and left the port alone. I left the volumes along, which was a bit of a pain as I had to track down the location for the data to back it up. Not hard, but a pain.

I then downloaded the Tesla Tokens app, which allows me to see the tokens that the Tesla website will return to me when I log in. This took a little googling around the Internet to figure out, but once you have this, then your container can log into your account and access data.

Then I built a CMD file, which looks like this:

I set this up in the Task Scheduler to run whenever the machine boots. This ensures that if I restart, my containers are running. Overall, this ensures I get most data.

Once I had it running, I can connect to port 4000 on my local machine. To make this easier, I set up DNS entries (since I own a domain) that map from a name to 192.168.100.200 (I have a different internal network). This makes it easy to get to the dashboards.

The View

Once I had the containers running, I can go into the Overview, and I’ll see things like this:

I have this list of dashboards:

Each of these has different sets of data available for me to view. For example, I can geofence my house, and then enter the cost of charging at home. This lets me see my charges:

I can also see my drives and get an idea of how much it costs me in power to go places. Here’s one of the recent ski trips I wrote about.

There’s a lot more data to analyze, and most of it I don’t care about, but I do like to see how much power I’m using on average and where I’m going often. It’s not that helpful, but it is interesting. One thing I could have guessed is how temperature affects things, but living in Denver, the most efficient times are the majority of the weather here.

I like the ability to gather my own data, and analyze it. At some point, I want to move the containers into AKS or another cloud service and monitor from there.

Of course, I back up my data periodically, and need to get an automated system running (I do have BackBlaze). Something for another day.

February 18, 2022