5

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!

Silvia Doomra
  • 947
  • 8
  • 16
ZoharYosef
  • 247
  • 1
  • 2
  • 11

1 Answers1

3

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!

arcyqwerty
  • 10,325
  • 4
  • 47
  • 84
Silvia Doomra
  • 947
  • 8
  • 16
  • 2
    Does this mean that if a row is added to a Reference table in one of the Shards that that row will be replicated across all Shards? Is this the best practise if a reference table is updated with a new row? – codeputer Nov 14 '17 at 17:10
  • When converting a normalized DB to work with Elastic DB, I believe there is a requirement to de-normalize to ensure that the Shard Key must be on all Sharded Tables. E.g. Customer is within a Region, an Order has one and only one Customer, so normalized Order does not have RegionId as it's derived from Customer. To shard however, RegionId must be added - correct? – codeputer Nov 14 '17 at 17:27