db sharding.

Heisenberg 261 Reputation points
2021-09-23T16:13:43.93+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,353 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 107.1K Reputation points
    2021-09-23T22:04:00.15+00:00

    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.

    0 comments No comments

  2. YufeiShao-msft 7,081 Reputation points
    2021-09-24T06:37:20.337+00:00

    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.

    0 comments No comments