Category: Windows

Windows Administration Blog Archives, by Peter Whyte (Database Administrator).
Includes: Database Admin, Windows Admin, PowerShell, WSL & more…

  • Introducing DBASco: SQL Server Database Admin Blog

    Introducing DBASco: SQL Server Database Admin Blog

    Greetings Tech People!

    If you’ve been following my technical journey on peter-whyte.com, I’m excited to share a new platform that dives into the world of Database Reliability Engineering and beyond – welcome to DBASco.com!

    At DBASco.com, I share my latest technical writings, focusing on a spectrum of topics catering to IT enthusiasts, database/system administrators, and those passionate about the software behind reliability engineering. The primary categories align with the ones you’re familiar with at peter-whyte.com, ensuring a seamless transition for any dedicated readers.

    This site here is now my secondary tech blog site. If you’ve been on here before you will have seen a lot of display ads, which are disabled for the time being. I was using Ezioc, and I was earning around $50-60 per month, until I neglected things for too long during 2023 and there was a drop in earnings. The site certificate had issues during 2023 Q4, and we are back fully operational as of this week.

    My enjoyment is the technical writing, refining, and seeing where tests lead me, exploring my passion for the things I do as a career and on the side. I hope you enjoy what I write, and it serves a purpose if visiting via Online Search to understand a concept or resolve an issue.

    SSMS Logo

    DBASco Blog Categories

    DBASco: SQL Server: Explore Microsoft SQL Server with in-depth insights into queries, optimization techniques, and best practices. Uncover the holistic experience, including the realm of Windows Administration that intertwines seamlessly with the life of a SQL Server DBA.

    DBASco: PowerShell: Dive into the enchanting world of PowerShell scripting with hands-on guides, expert tips, and powerful tricks. Unearth the magic of automation as you streamline your workflow with carefully crafted scripts and comprehensive guides.

    DBASco: WSL and Linux: Journey into the world of Windows Subsystem for Linux (WSL) and Linux, discovering the synergy between these platforms and unraveling the potential for seamless integration. Benefit from real-world experiences and extensive testing, providing you with valuable insights into cross-OS platform development.

    DBASco: Other: From RDMS’s like MySQL, Postgres, Redshift to semi-related technologies like AWS, and even technical guides on WordPress and website management – expect a wealth of knowledge that keeps you at the forefront of the ever-evolving tech landscape.

    SSMS Logo

    DBASco Latest Articles

    RedShift: Script to Show Running Queries
    Published on January 24, 2024
    Uncover the power of Amazon Redshift, a cloud-based data warehousing giant. Learn how to efficiently manage and analyze extensive datasets with a detailed script to reveal running queries.

    RedShift: Creating a User and Promoting to Superuser
    Published on January 24, 2024
    Unlock the secrets of Amazon RedShift as we guide you through creating a user and elevating their privileges to superuser status. Essential insights for effective cluster management.

    PowerShell Script: List Files with Sizes and Dates
    Published on January 23, 2024
    Master a practical PowerShell script that simplifies listing files in a directory. Dive into efficient file management with sizes and dates at your fingertips.

    Backing Up a Database in SQL Server Using Command
    Published on January 22, 2024
    Ensure the safety and integrity of your data with a comprehensive guide on backing up a database in SQL Server using command-line tools. A crucial aspect of database management unveiled.

    SQL Server Script: Get Last Backup Dates
    Published on January 22, 2024
    Stay informed about the last backup times, a critical element in maintaining data integrity. Enhance your SQL Server skills with a script that simplifies tracking backup dates.

    PowerShell: Counting Files, Words and Rows
    Published on January 22, 2024
    Embark on a PowerShell journey with a script that counts files, words, and rows. Discover the power of PowerShell for efficient data processing and analysis.

    Adding a Filegroup in SQL Server
    Published on January 21, 2024
    Learn the process of adding a new filegroup to a database in SQL Server. Elevate your database architecture skills with a step-by-step guide.

  • How to Set Environment Variables in PowerShell

    How to Set Environment Variables in PowerShell

    To get environment variables in PowerShell, we can use Get-ChildItem env: to list all the environment variables and $env: to return the value of a specific environment variable. This blog post should help explain this as well as how to set new values for Environment Variables.

    An environment variable is a value that is stored on a computer and can be accessed and used by programs to alter their behaviour. For example, in the AWS CLI you can set an environment variable called “AWS_REGION” and assign it a value such as “eu-west-1”. Then, you could have a program that reads this environment variable and uses it to specify the AWS region when making API requests.

    In PowerShell, you can access environment variables using a number of different cmdlets. This post covers cover two of the most common methods, Get-ChildItem and Get-Item.

    This post will cover the following:
    List All Environment Variables
    Get Value of a Specific Environment Variable
    Set New Environment Variable

    List All Environment Variables

    To list all of the environment variables on your system, use the Get-ChildItem cmdlet with the Env: drive. This displays a list of all the environment variables and their current values.

    You can also run gci which is an alias for Get-ChildItem as displayed in my screenshots below.

    # List all environment variables
    Get-ChildItem Env:
    
    PowerShell gci env:

    Get Value of a Specific Environment Variable

    To return the value of an environment variable in PowerShell, run the following:

    # Get the value of the aws_default_region environment variable
    gci Env:\AWS_DEFAULT_REGION
    
    # Do the same but alt (easier) syntax
    $env:AWS_DEFAULT_REGION
    
    PowerShell Show Environment Variable Value

    Set New Environment Variable

    To set a new value for the AWS_DEFAULT_REGION environment variable with PowerShell, use the following command:

    # Set the AWS Default Region Environment Variable
    $env:AWS_DEFAULT_REGION="eu-west-2"
    
    PowerShell Set Environment Variable

  • Git Log Author

    Git Log Author

    If you’re a Git user, you’re likely familiar with the git log command, which allows you to view the commit history for a repository. This blog post is an extra tip on the git log command, showing the --author option parameter which allows you to filter the commits displayed by the author’s name.

    This can be particularly useful if you are working on a large project with multiple contributors and want to see only the commits made by a specific person.

    Show Commits by Author in Git

    To use the –author option, simply pass the name of the author as an argument when running the git log command.

    For example, if you only want to see the commits made by “pete”, you would run the following command:

    git log --author="pete"
    

    Hit ‘q‘ to exit out of the git log return feed.

    We can also use the git one-line parameter while searching commits for an author, with a wildcard:

    git log --author="Sukki*" --oneline
    

    This will display a list of commits, each on a single line, with the author, commit message, and SHA hash of the commit.

    The git log command has options that allow you to tailor the information it displays. For example, you can use the –oneline parameter, which I covered in another blog post (including git log formatting).

  • Git Log Command

    Git Log Command

    Git log is an essential command for working with Git. It allows you to see the history of a repository, including details about each commit like the author, date, and commit message.

    Using the git log command is simple. Just navigate to your Git repository in a terminal or command prompt and run the git log command. This will display a list of all the commits in your repository, starting with the most recent.

    $ git log
    commit a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9t0u1v2w3x4y5z6
    Author: John Doe <john.doe@example.com>
    Date:   Tue Dec 1 13:45:26 2020 -0500
    
        Add new feature X
    
    commit a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6q7r8s9t0u1v2w3x4y5z6
    Author: Jane Smith <jane.smith@example.com>
    Date:   Mon Nov 30 12:15:43 2020 -0500
    
        Fix bug Y
    

    The git log command offers several options that allow you to tailor the information it displays. For instance, you can use the –oneline parameter, which I covered in another blog post (including git log formatting).

  • How to Install PowerShell on Ubuntu 20.04

    How to Install PowerShell on Ubuntu 20.04

    This post is a guide on installing PowerShell on Ubuntu 20.04.

    The Ubuntu Linux distribution being used in this demo is running as a Windows Subsystem for Linux (WSL) instance. I have not yet encountered issues with the PowerShell, Ubuntu and WSL cocktail.

    Be sure to check out Microsoft Documentation, Installing PowerShell on Linux (Ubuntu) for up-to-date information, particularly for PS Linux version compatibility and end-of-support dates.

    The following is covered in this demo:
    # Install PowerShell on Ubuntu
    # Open PowerShell on Ubuntu

    Install PowerShell on Ubuntu

    As mentioned above, I’m installing PowerShell on Ubuntu WSL for this demo. Why would you want to emulate Linux on Windows to then install PowerShell? Because it’s a great cross-platform programming language? The reason I’m performing this on WSL is that the Windows Subsystem is great for spinning up a test Ubuntu instance.

    To install PowerShell on Ubuntu 20.04, run through the Linux commands below. This is as described in the Install via Package Repository Microsoft documentation page, be sure to check that out for any updated information.

    # Update the list of packages
    sudo apt-get update
    
    # Install pre-requisite packages.
    sudo apt-get install -y wget apt-transport-https software-properties-common
    
    # Download the Microsoft repository GPG keys
    wget -q "https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/packages-microsoft-prod.deb"
    
    # Register the Microsoft repository GPG keys
    sudo dpkg -i packages-microsoft-prod.deb
    
    # Update the list of packages after we added packages.microsoft.com
    sudo apt-get update
    
    # Install PowerShell on Ubuntu
    sudo apt-get install -y powershell
    Ubuntu Install PowerShell

    The above PowerShell installation should only take a minute or so to complete, including the Advanced Package Tool (apt) updates.

    Open PowerShell on Ubuntu

    Now that we have PowerShell installed on this Ubuntu WSL instance, we can check the running PowerShell version and open PowerShell in Ubuntu.

    The default Alias for PowerShell is pwsh. You can find information about pwsh and pwsh parameters within the MS Docs as linked.

    # Check PowerShell Version on Ubuntu
    pwsh -v
    
    # Start PowerShell on Ubuntu
    pwsh
    
    Run PowerShell Ubuntu

    The above shows the running PowerShell version of 7.2.7, and we are entering PowerShell using the pwsh command.

    We can also run $PSVersionTable while in pwsh to check the PowerShell version in Ubuntu, which will show more OS and system information.

    Get PowerShell Version Ubuntu

    I hope this has been a helpful informational guide on installing PowerShell on Ubuntu. If you are interested in running Ubuntu on Windows, check out my other post – How To Install Windows Subsystem for Linux (WSL).

  • Create a New Firewall Rule with PowerShell

    Create a New Firewall Rule with PowerShell

    Create a New Firewall Rule with PowerShell

    This is a short post to share a PowerShell script that will create a new Windows Firewall Rule on a local computer.

    PowerShell Create New Firewall Rule

    We have to ensure that we run PowerShell as Administrator for this to work.

    # New firewall rule (run powershell as administrator)
    if (-not( Get-NetFirewallRule -DisplayName “Allow Inbound SQL (1433)” -ErrorAction SilentlyContinue)) { 
    New-NetFirewallRule `
        -DisplayName “Allow Inbound SQL - 1433” `
        -Direction Inbound `
        -Protocol TCP `
        -LocalPort 1433 `
        -Action Allow }
    PowerShell New Firewall Rule

    The above is creating a new firewall rule so incoming SQL Server (1433) traffic is allowed to happen on this computer.

    Other similar/useful posts I have on this include the following:
    # How to Check Windows Firewall Status with PowerShell
    # How to Enable Windows Firewall with PowerShell
    # How to Disable Windows Firewall with PowerShell

  • How to Automate PowerShell Scripts with Task Scheduler

    How to Automate PowerShell Scripts with Task Scheduler

    This post is useful if you need to run PowerShell Scripts automatically with a set schedule in Windows. In the demos below, we’re creating new Scheduled Tasks via GUI and with PowerShell.

    Task Scheduler is a Windows application that we can use to create and schedule any task. We can set our Task Actions to start programs, send emails, and display messages, or we can run any script – likely a PowerShell script since we’re on Windows. These Actions can be triggered on any schedule you configure your Scheduled Task to use.

    As a Systems Administrator, adding a Scheduled Task in Windows is something we might need to do regularly. As a SQL Database Administrator, we’d use the SQL Server Agent to schedule PowerShell scripts and other tasks. And Linux Sysadmins, they’ll use Cron.

    This post covers the following :
    # How to Create a Scheduled Task using PowerShell (CLI Option)
    # How to Create a Scheduled Task for a PowerShell Script (GUI Option)

    How to Create Scheduled Task using PowerShell

    First, we need to create the PowerShell script that we want to be executed o a schedule. In the screenshot below, I’m creating an example .ps1 script in my c:\temp directory for this demo.

    The script below updates the content of a text file, logging the current Average CPU of the computer with date/time. You could have anything in your PowerShell script, e.g. Restart-Service *ServiceName* or Restart-Computer.

    Create PowerShell Script

    Now, I’m creating a new Scheduled Task that will run the above PowerShell script on a daily Schedule. The following script uses the New-ScheduledTask and Register-ScheduledTask cmdlets.

    $actions = (New-ScheduledTaskAction -Execute 'C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1')
    $principal = New-ScheduledTaskPrincipal -UserId 'Administrator' -RunLevel Highest
    $trigger = New-ScheduledTaskTrigger -Daily -At '8:05 AM'
    $settings = New-ScheduledTaskSettingsSet -WakeToRun
    $task = New-ScheduledTask -Action $actions -Principal $principal -Trigger $trigger -Settings $settings
    $taskPath = '\Admin\'
    # create new scheduled task as per parameters above
    Register-ScheduledTask 'DailyPSCollector' -InputObject $task -TaskPath $taskPath
    PowerShell Create New Scheduled Task

    How to Create a Scheduled Task for a PowerShell Script (GUI)

    This is the GUI way of creating Scheduled Tasks in Windows. Open the Task Scheduler application to get started, and right-click in the empty area as shown to Create New Task.

    Populate details in the General tab as shown in the screenshot below.

    Task Scheduler Create New Task

    Go to the Triggers tab to configure the desired schedule for our new job.

    Task Scheduler Triggers Tab

    Next, in the Actions Tab click to create a new task:

    Action: Start a program
    Program/script: powershell
    Arguments: -File “C:\temp\PowerShell_Scripts\avg_cpu_collector.ps1”

    Task Scheduler Actions Tab

    Have a quick review of the Settings tab and click OK once done.

    Task Scheduler Settings Tab

    We can now review our new task in the Task Scheduler main window.

    Task Scheduler PowerShell Script

    And that’s us sorted. The PowerShell script will run daily at 6 pm as we specified during Task creation.

    Feel free to check out the Windows Admin and/or PowerShell Tips tags for more random informational blog posts.

  • SQL Server Default Port

    SQL Server Default Port

    The default port for the SQL Server Engine is 1433, which is a useful thing to know if you’re working as a Database Administrator (DBA) or Database Reliability Engineering (DBRE) type role.

    Though-out my career I’ve seen SQL Server run on the default 1433 port 99% of the time. For the other 1 percent, it didn’t matter much to me what port it was using.

    Occasionally as a DBA, you will need to arrange network line of sight (firewall rule changes) to be carried out by Network teams for MSSQL host-to-host connectivity. This is done via some request form a lot of the time, and we need to send source/destination addresses. Including IP addresses, we would also need to verify which port the SQL Server is using. Always best to double-check which MSSQL is running before submitting these types of requests, the demo below will help get this info.

    We also need to consider the local Windows Firewall on each SQL Server host. The rule should be scoped to the individual IP connecting, or subnet (10.20.30.0\24, all 256 addresses).

    SQL Server uses a lot of ports for various features & services, e.g. SQL Browser 1434 / SSIS 135. For anything beyond this basic SQL port info, MS Docs will provide you with what’s needed – there’s a lot, which I’m currently digesting at the time of writing.

    Anyway, with network stuff out of the way, this post contains the following demos:
    # How to Check Default SQL Server Port
    # How to Add New Windows Firewall Rule for Default MSSQL Port (1433)

    How to Check Default SQL Server Port

    Open SQL Server Configuration Manager, which is accessible via the Start Menu on any SQL Server host.

    Navigate as shown on the screenshot below, to SQL Server Network Configuration > Protocols for MSSQLSERVER > TCP/IP Properties > IP Address Tab

    SQL Server Check Default Port

    You can amend ports here too, but read MS Docs and test first if doing on Production, of course.

    How to Add New Windows Firewall Rule for Default MSSQL Port

    This is an added note on how to add the SQL Server 1433 Port & SQL Server Browser 1434 Ports. We can use the GUI >

    Windows Firewall Add New Rule

    And we can do the same with PowerShell, using the New-NetFirewallRule cmdlet.

    # Add SQL Server Local Firewall Rule
    New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
    # Add SQL Server Browser Local Firewall Rule
    New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow

    I hope this was a useful SQL Tip. I have another post that relates to Windows Ports- How to Find Which Process is Listening on a Port in Windows

  • How to Connect to SQL Server with a Different Domain User Account

    How to Connect to SQL Server with a Different Domain User Account

    This post is a guide on how to connect to SQL Server with Windows Authentication, but using a different Domain User rather than your own.

    The regular Windows SQL Server User connects & runs their queries via SQL Server Management Studio (SSMS). If you’re in a corporate environment, you’ll likely be logged into your computer with an Active Directory (AD) User. In this example, if we open SSMS we will likely connect with our currently logged-in AD account.

    This guide shows how to authenticate to MSSQL using an AD account that is not the same as your own logged-in user, which is particularly useful for testing newly created SQL Server AD User access and permissions.

    Open SSMS with Other Domain User

    A regular SQL Server Management Studio (SSMS) user in Windows has the application pinned to their taskbar. Search with the Windows button and follow the same on the SSMS app icon.

    Right-Click SSMS Icon –> Hold CTRL + Right-Click Icon –> Select Run As Different User

    SSMS Run as a Different User
    Run as a different user prompt

    Simple as that. No need for much longer explanations. All I can refer you to is my SSMS Tips tag for more Management Studio related blog posts.

  • PowerShell Restart Services on Multiple Hosts

    PowerShell Restart Services on Multiple Hosts

    In this post, we’ll walk through a script that restarts services on multiple remote hosts using PowerShell. A single PowerShell command will be run on several computers at the same time.

    If you need to run a command on multiple remote machines, the PowerShell Invoke-Command cmdlet is a great option. We use this to send commands for execution on remote computers.

    The command being passed in this demo is restarting the SQL Server Agent Service using the Restart-Service.

    Restart Services Multiple Hosts with PowerShell

    To use the script, simply populate the ComputerName array with your hostnames and then run the script. Here’s an example:

    # Restart service on multiple hosts
    $parameters = @{
      ComputerName = 
      	"Hostname1",
      	"Hostname2",
      	"Hostname3"
      ScriptBlock = { Restart-Service *SQLSERVERAGENT* }
    }
    Invoke-Command @parameters
    PowerShell Restart-Service

    The ScriptBlock argument can be amended to run a different PowerShell command, or you can add error checking if you’re building something more complex.

    Remember that you should always be careful when running scripts that modify services on remote hosts. It’s a good idea to test the script on a single host before running it on multiple hosts to ensure that it works as expected.

    As always, hope to follow this up with more of the same random blog posts. Feel free to check out my other PowerShell Tips.