Skip to content

Instantly share code, notes, and snippets.

@sdoubleday

sdoubleday/MyScriptForCustomizingSqlServerCodingVMs.ps1

Last active August 3, 2022 11:27
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save sdoubleday/d3f60f6ae22f216fd161cdf844fe243f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save sdoubleday/d3f60f6ae22f216fd161cdf844fe243f to your computer and use it in GitHub Desktop.
Download ZIP
My Script For Customizing Sql Server Coding VMs
Raw
MyScriptForCustomizingSqlServerCodingVMs.ps1
#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
@sdoubleday
Copy link
Author

sdoubleday commented Dec 5, 2021 

#Optional future extension

Write-Verbose -Verbose 'Install SQL Server 2019 engine developer edition...';
choco install sql-server-2019 -y;

Write-Verbose -Verbose 'Use the installation media that hopefully came with the engine installation to install Analysis Services...';
Install-DbaInstance -Feature AnalysisServices;

Write-Verbose -Verbose 'Install SQL Server Management Studio...';
choco install sql-server-management-studio -y;

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

Write-Verbose -Verbose 'Install Grafana to look at SqlWatch dashboard...';
choco install grafana -y;

Write-Verbose -Verbose 'Clone Darling Data for PressureDetector and HumanEvents...';
git clone https://github.com/erikdarlingdata/DarlingData;

@sdoubleday
Copy link
Author

sdoubleday commented Dec 7, 2021

#And also Power BI Desktop

Write-Verbose -Verbose 'Install Power BI to look at SqlWatch data...';
choco install powerbi -y;

@sdoubleday
Copy link
Author

sdoubleday commented Dec 22, 2021

#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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment