Musings of a Data professional

Stuart Moore

Category: Uncategorized Page 1 of 3

Pi-Hole and Plusnet

As a nice little lockdown project I decided to up our home network security game a bit. And it was a good excuse to finally buy a Raspberry Pi. I’ve fancied one for years, but always worried it’d end up buried in a drawer not getting used.

I’ve been using Steven Black’s blocking scripts (https://github.com/StevenBlack/hosts)on my Mac for ages now, and they do cut down the amount of annoying ads and other distractions that cover the internet these days. Seemed rude to not have the same protection running on all the hosts in the house. I am just running the lists that block know advertising sites and malicious site. There are also lists to block adults sites, gambling sites and others if you want to restrict what your network can be used for.

But maintaining the lists across multiple machines would be a complete pain. The iOS devices don’t let you do it for a start, and some machines I’m not allowed to play with, like C’s work laptop.

Pi-Hole

Enter Pi-Hole which is basically a DNS server that implements Steven’s host lists at a network level. A DNS server is what turns Domain Names like stuart-moore.com into the IP Addresses that the internet actually runs on, which in this case would be 77.72.1.19

Pi-Hole works by stepping in between your hosts and the normal DNS, and if a request is made to a ‘bad’ domain then it will answer the request with the address 0.0.0.0 rather than forwarding it on for the real answer. Your browser will ignore this address and it’ll be like your request just fell into a black hole

It has a very simple, almost automated (almost!), install which will get you up and running pretty quickly. Steven’s various host lists can be selected, and you can add you own or from other sources as well

Installation and Hardware

There’s a lot of good PiHole installation documents out there so I’m not going to repeat them here. The one I used was the official one here is the one I followed

Hardware wise I ordered myself a Raspberry Pi 4 2GB kit from The Pi Hut. Came with almost everything I needed, the only extra bits I added were a heatsink and a Cat 5 cable as shamefully I don’t have any lying around the house now it’s all Wifi

2GB seemed plenty enough to run PiHole and some VPN stuff I wanted to use (more details on that in another post), and it saved a few sheckles.

The install went really easily and soon I have my own DNS server running on a fixed IP address. During the install I’d selected to have all external DNS queries relayed to Cloudflare’s DNS servers (1.1.1.1) via an encrypted channel, so no chance of them being sniffed. An extra layer of security there.

Now to make it really useful I had to get that address out to all of the devices on the network. This is where PlusNet comes in

Configuring it with PlusNet

We have a nice simple router that came with out PlusNet fibre. Years ago I used to have custom firmware and things, but having a bit of an SLA with C we went to something simple and easy that I wasn’t likely to break at an inopportune moment.

Unfortunately the PlusNet router doesn’t give you very much control over the networking settings. To make configuring all the devices (and any new ones) easy I wanted to push the new DNS IP address out via DHCP so they’d pick it up automatically.

While the PlusNet router gives you a few options to alter a very small number of configuration values, the DNS server address isn’t one of them. Turns out this has been a know issue with these PlusNet routers for a while, with a long running thread on the PlusNet forums complaining about it

So that option was out, but…..

The solution to the PlusNet problem

Luckily PiHole comes with a solution to this. It has it’s very own DHCP server built in! So all I had to do was to configure that. It’s very simple to do.

Once you installed Pi-Hole it provides you with a nice Web admin interface to configure it with. Under Settings you can find the DHCP opion:

All you need to provide is the range of IPs you want you devices to be on. Keeping it on the 192.168.1.x range makes it easier with the PlusNet router as they’ll all be on the same subnet.

2 things to note in my screen shot:

  • A 1 hour lease is really short. I’ve set mine like that while I’m experimenting
  • The PlusNet router gateway is 192.168.254, use that one. Mine is different due to my VPN setup

You can only have 1 DHCP server on a network or the clients get confused over which one to get their configuaration. Now that we have an alternative to the PlusNet DHCP server we need to disable DHCP on the PlusNet router

To disable it
login to you PlusNet router by going to http://192.168.1.254
Click on the ‘Advanced Settings’ button
Login using the password from the back of your PlusNet router
Continue to Advanced Settings
Select ‘Home Network’
Select ‘IP Addresses’

You should now be at a screen that look like this:

Tick No next to ‘DHCP Server Enable’. Then click on apply at the bottom right, confirm you’re sure, and you’re all done.

Your little Raspberry Pi and PiHole are now controlling your DNS and your DHCP settings for everything on your home network.

PiHole so far

I’ve only had mine in for a little less than a week, and we appear to be blocking about 8-10% of DNS queries atm. Which is a solid amount of web queries that we’re not having to transfer over the network, so things are feeling a bit speedier than before.

Also seeing fewer pop ups coming through, and some sites are much more readable without the inserted images and videos getting in the way.

Looking at the load averages on my little Pi, it seems to be coping with all this with it’s feet up. So there’s plenty of room to add some more bits and pieces onto it. I’ve already added an outbound VPN connection for our outgoing traffic and there’ll be more coming along as well

Conclusion

Adding the Pi-Hole was a simple and easy way to block out annoying internet adverts and potential malicious sites for every device on our network. If we had children then I’d definitely be using some of the stricter lists to keep an lid on what they could stumble across.

It would have been simpler if the PlusNet router allowed you to alter a few more settings, but disabling it only takes a few clicks through simple admin interfaces to sort out.

Microsoft Data Platform MVP – Year 3

I’m very proud to have been selected to be a Microsoft Data Platform MVP for the 3rd year running. Until you’ve finally ‘signed’ the NDA you’re not sure if you’re going to be re-confirmed or if there’s been an admin mistake.

It’s quite a bit of waiting from the end of March to now to find out if you’re activities have been deemed worthy of renewal. No one’s quite sure what you have to do to become and MVP, which is good because it allows Microsoft to reward different skill sets or pick people who support the community in different ways, which just adds to the suspense as you don’t even know what or how much they’re looking for.

I gave myself a little talk the night before, about how I’d spent the year doing all of the things I normally do in the community and enjoying myself whilst doing them. So if I wasn’t reconfirmed then I’d lost absolutely nothing. My technical skills were going to be exactly the same and the talks I present would be just the same in terms of quality and content.

I think if you’re just doing community events to ‘rack up points’, you’re missing quite a big point. If you’re only doing something to try to become (or keep) being an MVP then it’ll probably be noticed, but not in the way you want to be.

Having run events before and after becoming an MVP I can tell you that it’s not the though of ‘MVP points’ that keep you going when things breakdown, it’s the wanting to provide something fun and interesting for people in your community.

All of the MVPs I know and have met, are doing this because it’s fun. This makes working with them or learning from them fun. Someone who’s just ‘grinding’ away trying to level up will not be as much fun to learn from or cooperate with, and people will notice.

So if you’re a fellow recertified MVP, well done for keeping on doing what you’ve been doing so well. Hopefully we’ll start bumping into each other again once life is less virtual

Or if you fancy becoming an MVP one day, then get into things because they’re fun and things may happen along the way. If you don’t become an MVP, then at least you’ll have had a lot of fun along the way and met a lot of great people who are part of the technical community.

So this year I’m just going to keep on doing what I do each year and fingers crossed, this time in 2021 I’ll be looking at MVP number 4. If not, I’ll still have a grin thinking about everything I did and more knowledge than I do today, so nothing to be upset about there.

Journaling, my year so far

No, this isn’t an in depth discussion of various *nix filesystem internals. This is about the old fashioned idea of keeping a written journal on a regular basis.

I’ve been journaling for most of 2020 so far, 158 days of entries so you can work out the miss/hit ratio yourself 🙂

I started on the first of January, wanting to capture what I actually do and achieve over a year. And also as a way to record things like mood, sleep and my running. Not because of any great historical legacy, but just because I wanted to see how things correlated and see if I could improve things

Starting off

I started off as cheaply and easily as I could, with a £1 reporters notebook and the first pen I could find with ink in it.

My original journal. Cheap as chips from Wilkos

The first entries were very perfunctory, and usually just a single side of writing. Picking a random day in February I seem to have been concerned about Project ‘X’ at work, the chance of getting rained on during the next day’s run to the office, and if I needed a smaller notebook for my ToDo list so I was more likely to carry it around.

So, no Earth shattering revelations or Zen like wisdom seeping out 38 days in then.

Getting into it

It really has come into it’s own as we’ve been locked down for COVID. Looking back now I can see we started out personal lock down on 14th Mach (we’re so cool, we started early) as that was the first mention of C’s illness. In that way it’s been good to keep track of that happens when time and life seem to go very slow or almost stop.

Not being in the office, and having to rely on the magic that is Teams to talk anything through it’s not always possible to gauge what’s going on or what the end game is, and to simmering over insignificant issues.

Often, just writing something down is enough to see a solution or take some weight off. I’d quite often find it wasn’t as big an issue as I expected, or that it’s something I need to flag up for a more senior answer. Not having anyone looking over my shoulder I’m freer to write down the first answer that comes to mind, and then to negate that in the next sentence as I spot why it was a stupid idea. Gradually inching my way to a solution

The longer I’ve been journaling, the longer my entries have become and the easier I’ve found to just put down anything that comes to mind. When I started there was a bit of hesitancy that you’re ‘oversharing’, but the realisation creeps in that you’re only sharing what you already know with yourself.

Occasionally I’ll throw in a prompt to make me think about something. Nothing to deep, so I stick to the 3 standards that all Journaling sites recommend:

  • What are you grateful for today
  • What do you want to achieve tomorrow
  • What went well today

I tend to throw these in if the entry seems to be a bit of a downer. Just because I’ve been struggling with a SQL Server bug for hours, it doesn’t mean that the day was a complete write off, so this forces me to think back and look for something good in the day. Or if the day has been a hell storm of despair, I can acknowledge that but try to give myself something to look forward to tomorrow.

It’s also a great way to spend 15-20 minutes concentrating on something that doesn’t involve either your work or home life. You might be writing about them, but the action is completely separate. I tend to finish the kitchen chores in the evening and then spend the time before I head up to bed sitting at the kitchen table writing it out. I find it leaves me calmer and a bit more peaceful as I head upstairs.

You don’t need to handwrite it! I’m sure, like myself, and a lot of people reading this don’t write long hand much outside of meetings. I wanted to improve my handwriting this year, so writing things out in long had to make sure I practiced regularly. I also wanted to make sure I wasn’t distracted by any incoming alerts or the ‘Oh, I could fix that quickly’ thoughts. If you want to just write a daily .txt file in your Journal folder, that’s great as well.

To me the real benefit just comes from the doing, not how you do it. My journal is pretty plain, maybe a horizontal line between entries, but that’s about it, but it works for me. If you prefer some drawing, or more colour a la Bullet Journals then that’s great as well

I don’t beat myself up if I miss a night. Real Life crops up a lot, if we’ve been out then it might too late by the time we get back. Or if I’ve been out for a couple of pints my handwriting goes from bad to unreadable pretty quickly. I’ll try not to miss more than one night on the trot though. With Covid we’ve not been anywhere, but I’d take my journal with me in future.

Routine

Like all habits, once you’re on the treadmill and have a routine then it becomes second nature. I look forward to writing each evening now, and even treated myself to my first fountain pen since High School 28 years ago, some quality ink and a nice Lamy notebook

my current journalling

Now I have the routine, I’m not sure if I’d want to stop really. It has been good to flick back to see what’s happening or remind myself that things are changing. And there will always been something to write about

Give it a go

So, go find an old notebook and a biro, or fire up your text editor of choice, and give it a go for a couple of weeks, you might find up you enjoy it and keep on going for a lot longer.

Break in service

First off, apologies to everyone who who was following the 31 days of dbatools Backup and Restores, I will finish it off! Just need to get back into a bit more of a stable routine so I’ve got time to finish it off.

Like everyone else my life has had a big change with the current COVID lockdown in the UK. I’m pretty lucky that I can work from home and that my employer wants us to keep working so no risk of redundancy or furloughing (yet 🤞)

Not having a proper home office made the first couple of weeks quite painful, as long days at the kitchen table weren’t kind on my back. But with some luck on eBay, I’ve now got myself somewhere more ergonomic to work.

Getting used to this new way of working, and also having to pick up some new skills (today I’ve been debugging a JS app) has meant working on my setup, planning skills and the software I have available to me. So there’s going to be some posts on those topics coming up over the next couple of weeks as they’re easier to fit in around my current schedules.

Hope everyone else is doing well in lock down, and look forward to when we can start getting back to meeting up at Data Platform events.

Day 6 – Backup-DbaDatabase in scripts – 31 days of dbatools Backup and Restores

Day 6 – Backup-DbaDatabase in Scripts

One of the great things with dbatools is that you can pull all of the different commands together to make some really powerful scripts for yourself.

Backup-DbaDatabase is no exception. In today’s post I’m going to show you a couple of things you can do with it, and hopefully inspire you to come up with some ideas for your own usage

Day 3 – Backup-DbaDatabase Filenames and paths – 31 days of dbatools backup and restores

Day 3 – Backup-DbaDatabase Filenames and paths

Yesterday we looked at the basics of using the Backup-DbaDatabase function. In all of those examples the backup files were going to the default backup location of our SQL Server instance, and using the default name generation process of Backup-DbaDatabase.

This often isn’t what you want, so we offer you plenty of options on how to create and handle paths

Day 2 – Backup-DbaDatabase Basics – 31 days of dbatools backup and restores

Day 2 – Backup Basics

One of the most basic things a DBA needs to do is to take reliable backups. Before you undertake any piece of work you need to have somewhere to roll back to in case it all goes horribly wrong.

The dbatools Backup-DbaDatabase function can really help out here.

While it can be automated with SQL Agent jobs, it’s not it’s real strong point. For that you’re better off sticking with Ola Hallengren’s maintenance solution.

The Basics

At it’s most basic all Backup-DbaDatabase needs is a sql instance:

Backup-DbaDatabase -SqlInstance server1\instance 

This will take a full backup of every database on instance server1\instance into the instance’s default backup location

This assumes that you’re running Windows authentication on your SQL instance. If not, you need to provide the credentials to connect with.

You do this with the SqlCredential parameter like so:

 
Backup-DbaDatabase -SqlInstance server1\instance -SqlCredential $credential

To create a credential you have 2 options:

Using Get-Credential. This will pop up a box for you to enter the username and password to create the credential:

$credential = Get-Credential

Or you can create one on the command line, though this will leave your password in the command history:

$password = ConvertTo-SecureString 'P@ssw0rd' -AsPlainText -Force
$credential = New-Object System.Management.Automation.PSCredential ('username', $password)

Just in case you don’t know what the default backup location for an instance is, you can grab it like so:

Get-DbaDefaultPath -SqlInstance server\instance

If you want to backup a specific database or more then you can use the -Database parameter

Backup-DbaDatabse -SqlInstance server1\instance -Database db1, db4

which will backup db1 and db4. or you can pipe in values from Get-DbaDbDatabase like so:

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase

Which will back up every database whose name ends in test on the server\instance. Really hand for when the devs want to deploy something new

Other backup types

Now, it’s not always just about the full backups. Of course we can handle differential and log backups, just a parameter away

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase -Type Differential

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase -Type Log

There is one other type of backup that is very important to be aware of, the COPYONLY backup. When a SQL Server database is in full recovery mode you want to make sure that you don’t interrupt the backup chain. This can ruin your chances of performing a successful restore.

For instance, if you’re using Differential backups. Each differential backup is the sum of all changes from a specific full backup. If you take a new backup, all subsequent differentials are based on that one. That can be a problem if you’re just grabbing a backup to refresh test or to investigate an issue.

A COPYONLY backup does not break the chain, so it can be removed or deleted once it’s no longer needed without breaking your recovery plan

To take one of these, just use the -CopyOnly switch:

Get-DbaDatabase -SqlInstance server\instance | Where-Object {$_.name -like '*test'} | Backup-DbaDatabase -CopyOnly

Testing a backup command

Maybe you want to try something out and see what’s going to happen, but you don’t want to actually do the backup?

That’s what the -OutputScriptOnly switch is for. This will go through all the same processing as a full run would, the only difference is that Backup-DbaDatabase will just spit out the T-SQL without running the command.

Running this:

Backup-DbaDatabase @Script:appsplat -Database roles1 -FileCount 3 -OutputScriptOnly

will not perform a backup, but will produce this for you:

BACKUP DATABASE [roles1] TO DISK = N'/var/opt/mssql/data\roles1_202004281714-1-of-3.bak', DISK = N'/var/opt/mssql/data\roles1_202004281714-2-of-3.bak', DISK = N'/var/opt/mssql/data\roles1_202004281714-3-of-3.bak' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, STATS = 1

We’ll be covering what some of those other options are in a post in a couple of days

Backup-DbaDatabase Output

Backup-DbaDatabase produces a lot of output that can be of interest. Quite a lot of it isn’t shown at first glance so we’ll take a look at what’s available:

$output = Backup-DbaDatabase @Script:appsplat -Database roles1 -FileCount 3

Now, the standard output looks like this:

Which gives you the basic information. But if you then ask for everything in the output you get a lot more information:

That’s just about every stat you might want for your backup, or everything you might want to test for after taking the backup.

Conclusion

Today we’ve looked at the basics of what Backup-DbaDatabase can do, tomorrow we’ll be looking at handling backup files

All posts in this series can be found at 31 Days of dbatools Backup and Restores

 

Data Platform Discovery Day 2020 Agenda available

After sifting through a huge heap of great session submission we finally got it narrowed down to 10 sessions for the US day and the European day.

We’ve got some really top quality speakers and sessions for you. We’ve got introductions to Power BI, Azure Data Factor, Containers, SQL Queries and using Azure Notebooks for Data Science

To have a look for yourself the agendas are here:

Data Platform Discovery Day 2020 – US Agenda

Data Platform Discovery Day 2020 – European Agenda

You can attend one, or both if you don’t mind a very early morning or very late night. Registration is for the entire day, but you can drop in and out for the sessions you want to see.

To register head over to these links:

Data Platform Discovery Day – US Registration

Data Platform Discovery Day – European Registration

More details are available from the event website – Data Platform Discover Day website

Custom Teams Backgrounds on Mac OS

With the current pandemic enforced usage of Teams over chatting at the water machine I wanted a way to hide the background.

My wife’s a nurse, so the home ‘office’ is hers for patient confidentiality atm, which means I’m nomading around the house. This means my background varies a lot, so having something different than an untidy shelf of books, the kitchen counter or a blinding window glare behind me would be nice

There’s plenty of windows hints on having a custom Teams background on Windows, but not so much about having them on a Mac. With a bit of digging it turn out that if you want a custom Teams background on your Mac it’s quite simple after all.

All you need to do is to drop a file into:

~/Library/Application Support/Microsoft/Teams/Backgrounds/Uploads

If you’re not used to *nix paths, the ~ translates to your home directory. So for me on my Macbook Pro with the username stuart, that ends up being

/Users/stuart/Library/Application Support/Microsoft/Teams/Backgrounds/Uploads

Just drop the images you want Teams to use as a custom background into that folder and they’ll show up in the gallery.

It seems Teams isn’t picky about which file format you use for a custom background. I’ve tried png, jpeg and gif, and they all work fine. Unfortunately animated gifs don’t work, which is both a good and a bad thing depending on your personal love of gifs

Announcing Data Platform Discovery Day

With a number of the large in person Data Platform conferences and meetups having to cancel or take a hiatus due to the current Covid-19 outbreak, Matt Gordon (b | t) and myself decided to do something about it.

So we came up with Data Platform Discovery Day. A 2 day online event aimed at those people who are looking for a start in the Data Platform world or are wanting to learn some fundamentals about an aspect of it.

Each day with run alongside business hours across a different continent, and will feature ten 50 minute sessions. Each day will have different speakers, and if you want there’s nothing to stop you attending both if you fancy an early morning or late night

Day 1 is running during US daytime on 29th April. The first session will begin at 9am Eastern Time, and then a new session will being at the top of each hour

Day is running during European daytime on 30th April. The first session will begin at 9am BST (UTC+1), again with a new session starting at the top of each hour.

Registration for the events with start once we’ve finalised the speakers.

And talking of speakers, the Call for Papers for both events are open. You can submit to both events if you want. Please remember that sessions are 50 minutes, and content should ideally be Level 100 material.

US Data Platform Discovery Day call for papers

European Data Platform Discover Day call for papers

We’d love to encourage first time speakers as well, so if you’d like ask any question then please get in touch with me (twitter) or Matt (twitter)

Page 1 of 3

Powered by WordPress & Theme by Anders Norén