Moving data from Azure SQL to shards using elastic query

Question

I am new to Azure Elastic Pool and here is what I am trying to achieve

An SQL database named "Source" contains several tables of which some are centered around Customers, which needs to be moved to Elastic Pool and sharding needs to be done. That way, there will be a shard manager database and shard databases (database with tables in structure similar to "Source" with no data, one per each customer) will be created.

My question is - how do I transfer data from Source database to individual shards without having to know which shard database is mapped to which Customer? i.e. Does the data movement have to go from Source to Shard manager db?

To summarize, think of a web app which gives the user an option to archive data for a specific Customer. i.e. move the data from Source database to shard (specific to the Customer).

Answer 1

To move data from your Source database to individual shards without having to know which shard database is mapped to which customer.

You can follow the below steps:

Step 1: Create a shard map manager database and shard map for your Elastic Pool.

• The shard map manager is a special database that maintains global mapping information about all shards (databases) in a shard set.

Know more about Prepare the shard map manager database.

Step 2: Create shard databases (one per each customer) in your Elastic Pool.

Step 3: Use the Elastic Database Client Library to perform the data movement.

You can use either the .NET Framework client library, or the PowerShell scripts found at Azure SQL Database - Elastic Database tools scripts.