Azure SQL Federation (Federated)

Say Federation (Union) before the first, the table of vertical segmentation and horizontal Division
-------------------------------------------------------------------Horizontal Split-------------------------------------------------- ---
With an example: for example, everyone has a social security number above your information, then there must be a table to hold this information, China's 1.3 billion people's identity card information, it is not possible to put all in a table,

If you put in a table, the query will be slow, now what to do????

We can divide the table into 9 pieces, namely Table1, table2,table3,table4,table5 ...
If you add a piece of data, you can insert a different table depending on the ID,
For example: id%10: Represents the remainder of ID 10 = 0~9, insert into table name + value of id%10 ()
if id%10=2, then insert: INSERT INTO table2 ()

Query is also kind of:

If you pass an ID, get the ID, id%10=2
select* from TABL2 where Id=id

-------------------------------------------------------------------Vertical Split-------------------------------------------------- ---
Some tables have a lot of data, the single field is very long, the table takes up a lot of space, and it takes a lot of i/0 to perform the retrieval, which can affect performance.
This time you need to put the big field in another table, and the original table is one-to-one relationship.
This will not draw, I believe that smart you must understand.

Now let's introduce the database: extensibility model, with two extension models: Scale up and scale out
Scale up: It is a database located on a server, in which case it is difficult to handle a large load, if you need to scale up you have to add CPU RAM, when the service capacity is not enough you need to introduce a good machine,
But there is a spiritual point of the machine. This upward scaling is difficult to handle for larger loads.

Scale out: Select and multi-database, across multiple independent nodes scattered those databases, this model with the economy, scale out is typically horizontal partitioning, that is, multiple independent nodes split the database,
This is where Azure SQL Federation is playing an advantage.
Example:

For example, you sell mooncakes on the internet, the Mid-Autumn Festival, which means that you need to buy with good hardware, to deal with, but the Mid-autumn Festival to do, appear, excessive supply,

For example: Azure SQL Federation does not appear in these cases, should be flexible for him, scaling on demand, shrinking, pay-per-use, this is the rationale for the Union.

------------------------------------------How to create a Union------------------------------------------------------------------------ ----

These are the joint theories:

Federation
We are familiar with Table View Proc These objects are contained within a database boundary.
The Union uses the Create FEDERATION statement like any other object federation_name
But the difference is that a union is a special object that extends beyond the bounds of the database, because with the extension, he
Need to know what we
To extend to different union members.

Federation Root
Union is a central repository for storing information about scale-out data distribution
He's just a database,
Together with the Federated object, we can create a federated object

Federation Member (co-member)
A federated member is the physical container that holds the data part, the union member is the actual physical database, they are
SQL Server management, but they are no longer in the database list in SQL Server Management Studio

Federation Key
Union keys are used to define data distribution
Is the customer ID, Product ID, order ID
Union keys define how data is distributed and split
Now the Union key is the int bigint GUID varbinary

Atomic Unit
An atomic unit represents a single instance of a union
For example, the customer will have the relevant records: for example
Customer service address, customer service telephone, customer service order
So a union table has the same union key value on all rows representing an atomic unit
For example: If I have a customer ID that is 10 all related records: for example
Customer phone, customer address, customer order, all in one atomic unit, because
They relate to the ID of the customer whose value is 10 in the Customer table, and these related records are used
form an atomic unit

Architecture Cont. (Union tables and reference tables)
The Union table is the table on which we want to partition
For example: Customer phone, customer address ...
We're going to call these union tables

Reference table is a table that is not partitioned
We don't split the data, but the reference table is a static table.
There's no big change in the data: like status or ZIP code
They are non-union watches
As we split the data and create a multi-union member,
These tables are copied to each of the federated members,
They're not united, so we don't split their data,
But the entire table is copied to each of the federated members

"1. Create a database server

"2. Connect with this database: connection string here

"3. Create a Database

Note: Create under Master

3 in available databases , select the federated root that you created in the previous procedure

CID : Represents a federated distribution key
Because of the time relationship: Today is the first to say here, the next one will be more detailed.

I hope you get a profit from it!