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/
- Slides: 39