Post
Ask Question

Create An Azure SQL Database Using PowerShell

In this article, you will learn to use PowerShell to deploy an Azure SQL database in an Azure resource group.

Prerequisites

  • Installs needed
    Azure PowerShell

  • Signups needed
    Azure Account

Install Azure PowerShell

Run the following command from an elevated PowerShell session: (Right Click on PowerShell and select Run as Administrator)

  1. Install-Module AzureRM -AllowClobber

PowerShell gallery is not configured as a trusted repository for PowerShellGet by default so for the first time you use the PSGallery you will see the following prompt:

If you don’t have NuGet or a version older than 2.8.5.201 of NuGet, you are prompted to download and install the latest version of NuGet.

Load the AzureRM module

Once the module is installed, you need to load the module into your PowerShell session. You should do this in a normal (non-elevated) PowerShell session. Modules are loaded using the Import-Module cmdlet, as follows:

  1. Import-Module AzureRM

If you get this error run the following command first before running Import-Module .

  1. Set-ExecutionPolicy RemoteSigned
Log in to Azure

Now Log in to your Azure Subscription using this command.

  1. Add-AzureRmAccount

You will get this screen pop up Log in with your credential.

Now let’s create variables to use in scripts

  1. # Resource name for your resources  
  2. $resourcegroupname = "myResourceGroup"  
  3. $location = "WestEurope"  
  4. # The logical server name: Use a random value or replace with your own (do not capitalize)  
  5. $servername = "server-$(Get-Random)"  
  6. # Set login and password for your database  
  7. # The login information for the server  
  8. $adminlogin = "ServerAdmin"  
  9. $password = "ChangeYourAdminPassword1"  
  10. # The ip address range that you want to allow to access your server   
  11. $startip = "0.0.0.0"  
  12. $endip = "0.0.0.0"  
  13. # The database name  
  14. $databasename = "mySampleDatabase"   

Create a resource group

A resource group is a logical container into which Azure resources are deployed and managed as a group. So let's create a resource group for that using New-AzureRmResourceGroup command.

  1. New-AzureRmResourceGroup -Name $resourcegroupname -Location $location

Create an Azure SQL Database logical server

A logical server contains a group of databases managed as a group. Let’s create a logical server using New-AzureRmSqlServer command.

  1. New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `  
  2. -ServerName $servername `  
  3. -Location $location `  
  4. -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))  

Create an Azure SQL Database server-level firewall rule using the New-AzureRmSqlServerFirewallRule command. A server-level firewall rule allows an external application, such as SQL Server Management Studio or the SQLCMD utility to connect to a SQL database through the SQL Database service firewall. In the following example, the firewall is only opened for other Azure resources. To enable external connectivity, change the IP address to an appropriate address for your environment. To open all IP addresses, use 0.0.0.0 as the starting IP address and 255.255.255.255 as the ending address.

  1. New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `  
  2. -ServerName $servername `  
  3. -FirewallRuleName "AllowSome" -StartIpAddress $startip -EndIpAddress $endip  
Create a database in the server with sample data

Create a database with an S0 performance level (Check Pricing Tiers in the server using the New-AzureRmSqlDatabase command). The following example creates a database called mySampleDatabase and loads the AdventureWorksLT sample data into this database.

  1. New-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname `  
  2. -ServerName $servername `  
  3. -DatabaseName $databasename `  
  4. -SampleName "AdventureWorksLT" `  
  5. -RequestedServiceObjectiveName "S0"  
In the next post, I will show you how to connect & query the database we created today.


Similar Articles