In my previous post I introduced the topic of database scalability using XPO. In that post we looked at a simple method of partitioning customers between two database servers, one covering customers in the north and the other covering customers in the south. In that post we had the simplistic scenario of clients only being interested in customers either in the north or the south and so they were set up (via connection string) to look only at those servers.
Whilst being sufficient to introduce the topic, there will only be a few scenarios whereby you can partition your data and your client applications in the same way. In most situations your client applications will want to access both servers to store and fetch customer information, and they will have to know which server to access based on information only available at runtime. So how is this achieved?
Looking at the following graphic you can see that Customers have been horizontally partitioned into two databases (CustomersA-M and CustomersN-Z). You will also see that there is an Index server who’s purpose it is to serve a connection string to the required server based on the last name of the customer.
In the above schema the Index database has a CustomerIndex table with the following structure:-
Whereby the StartLetter column holds the first letter of the partitioned data and the StopLetter column holds the last letter of the partitioned data. The connection string for the partitioned server is held in the ConnectionString column. So now, when we want to know the connection string of the server for a particular customer we simply take the first letter of their last name and execute:-
select connectionstring
from CustomerIndex
where @letter between StartLetter and StopLetter
Using this schema, it is easy for further partition the data in the future, should that be necessary. It is just a case of provisioning another server, splitting the data evenly across all servers and then updating the Index above.
Okay, so let’s see an example. The first thing we have to do is to abstract this away from the application developer because they don’t want to know anything about our database scalability solution. To do that, let’s build a helper class:-
class DBHelper {
public static string GetConnectionStringForCustomer(string customerName){
//GS - Guard against empty strings
if (customerName == String.Empty) { return String.Empty; }
//GS - Get the first letter of the Customer's last name
string firstLetter =
customerName.Split(" ".ToCharArray())[1].Substring(0, 1);
//GS - Guard against null or empty returns
if (String.IsNullOrEmpty(firstLetter)) { return String.Empty; }
/* Now that we have the first letter of the customer's last name
* we can access the index server and find out the connection
* string of the server to which this customer's details
* should be saved */
const string CONN_STRING =
"Data Source=.;Initial Catalog=Index;Integrated Security=True";
string targetConnectionString;
using (SqlConnection conn = new SqlConnection(CONN_STRING)) {
using (SqlCommand cmd = new SqlCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT ConnectionString FROM " +
"CustomerIndex WHERE @Letter between " +
"StartLetter and StopLetter";
cmd.Parameters.AddWithValue("@Letter", firstLetter);
cmd.Connection.Open();
targetConnectionString =
Convert.ToString(cmd.ExecuteScalar());
cmd.Connection.Close();
}
}
return String.IsNullOrEmpty(targetConnectionString) ?
String.Empty : targetConnectionString;
}
}
Now let’s create a function to call that abstraction:-
private static void SetDataLayerForCustomer(string custName) {
//GS - Get the connection string
string conn =
DBHelper.GetConnectionStringForCustomer(custName);
//GS - Guard against empty connections string
if (conn == String.Empty) {
throw new Exception("No connection string found!");
}
//GS - Explicitly set the datalayer
XpoDefault.DataLayer = XpoDefault.GetDataLayer(
conn, AutoCreateOption.DatabaseAndSchema);
}
Having done that, we can use XPO in the normal way:-
static void Main(string[] args) {
string custName = "Gary Short";
CreateCustomerAndOrders(custName);
ShowOrdersForCustomer(custName);
}
private static void CreateCustomerAndOrders(string custName) {
//GS - Set the datalayer for the customer
SetDataLayerForCustomer(custName);
//GS - Create a Customer and an Order and persist them
using (UnitOfWork uow = new UnitOfWork()) {
Customer customer = new Customer(uow);
customer.Name = custName;
//GS - Create a few orders
new Order(uow) { Customer = customer, OrderNumber = 1 };
new Order(uow) { Customer = customer, OrderNumber = 2 };
new Order(uow) { Customer = customer, OrderNumber = 3 };
new Order(uow) { Customer = customer, OrderNumber = 4 };
uow.CommitChanges();
}
}
private static void ShowOrdersForCustomer(string custName) {
//GS - Set the datalayer for the customer
SetDataLayerForCustomer(custName);
//GS - Fetch the customer
using (var uow = new UnitOfWork()) {
BinaryOperator criteria = new BinaryOperator("Name", custName);
Customer customer = uow.FindObject<Customer>(criteria);
//GS - Write out data
Console.WriteLine("Customer: " + custName +
" has the following order numbers: ");
foreach (Order order in customer.Orders) {
Console.WriteLine("Order Number: " + order.OrderNumber);
}
}
}
If you run this code you will find tables created in the CustomerN-Z database; changing the customer name from “Gary Short” to “Gary Adams” will cause tables to be created in the CustomerA-M database, with no change to the client code. For demonstration purposes I have only attached an Order to the Customer, and a very simple Order at that, but you can see the principle.
Now that I’ve shown how to work with individual customers and their related data, the question is, how do you work with groups of Customers? How do you answer questions like “show me the customers with orders placed between 2006 and 2007 where the combined value of orders placed is greater than 3M GBP or where a single order value is greater than 120K GBP”? Well, the short answer is, you don’t!
Okay, so that’s not much help; the slightly longer answer is that if you have so many customers that you are considering a scalability solution then you would never query your OLTP system for report data. The data in the OLTP system would be archived off (perhaps instantly using triggers or in an over night batch job) into a reporting system where you would run queries using your particular reporting solution, Sql Server Reporting Services perhaps.
So, to sum up then, in this post I’ve shown you how to horizontally partition customer data and how to abstract that away from the application programmer so that they are using XPO in a familiar manner.