Comprehensive SQL Server Administration Part 2

Explore SQL server migration methods from on prem to azure and azure to on prem, and create azure SQL database, managed instance, or a virtual machine in the azure portal.

Initiate a new Microsoft Azure account by visiting portal.azure.com in incognito, entering a new email and password, and completing email verification with a code.

start an Azure free trial to receive a $200 credit for 30 days, then switch to pay-as-you-go after verification of name, phone, address, and card details.

Explore three Azure sql deployment options for migrating sql server: Azure sql database, sql managed instance, and sql virtual machine, comparing iaas and paas and the level of abstraction.

Learn to migrate databases to azure sql database, managed instance, or azure vm, and create a single database with sql authentication and DTU or vCore pricing.

Copy the Azure server name, connect with SQL Server authentication, and create a firewall rule to allow your client IP, highlighting the difference between on prem and Azure SQL Database.

Demonstrate creating an Azure SQL managed instance, selecting compute power and networking, configuring backups and authentication, and comparing it with a single instance and Azure SQL database.

Demo shows migrating databases to and from Azure SQL Database using the Deploy Database Wizard. Learn supported paths (on-prem to Azure, Azure to on-prem, Azure to Azure) and deployment steps.

Demonstrates migrating on-premises databases to Azure SQL Database or managed instance using data-tier application (.bacpac). Export a .bacpac, save locally or to Azure, then import to Azure.

Migrates an Azure SQL database to on-prem using a data-tier application .bacpac, exporting from Azure and importing on-prem, noting missing server objects and features such as replication and SQL agent.

Explore migrating databases with Data Migration Assistant, evaluating compatibility for on-prem to Azure, noting feature parity, breaking changes, and deprecated features, and learn DMA installation steps.

Assess and migrate from on-prem SQL Server to Azure SQL Database with DMA, the data migration assistant, evaluating compatibility and blockers and considering elastic database jobs or managed instance.

Assess compatibility and plan migration using DMA Part 2, identify blockers and breaking changes, decide between schema and data migration, then deploy schema and migrate data to Azure SQL.

Four tables migrate successfully to the destination azure sql database, while two fail due to a data type issue; the schema exists but lacks data. Next, migrate databases using powershell.

Explore the dbatools PowerShell module for backup, restore, log shipping, always on, and migrations across on-prem and Azure; upgrade to PowerShell 5.1 and verify with PSVersionTable.

Learn how to install the dbatools PowerShell module for SQL Server administration, troubleshoot TLS settings, run as admin, accept untrusted repositories, and verify the installed version.

Explore the dbatools PowerShell module for SQL Server to execute migrations, backups, restores, log shipping, and availability group tasks from PowerShell.

Utilize the PowerShell dbatools module to automate SQL Server migrations with copy DBA database, via detach/attach or backup/restore, migrating single databases or entire instances across on-prem and Azure.

Learn to migrate an entire SQL Server instance using PowerShell and the DBATools module, moving databases, logins, jobs, replication, and more with a single automated command.

Learn to perform database refresh by backing up from production and restoring to lower environments on demand, such as QA, UAT, stage, or dev, enabling testing with near real-time data.

Prepare prerequisites for a database refresh by ensuring the source SQL Server version is earlier or equal to the destination and that there is sufficient disk space for the restore.

Script out database users and their permissions before restoring with replace to overwrite the existing database, then reapply the scripted permissions to recreate destination users for prod and dev.

Demonstrates a risky database refresh: backing up prod and restoring to dev with replace can overwrite the target database and its users, potentially kicking out the DBA.

Demonstrate a database refresh workflow with backup restoration, replace overwrite, and scripting destination users' permissions to restore prod access on the destination VM02.

Explore patching SQL Server and Windows OS through service packs, cumulative updates, and hotfixes to fix bugs, improve security, and add functionality, on a roughly 60-day cycle.

Trace how service packs and cumulative updates evolved from 2014 to 2022, noting end of service packs after 2017; use build numbers and kb references to verify sql server versions.

Identify the current patch level of the SQL Server 2016 version, target SP3 build 13.0.63002, and download and install ESP3 which contains all prior updates and adds security enhancements.

Follow SQL Server patching steps: test in lower environment, back up system and user databases, download service pack, ensure no running jobs, and coordinate always-on availability groups with manual failover.

Synchronize logins, jobs, and dependencies across the availability group, then patch the secondary first to maintain connectivity through the listener and avoid downtime.

Patch the secondary SQL Server in an availability group, suspend data movement, install SP3, back up primary databases, bounce the server, then resume, verify synchronization, and perform a manual failover.

Patch two SQL Server virtual machines in an availability group by patching the secondary first with the 2016 SP3 update, then resume data movement, verify synchronization, and optional failback.

Learn how to upgrade SQL Server using in-place and side-by-side approaches, including migrating databases and evaluating downtime and risk for a smoother server-to-server upgrade.

Follow in-place upgrade prerequisites: back up the system and user databases, and ensure no connections or jobs. For side-by-side upgrades, install the new version with the downloaded SQL Server setup.

Demo of in place SQL Server upgrade on a VM after removing from availability group, with backups of system and user databases and session cleanup before installing SQL Server 2017.

Upgrade SQL Server from 2016 to 2017 using the upgrade path, reboot the server, verify the build is 14, and update each database's compatibility level to 140.

Move database files for user and system databases by backing up, locating current and new paths, using T-SQL to point files to the new location, then offline, move, and online.

Move the data and log files of a user database by updating their paths with alter database, verify service account permissions, and take the database offline to complete the move.

Move the Adventureworks 2016 database data and log files from E and F drives to G and H drives, then bring the database online and verify the new locations.

Explore moving system database files in sql server, focusing on tempdb, msdb, and model, while noting resource database cannot be moved and master uses configuration manager; restart to recreate tempdb.

Learn how to move system database files in SQL Server, including tempdb and master, by stopping the service, relocating data and log files, and updating paths.

Understand how transparent data encryption protects data at rest by encrypting all database files and backups with keys and certificates, including tempdb and secondary databases in high availability setups.

Follow four tde steps: create a database master key in the master database, create a server certificate, create a database encryption key, and enable encryption on the database.

This lecture demonstrates enabling transparent data encryption for a SQL Server database using T-SQL, creating a master key and certificate, and verifying encryption state.

Verify encryption state by running queries to show encrypted user databases and automatic tempdb encryption after encryption. Use server certificate for user databases; tempdb uses default asymmetric key.

Demonstrates backing up an encrypted database with t-sql, checks whether the certificate and private key have been backed up, verifies the backup, and tests cross-server restore with version compatibility.

Create a master key, certificate, and encryption key to enable encrypted backups. Back up the certificate and restore on the destination VM after deploying the certificate.

Turn off TDE, verify the encryption state, and drop the database encryption key to complete decryption; note the tmdb remains encrypted until the key is removed.

Explain encryption in Always On availability groups, including automatic flow from primary to secondary, graphical user interface limitations, and using master keys and certificates via T-SQL or PowerShell.

This lecture introduces dynamic data masking (ddm) in SQL Server, a column-level security feature that masks sensitive data in query results for non-privileged users, showing only the last three digits.

Explore dynamic data masking in SQL Server by creating a DM schema and a membership table, then apply masking functions such as default, partial, email, and random to sensitive columns.

Explore dynamic data masking in SQL Server by comparing privileged and non-privileged users, applying column-level masking functions to first names, emails, phones, and discounts, with revert and drop DM.

Discover row level security, always encrypted, and column level encryption to protect data. Enforce password policy and expiration for sql logins, and secure data in motion with secure socket layer.