PowerShell to create Azure SQL DBs

Recently I have been creating many Azure SQL databases via the portal where I started to find it quite tedious.

So I decided to find a scripted approach via PowerShell where I could just change some parameters and hit enter. I will go through the individual cmdlets that I use then provide the completed script.

The first step is to install Azure PowerShell; this will give you a set of modules that provide cmdlets to manage Azure.

You can download it from this link – https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Once you have installed Azure PowerShell enter:

Add-AzureRmAccount

You will then be presented with a sign-in screen where you should enter your credentials.

Once you have signed in use the Get-AzureRmContext cmdlet to get the environment details used to authenticate Azure Resource Manager requests

Get-AzureRmContext

From this you can confirm whether or not you are operating under the correct account and subscription ID.

qq1

If you have many subscriptions you should use Set-AzureRmContext cmdlet to select which subscription your PowerShell session should use.

Set-AzureRmContext -SubscriptionId "id"

So now we will do the following:

  • Create a resource group
  • Create a server
  • Create a firewall rule
  • Create the SQL database

First, let’s get a list of valid locations available to us.

Get-AzureRmResourceProvider -ListAvailable | Where-Object {$_.ProviderNamespace -eq 'Microsoft.Sql'}).Locations

qq2

We will now create a resource group within a valid location. For this example I will create MY_ENV resource group in North Europe.

New-AzureRmResourceGroup -Name "MY_ENV" -Location "North Europe"

Using the below I am creating a server called azuresqlserver01 within MY_ENV resource group.

New-AzureRmSqlServer -ResourceGroupName "MY_ENV" -ServerName "azuresqlserver01" -Location "North Europe" -ServerVersion "12.0"

I will now create a database called SQLDB12 that is standard S1 edition within my resource group MY_ENV on server azuresqlserver01.

New-AzureRmSqlDatabase -ResourceGroupName "MY_ENV" -ServerName " azuresqlserver01" -DatabaseName "SQLDB12" -Edition "Standard" -RequestedServiceObjectiveName "S1"

qq3

So next I will share the complete script that I personally use.

Param

(

$Environment=(read-host "Please enter an Environment Name:"),

$server=(read-host "Please enter a SQL Server Name:"),

$databaseName=(read-host "Please enter a Database Name:"),

$databaseService="S1",

$DCLocation="North Europe",

$startIP="10.xx.x04.xx",

$EndIP="10.00.0x0.xx"

)

Try

{

New-AzureRmResourceGroup -Name $Environment -Location $DCLocation

New-AzureRmSqlServer -ResourceGroupName $Environment -ServerName $Server -Location $DCLocation -ServerVersion "12.0"

New-AzureRmSqlServerFirewallRule -ResourceGroupName $Environment -ServerName $Server -FirewallRuleName "clientFirewallRule1" -StartIpAddress $startIP -EndIpAddress $EndIP

New-AzureRmSqlDatabase -ResourceGroupName $Environment  -ServerName $Server -DatabaseName $databaseName -Edition "Standard" -RequestedServiceObjectiveName $databaseService

}

Catch

{

$LastError=$Error[0].ToString()

Write-Host "Something went wrong - confirm correct syntax see https://msdn.microsoft.com/en-us/library/azure/jj554330.aspx for help."

Write-Host "The error was: $LastError"

What I have done here is hard-code three parameters ( database edition, start IP address and end IP address) which for my situation won’t change but I have given the ability to pass in the environment name, SQL Server name and database name.

So a prompt will be presented to the user – here you should enter the relevant details and click enter.

qq4

So the environment gets created.

qq7

Next we get asked for the username and password for the SQL Server that you would like to setup.

qq9

Then finally the SQL Server and database gets created

qq10

Using the  above “completed” script the process is much quicker than using the portal. Next week I will share other cmdlets that I find useful.

 

 

Advertisements

PowerShell – Deleting those files

Usually I prefer to use C# to delete files (as part of my retention policy for backups) as a stored procedure (SQLCLR), however after some homework I now have a PowerShell script that will delete files based on a variable NumberOfDaysRetention targeting your backup location looking for certain files.

You may need to tweak it as I only use BAK and TRN extensions for backups.

Hopefully you will find it useful as do I.

# Number of Days to Retain on the Local Storage
$NumberofDaysRetention = 7

# Location of the Backup Files
$path = '\\YOURBACKUPLOCATION'
$CutOfDate = (Get-Date).AddDays(-$NumberofDaysRetention)

# Search and Remove Full Backup Files
if ((Test-Path $path) -eq "True")
{
$count = (Get-ChildItem $path -include *.bak -recurse | ?{$_.LastWriteTime -lt        $CutOfDate -and !$_.PSIsContainer}).Count

if ($count -eq $null)
{
$count = 0
}

Get-ChildItem $path -include *.bak -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer} | Remove-Item
Write-Host "There were" $count "Full Backup files deleted"
}
else
{
Write-Host "There are no Full Backup files to be deleted"
}

# Search and Remove Transaction Log Files
if ((Test-Path $path) -eq "True")
{
$count = (Get-ChildItem $path -include *.trn -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer}).Count

if ($count -eq $null)
{
$count = 0
}

Get-ChildItem $path -include *.trn -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer} | Remove-Item
Write-Host "There were" $count "Transaction Log files deleted"
}
else
{
Write-Host "There are no Transaction Log files to be deleted"
}

#Example of going through from a root folder i.e. the instance name and from agent

$NumberofDaysRetention = 7

# Location of the Backup Files
$path = '\\YOURBACKUPLOCATION'
$CutOfDate = (Get-Date).AddDays(-$NumberofDaysRetention)

# Search and Remove Full Backup Files
if ((Test-Path $path) -eq "True")
{
$count = (Get-ChildItem -Force $path -include *.bak -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer}).Count

if ($count -eq $null)
{
$count = 0
}

Get-ChildItem $path -include *.bak -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer} | Remove-Item
#Write-Host "There were" $count "Full Backup files deleted"
}

# Search and Remove Transaction Log Files
if ((Test-Path $path) -eq "True")
{
$count = (Get-ChildItem $path -include *.trn -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer}).Count

if ($count -eq $null)
{
$count = 0
}

Get-ChildItem $path -include *.trn -recurse | ?{$_.LastWriteTime -lt $CutOfDate -and !$_.PSIsContainer} | Remove-Item
#Write-Host "There were" $count "Transaction Log files deleted"
}