Adam Anderson Environment Automation Visit the Sponsor Booths

Adam Anderson Environment Automation

• Visit the Sponsor Booths • Lots of Great Raffle Prizes! • Get your parking paid via Sponsor Bingo Platinum Sponsor: Gold Sponsors: Global Alliance Partners: Thank you Sponsors!

PASSMN – News/Info Thanks to all our sponsors of 2019! We need Speakers & Sponsors for 2020 PASSMN Meetings! • Sign up to present at one of the monthly meetings! Monthly Meetup: 3 rd Tuesday of Each Month (except Oct) at Microsoft MTC in Edina (food usually provided) Signup on Meetup: https: //www. meetup. com/MN-SQL-Server-User-Group-PASSMN/ Board Member Elections in November/December: • Your chance to help out the MN SQL community!

Join the brightest data professionals focused on the Microsoft Data Platform! November 3 th Through November 8 th • • Pre-Conference Sessions – Monday/Tuesday Conference – Wednesday through Friday

SQLSaturday #913 – After Party Location: 4 th Floor of Mall of America Time: 6: 30 PM – 10 PM There will be drinks and appetizers as well as free game cards and bowling! Hang out with some new friends you’ve made.

About Me Adam Anderson • @SQLPioneer • sqlpioneer. wordpress. com • www. linkedin. com/in/adam-anderson-dba • https: //github. com/SQLPioneer/

Agenda • • • Introduction Docker Environment Power. Shell Automation Software Management using Chocolatey Use Case Review

Docker

VM versus Container https: //www. backblaze. com/blog/vm-vs-containers/

Docker Architecture Registry Docker Host Client Docker build Docker pull Docker run Docker Daemon Containers https: //www. backblaze. com/blog/vm-vs-containers/ Images

Download SQL Server 2017 Container

Create a Container from an Image

Optional Parameters --name: Name of the container --hostname or -h: Network alias --volume or –v: volume path (OS: Container) --env or –e: Environment variable --rm Remove container when stopped --detatch or -d: Start container detatched

Create Container with Optional Parameters

Docker Commands to get Started Docker pull: Download an image from the docker repository Docker run: Create a container from the image. Docker start: Start a container that has been stopped Docker stop: Stop a container that is running Docker help: Show help for Docker commands

Docker Commands to Manage Containers Docker ps: Show running containers Docker ps -a: Show all containers Docker rm: Remove stopped container Docker Exec: Execute a command in a running container

Docker Commands to Manage Images Docker images: Show and work with images Docker rmi: Remove image Docker commit: Create an image from a container

Powershell

Powershell for the Win • Easy to automate • Community Tools • • Dbatools Dbachecks • Security • Short development cycle, long ROI dbachecks

T-SQL versus DBATools backup DECLARE @filename nvarchar(200) SELECT @filename + 'D: BKPCoreMy. DB_Log_' + convert(varchar(23), getdate(), 126) + '. bak' BACKUP DATABASE My. DB TO DISK = @filename Backup-Dba. Database -Sql. Instance localhost

Help Backup-dba. Database

Backup and Restore Backup-Dba. Database ` -Sql. Instance $Prod ` -Sql. Credential $mycred ` -Database Adventure. Works ` -Path c: backup ` -Backup. File. Name dbname. bak ` -Replace. In. Name Restore-Dba. Database ` -Sql. Instance $Host. Name ` -Sql. Credential $mycred ` -Path c: backupAdventure. Works. bak ` -Database. Name Adventure. Works ` -Destination. Data. Directory c: data ` -Destination. Log. Directory c: data

Get-Command -Module dbatools | Out-Grid. View

Powershell Basics $Twittter = “@sqlpioneer” Set-Alias -Name Edit -Value "notepad++. exe" Edit $profile New-PSDrive -name Pres -PSProvider File. System Root "C: gitGit. HubPresentations“ Set-Location Pres:

Hash Table $Config = @{ "Server. Instance"="localhost, 1401" "Database"="FIFA" "Username"=$user "Password"=$password "Query"="SELECT @@Server. Name AS Server. Name" } $Config. Server. Instance Invoke-Sqlcmd @Config

Database Testing $Check = @{ "Sql. Instance"=$Host. Name "Sql. Credential"=$mycred "Tags"="Instance. Connection" } Invoke-Dbc. Check @Check -Pass. Thru | Update. Dbc. Power. Bi. Data. Source

Chocolatey / Nu. Get

Application Installations • Packaged Code • Shared Components for Reuse • Pulls from Nu. Get Repos • Fully Configurable • Private Repo Nu. Get Chocolatey

Nu. Get • • Single ZIP file with the. nupkg extension Contains any files associated with package Descriptive Manifest Version Number format must be 1. 0. 2

Chocolatey • Pulls packages from public and private repositories • Creates Nu. Get packages • Works with common Configuration Managers

Docker Images to Support Test and Dev Pre-Production Image Full Prod Data Testing Image Confidential Data Removed Full Data Development Image Confidential Data Removed 90% Delete

Environment Overview

Docker Setup Development Docker Environment Base Images Docker Bind Mount SQL Server Image Dev Image Database Backup Docker Volume DB. mdf DB_log. ldf QA Image Database Prod Like Image Dev 1 Database Port: 1435 Dev 2 Port: 1436 Database Version Control Testing QA 1 Database Port: 1437 Build Server Prod Like Pre. Prod Port: 1433 Artifact Repository

Volumes Development Docker Environment Base Images Docker Bind Mount SQL Server Image Dev Image Database Backup Docker Volume DB. mdf DB_log. ldf QA Image Database Prod Like Image Dev 1 Database Port: 1435 Dev 2 Port: 1436 Database Version Control Testing QA 1 Database Port: 1437 Build Server Prod Like Pre. Prod Port: 1433 Artifact Repository

Docker Images Development Docker Environment Base Images Docker Bind Mount SQL Server Image Dev Image Database Backup Docker Volume DB. mdf DB_log. ldf QA Image Database Prod Like Image Dev 1 Database Port: 1435 Dev 2 Port: 1436 Database Version Control Testing QA 1 Database Port: 1437 Build Server Prod Like Pre. Prod Port: 1433 Artifact Repository

Containers Development Docker Environment Base Images Docker Bind Mount SQL Server Image Dev Image Database Backup Docker Volume DB. mdf DB_log. ldf QA Image Database Prod Like Image Dev 1 Database Port: 1435 Dev 2 Port: 1436 Database Version Control Testing QA 1 Database Port: 1437 Build Server Prod Like Pre. Prod Port: 1433 Artifact Repository

Developer Workflow Docker Environment Base Images Docker Bind Mount SQL Server Image Dev Image Database Backup Docker Volume DB. mdf DB_log. ldf QA Image Database Prod Like Image Development Dev 1 Database Port: 1435 Dev 2 Port: 1436 Database Version Control Testing QA 1 Database Port: 1437 Build Server Prod Like Pre. Prod Port: 1433 Artifact Repository

Summary • Docker can be used to create dynamic database environments that can be used for • • Testing Development

Contact Information Adam Anderson • @SQLPioneer • sqlpioneer. wordpress. com • www. linkedin. com/in/adam-anderson-dba • https: //github. com/SQLPioneer/