Sharding in SQL Server is accomplished by spreading data over several database instances. Every instance contains a shard of the data. 

Based on the sharding key, the program directs requests to the appropriate shard. SQL Server does not natively support sharding, so this requires custom logic.

 

Key points while using sharding

• Select shard keys that evenly spread the load among shards to prevent hotspots. 
• Create strong application logic to send requests to the relevant shard. 
• Use distributed transactions carefully if transactions cross several shards since they may affect performance. 
• Watch and Balance Shards: Consistently keep an eye on shard load and data dispersion. Should you find it required, redistribute shards.

 

Application-Level Sharding
if (recordID <= 1000)  {
   // Route to Shard 1 (Database1)
}
else {
   // Route to Shard 2 (Database2)
}