19 January,2011 by Jack Vamvas
Database system scaling is an important topic. Planning and design of a database system should include methods for scaling - upwards and outwards. Requirement to scale examples are; (a) change of focus on data gathering , which leads to different size of datasets b) business acquisition which requires integration with other systems.
Some features I've found useful in SQL Server for scaling are:
-Services Oriented Database Architecture(SODA)
-Data Dependant Routing (DDR)
A Partioned View splits data from a larger table into smaller tables (member tables).The partitioning occurs via a range(s) of values in a defined column horizontally.
Service Broker allows loosely held tasks to work together as an asynchronous application.
SODA Large centralised databases are still key but think of multiple large databases that are repositories for different classes of data
Compression used with Read Only file groups , useful in databases with multiple filegroups
DDR application intelligence#development is focused on middle tier , to decide on which databases to route to. Note: this is different from views across multiple databases.
Table partitioning (& Index Partioning) Spread tables and indices across multiple file groups in partitions .This allows flexibility in spreading across multiple devices. Useful in assisting data archiving strategy