Exercise - Create a SQL elastic pool

  • 10 minutes

In this exercise, you'll create two Azure SQL databases to represent two fitness locations, then create a SQL elastic pool to manage two databases.

Create SQL servers

Start by creating a SQL server for our fitness databases, then add databases for two locations. The following steps use Vancouver and Paris, but feel free to use other location names.

  1. Run the following commands in Azure Cloud Shell to declare a few helper variables. The SERVERNAME variable will have a random number after the "-". Replace <location> with one of the locations in the following list, and replace <password> with a complex password for the databases.

    The free sandbox allows you to create resources in a subset of the Azure global regions. Select a region from this list when you create resources:

    • westus2
    • southcentralus
    • centralus
    • eastus
    • westeurope
    • southeastasia
    • japaneast
    • brazilsouth
    • australiasoutheast
    • centralindia
    ADMIN_LOGIN="ServerAdmin"
RESOURCE_GROUP=<rgn>[sandbox resource group name]</rgn>
SERVERNAME=fitnesssqlserver-$RANDOM
LOCATION=<location>
PASSWORD=<password>

    Tip

    To paste a command into the Azure Cloud Shell, right-click in the window and select Paste or use ctrl+shift+v.

  2. Create a server named fitnesssqlserver-nnnn (the nnnn is replaced by a randomly generated number).

    az sql server create \
--name $SERVERNAME \
--resource-group $RESOURCE_GROUP \
--location $LOCATION \
--admin-user $ADMIN_LOGIN \
--admin-password $PASSWORD

  3. Add a database named fitnessvancouverdb to fitnesssqlserver-nnnn.

    az sql db create \
--resource-group $RESOURCE_GROUP \
--server $SERVERNAME \
--name fitnessvancouverdb \
--max-size 2GB

  4. Add a database named fitnessparisdb to fitnesssqlserver-nnnn.

    az sql db create \
--resource-group $RESOURCE_GROUP \
--server $SERVERNAME \
--name fitnessparisdb \
--max-size 2GB

Create a SQL elastic pool

You're ready to set up the resources for the SQL elastic pool. Switch to the portal to create the elastic pool.

  1. Sign in to the Azure portal using the same account you used to activate the sandbox.

  2. From the Azure portal menu or the Home page, under Azure services, select Create a resource. The Create a resource pane appears.

  3. Search for and select SQL Elastic database pool. The SQL Elastic database pool pane appears.

  4. Select Create. The Create SQL Elastic pool pane appears.

  5. On the Basics tab, enter the following values for each setting.

    Setting Value
    Project details
    Subscription Concierge Subscription
    Resource group From the dropdown list, select [sandbox resource group name]
    Elastic pool details
    Elastic Pool Name Provide a meaningful name, such as fitnesssqlpool.
    Server Ensure your existing fitnesssqlserver-nnnn server is selected.
    Compute + storage Select the Configure elastic pool link. The Configure pane appears.

  6. On the Pool settings tab, enter the following value for the setting.

    Setting Value
    Service and compute tier
    Service tier From the dropdown list, under DTU-based purchasing model, select Basic (For less demanding workloads).

  7. Select Apply. The Create SQL Elastic pool pane reappears.

    Select the Tags tab. In your environment, adding tags is a recommended way to store metadata about Azure resources, for example CreatedBy with your name or Environment to indicate a dev, test, or production resource. Tags are a great way to notate the importance and provenance of Azure assets to others in your organization.

  8. Select Review + create. Review your entries, and then select Create. The SQL elastic pool might take several minutes to provision.

Add existing databases to the elastic pool

  1. Upon successfully completing deployment of your resources, select Go to resource. Your SQL elastic pool pane appears on the Overview section. Note there are currently no databases in the pool.

  2. In the top menu bar, select Configure. The Configure pane appears for your SQL elastic pool.

  3. Select the Databases tab, and then select Add databases. The Add databases pane appears.

  4. Select the databases you created for both locations, then select Apply. The Configure pane reappears.

  5. In the top menu bar, select Save.

Congratulations, you successfully added databases to a SQL elastic pool.