{
"metadata": {
"kernelspec": {
"name": "powershell",
"display_name": "PowerShell"
},
"language_info": {
"name": "powershell",
"codemirror_mode": "shell",
"mimetype": "text/x-sh",
"file_extension": ".ps1"
}
},
"nbformat_minor": 2,
"nbformat": 4,
"cells": [
{
"cell_type": "markdown",
"source": [
"Create Azure SQL Database\n",
"==================================\n",
"\n",
"Description\n",
"-----------\n",
"This notebook will help you get created single Azure SQL Database inside Azure SQL Server and configure a server-level firewall rule. For more information see Use PowerShell to create a single database and configure a server-level firewall rule\n",
""
],
"metadata": {
"azdata_cell_guid": "6af59d69-ade7-480a-b33e-52a86fe5bfd3"
}
},
{
"cell_type": "markdown",
"source": [
"Steps of this procedure include:\r\n",
"1. Connect to Azure subscription\r\n",
"1. Provision resource group for SQL Managed Instance\r\n",
"2. Create Sql Server\r\n",
"3. Provision firewall rules to allow access\r\n",
"4. Create Sql Database"
],
"metadata": {
"azdata_cell_guid": "d5346c50-c03b-4e3a-983f-7b4b22c78319"
}
},
{
"cell_type": "code",
"source": [
"# Resource Group \r\n",
"$resourceGroupName = \"\" # Name of the resource group to create in the current subscription\r\n",
"$location = \"\" # Name of location (see Appendix for a list of location settings)\r\n",
"\r\n",
"# SQL Server\r\n",
"$serverName = \"mysqlserver-$(Get-Random)\"\r\n",
"$adminLogin = \"\" # Username of Sql server. Default would be 'azureuser'\r\n",
"$password = \"\" # Desired password for Sql Server\r\n",
"\r\n",
"# SQL Database\r\n",
"$databaseName = \"\" # Name of database to be created\r\n",
"\r\n",
"# The ip address range that you want to allow to access your server\r\n",
"$startIp = \"0.0.0.0\"\r\n",
"$endIp = \"0.0.0.0\""
],
"metadata": {
"azdata_cell_guid": "c5c06fd6-8e47-4abb-808a-edc8b1c2d690"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Connect to Azure \r\n",
"Below command will open a _Dialouge Box_ asking your account credentials."
],
"metadata": {
"azdata_cell_guid": "e34334a7-0d55-4c18-8c0a-1c4a673629cd"
}
},
{
"cell_type": "code",
"source": [
"Connect-AzAccount"
],
"metadata": {
"azdata_cell_guid": "96800b54-48a8-463b-886c-3d0e96f29765"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Get Subscription\r\n",
"Below command will open a _Dialouge Box_ with list of subscriptions. Selecting one of those will set that subscription for rest of the commands."
],
"metadata": {
"azdata_cell_guid": "ed6b781d-ce7e-4b51-a7ec-1eeeb2032c73"
}
},
{
"cell_type": "code",
"source": [
"$subscription = Get-AzSubscription | Out-GridView -PassThru\r\n",
"Set-AzContext -SubscriptionName $subscription"
],
"metadata": {
"azdata_cell_guid": "17b57956-98cf-44de-9ab5-348469ddabf4"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create the New Resource Group\r\n",
"_New-AzResourceGroup_ command will create new resource group in given subscription."
],
"metadata": {
"azdata_cell_guid": "3ecc2a29-fb77-4f7f-8901-e9c5c71ce1a2"
}
},
{
"cell_type": "code",
"source": [
"# Create Azure resource group, if necessary\r\n",
"$rg = Get-AzResourceGroup | Where ResourceGroupName -eq $resourceGroupName\r\n",
"\r\n",
"if (!$rg)\r\n",
"{\r\n",
" # Need to create a new resource group\r\n",
" Write-Output \"Resource Group $resourceGroupName does not exist. Creating...\"\r\n",
" $rg = New-AzResourceGroup -Name $resourceGroupName -Location $location\r\n",
"}"
],
"metadata": {
"azdata_cell_guid": "4837690a-2204-49ab-8a19-414a8ce782b6"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create a SQL server with a system wide unique server name\r\n",
"_New-AzSqlServer_ will create Sql Server with required configurations."
],
"metadata": {
"azdata_cell_guid": "2d951526-40dc-49cc-8668-c393eaf58000"
}
},
{
"cell_type": "code",
"source": [
"Write-Output \"Creating SqlServer with name $serverName ....\"\r\n",
"New-AzSqlServer -ResourceGroupName $resourceGroupName `\r\n",
" -ServerName $serverName `\r\n",
" -Location $location `\r\n",
" -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `\r\n",
" -ArgumentList $adminLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))"
],
"metadata": {
"azdata_cell_guid": "c45757ac-6a58-468d-a04c-04504f8a2e0e"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create a server firewall rule that allows access from the specified IP range\r\n",
"_New-AzSqlServerFirewallRule_ creates a new firewall rule for sql server"
],
"metadata": {
"azdata_cell_guid": "ba895abf-3176-48b5-9e49-a060b3f74370"
}
},
{
"cell_type": "code",
"source": [
"Write-Output \"Configuring firewall for Sql Server...\"\r\n",
"New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `\r\n",
" -ServerName $ServerName `\r\n",
" -FirewallRuleName \"AllowedIPs\" -StartIpAddress $startIp -EndIpAddress $endIp"
],
"metadata": {
"azdata_cell_guid": "ceae5670-292f-4c45-9c10-4ac85baf2d07"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Create SQL Database\r\n",
"_New-AzSqlDatabase_ command will create new database in the server."
],
"metadata": {
"azdata_cell_guid": "b460ca8f-65a7-4d6c-94b7-6d7dd9655fad"
}
},
{
"cell_type": "code",
"source": [
"Write-Output \"Creating a gen5 2 vCore database...\"\r\n",
"$database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `\r\n",
" -ServerName $ServerName `\r\n",
" -DatabaseName $databaseName `\r\n",
" -Edition GeneralPurpose `\r\n",
" -VCore 2 `\r\n",
" -ComputeGeneration Gen5 `\r\n",
" -MinimumCapacity 2"
],
"metadata": {
"azdata_cell_guid": "dc3b2f6f-83ac-4a4d-9d81-2f534e90913e"
},
"outputs": [],
"execution_count": null
},
{
"cell_type": "markdown",
"source": [
"### Get Database details \r\n",
"_Get-AzSqlDatabase_ command gets database details on the server specified."
],
"metadata": {
"azdata_cell_guid": "0b35ed4f-1786-4102-a09a-a6a360fd20f2"
}
},
{
"cell_type": "code",
"source": [
"Get-AzSqlDatabase -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName"
],
"metadata": {
"azdata_cell_guid": "5001bf24-5f3f-434e-abf6-a5c21af4aa32"
},
"outputs": [],
"execution_count": null
}
]
}