There is often a mismatch between needs of the development team (multiple discreet T-SQL files for separate concerns) and the release team (the requirement for one step automated deployment) . The script bridges the requirement by using sqlcmd.exe to run a batch of SQL scripts.
A text file is used listing all sql files that need to run in a particular order to avoid errors which may occur if there are dependencies between the scripts. Instead of using a text file a number can be prefixed to the scripts based on the order they need to run.
The script expects two parameters –
Path of folder containing the set of T-SQL files (and the manifest file, see below)
Connection string
The script can be downloaded from technet gallery.
## Provide the path name of the SQL scripts folder and connnection string
##.\SQLBatchProcessing.ps1 -SQLScriptsFolderPath "C:\Sql Batch Processing\SQLScripts" -ConnectionString "DEV-DB-01"
Param(
[Parameter(Mandatory=$true)][String]$ConnectionString ,
[Parameter(Mandatory=$true)][String]$SQLScriptsFolderPath
)
Set-ExecutionPolicy -ExecutionPolicy:Bypass -Force -Confirm:$false -Scope CurrentUser
Clear-Host
#check whether the SQL Script Path exists
$SQLScriptsPath = Resolve-Path $SQLScriptsFolderPath -ErrorAction Stop
#a manifest file will exisit in the SQL scripts folder detailing the order the scripts need to run.
$SQLScriptsManifestPath = $SQLScriptsFolderPath + "\Manifest.txt"
#Find out whether the manifest file exists in the the SQL Scripts folder
$SQLScriptsManifestPath = Resolve-Path $SQLScriptsManifestPath -ErrorAction Stop
#if manifest file found iterate through each line , validate if corresponding SQL script exists in file before running each of them
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
Resolve-Path $SQLScriptsPath -ErrorAction Stop
}
$SQLScriptsLogPath = $SQLScriptsFolderPath + "\" + "SQLLog.txt"
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "Started processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
Get-Content $SQLScriptsManifestPath | ForEach-Object {
$SQLScriptsPath = $SQLScriptsFolderPath + "\" + $_.ToString()
$text = "Running script " + $_.ToString();
Add-Content -Path $SQLScriptsLogPath -Value $text
sqlcmd -S "DEV-DB-01" -i $SQLScriptsPath | Out-File -Append -filepath $SQLScriptsLogPath
}
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value "End processing at [$([DateTime]::Now)]."
Add-Content -Path $SQLScriptsLogPath -Value "***************************************************************************************************"
Add-Content -Path $SQLScriptsLogPath -Value ""
I was interested in SQL 2012 SP2 Standard version. Fortunately the Azure Image Offer Names are intuitive, e.g. Name SQL2012SP2-WS2012R2 means windows server 2012 R2 virtual machine with SQL Server 2012 SP2 installed.
I also needed the SKU value of the SQL 2012 SP2 using the cmdlet Get-AzureRmVMImageSKU
The following SKUs for SQL2012SP2-WS2012R2 are available
Skus
----
Enterprise
Enterprise-Optimized-for-DW
Enterprise-Optimized-for-OLTP
Standard
Web
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
Two results are returned : “2013” and “2016”. I am interested in the “2013” value which refers to the Microsoft SharePoint Server 2013 version.
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
The changes from the original script are on the following lines
line 18: $vmName=“sp2013VM”
line 26:$vnet=Get-AzureRMVirtualNetwork -Name “SP2013Vnet” -ResourceGroupName $rgName
line 34: $skuName=“2013”
The end result of the PowerShell scripts is a resource group with the virtual machines (adVm, sp2013Vm and sql2012VM), network interfaces, availability sets, storage account and public IP addresses to enable SharePoint 2013 to run in Azure VMs.
reshmeeauckloo 
