Powershell for SQL Server DBA

Set up a lab on your workstation with VMware Workstation Player to create three VMs: central server, vm1, and vm2. Use PowerShell automation from the central server to manage them.

Create VM1 in VMware Player by downloading Windows Server 2016 ISO, configuring a 30 GB disk, 1 GB RAM, 2 CPUs, and installing the Windows Server 2016 Standard Evaluation.

Install VMware tools to enhance virtual machine performance and host interaction, enabling full-screen, copy-paste, and improved mouse and screen resolution, by running the VMware tools installer and restarting the VM.

Install VM2 and central server in parallel with VMware Workstation, deploy Server 2016, install VMware tools, fix EFI boot via VMX edit, and outline domain controller and remote PowerShell use.

Promote the central server to a domain controller, install Active Directory Domain Services and Group Policy Management, and join VM1 and VM2 to the lab.com domain.

Set static IP addresses on three servers, configure the domain controller as DNS, join vm1 and vm2 to the lab domain, and create a domain admin user for PowerShell tasks.

Set up VM1, a virtual machine with VMware Workstation Pro, customize hardware, attach the Windows ISO, and install VMware tools to optimize display and performance.

Fix internet connectivity for VMware workstation virtual machines by adding a new network adapter and configuring bridged mode, then verify access with a successful ping to google.com.

Explore PowerShell modules, bundles of cmdlets and functions packaged in a .psm1 file for reusable automation, and learn the four types: script, binary, manifest, and dynamic.

Demonstrates creating and importing PowerShell modules by bundling functions into a .psm1 file, then importing from a module path to reuse machine name, user name, and bits service status.

Explore the PowerShell Gallery, a Microsoft online repository for downloading and installing PowerShell modules, scripts, and resources, and learn to search, inspect, install, and update modules.

Install dbatools, a free PowerShell module that automates SQL Server tasks, then import it to use its cmdlets for managing SQL Server, including offline installation by copying the module folder.

Learn to install and import the SQL dba tools module to enable SQL server uninstall capabilities, and understand repository trust, admin rights, and module placement on a SQL server.

Learn how to install and import the PS Windows Update module to manage Windows updates from PowerShell, including handling dependencies like dbatools and verifying available commandlets.

Learn to create and reuse code in PowerShell by turning script blocks into functions, passing parameters, and calling functions like sum, get processes, and OS checks.

Explore how PowerShell DSC defines and enforces the desired state of Windows systems using configuration scripts and resources, with a pool server and Local Configuration Manager ensuring consistency.

Learn how to install SQL Server with PowerShell DSC by defining a configuration script, declaring resources and MOF files, and applying configurations via pull or push modes using the LCM.

Learn to install sql server 2022 using PowerShell with DSC: download binaries to a shared location, compile a configuration into a MOF, and apply it to remote servers with Start-DscConfiguration.

Demonstrates installing sql server 2022 developer edition on a virtual machine using PowerShell dsc, including downloading binaries, mounting the iso, creating a shared deployment folder, and applying configuration to vm1.

Uninstall a SQL Server instance on a remote VM using the SQLDBTools module in PowerShell, including backups and ServiceNow ticket prompts.

Learn to install SQL Server on VM one using the dbatools install db instance command with engine, replication, analysis services, integration services, and reporting services.

Install SQL Server Management Studio via PowerShell DSC to install SSMS on a remote VM, using a three-part configuration, MOF generation, and a passive install.

Learn how to retrieve the SQL Server Management Studio product ID with a PowerShell DSC script, enabling proper SSMS installation.

Learn to install SQL Server with SSIS, SSAS, and SSRS using PowerShell DSC, configure credentials and data directories, and verify services on a virtual machine.

Patch a SQL Server 2022 instance from CU11 to CU12 using a PowerShell DSC script, defining a package resource, compiling the MOF, and applying with start-dscconfiguration.

Learn to use the PS Windows Update module to automate Windows updates, enhancing security and performance of database servers, with a central update demo for vmx1 and vm2.

Update Windows servers from a central server using PowerShell and the PS Windows Update module to manage VM1 and VM2, test connectivity, and monitor updates via Task Scheduler.

Verify Windows updates across the server fleet, confirm uptime and SQL restart with get dba uptime, and ensure SQL services run with get dba service; test connectivity.

Explore the dbatools website page that lists commandlets by category, for powershell for sql server dba, with syntax, parameters, and practical examples to tailor your database scripts.

Manage SQL server databases with PowerShell DBA tools using new DBA database, get DBA database, remove DB database, and copy DBA database to create, view, or copy databases.

Use the New DBA database cmdlet in powershell to create a database on two SQL servers VM one and VM two, after checking there are no existing databases.

Learn to create multiple databases across two SQL servers using a single PowerShell cmdlet, and configure secondary file size, secondary file growth, log size, and log growth to 20 MB.

Learn to create a SQL database with multiple parameters using a PowerShell commandlet, passing server name, database name, log and data file sizes and growth, and suffixes.

Use get-dba database to pull database level information from SQL Server instances, excluding system databases, and tailor output by selecting columns or filtering to a single database.

Filter the Get-dbadatabase output by a specific database or any column using a where clause and pipeline, leveraging $_.name and other properties for precise results.

Use the Set-DbaDbOwner command to change a database’s owner to a specific login, then verify the new owner on VM1 and VM2 SQL Server instances.

Learn to change the SQL Server database recovery model and adjust file growth with PowerShell, verifying changes on multiple instances and using full, bulk logged, and simple recovery models.

Learn to remove databases on SQL Server instances using PowerShell by targeting VM1 and VM2 and supplying the database names. Verify deletions by refreshing the instances so no databases remain.

Copy a database from one SQL Server instance to another using the copy-dba-database commandlet with backup restore, setting a shared path and validating the copy on VM two.

Back up a SQL Server database and restore it to another instance using dbatools in PowerShell, including full, differential, and log backups.

Learn to restore SQL Server databases with dbatools by modifying backup metadata, replacing database names and file paths, and executing advanced restore with restored data directories and standby options.

Learn to manage sql server logins and users using db tools and commandlets, including getbyid login, new DB login, remove login, and get dba db role and JTB server role.

Learn to create SQL server logins with PowerShell across multiple servers, secure passwords with secure strings, and add Active Directory logins such as lab/business analyst, lab/developers, and lab/db architects.

Create database users with the New-DBUser cmdlet, linking them to existing logins such as lab/developers, lab/architects, and lab/business analyst across dba, db3, db4, and db5.

Learn to use powershell to assign a server role to a sql login by running set dba login, granting sysadmin status, and verifying the role in security settings.

Learn to retrieve login information across SQL Server instances with the Get-DBALogin commandlet, including login type, last login, access status, and password change requirements for automation.

Execute the find db login group commandlet to list members of Active Directory groups linked to your SQL server, providing quick visibility for DBAs.

Learn to list database roles with get db db role, assign db owner to a login across databases, and verify membership with db role member.

Grant and revoke server roles for SQL Server logins using add db server role member and remove db server role member, and verify privileges like db creator and sysadmin.

Learn how to copy a login from a central server to another server using the copy command, demonstrated by duplicating the David login to VM one and confirming the transfer.

Discover how to remove logins across SQL Server instances with the remove hyphen db login cmdlet, force removal when active sessions exist, and manage users, AD groups, and server roles.

Collect instance level information using PowerShell to manage SQL Server instances with DB tools, check service status, start, stop, restart, and view install date.

Master SQL Server agent management with PowerShell using DBA tools to create, configure, schedule, and monitor jobs, including starting, stopping, copying, and querying history.

Learn to manage sql server linked servers with powershell, creating a link server with sql instance, data source, provider, and security context, test connections, and export or remove linked servers.

Read and export sql server error logs with powershell using the db log sql instance cmdlet, filtering by text, date, source, and log number for troubleshooting and monitoring.

Manage orphaned users in SQL Server with PowerShell DBA tools, repair SIDs, and restore proper login mappings across databases using the repair and orphaned user commands.

Learn to connect to a SQL server with PowerShell, run queries using Invoke-Sqlcmd, and export results to HTML, including credentials handling, timeouts, and optional SQL files.

Automate SQL Server replication across two servers by scripting replication, dropping before daily restores, restoring from production, and recreating replication with PowerShell.

Set up transactional SQL Server replication from the central server to VM1, creating DB primary and DB secondary, populating customers and employees, and validating replication with the replication monitor.

Demonstrate the impact of restoring a replicated SQL Server database from a backup without replication, causing the publication to vanish and requiring reconfiguration of replication in SQL Server DBA workflows.

Explore how the export db rep server setting commandlet exports replication configurations to a SQL file and how script options separate publisher, distributor, and subscriber scripts.

Automate SQL Server replication with PowerShell by scripting creation and dropping of publications, subscriptions, and subscribers, exporting scripts, and recreating replication across primary and secondary databases.

Discover how to use the PowerShell switch statement and its wild card parameter to simplify conditional logic, implement pattern matching, and streamline replication automation scripts.

Explore PowerShell automation for SQL Server replication, including exporting replication configurations, standardizing scripts, and implementing create publication, replication jobs, and subscriptions for publisher and subscriber setups.