Create SQL database on Azure using PowerShell and access from on Premises Microsoft SQL Server Management Studio
SQL Azure is cloud database-as-a-service provided by Microsoft cloud. The best part is that the data is hosted and managed by Microsoft data centers. We can build our applications on premises and move our data to cloud.
Azure SQL model provides:
- Elastic database pools – Allows you to provisioned 1000’s of database table as you need. Grow and shrink based on your requirement.
- Azure hosted database
- Pay for only what you use (no of plans available)
- Also enable Geo-replication for disaster recovery
- Reduces hardware cost and maintenance
In this example, we would be creating an SQL database on Azure Cloud and try to access SQL Server from on-premises SQL server management studio. We would be implementing this by using the PowerShell command and later configuring firewall rules that allow access from external parties.
- Azure subscription
- Azure PowerShell modules version 4.0 and later (Azure PowerShell Web API)
Once we are ready with the PowerShell modules. Open Windows PowerShell ISE (x86) as an administrator.
- Login to your Azure portal using the below command.
Apply necessary login details and click OK. After successful login,
- Lets create a Resource Group.
$ location = "west tour" $ resourceGroup = "mysqlserverRG" New-AzureRmResourceGroup -ResourceGroupName $ resourceGroup -Location $ location
- Let’s create a Logical SQL Server. We would be using “New-AzureRmSqlServer” command. A Logical SQL server contains a group of databases managed as a group.
$ servername = "myblogazuresqlserver" $ username = "mypersonallogin" $ password = "xxxxxxx" New-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $ serverName -SllAdministratorCredentials $ ( New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $ username , $ ( ConvertTo-SecureString -String $ password -AsPlainText -Force ))
Once the Logical Server is in place.
- Next step is to create a database within that server.
$ databasename = "myBlogDatabase" New-AzureRmSqlDatabase -ResourceGroupName $ resource_groupname -ServerName $ serverName -DatabaseName $ database name
Let’s navigate to the Azure portal and check the database we created “myBlogDatabase”. It uses a standard pricing tier.
- DTU: 10 (S0) “Database Transaction Units”
- Storage: 250 GB
Now when you try to log in to Cloud SQL Server from your own premises – SQL server management studio. You will receive an error message as below.
Navigate to the Azure portal and configure the firewall rules to allow access to your on-premises server.
Navigate to your database created in cloud -> Click Firewall setting -> specify the Rule name and Start and End IP. Once the rules are in place, you can now successfully login to Azure SQL Server from on-premises SQL Management Studio.