Azure SqlServer and SqlDatabase resource groups

I would like to use Azure to create environments under a single ResourceGroup for clients comprising:

• X web servers
• Y app servers
• 1 database

Ideally, the database would be hosted on a server that is part of a different resource group, so I can leverage elastic pools across multiple clients.

When I attempt to use New-AzureRmResourceGroupDeployment to do this, I get an error stating that the parent SqlServer cannot be found.

I have created a SqlServer via PowerShell:

New-AzureRmSqlServer -servername "MyServer" -ResourceGroupName "Common" -Location "South Central US"

I then attempt a deployment for ClientA with:

New-AzureRmResourceGroup -Name 'ClientA' -Location "South Central US"
New-AzureRmResourceGroupDeployment -ResourceGroupName 'ClientA' -TemplateFile azure.ClientDeployment.json -Client 'ClientA'

My deployment configuration is:

{
  "parameters": { ... }
  "variables": { ... }
  "resources": [

    {
      "name": "MySqlServer/ClientA",
      "type": "Microsoft.Sql/servers/databases",
      "location": "[resourceGroup().location]",
      "apiVersion": "2014-04-01",
      "tags": {},
      "location": "[resourceGroup().location]",
      "properties": {
        "edition": "Basic"
      },
    }

  ],
  "outputs": {  }
}

Results in the error message:

New-AzureRmResourceGroupDeployment : 5:04:33 PM - Resource Microsoft.Sql/servers/databases 'MySqlServer/ClientA' failed with message '{
  "code": "NotFound",
  "message": "Server 'MySqlServer' does not exist in resource group 'ClientA' in subscription '{...}'.",
  "target": null,
  "details": [],
  "innererror": []
}

Note that both resource groups (Common and ClientA) are in the same subscription and location.

Is it possible to have a SqlServer part of one resource group, and the SqlDatabase part of a different resource group?

Separating SQLServer with one Subscriptions(location)and it's Database to another Subscriptions is not possible as of now.

See the similar question here in MSDN forum

An Azure resource group is also defined to be a strong container for its resources, which defines their identity (look at the URI of all Azure resources), and propagation of role-based access rights, and controls resource lifetimes - thus granting access to a resource group grants rights to its resources and deleting a resource group deletes the resources it contains.

Combining the above means that the resource group that contains a server must also contain the databases hosted/nested on that server.

Even if you try to create all in a single subscription and try to move the particular SQLDB resource group after. It won't allow doing that (In Portal)

For your case, I think to create all the services in a common subscription where the location common or easily accessible by all the client.

You can use AzureSpeed to get the latency for all the location from your current location and try to create the subscription in a common location which is having minimal latency for all your clients.