db sharding.

Question

hi Folks,
Currently our sql server database host data for all the regions like US/UK/Asia. However we have a new project coming up to split the individual region data to their respective data centers. For example if we have customers table that has a column "region" representing each row where it belongs to, we need respective regions data in this table to be stored in each of its own geographic region. so each region will have its own datacenter and db server and when a user in US inserts a data for US region it should go to database hosted in US region, if same user inserts data for EU region data should go to database hosted in EU region , same goes for data retrieval , any region should be able to retrieve each other region's data. Only thing is , respective region's data ownership should belong to their respective data center.

If im aware only way to do this is using linked server and distributed partitioned views. Is my understanding correct? or SQL server has any new feature introduced for data sharding in their latest versions?

Answer 1

Correct, there are not really any newer features that directly support this scenario. Distributed partitioned views would be the simple-minded way to go. You probably want Enterprise Edition to get federated servers going.

I would not be surprised if you run into issues, and in such case you may have to set up replication to a central store to make query across-the-board to be possible.

Answer 2

Hi @SQLServerBro,

A distributed partitioned view joins data from one or more SQL Server database, when developing a horizontally partitioned database environment, you can use distributed partitioned views to join your partitioned tables from different servers so it appears that all of the data is on the same server.
And linked servers enable the SQL Server Database Engine and Azure SQL Managed Instance to read data from the remote data resources and execute commands against the remote database servers outside of the instance of SQL Server.
You can use them to achieve your purposes.