Backup Architecture

Posted on February 21, 2022 by way0utwest

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.

Posted in Editorial | Tagged , | Comments Off on Backup Architecture

Daily Coping 21 Feb 2022

Posted on February 21, 2022 by way0utwest

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.

20200519_130614_HDR (1)

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.

20201121_164505

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.

Exav7IGVgAA9oRI orig

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.

20200314_163948(1)

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.

20200814_153603

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.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 21 Feb 2022

Getting Started with TeslaMate

Posted on February 18, 2022 by way0utwest

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:

2022-02-12 13_53_02-teslamatestart.cmd - sqlsatwebsite - Visual Studio Code

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:

2022-02-12 13_38_19-Overview - Grafana — Mozilla Firefox

I have this list of dashboards:

2022-02-12 13_55_53-Overview - Grafana — Mozilla Firefox

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:

2022-02-12 13_56_46-Charges - Grafana — Mozilla Firefox

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.

2022-02-12 13_57_35-Drives - Grafana — Mozilla Firefox

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.

2022-02-12 14_00_05-Efficiency - Grafana — Mozilla Firefox

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.

Posted in Blog | Tagged , | Comments Off on Getting Started with TeslaMate

An Active Job Search

Posted on February 18, 2022 by way0utwest

I caught this link in a Brent Ozar newsletter. It’s a post from Alice Goldfuss noting she’s on the market for hiring. Not that she’s looking for a job, but rather, saying she’s willing to talk to you if you want to hire her. Quite a bold announcement, but she’s quite a talent that many organizations might want. I don’t know that many of us could post something and have companies come find you, but I did like the list of items she lays out for a hiring conversation.

These are the types of questions that you might keep handy, even as notes, for your next interview. I’ve often had a series of questions like this that I ask when I go into an interview for a job. I’ve learned that I need to be curious and careful if I want a successful position for both me and the company. Both of us are trying to put our best foot forward, sometimes stretching the positives and minimizing the downsides. That means that both sides can be surprised later by how the relationship actually proceeds.

Many of us proceed from job to job, often taking the first offer we get whenever we are looking for a job. That usually occurs because we need to make money and cover bills. The timing of job interviews and offers usually means that we can’t often consider two at once. Sometimes we can, but it’s been rare for me.

My view has been that the best time to find a new job is when you have a job. If you are unhappy, or you feel that you want a better position/challenge/compensation/etc., then look for alternatives. If for no other reason, you should be aware of how your current job compares to others. It’s easy to get complacent and not realize that the world of employment has changed dramatically.

At the same time, I also ask you to actively think about the things that you like and don’t like about your job. Think about what you really want from employment. It could be the compensation, the hours, the challenge, the colleagues, the benefits, or anything else. Make a real list, and then assign some weights to these factors and decide what is important to you. I actually like the Thymometrics that Redgate uses to track employee satisfaction, and I take the data I’ve entered and use that to evaluate my own satisfaction with employment. I do this every year, making an evaluation of my own position and then making a conscious decision to stay. I’ve written about the service, and I love it.

For 14 years I’ve made the decision to stay in my position, usually because I don’t find other positions offering a better balance of the things important to me. I could make more money, but I’d have to change other things, and I haven’t found the overall value from alternatives to be worth me making a move. I hope you can say the same, but in order to do that, make sure you are actively thinking about what you want from employment, evaluating your current environment, and then actively managing your career in the way the direction that matters to you.

Steve Jones

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

Posted in Editorial | Tagged | Comments Off on An Active Job Search