Adding a SQL Azure Db to an Existing Logical Server or Pool

[seangwright]

Looking at the Farmer documentation for SQL Azure I can't seem to find a way to specify that I want to add a database to an existing Azure SQL logical server or an existing elastic pool.

We have these resources already allocated in an environment, and each new 'project' would create a database in the existing pool.

It seems in some places I can refer to existing resources (webApp service_plan_name to add a Web App to an existing plan) but in others the APIs seem strictly greenfield project oriented.

Am I missing something in the docs or is there an existing issue or PR for something like this?

Farmer seems like a very promising tool for us (and a great way to bring F# into our org) as long as it enables creating new resources that reference existing ones.

Thanks!

[isaacabraham]

Hey @seangwright. I think this is a new feature request - I originally saw as, exactly as you say, a "greenfield" model designed to get people up and running in a highly opinionated fashion. Over time though, we've observed Farmer being used for hybrid approaches as you suggest, with people wanting to use Farmer to "piggy back" onto existing resources.

There's two options:

1. We add the ability to connect standalone databases to existing SQL servers. We already have a mechanism for this - the ResourceId type, which is a resource to any resource that may already exist in Azure.
2. You create a Farmer template where the server details e.g. name etc. matches the existing SQL server that you've already deployed. Because of the nature of ARM templates, you can deploy them over the top of existing resources and they simply do nothing if the spec matches. There's even a tool (which Farmer has a thin API for as well) called what-if, which allows you to compare an ARM template to an existing resource group to see what will happen.

[seangwright]

With 1., I'm assuming this will express this ResourceId in the ARM .json and Azure (either the portal or the CLI) will handle all the associations?

With 2., you mention "they simply do nothing if the spec matches". What encompasses the 'spec'? The globally unique name/identifier of the resource, or some combination of properties like Tier/Resource Group/Location? Will it upsert settings that don't match?

I guess I can do some testing on my end 😁 but I didn't see any examples or mentions in the docs around these use-cases.

[isaacabraham]

With 1., I'm assuming this will express this ResourceId in the ARM .json and Azure (either the portal or the CLI) will handle all the associations?

Basically, yes. The resource already exists. You provide a ResourceId to that resource such as the name, resource group and subscription - we already have helpers to do this that create the full ARM syntax for specific Azure resources - and then ARM will link them up. Obviously if there are restrictions that Azure places on you, you'll need to conform to them e.g. you might need the Server and DB to live in the same subscription (maybe the same resource group? Maybe not).

With 2., you mention "they simply do nothing if the spec matches". What encompasses the 'spec'? The globally unique name/identifier of the resource, or some combination of properties like Tier/Resource Group/Location? Will it upsert settings that don't match?

The spec is basically all properties on the SQL Azure Server / DB instance. If it already exists, ARM will do its best to upsert the destination server with the properties in the template that you supply. That's why I suggest a soft test - create a template (using Writer.quick) and run the what-if command on that template to see what Azure thinks of them.

Yes, documentation would help here - can you raise a separate issue on this as it's an ongoing theme.

[danieldrokecww]

We also are stuck with a scenario where we need to create new a new database within a pre-existing server and add it to a pre-existing elastic pool (and pre-existing failover group). This generalizes to a myriad of other resource types as well, i.e adding additional functions to an existing app service plan, and we were wondering if there is a pattern that could be applied to all resources in Farmer.

@isaacabraham your mention of a ResourceId type to refer to other pre-existing dependencies in Azure seems like a nice concept. Discrete infrastructure would be able to reference common infrastructure without reconfiguring it during a deployment. And this (seemingly) generalizes to any dependency structure of resources. What would the SDK look like to expose this to users? Do they directly create a ResourceId? Do they create a builder and use it's generated value? I like the latter approach personally as long as we can also indicate a different resource group to account for scenarios where common infrastructure may live elsewhere in a subscription.

Specifically for the SQL Azure Server / DB resource types, since a user is first required to build a sqlServer {} so that they can add a sqlDb {}, would sqlDb also be updated as an IBuilder that DeploymentBuilder could accept in add_resource(s)? This would prevent us from creating a SQL Server that shouldn't exist.

Conceptual thoughts:

let commonServer = sqlServer {
    name "shared-infra-sql"
}

let discreteDatabase = sqlDb {
    name "discrete-database"

    // indicates dependsOn() for ARM
    depends_on [
        // returns a new ResourceId with the specified group
        (commonServer :> IBuilder).ResourceId.inGroup "common-resource-group";
    ]
}

arm {
    location Location.SouthCentralUS
    add_resource discreteDatabase
    add_resource commonServer // theoretical exception
}

or

let commonServer = sqlServer {
    name "shared-infra-sql"

    // prompts Farmer to exclude from ARM
    // could also throw exception if added as a resource to a deployment
    resource_group "common-resource-group"
}

let discreteDatabase = sqlDb {
    name "discrete-database"

    // by resource reference allows less verbose user experience
    depends_on [
        commonServer;
    ]
}

arm {
    location Location.SouthCentralUS
    add_resource discreteDatabase

    // theoretical exception
    add_resource commonServer
}