Month: April 2024

Query Azure MySQL Audit Logs

The common practice for user management in Azure AD is to create Azure AD Security Groups for users and assign permissions to groups rather than individual users. For example, the common practice for users who need access to Azure MySQL instance would to be add them to an Azure AD Group (say MySqlDb_Users) and then setup this Azure AD Group for access on Azure MySQL Instance.

Pre-requisite

  • Ensure that Audit Logs are enabled on all the production instances for Azure MySql as documented here — https://docs.microsoft.com/en-us/azure/mysql/howto-configure-audit-logs-portal
  • Make sure to enable Auditing on Server Parameters section (at least Connection audit log event type).
  • The logs can be saved to Storage Account, Event Hubs or Azure Log Analytics Workspace. We used Log Analytics Workspace for the runbook because it provides rich querying capabilities.

The following Log Analytics Query and the corresponding screenshot shows that individual user activity is tracked in the Audit Logs in spite of them using the Azure AD Group as username to connect Azure MySQL — external_user_s field logs the exact individual user, connection_id_d and thread_id_d carry over the Connection/ThreadId which can be used to tie the user across all log records.

AzureDiagnostics
| where Category == "MySqlAuditLogs"
| order by TimeGenerated desc
| project TimeGenerated, event_class_s, event_subclass_s, user_s, external_user_s, connection_id_d, thread_id_d, event_time_t, sql_text_s, db_s, table_name_s, error_code_d

Restore all databases from a backup folder using powershell

# Import the dbatools module
Import-Module dbatools

# Set the folder path where the backups are located
$backupFolderPath = "E:\MSSQL\Backups"

# Get all backup files in the folder
$backupFiles = Get-ChildItem $backupFolderPath -Filter "*.bak"

# Set SQL Server instance name
$serverInstance = "localhost"

# Loop through each backup file and restore each database separately
foreach ($backupFile in $backupFiles) {
    $backupFileName = $backupFile.FullName
    $databaseName = $backupFile.BaseName

    # Restore the database
    Restore-DbaDatabase -SqlInstance $serverInstance -Path $backupFileName -Database $databaseName -SqlCredential sa
}

Powershell to mirror the folder structure of an entire drive to another drive

To mirror the folder structure of an entire drive to another drive using PowerShell, you can modify the script to iterate through all folders on the source drive. Here’s the updated script:

# Define source and destination drives
$sourceDrive = "E:"
$destinationDrive = "S:"

# Function to recursively mirror directory structure
function Mirror-DriveFolders {
    param (
        [string]$sourceDrive,
        [string]$destinationDrive
    )

    # Get all folders on the source drive
    $sourceFolders = Get-ChildItem -Path $sourceDrive -Directory -Recurse

    foreach ($folder in $sourceFolders) {
        # Construct the corresponding destination path
        $destinationPath = $folder.FullName.Replace($sourceDrive, $destinationDrive)

        # Create the directory in the destination if it doesn't exist
        if (!(Test-Path -Path $destinationPath)) {
            New-Item -ItemType Directory -Path $destinationPath -Force
        }
    }
}

# Mirror the directory structure of the entire drive
Mirror-DriveFolders -sourceDrive $sourceDrive -destinationDrive $destinationDrive

Write-Host "Folder structure of drive mirrored successfully."

How do you check if logical replication is running on a postgres instance?

  1. Check if logical replication is enabled in the PostgreSQL configuration file:Open the PostgreSQL configuration file (postgresql.conf), usually located in the PostgreSQL data directory. Look for the wal_level parameter. Logical replication requires at least wal_level to be set to logical or replica. If it’s set to logical, then logical replication is enabled. You can find the location of the PostgreSQL data directory by querying the pg_settings system catalog table: SELECT name, setting FROM pg_settings WHERE name = 'data_directory';
  2. Check if logical replication slots are being used:Logical replication uses replication slots to keep track of the status of replication. You can check if any replication slots are in use by querying the pg_replication_slots system catalog table: SELECT slot_name, plugin, slot_type, database FROM pg_replication_slots; If there are slots with slot_type as logical, then logical replication is being used.
  3. Check if there are any active subscriptions:Logical replication involves publishers and subscribers. Publishers publish changes to the replication stream, and subscribers consume these changes. To see if there are any active subscriptions, you can query the pg_subscription system catalog table:SELECT * FROM pg_subscription; If there are subscriptions listed here, then logical replication is in use.
  4. Check if there are any replication connections:Logical replication typically involves connections from publishers to subscribers. You can check if there are any active replication connections by querying the pg_stat_replication system view:SELECT * FROM pg_stat_replication; If there are entries listed here, then logical replication connections are active.

By following these steps, you can determine if logical replication is running on your PostgreSQL instance.