In last month’s newsletter I reviewed some of the new features of SQL Server 2005 that support scaling up and scaling out database servers, including technologies to scale out database reads. This month I’ll take a closer look at the features supporting scale-out writes in SQL Server 2005, and some possible directions suggested by those features.
The Goal
Many DBAs want a seamless method of scaling out writes in order to relieve the pressure on high-volume databases. For low-volume systems, you can use SQL Server’s merge replication or peer-to-peer transactional replication to scale out writes. However, in high-volume systems you might like to ease up pressure and extend their life by just adding additional servers.
Ideally, you could just add a new database server and have the database updates handled automatically. Oracle 10g’s Real Application Clusters (RAC), also called a “shared cache” cluster, allows several database servers to appear as one database server to an application. This does not come free: the shared cache RAC system requires significant memory-to-memory communication between the servers. Nonetheless, you can add a new server and have it join the RAC cluster, thereby scaling out database writes.
Microsoft has made a contribution to “grid” computing with the most recent release of Windows Server 2003 Compute Cluster for high-performance parallelized computations. You can add new servers (compute nodes) to a compute cluster seamlessly, so it is a form of scale-out, but not, as of yet, for SQL Server. Windows Compute Cluster does not apply to database data-it is only for computations that can apply parallel computing algorithms.
For scaling out databases, Microsoft tends to favor data partitioning. As Microsoft SQL Server Architect James Hamilton says:
When multi-node database solutions are needed to achieve the goals of the application, two general approaches can be employed. One approach is to delegate completely to the DBMS, and depend upon a cluster DBMS such as RAC. Another approach is to depend upon data partitioning with data-directed routing and/or midtier caching. The latter requires additional application design investment, but when this is done, it offers more robustness, lower cost, and greater application flexibility.
(For the entire paper see “Oracle Real Application Clusters and Industry Trends in Cluster Parallelism and Availability” at http://www.microsoft.com/sql/prodinfo/compare/oracle/oracle_rac2.mspx.)
While SQL Server does not yet support a seamless method of scaling out high-volume database writes, there are some indications of what a future solution might involve. We’ll start by examining federated servers, and then discuss two new developments in SQL Server 2005 related to partitioning.
Federated Database Servers
To date, the classic approach for scaling out writes in a high-volume SQL Server environment has been to partition database table data across multiple update servers. To accomplish this, SQL Server supports “federated” database servers (also called distributed partitioned views) where multiple servers support databases that contain tables containing partitioned amounts of the data, and each server contains a view definition using linked server references. Each table is constrained to take only a specified range of data, and the view on each server uses linked server definitions to reference the other related tables in the other servers. Queries and updates are directed to the views on any given server, and SQL Server handles placing updates in the correct database.
The main problem with federated serves has been building and maintaining them. Linked servers can be slow and difficult to troubleshoot, and migrating partitions of data from one server to another can be very laborious. Further, developers must often ensure that global non-partitioned data, such as lookup tables, exist on each database server and be kept up to date, which may require supplementing the system with replication.
Microsoft hasn’t really changed the technology for federated databases in SQL Server 2005, but they offer a new type of partitioning at the table level that offers a glimpse of a new approach.
Table Partitioning
SQL Server 2005 introduces support for partitioning at the table level without requiring views. When you partition a table, you define it to contain more than one partition, specifying the partitioning key which determines partition ranges, and then optionally placing those partitions on individual filegroups. You always reference just the table in your queries, not the partitions, and you can also partition the indexes on the table. Whenever you query or update the table, SQL Server distributes the query or update appropriately to the correct partitions. Table partitioning is a core internal feature in SQL Server 2005. Every SQL Server 2005 table has one partition by default. By creating a partition scheme and partitioning the table, you are actually adding additional partitions.
Instead of table partitioning, you can create views across distinct tables that contain partitions of the data, called “local” or “union” partitioned views. This technique is available in both SQL Server 2005 and 2000. The downside is that local partitioned views require a great deal of custom coding to manage, and you have to create and maintain indexes for each underlying table manually. The great advantage of partitioned tables over views is that SQL Server handles much of the maintenance (partitioning indexes and migrating data when required, for example) and all the partitions appear as one table.
Table partitioning is a step towards scaling out data because you can easily add and remove partitions from the partitioned table. In other words, you can extend the range of the partition to either an empty table or a table with new data in it, provided the target table is compatible. This concept, called a “sliding window scenario” uses individual filegroups per partition.
Read Only Filegroups
There are two new features of filegroups that have a bearing on scaling out database writes. In addition to using filegroups for table partitioning, SQL Server 2005 uses filegroups for “piecemeal restore”, a process by which you can restore filegroups in sequence.
What’s new is that you can restore read-only filegroups to a recovered database. Prior to SQL Server 2005, DBAs have had to restore all filegroups before recovering the database. If you do not restore a read-only filegroup, queries accessing it will fail even though the database is in a recovered state. You just get an error message if you try to reference the read-only data that is missing.
In addition, you can remove a read-write or read-only filegroup from a database, called making the filegroup “defunct”, using the ALTER DATABASE command. SQL Server will ensure that the database is consistent, and you will never be able to restore the filegroup to that database again once it is defunct.
These new features are small steps in the direction of scaling out database writes because the filegroups of a database are starting to become independent components of the database. It’s only a short step now to making read-only and read-write filegroups portable: it seems a natural next step to take a filegroup from one database and restore it to another database, provided that Microsoft can work out how to preserve database consistency.
Future Directions for Scaling Out
Microsoft has yet to reveal anything concrete about the next versions of SQL Server, but the stated architectural commitment to partitioning rather than shared-disk clusters, along with the above features, suggests several possible directions. For example, they might extend table partitioning beyond the single database level to encompass tables spanning multiple databases. Microsoft could extend how they handle filegroups to make filegroups more portable between databases. Both of these features might figure into a revised architecture for federated database servers. It seems clear that SQL Server is on its way to fully addressing the issue of scaling out database writes.
