Creating SQL Azure Server using Powershell with AzureResourceManager Mode

Question

Seems that you can only create a SQL Server through the Powershell on Azure using AzureServiceManagement mode. This creates a new server for me into a default resource group named "Default-SQL-WestUS". But how can I place this into an existing Resource Group so I can manage it alongside other relevant resources?

When I have attempted using AzureResourceManagement the Cmndlet chokes on the -Name parameter:

New-AzureResource -Name "testName" -Location "West US" -ResourceGroupName "TestGroupName" -ResourceType Microsoft.Sql/servers -ApiVersion "2014-04-01"

When I run the above I get:

'' is an invalid name because it contains a NULL character or an invalid unicode character.

I've also removed the -Name parameter (thinking Azure SHOULD create this name for me as it does with ServiceManager):

New-AzureResource -Location "West US" -ResourceGroupName "TestGroupName" -ResourceType Microsoft.Sql/servers -ApiVersion "2014-04-01"

But this just prompts me for a name, and I get the same error. I've tried using a null value for name as well and get the following:

Cannot validate argument on parameter 'Name'. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.

Is there a better way to create a new SQL Server within a ResourceGroup in Azure?

Thanks in advance!

-- UPDATED: --

Looks like you have to? use AzureServiceManagement Mode to create a SqlServer. You cannot link a SqlServer to a Resource group (is this coming in the future?).

BUT you SHOULD be able to add a Sql Database to ResourceManager. So I switched to ServiceManagement mode, created a SqlServer, switched back to ResourceManagement mode, created a resource group and I've updated my database creation script to link to both the server and the resource group as follows:

New-AzureResource –Name "TestDBName" –ResourceType Microsoft.Sql/servers/databases –ResourceGroup "TestGroupName" –Location "West US" -ApiVersion "2014-04-01" –ParentResource servers/[servername]

Which I have seen as the correct way to do this in many examples online, but for some reason I now just get a general error that give me no indication of what went wrong

New-AzureResource : : An error occurred while processing this request.

I want to be able to create a SqlServer on Azure into an existing ResourceGroup and then add databases to the server via PowerShell. I'll be continuing to try different variations on my scripts to get this to work, but I feel as if I have hit a wall. Many thanks (and points) to anyone who can help!

Umachandar - Microsoft · Accepted Answer · 2015-03-25 22:18:59Z

2

Below is a working example on how to create SQL Server resource using PS cmdlet. The issue was that you were missing some mandatory parameters like admin credentials for server & so on.

New-AzureResource -Name server1 -ResourceGroupName "Default-SQL-WestUS" -Location "West US" -ResourceType "Mirosoft.Sql/servers" -ApiVersion 2014-04-01 -PropertyObject @{administratorLogin="admin"; administratorLoginPassword="Passw@rd01"; version="12.0"}

You can also use a template with New-AzureResourceGroup cmdlet to create the server. See template below.

{
  "$schema": "http://schemas.management.azure.com/deploymentTemplate?api-version=2014-04-01-preview",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "administratorLogin": {
      "type": "string"
    },
    "administratorLoginPassword": {
      "type": "securestring"
    },
    "serverLocation": {
      "type": "string"
    },
    "serverName": {
        "type": "string"
    }
  },
  "resources": [
    {
      "apiVersion": "2014-04-01-preview",
      "location": "[parameters('serverLocation')]",
      "name": "[parameters('serverName')]",
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "version": "12.0"

      },
      "resources": [
        {
          "apiVersion": "2014-04-01-preview",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
          ],
          "location": "[parameters('serverLocation')]",
          "name": "AllowAllWindowsAzureIps",
          "properties": {
            "endIpAddress": "131.107.255.255",
            "startIpAddress": "131.107.0.0"
          },
          "type": "firewallrules"
        }
      ],
      "type": "Microsoft.Sql/servers"
    }
  ]
}

answered Mar 25, 2015 at 22:18

Umachandar - Microsoft

4843 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

INNVTV Over a year ago

Thank you Umachandar! I was able to create a SQL Server this way. Is Cloud Services suported within Resource Groups yet? I still get an error when attempting to add a database to the server, again it is a very vague message "An error occurred while processing this request". Even after I set my firewall rules and everything. Any chance you can help? Is there a good resource online for all of this? It is very hard ot find answers!

Umachandar - Microsoft Over a year ago

Please provide the sample command for creating the database. It is similar to server creation & make sure you have the mandatory parameters specified. Unfortunately we do not have good tutorials for the new ARM REST API & how to use that with SQL Database. We are working on that & hope to publish that soon. Firewall rules are required only for applications that use TDS to connect to the database. Management operations done via PS cmdlets or REST API bypass the firewall.

INNVTV Over a year ago

Thanks again, not sure what the mandatory parameters are since the error message is so vague. Here is what I am using: $DBProperties = @{Edition = "Enterprise"; MaxSizeGB = 100} New-AzureResource –ParentResource servers/servername -PropertyObject $DBProperties –ResourceType Microsoft.Sql/servers/databases –Name "TestDBName" –ResourceGroup "TestGroupName" –Location "West US" -ApiVersion "2014-04-01"

INNVTV Over a year ago

Umachandar: I created a new question for this here: stackoverflow.com/q/29294101/638311?stw=2 feel free to answer the database question there!

granth · Accepted Answer · 2016-03-24 00:37:56Z

If you are using Azure PowerShell 1.0 or later, and you have the AzureRM.Sql module installed, then you can use New-AzureRmSqlServer to achieve this.

See the official Azure docs for more details.

# Login to your Azure account
Add-AzureRmAccount

# Select an appropriate subscription
Select-AzureRmSubscription -SubscriptionId 4cac86b0-1e56-bbbb-aaaa-000000000000

# Create a new resource group and put a SQL server in it
# It will prompt you for the credentials that you want to set
# for the SQL administrator account
New-AzureRmResourceGroup -Name "yourresourcegroup" -Location "East US" | `
New-AzureRmSqlServer -ServerName "yoursqlservername" -Location "East US" `
-ServerVersion "12.0"

Note that this will not configure any firewall rules or databases. You will need to use New-AzureRmSqlServerFirewallRule and New-AzureRmSqlDatabase to do that.

Collectives™ on Stack Overflow

Creating SQL Azure Server using Powershell with AzureResourceManager Mode

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related