Last active
August 3, 2022 11:27
-
-
Save sdoubleday/d3f60f6ae22f216fd161cdf844fe243f to your computer and use it in GitHub Desktop.
My Script For Customizing Sql Server Coding VMs
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #Run this on new Microsoft Development VMs. | |
| #Powershell run as administrator | |
| #region PowerShell and Package Managers | |
| #Set Execution Policy | |
| Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned -Force; | |
| Write-Verbose -Verbose 'Start with package managers...'; | |
| Write-Verbose -Verbose 'Chocolatey...'; | |
| #Get Chocolatey | |
| Set-ExecutionPolicy Bypass -Scope Process -Force; [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1')); | |
| #Microsoft dev VMs need VS's package manager fixed. Name and source: | |
| #nuget.org | |
| #https://api.nuget.org/v3/index.json | |
| Write-Verbose -Verbose 'NuGet...'; | |
| Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force; | |
| Write-Verbose -Verbose "Now let's grab dbatools..."; | |
| #Install dbatools (may need nuget updated first?) | |
| Install-Module dbatools -force; | |
| Write-Verbose -Verbose 'And my powershell profile...'; | |
| #Get my preferred PowerShell Profile | |
| New-Item -ItemType File -Path $PROFILE | Add-Content -Encoding UTF8 -Value $((New-Object System.Net.WebClient).DownloadString('https://gist.github.com/sdoubleday/34bb36ff738a4c6ceab9eb65ebe565d6/raw')); | |
| #Load Profile | |
| . $PROFILE; | |
| #endregion PowerShell and Package Managers | |
| #region Development tools | |
| Write-Verbose -Verbose 'Time to get Visual Studio extension for Analysis Services (we will run the .vsix file later)...'; | |
| $vsixDownloadUrl = 'https://marketplace.visualstudio.com/_apis/public/gallery/publishers/ProBITools/vsextensions/MicrosoftAnalysisServicesModelingProjects/2.9.18/vspackage'; | |
| $downloadFileName = "$home\downloads\Microsoft.DataTools.AnalysisServices.vsix"; | |
| $webclient = New-Object 'System.Net.Webclient'; | |
| $webclient.DownloadFile($vsixDownloadUrl, $downloadFileName); | |
| Write-Verbose -Verbose "Download TabularEditor (we need the Analysis Services Visual Studio extension to make that work) and my data-warehouse sql generators to C:\Users\User\Downloads (NOTE: Versions are hardcoded, update if needed)..."; | |
| choco install tabular-editor -y; | |
| $version = 'v0.1.4'; Invoke-WebRequest -Uri "https://github.com/sdoubleday/scdtype6generator/releases/download/$($version)/GenerateSCDType6Files-$($version)-win-x64.zip" -OutFile "$home\Downloads\GenerateSCDType6Files-$($version)-win-x64.zip"; | |
| $version = 'v0.0.8'; Invoke-WebRequest -Uri "https://github.com/sdoubleday/factTableGenerator/releases/download/$($version)/GenerateFactTableFiles-$($version)-win-x64.zip" -OutFile "$home\Downloads\GenerateFactTableFiles-$($version)-win-x64.zip"; | |
| $version = 'v0.0.2'; Invoke-WebRequest -Uri "https://github.com/sdoubleday/scdRpGenerator/releases/download/$($version)/GenerateSCDRpFiles-$($version)-win-x64.zip" -OutFile "$home\Downloads\GenerateSCDRpFiles-$($version)-win-x64.zip"; | |
| Write-Verbose -Verbose 'Install commandline git...'; | |
| #Install and configure Git | |
| choco install git -y; | |
| Write-Verbose -Verbose "This bit might be a little manual. We are installing the Analysis Services vsix, you might need to click things."; | |
| $VSYearEdition = "2019"; | |
| & "C:\Program Files (x86)\Microsoft Visual Studio\$($VSYearEdition)\Community\Common7\IDE\VSIXInstaller.exe" $downloadFileName /admin; #optional, if you want to try: /quiet | |
| Write-Verbose -Verbose 'Almost done! Close this shell, launch a new one, and run these to configure git:'; | |
| Write-Verbose -Verbose @' | |
| git config --global user.email "blah"; | |
| git config --global user.name "blah"; | |
| '@; | |
| #endregion Development tools | |
| #region Install SQL Server services | |
| Write-Verbose -Verbose 'Install SQL Server 2019 engine developer edition...'; | |
| choco install sql-server-2019 -y; | |
| #This might bomb out requiring a restart first. | |
| Write-Verbose -verbose "Mount chocolatey's SQL Server iso..." | |
| $volume = Get-ChildItem "$home\AppData\Local\Temp\chocolatey\sql-server-2019\*\*iso" | Mount-DiskImage | Get-Volume; | |
| #that's where you find the iso | |
| Write-Verbose -verbose "Specify Analysis Services configs..." | |
| #Then you need to specify an admin for the Analysis services installation | |
| $config = @{ | |
| ASSERVERMODE="TABULAR" | |
| ASSYSADMINACCOUNTS="$($env:USERDOMAIN)\$($env:USERNAME)" | |
| }; | |
| Write-Verbose -verbose "Install Analysis Services in Tabular Mode..." | |
| Import-Module dbatools; | |
| Install-DbaInstance -Path "$($volume.DriveLetter):\" -Version 2019 -Configuration $config -SqlInstance . -Feature AnalysisServices -Confirm:$false; | |
| #endregion Install SQL Server services | |
| #region SQL Server Engine Maintenance and Utilities | |
| Import-Module dbatools; | |
| Write-Verbose -Verbose 'Install Free Community Tools from dbatools: Adam Machanic WhoIsActive...'; | |
| Find-DbaInstance | Install-DbaWhoIsActive; | |
| Write-Verbose -Verbose 'Install Free Community Tools from dbatools: Blitz and First Responder Kit...'; | |
| Find-DbaInstance | Install-DbaFirstResponderKit; | |
| Write-Verbose -Verbose 'Install Free Community Tools from dbatools: Ola Hallengren Maintenance Solution...'; | |
| Find-DbaInstance | Install-DbaMaintenanceSolution; | |
| Write-Verbose -Verbose 'Install Free Community Tools from dbatools: Marcin Gminski SqlWatch...'; | |
| Find-DbaInstance | Install-DbaSqlWatch; | |
| Write-Verbose -Verbose 'Schedule Ola Hallengren Maintenance Solution on a basic schedule pattern...'; | |
| New-DbaAgentSchedule; #expand this | |
| <# Relies on git, which yes, is up in the development tools region, but needs a new powershell window to work. | |
| Write-Verbose -Verbose 'Clone Darling Data for PressureDetector and HumanEvents...'; | |
| git clone https://github.com/erikdarlingdata/DarlingData; | |
| #> | |
| #endregion SQL Server Engine Maintenance and Utilities | |
| #region Client/Admin/End User Tools | |
| Write-Verbose -Verbose 'Install SQL Server Management Studio...'; | |
| choco install sql-server-management-studio -y; | |
| Write-Verbose -Verbose 'Install Grafana to look at SqlWatch dashboard...'; | |
| choco install grafana -y; | |
| Write-Verbose -Verbose 'Install Power BI to look at SqlWatch data...'; | |
| choco install powerbi -y; | |
| #Checksum on this was broken when I tried it | |
| #endregion Client/Admin/End User Tools | |
| #region Azure Deployment Tools | |
| Write-Verbose -Verbose 'Install the Azure CLI...'; | |
| choco install azure-cli -y; | |
| Write-Verbose -Verbose 'Install terraform...'; | |
| choco install terraform -y; | |
| #endregion Azure Deployment Tools |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
#region Scheduling Ola Hallengren Maintenance
#Multiple times a day
'DatabaseBackup - USER_DATABASES - LOG' | New-DbaAgentSchedule -FrequencySubdayType Minute -FrequencySubdayInterval 1 -FrequencyType Daily -StartTime "000000" -FrequencyInterval EveryDay -SqlInstance . -Job $_ -Schedule $_ -Force;
#Daily
'DatabaseBackup - SYSTEM_DATABASES - FULL' | New-DbaAgentSchedule -FrequencyType Daily -StartTime "001500" -FrequencyInterval EveryDay -SqlInstance . -Job $_ -Schedule $_ -Force;
'DatabaseBackup - USER_DATABASES - DIFF' | New-DbaAgentSchedule -FrequencyType Daily -StartTime "010000" -FrequencyInterval EveryDay -SqlInstance . -Job $_ -Schedule $_ -Force;
'DatabaseIntegrityCheck - SYSTEM_DATABASES' | New-DbaAgentSchedule -FrequencyType Daily -StartTime "011500" -FrequencyInterval EveryDay -SqlInstance . -Job $_ -Schedule $_ -Force;
'DatabaseIntegrityCheck - USER_DATABASES' | New-DbaAgentSchedule -FrequencyType Daily -StartTime "021500" -FrequencyInterval EveryDay -SqlInstance . -Job $_ -Schedule $_ -Force;
#Weekly
'IndexOptimize - USER_DATABASES' | New-DbaAgentSchedule -FrequencyType Weekly -FrequencyInterval Sunday -StartTime "030000" -FrequencyRecurrenceFactor 1 -SqlInstance . -Job $_ -Schedule $_ -Force;
'DatabaseBackup - USER_DATABASES - FULL' | New-DbaAgentSchedule -FrequencyType Weekly -FrequencyInterval Sunday -StartTime "040000" -FrequencyRecurrenceFactor 1 -SqlInstance . -Job $_ -Schedule $_ -Force;
#Monthly, Second day of month
'sp_delete_backuphistory' | New-DbaAgentSchedule -FrequencyType Monthly -FrequencyInterval 2 -FrequencyRecurrenceFactor 1 -StartTime "050000" -SqlInstance . -Job $_ -Schedule $_ -Force;
'sp_purge_jobhistory' | New-DbaAgentSchedule -FrequencyType Monthly -FrequencyInterval 2 -FrequencyRecurrenceFactor 1 -StartTime "051500" -SqlInstance . -Job $_ -Schedule $_ -Force;
'CommandLog Cleanup' | New-DbaAgentSchedule -FrequencyType Monthly -FrequencyInterval 2 -FrequencyRecurrenceFactor 1 -StartTime "053000" -SqlInstance . -Job $_ -Schedule $_ -Force;
'Output File Cleanup' | New-DbaAgentSchedule -FrequencyType Monthly -FrequencyInterval 2 -FrequencyRecurrenceFactor 1 -StartTime "054500" -SqlInstance . -Job $_ -Schedule $_ -Force;
#endregion Scheduling Ola Hallengren Maintenance