My first presentation of 2008 is booked. I'm talking about enterprise and design patterns at the CPD Centre, Glasgow Caledonian University at 7pm on 16 January. More details are available on my
events calendar.
On a recent train journey I overheard a couple of guys talking about the fact that SQL Server didn't scale. Oh really, thought I. Now I'm no SQL Server DBA guru so I don't know what out of the box scalability doodahs SQL Server has, and to be honest, I don't much care. Why? Well I'm glad you asked that, the answer is pretty simple really; there's a cool database scalability pattern that you can apply to scale out an RDBMS regardless of whether or not is supports scalability out of the box. As the solution is generic, it's my prefered way to go.
So, ever the evangelist, I enlightened my fellow travellers; I further endeared myself to them by entering the conversation using the approved Scots phrase
Yous two are talking pish!
Having explained it to them I thought I'd blog it here for your pleasure.
Okay, so here's the scenario; You have a database of Customers. Its fine whilst you have 10, 100, 1000 and so on Customers, but somewhere along the orders of magnitude you are going to have to scale your solution and to do that we are going to use the Data Dependant Routing pattern. The pattern does what it says on the tin, it routes you depending on what data you require.
What we do is to horizontally partition the Customers table, putting those with last names starting in the first half of the alphabet in a table on one server and those with last names starting in the second half of the alphabet in a table on another server. So, lets do that
CREATE DATABASE CustomersA_to_M
go
USE CustomersA_to_M
go
CREATE TABLE Customer
(
CustomerId INT IDENTITY(1,1) not null PRIMARY
KEY CLUSTERED,
FirstName NVARCHAR(20) not null,
LastName NVARCHAR(20) not null
)
go
CREATE PROCEDURE GetCustomersByLastName
@LastName NVARCHAR(20)
AS
SELECT
c.CustomerId,
c.FirstName,
c.LastName
FROM
Customer c
WHERE
c.LastName = @LastName
go
CREATE DATABASE CustomersN_to_Z
go
USE CustomersN_to_Z
go
CREATE TABLE Customer
(
CustomerId INT IDENTITY(1,1) not null PRIMARY
KEY CLUSTERED,
FirstName NVARCHAR(20) not null,
LastName NVARCHAR(20) not null
)
go
CREATE PROCEDURE GetCustomersByLastName
@LastName NVARCHAR(20)
AS
SELECT
c.CustomerId,
c.FirstName,
c.LastName
FROM
Customer c
WHERE
c.LastName = @LastName
go
Having done that, we need to set up a read only table on another server which will allow use to route to the correct server depending on the Customer's surname. The server is the Data Dependant Routing Manager and is created like so
CREATE DATABASE DDRManager
go
USE DDRManager
go
CREATE TABLE CustomerNameToConnectionStringMapping
(
CNTCSMId INT IDENTITY(1,1) not null PRIMARY
KEY CLUSTERED,
StartChar NCHAR(1) not null,
EndChar NCHAR(1) not null,
ConnectionString NVARCHAR(256) not null
)
go
CREATE PROCEDURE GetConnStringForChar
@Char NCHAR(1)
AS
SELECT
c.ConnectionString
FROM
CustomerNameToConnectionStringMapping c
WHERE
@Char between c.StartChar and c.EndChar
go
Now that we have the database sorted, we need to abstract out that complexity on the data layer. So first, let's define a Customer object
public class Customer
{
public int CustomerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
Then we'll define a DataFactory class that will abstract away the database scaling pattern and allow callers to have an easy interface into it
public class DataFactory
{
public List<Customer> GetCustomersByLastName(
string lastName)
{
//GS - Connect to the DDR manager and get a
//connection string to the server we need for
//this set of customers
string connString = String.Empty;
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString =
ConfigurationManager.ConnectionStrings[
"DDRManagerConnectionString"]
.ConnectionString;
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType =
CommandType.StoredProcedure;
cmd.CommandText = "GetConnStringForChar";
cmd.Parameters.AddWithValue("@Char",
lastName.ToCharArray()[0]);
cmd.Connection.Open();
connString = (string)cmd.ExecuteScalar();
}
//GS - Now use that connection string to
//fetch the data
List<Customer> Customers = new List<Customer>();
using (SqlConnection conn2 = new SqlConnection())
{
conn2.ConnectionString = connString;
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn2;
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "GetCustomersByLastName";
cmd2.Parameters.AddWithValue(
"@LastName", lastName);
cmd2.Connection.Open();
SqlDataReader reader = cmd2.ExecuteReader();
while (reader.Read())
{
Customer c = new Customer();
c.CustomerId = reader.GetInt32(0);
c.FirstName = reader.GetString(1);
c.LastName = reader.GetString(2);
Customers.Add(c);
}
}
//GS - Return the list of Customers
return Customers;
}
}
Next we'll group the DataFactory in a set of the other factories we may need in the future, using the Singleton pattern
public class Factories
{
public static readonly DataFactory DataFactory =
new DataFactory();
}
Finally, we'll give the pattern a little test
class Program
{
static void Main(string[] args)
{
string message = "The customer's last name is ";
message +=
Factories.DataFactory.GetCustomersByLastName(
"Alton")[0].LastName;
Console.WriteLine(message);
Console.Read();
}
}
And that's how we do that! :-)
Technorati Tags: SQL Server Scalability, Design Patterns