Administration
Verify access to many SQL Server instances
There may come a day where you need to verify you have access to connect to a SQL Server instance. Perhaps you need to verify this access for a hundred or maybe even a thousand servers. Doing this by hand is not great and you’re going to want to capture what you did and did not have access to. This PowerShell script will help you accomplish that.
I took the majority of the script from SQL Shack which was written by Shawn Melton, so thank you for saving me a lot of the leg work Shawn! The rest of the tweaking I did was to create files / overwrite files, append a try-catch, and make the script easily executable with variables.
Get notified when a user with administrative privileges logs into your SQL Server
I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.
Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.
Permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’.
I ran into an interesting issue where all DDL and DML statements were being denied on my SSISDB, but specifically on my Internal schema. Here’s a few example error messages I saw:
Description: The SELECT permission was denied on the object ‘master_properties’, database ‘SSISDB’, schema ‘internal’. The UPDATE permission was denied on the object ‘operations’, database ‘SSISDB’, schema ‘internal’. The EXECUTE permission was denied on the object ‘insert_operation’, database ‘SSISDB’, schema ‘internal’.
Alert when SQL Server jobs fail
I would always recommend an enterprise monitoring solution for your important SQL Servers. There may be some that are less important though. You can always configure alerts at an individual job level, you can create triggers or extended events to notify you, or even use base alerts in SQL Server. If you need a quick and dirty solution to throw into place temporarily though, I like this option.
Here we have a T-SQL statement that we can either create as a stored procedure or just run as T-SQL in a job step. It looks back over the last 10 minutes and looks for job failures. I would recommend scheduling this T-SQL to run every 5 minutes, you will get duplicate entries for a short period of time, but ideally, you shouldn’t get any failures anyway right? Plus once you’re notified, you can turn this off while you work on it or you can specify in the where clause to remove this job until fixed.
SQL Server Database in Suspect mode
I’m going to preface by saying, I ran into a specific scenario and I strongly suggest you see if this scenario is what’s currently affecting you before you try these troubleshooting steps.
Scenario and problem
Recently, a database of mine was placed into Suspect mode. This database is part of a two-node Availability Group. The primary problem was that the Transaction Log drive filled up on space, this caused the DB to be placed into suspect mode. Once the DB was in suspect mode, my backups began failing. (Because the DB was inaccessible and caused the job to fail.)
Ola Hallengren not truncating T-Logs after backup. Prefer Secondary.
Scenario, you have an availability group with active / passive (read only) set up and backups are set to be preferred on secondary. You install Ola on nodes A (RW) and B (R). A few days later, your T-Logs fill up and things are broken, what’s going on?
To cut to the chase, my problem was that I did not have T-Log backups installed on A and B, I only had them installed on A while I had the Full installed on A and B. So this means each weekend we would still get a full backup. When I checked the listener or node A/B, the full backups looked great. I didn’t realize immediately to check the T-Log job on the B node (secondary). Putting the T-Log backup process on A / B and scheduling them the same fixed it for me.
Connecting PowerBI to RaspberryPi (MariaDB Engine)
It’s been a little while since I had PowerBI working against a remote repository on my Raspberry Pi and I had some troubles recalling how to configure it. As a result, it was an excellent experience troubleshooting it. This also led to me realizing, there’s no guide to configuring PowerBI to MySQL(MariaDB) on a Raspberry Pi. There are a lot of similar posts, but the problem with most of scenarios I found online is that the MySQL DB was most frequently running locally with PowerBI, whereas in my circumstance it was remote.
MySQL (Really, MariaDB) on the Raspberry Pi!
I’ve had a few people ask me lately about how I got MySQL running on my Raspberry Pi, so here’s my guide on how to go from almost 0 to 100. This guide assumes you understand the basics of Linux and Raspberry Pi’s. You’ve probably done a few different tutorials and you have tinkered with it before.
1. Download Raspbian.
2. Wipe the sd card and format it. (SDFormatter is a free tool you can use.)