azure-sql-databaseazure-elastic-scale

What is the difference between ReferenceTableInfo to ShardedTableInfo in Azure Elastic DB?

I downloaded the sample code -> "ElasticScaleStarterKit" (in visual studio -> file -> new -> project -> online -> Elastic DB Tools for Azure SQL - getting Started).

the schema defined as follows:

schemaInfo.Add(new ReferenceTableInfo("Regions"));
schemaInfo.Add(new ReferenceTableInfo("Products"));
schemaInfo.Add(new ShardedTableInfo("Customers", "CustomerId"));
schemaInfo.Add(new ShardedTableInfo("Orders", "CustomerId"));

What is the difference between ReferenceTableInfo to ShardedTableInfo?

I understand that the simple difference is between "dry" information that is true for all databases (like status table etc...), and personal information - for a specific customer.

But, what would happen if all the tables was set to be References?? What's the downside to this kind of setting:

schemaInfo.Add(new ReferenceTableInfo("Regions"));
schemaInfo.Add(new ReferenceTableInfo("Products"));
schemaInfo.Add(new ReferenceTableInfo("Customers"));
schemaInfo.Add(new ReferenceTableInfo("Orders"));

hope for any help :)

thank you!

Solution

  • Reference Tables are tables whose data is replicated, which means that if the reference table has 5 rows, then those 5 rows will exist on all instances of reference table.

    However, Sharded Table is the one in which data is partitioned. For example if you have 5 rows of data in Sharded table then 2 will live on one Shard (or database) and 3 on other. So no two databases will have same set of rows.

    This information is also used by the split/merge tool. For replicated tables, all rows are copied from source to target while rows are moved from source to target for sharded tables.

    Hope this helps!