In this third article on building Microsoft MEAP, I’ll show you how to Shard your SQL Server 2012 database using Replication to create a Shared Nothing data architecture to support Internet-scale mobile solutions.

In the previous article, I discussed Gartner’s Enterprise Application Integration Tools (EAI) critical capability for building a Mobile Enterprise Application Platform (MEAP) using Microsoft technologies. Tying into multiple backend packages and data sources is an essential CIO requirement for moving enterprise data out to mobile devices and SQL Server Integration Services (SSIS) performs this task beautifully.

Like virtually every enterprise and Internet based application or website, the database is the heart of the system. This is also true for MEAP systems. Don’t be fooled by MEAP vendors that use clever marketing terms to cover up this fact. The Mobile Middleware often associated with MEAP systems is a database and some kind of web/app server or HTTP listener. Staging tables in the database are needed to cache data moving between devices and backend systems. Since mobile devices run on the Internet via mobile data networks, web/app servers are needed to transmit data over HTTPS. The problem with most on-premise and even some cloud-based MEAP solutions is that they can’t deliver Internet scalability. I’m talking tens of thousands, hundreds of thousands, or even millions of devices.

When it comes to boosting performance and supporting more concurrent clients, you think of scaling up with beefier hardware and scaling out with more servers. Unfortunately, most systems I’ve observed around the world limit their scaling to load-balanced web/app servers pointed at a single database. I know, hard to believe.

Obviously, this only takes you so far before you run out of gas. Infrastructure Architects need to aim higher and learn from the scalability best practices of the world’s largest search engines, social networks, and e-commerce sites. Guess what, if your favorite social network has a billion users, a single database and a bunch of load-balanced web/app servers are going to melt down in the first few nanoseconds of operation. Instead, multiple layers of scaleout architectures are employed to support a large percentage of the global population, and the databases are no exception. Since databases are usually responsible for around 90% of all system bottlenecks, I would venture to say scaling out your database is one of the most important things you can do.

Data replication technologies are used by the world’s largest sites to horizontally partition Relational and NoSQL databases. Google coined the term “sharding” to refer to the shared-nothing database partitioning used by their Big Table architecture. Common sense tells you that even the world’s most powerful database running on the world’s biggest server will eventually hit a saturation point. Replicating either complete or partial copies of your big database to tens of thousands of commodity servers that don’t share a common disk is the key to scaling out. Sorry about the big SAN you just spent a few million dollars on. The other nuance you see with these large, replicated systems is breaking up the reads and writes into different groups of servers. Following the 80/20 rule, most clients on the Internet are SELECTing data while a much smaller group are uploading INSERTs, UPDATEs, and DELETEs. It therefore follows that the bulk of the replicated database shards are read-only and load-balanced just like web servers. These servers have a lower amount of replication overhead because data updates only come to them in one direction from the top of the hierarchy. A second, smaller group of load-balanced database servers handle all the writes coming in from the Internet clients. They track and merge those changes into the multi-master databases at the top of the hierarchy. Hopefully, all this makes sense to you. To put it simply, instead of a billion people trying to hit a single database, smaller fractions of a billion people are hitting multiple copies of that same database. Additionally, these database shards are smaller and therefore faster because disk I/O is reduced since the ratio between memory and data on disk is improved. Of course, it can get more granular than that. Instead of replicating complete copies of your database, you could replicate each table down to its own individual database server to scale out even further. If that’s not enough, you can replicate different ranges of table rows down to their own individual database servers so that a single table spans multiple servers. In all cases, your app servers would maintain the intelligence to know which sharded database servers to communicate with in order to get the right answer for a mobile user. It also means that you need design your databases differently to avoid table JOINs across replicated server nodes.

While you’re probably well-versed in scaling out your stateless web/app servers using load-balancers, there’s one other ingredient that’s commonly used across the world’s largest systems. Distributed caching. While scaling out replicated database shards and web/app servers make it possible to support a large chunk of the planet, it’s caching that takes this performance and scalability to the next level. It’s great that the answer to your query is found on a nearby, replicated database shard. But what if your answer was already in RAM and you didn’t have to query the database in the first place? A tier of distributed caching servers holding terabytes of data in RAM is what helps your Facebook page load fast when you and a few hundred million of your best friends are all trying to access the site at the same time.

Now that you have a bite-sized primer on how the world’s largest systems scale and perform, you’re probably wondering if you can do the same thing to scale your MEAP solution using the Microsoft servers and technologies that you already own.

The answer is Yes!

While I understand that you might not need to support millions of users with your solution, the important thing to take away from this article is that with Microsoft MEAP you can. As you can guess from the title of this article, I’m going to focus on scaling out SQL Server since this might be more unfamiliar to you than scaling out Internet Information Services (IIS) using the Network Load Balancing (NLB) feature of Windows Server. Also, if you’re wondering about that cool distributed caching thing, we have that too in the form of the Windows Server AppFabric Cache. Both our load-balancing and distributed cache technologies are included features of Windows Server.

Let’s jump into scaling SQL Server since it’s the heart of Microsoft MEAP. From a scale-up perspective, here’s a few things you can do with SQL Server Standard Edition:

It supports up to 64 GB of RAM. Hey, RAM is cheap. Buy all 64 GB and allow your database to run in memory as much as possible to avoid disk I/O.

It supports up to 16 cores. While you might use this many cores (or more) on the SQL Server at the top of your hierarchy, you won’t need this many for your shard servers. Faster clock speeds and the largest possible L3 shared cache you can find are key.

It’s time to make the move from spinning disks to solid state drives (SSD). Upgrading to a SATA SSD can give you up to a 100x performance boost over their rotating counterparts. Do your research when looking a different vendors.

Make sure the replicated data is moving as fast as possible between your servers by using 10 gigabit Ethernet network cards and switches. Make sure to keep your network drivers updated as your vendor releases new ones.

Let’s scale out!

The first thing to consider when scaling out any database, is that the schema must be designed to support breaking the database apart. In other words, think about how you might turn your relational database with referential integrity, foreign keys, and other constraints into something that looks more like a NoSQL database. Denormalize to eliminate performance-killing JOINs. Keep in mind that if you can’t build a database this way or you have an existing database that you can’t change, you’ll need to use Transactional Peer to Peer Replication to make complete copies of your database to scale out. Normally, I would prefer that you only use P2P or “Always On” to maintain a separate replica hierarchy in another data center.

Those of you who know me are aware that I’ve been lucky enough to build many of the largest mobile systems in the world utilizing SQL Server Merge Replication. I’ve also done my best to teach others what I’ve learned along the way. This time around, I’m going to show you how to use this same replication technology to create a variety of database shards. As you might imagine, I’ll use the ContosoFruit database of data aggregated via SSIS from the backend data sources from the last article.

Replication in SQL Server uses the concepts of Publishers to describe which database is making its data available and Subscribers to describe which databases are consuming replicas. As you might imagine, the ContosoFruit database will be the Publisher. That being said, since I will be creating 3 shards, one for each table, I won’t be publishing the entire database as a single entity. You’ll have 3 Publications instead. In order to create a new Publication based on this database, I need to you expand the Replication folder in the Object Explorer.

Customer Publication

Right click on the Local Publications folder and select New Publication to launch the New Publication Wizard. Click Next.

If your Distributor isn’t already configured, you’ll be taken to the Distributor dialog where you will select the first radio button to allow your SQL Server to act as its own Distributor . Click Next .

isn’t already configured, you’ll be taken to the dialog where you will select the first radio button to allow your . Click . If your SQL Server Agent isn’t already configured, you’ll be taken to the SQL Server Agent Start dialog where you should select the Yes radio button to start the Agent service automatically. Click Next .

isn’t already configured, you’ll be taken to the SQL dialog where you should select the radio button to start the Agent service automatically. Click . If you don’t yet have a folder to hold the initial database snapshots, you’ll be taken to the Snapshot Folder dialog. Before entering a path in the Snapshot folder text box, create a folder on your local PC called Snapshot. Share that folder as a network share that’s available to Everyone with Read/Write permissions . Now go back to the Snapshot folder text box and enter \\MachineName\Snapshot and then click Next .

dialog. Before entering a path in the text box, create a folder on your local PC called Snapshot. Share that folder as a network share that’s available to with . Now go back to the text box and enter and then click . Click Next to move to the Publication Database dialog.

to move to the dialog. Select the ContosoFruit database and click Next .

database and click . In the Publication Type dialog, select Merge Publication and click Next .

dialog, select and click . In the Subscriber Types dialog, select SQL Server 2008 or later and click Next .

dialog, select and click . In the Articles dialog, expand the Tables tree view and check the Customers check box. This means that the Customers table and all its columns will be replicated. Check the Highlighted table is download-only check box. This ensures that only changes made to the Publisher at the top of the hierarchy will be replicated down to read-only Subscribers. Click Next .

dialog, expand the tree view and check the check box. This means that the Customers table and all its columns will be replicated. Check the check box. This ensures that only changes made to the Publisher at the top of the hierarchy will be replicated down to read-only Subscribers. Click . In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table. Merge replication uses this to track each row for changes. If your Primary Key is already a Uniqueidentifier, the system will use it instead of adding a new one. Uniqueness is an important part of any data sync system. Click Next .

dialog you’re informed that a column will be added to your table. Merge replication uses this to track each row for changes. If your Primary Key is already a Uniqueidentifier, the system will use it instead of adding a new one. Uniqueness is an important part of any data sync system. Click . In the Filter Table Row dialog, I don’t want you to create any filters because I want this Customers shard to contain the complete table. Click Next .

dialog, I don’t want you to create any filters because I want this Customers shard to contain the complete table. Click . In the Snapshot Agent dialog, check both check boxes. Click the Change button and in the New Job Schedule dialog, change the Recurs every: text box to 1 day(s) instead of 14. Click OK and then click Next .

dialog, check both check boxes. Click the button and in the dialog, change the text box to 1 day(s) instead of 14. Click and then click . In the Agent Security dialog, click the Security Settings button. In the Snapshot Agent Security dialog, select the second radio button to run it under the SQL Server Agent service account . You won’t do this in production but we’re doing it now for expedience sake. Click OK and then click Next .

dialog, click the button. In the dialog, select the radio button to run it under the . You won’t do this in production but we’re doing it now for expedience sake. Click and then click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the and click . In the Complete the Wizard dialog, enter CustomerShard and click Finish .

dialog, enter and click . In the Creating Publication dialog, if everything succeeds, click Close.

Sorry about all those tedious steps. Keep in mind that the next two shard Publications will be easier to create so let’s get to it.

Product Publication

Like before, expand the Replication folder in the Object Explorer, right click on the Local Publications folder, select New Publication to launch the New Publication Wizard and click Next.

In the Publication Database dialog, select the ContosoFruit database and click Next .

dialog, select the database and click . In the Publication Type dialog, select Merge Publication and click Next .

dialog, select and click . In the Subscriber Types dialog, select SQL Server 2008 or later and click Next .

dialog, select and click . In the Articles dialog, expand the Tables tree view and check the Products check box. Check the Highlighted table is download-only check box. Click Next .

dialog, expand the tree view and check the check box. Check the check box. Click . In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table. Click Next .

dialog you’re informed that a column will be added to your table. Click . In the Filter Table Row dialog, I don’t want you to create any filters because I want this Products shard to contain the complete table. Click Next .

dialog, I don’t want you to create any filters because I want this Products shard to contain the complete table. Click . In the Snapshot Agent dialog, check both check boxes. Click the Change button and in the New Job Schedule dialog, change the Recurs every: text box to 1 day(s) instead of 14. Click OK and then click Next .

dialog, check both check boxes. Click the button and in the dialog, change the text box to 1 day(s) instead of 14. Click and then click . In the Agent Security dialog, click the Security Settings button. In the Snapshot Agent Security dialog, select the second radio button to run it under the SQL Server Agent service account . Click OK and then click Next .

dialog, click the button. In the dialog, select the to run it under the . Click and then click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, enter ProductShard and click Finish .

dialog, enter and click . In the Creating Publication dialog, if everything succeeds, click Close.

Only one more shard to go. This one will be different because it’s designed to support write operations coming in from mobile devices.

Order Publication

Expand the Replication folder in the Object Explorer, right click on the Local Publications folder, select New Publication to launch the New Publication Wizard and click Next.

In the Publication Database dialog, select the ContosoFruit database and click Next .

dialog, select the database and click . In the Publication Type dialog, select Merge Publication and click Next .

dialog, select and click . In the Subscriber Types dialog, select SQL Server 2008 or later and click Next .

dialog, select and click . In the Articles dialog, expand the Tables tree view and check the Orders check box. Click Next .

dialog, expand the tree view and check the check box. Click . In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table. Click Next .

dialog you’re informed that a column will be added to your table. Click . In the Filter Table Row dialog, click the Add button and select Add Filter. In the Add Filter dialog, go to the Filter statement text box and add 1 = 0 to the end of the WHERE clause. The filter should look like the following when you’re done:

Using 1 = 0 as the table filter causes Replication to work in an upload-only manner. When the Orders table is synchronized, only the empty shell of the table will be created in the Subscriber database. Any new data added to it will be uploaded to ContosoFruit and then removed from the Subscriber database. Click Ok and Next .

as the table filter causes Replication to work in an manner. When the Orders table is synchronized, only the of the table will be created in the Subscriber database. Any new data added to it will be uploaded to ContosoFruit and then removed from the Subscriber database. Click and . In the Snapshot Agent dialog, check both check boxes. Click the Change button and in the New Job Schedule dialog, change the Recurs every: text box to 1 day(s) instead of 14. Click OK and then click Next .

dialog, check both check boxes. Click the button and in the dialog, change the text box to 1 day(s) instead of 14. Click and then click . In the Agent Security dialog, click the Security Settings button. In the Snapshot Agent Security dialog, select the second radio button to run it under the SQL Server Agent service account . Click OK and then click Next .

dialog, click the button. In the dialog, select the to run it under the . Click and then click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, enter OrderShard and click Finish .

dialog, enter and click . In the Creating Publication dialog, if everything succeeds, click Close.

You now have 3 Publications and you can have as many Subscription databases as you need to scale out. In this example, I will just have you create 1 Subscription to match each Publication.

Customer Subscription

Expand the Replication folder in the Object Explorer, right click on the Local Subscriptions folder, select New Subscription to launch the New Subscription Wizard and click Next.

In the Publication dialog, select CustomerShard and click Next .

dialog, select and click . In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor . Click Next .

dialog, select the radio button to . Click . In the Subscribers dialog, check the check box for the local SQL Server that you’re using. Click the Subscription Database combo box and select New Database . In the New Database dialog, enter Customer1 in the Database name text box and click OK . Click Next .

dialog, check the check box for the that you’re using. Click the combo box and select . In the dialog, enter in the text box and click . Click . In the Merge Agent Security dialog, click the ellipsis on the far right. In the dialog, select the second radio button to run under the SQL Server Agent service account . You won’t use this security option in production. Click OK and click Next .

dialog, click the on the far right. In the dialog, select the radio button to run under the . You won’t use this security option in production. Click and click . In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously . This is the obvious choice since you creating a real-time, OLTP database. Click Next .

dialog, click the combo box and select . This is the obvious choice since you creating a real-time, OLTP database. Click . In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next .

dialog, stick with the default value of and click . In the Subscription Type dialog, click the Subscription Type combo box and select Client . This prevents new data from being added at the Subscriber and uploaded back to the Publisher. Click Next .

dialog, click the combo box and select . This prevents new data from being added at the Subscriber and uploaded back to the Publisher. Click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, click Finish. If everything succeeds, click Close

To verify that all your settings are correct and that everything is working, open your new Customer1 database, right click on the Customers table and Select Top 1000 Rows. The table should be filled with the same list of customers that you find in the ContosoFruit database. The next test is to add a new row in the ContosoFruit Customers table, wait for several seconds, and then refresh the Customer1 Customers table. The new row should appear and you now have your first read-only database shard based on SQL Server.

Product Subscription

Expand the Replication folder in the Object Explorer, right click on the Local Subscriptions folder, select New Subscription to launch the New Subscription Wizard and click Next.

In the Publication dialog, select ProductShard and click Next .

dialog, select and click . In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor . Click Next .

dialog, select the radio button to . Click . In the Subscribers dialog, check the check box for the local SQL Server that you’re using. Click the Subscription Database combo box and select New Database . In the New Database dialog, enter Product1 in the Database name text box and click OK . Click Next .

dialog, check the check box for the SQL Server that you’re using. Click the combo box and select . In the dialog, enter in the text box and click . Click . In the Merge Agent Security dialog, click the ellipsis on the far right. In the dialog, select the second radio button to run under the SQL Server Agent service account . Click OK and click Next .

dialog, click the on the far right. In the dialog, select the radio button to run under the . Click and click . In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously . Click Next .

dialog, click the combo box and select . Click . In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next .

dialog, stick with the default value of and click . In the Subscription Type dialog, click the Subscription Type combo box and select Client . Click Next .

dialog, click the combo box and select . Click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, click Finish. If everything succeeds, click Close

To verify that all your settings are correct and that everything is working, open your new Product1 database, right click on the Products table and Select Top 1000 Rows. The table should be filled with the same list of products that you find in the ContosoFruit database. The next test is to add a new row in the ContosoFruit Products table, wait for several seconds, and then refresh the Product1 Products table. The new row should appear and you now have your second read-only database shard based on SQL Server.

Order Subscription

Expand the Replication folder in the Object Explorer, right click on the Local Subscriptions folder, select New Subscription to launch the New Subscription Wizard and click Next.

In the Publication dialog, select OrderShard and click Next .

dialog, select and click . In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor . Click Next .

dialog, select the radio button to . Click . In the Subscribers dialog, check the check box for the local SQL Server that you’re using. Click the Subscription Database combo box and select New Database . In the New Database dialog, enter Order1 in the Database name text box and click OK . Click Next .

dialog, check the check box for the SQL Server that you’re using. Click the combo box and select . In the dialog, enter in the text box and click . Click . In the Merge Agent Security dialog, click the ellipsis on the far right. In the dialog, select the second radio button to run under the SQL Server Agent service account . You won’t use this security option in production. Click OK and click Next .

dialog, click the on the far right. In the dialog, select the radio button to run under the . You won’t use this security option in production. Click and click . In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously . This is the obvious choice since you creating a real-time, OLTP database. Click Next .

dialog, click the combo box and select . This is the obvious choice since you creating a real-time, OLTP database. Click . In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next .

dialog, stick with the default value of and click . In the Subscription Type dialog, click the Subscription Type combo box and select Client . This prevents new data from being added at the Subscriber and uploaded back to the Publisher. Click Next .

dialog, click the combo box and select . This prevents new data from being added at the Subscriber and uploaded back to the Publisher. Click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, click Finish. If everything succeeds, click Close.

To verify that all your settings are correct and that everything is working, open your new Order1 database, right click on the Orders table and Select Top 1000 Rows. The table should be empty. The next test is to add a new row in the empty Order1 Orders table, wait for several seconds, and then refresh the ContosoFruit Orders table. The new row should appear and you now have your writable database shard based on SQL Server.

Congratulations! You’ve scaled out one database into one writable database shard and two read-only shards as shown below:

The ContosoFruit database will no longer have to bear the brunt of all your mobile devices retrieving Customers and Products while uploading new Orders. ContosoFruit will only see 3 connections moving and merging data back and forth instead of thousands.

The web services I’ll be showing you how to create in the next article will point to the appropriate shards from the IIS app servers. Keep in mind that in a production system, you’ll need to create at least 2 load-balanced SQL Servers for each shard in order to maintain high availability.

Now that you know how to shard complete tables to n database server nodes, you probably want to know how to shard at an even more granular level. I’m talking about scaling ranges of table rows across multiple server nodes. The example you always hear about is partitioning a Customer table with tens of millions of rows by the first letter of a customer’s last name. For instance, node 1 gets customers A – I, node 2 gets J – R, and node 3 gets S – Z.

You can slice and dice this any way you want. You could even have 26 separate nodes for every letter of the alphabet if you need that level of scale. Keep in mind that you won’t necessarily get an even distribution of table rows across nodes since the “S” node will have dramatically more customers than the “Q” node. Using a customer Id column to filter on might yield better results when it comes to numerically balancing the load. Speaking of balancing, as the number of rows in a given table increase, you will find that some nodes will start to have more rows than others. From time to time, you’ll need to re-balance them.

Luckily, Merge Replication Publications have the ability to perform row filtering which makes this more granular level of sharding pretty simple. Keep in mind that you will only do this type of filtering for your download-only/read-only shards. For this example, I’m going to create 2 Customer Publications that filter the rows based on the Id column in order to get 2 nodes with a roughly equal number of customers.

Customer (First Half) Publication

Expand the Replication folder in the Object Explorer, right click on the Local Publications folder, select New Publication to launch the New Publication Wizard and click Next.

In the Publication Database dialog, select the ContosoFruit database and click Next .

dialog, select the database and click . In the Publication Type dialog, select Merge Publication and click Next .

dialog, select and click . In the Subscriber Types dialog, select SQL Server 2008 or later and click Next .

dialog, select or later and click . In the Articles dialog, expand the Tables tree view and check the Customers check box. Check the Highlighted table is download-only check box. Click Next .

dialog, expand the tree view and check the check box. Check the check box. Click . In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table. Click Next .

dialog you’re informed that a column will be added to your table. Click . In the Filter Table Row dialog, click the Add button and select Add Filter. In the Add Filter dialog, go to the Filter statement text box and add Id <= (SELECT COUNT(*)/2 FROM [dbo].[Customers]) to the end of the WHERE clause. The filter should look like the following when you’re done:

The subquery in the WHERE clause calculates the total number of rows, divides them by 2, and then returns a list of customers whose Id is less than or equal to the midpoint of the list. Click OK and then click Next .

and then click . In the Snapshot Agent dialog, check both check boxes. Click the Change button and in the New Job Schedule dialog, change the Recurs every: text box to 1 day(s) instead of 14. Click OK and then click Next .

dialog, check both check boxes. Click the button and in the dialog, change the text box to 1 day(s) instead of 14. Click and then click . In the Agent Security dialog, click the Security Settings button. In the Snapshot Agent Security dialog, select the second radio button to run it under the SQL Server Agent service account . Click OK and then click Next .

dialog, click the button. In the dialog, select the radio button to run it under the . Click and then click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, enter CustomerFirstHalfShard and click Finish .

dialog, enter and click . In the Creating Publication dialog, if everything succeeds, click Close.

Customer (Second Half) Publication

Expand the Replication folder in the Object Explorer, right click on the Local Publications folder, select New Publication to launch the New Publication Wizard and click Next.

In the Publication Database dialog, select the ContosoFruit database and click Next .

dialog, select the database and click . In the Publication Type dialog, select Merge Publication and click Next .

dialog, select and click . In the Subscriber Types dialog, select SQL Server 2008 or later and click Next .

dialog, select and click . In the Articles dialog, expand the Tables tree view and check the Customers check box. Check the Highlighted table is download-only check box. Click Next .

dialog, expand the tree view and check the check box. Check the check box. Click . In the Filter Table Row dialog, click the Add button and select Add Filter. In the Add Filter dialog, go to the Filter statement text box and add Id > (SELECT COUNT(*)/2 FROM [dbo].[Customers]) to the end of the WHERE clause. The filter should look like the following when you’re done:

The subquery in the WHERE clause calculates the total number of rows, divides them by 2, and then returns a list of customers whose Id is greater than the midpoint of the list. Click OK and then click Next .

and then click . In the Snapshot Agent dialog, check both check boxes. Click the Change button and in the New Job Schedule dialog, change the Recurs every: text box to 1 day(s) instead of 14. Click OK and then click Next .

dialog, check check boxes. Click the button and in the dialog, change the text box to 1 day(s) instead of 14. Click and then click . In the Agent Security dialog, click the Security Settings button. In the Snapshot Agent Security dialog, select the second radio button to run it under the SQL Server Agent service account . Click OK and then click Next .

dialog, click the button. In the dialog, select the radio button to run it under the . Click and then click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, enter CustomerSecondHalfShard and click Finish .

dialog, enter and click . In the Creating Publication dialog, if everything succeeds, click Close.

Customer (First Half) Subscription

Expand the Replication folder in the Object Explorer, right click on the Local Subscriptions folder, select New Subscription to launch the New Subscription Wizard and click Next.

In the Publication dialog, select CustomerFirstHalfShard and click Next .

dialog, select and click . In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor . Click Next .

dialog, select the radio button to . Click . In the Subscribers dialog, check the check box for the local SQL Server that you’re using. Click the Subscription Database combo box and select New Database . In the New Database dialog, enter CustomerFirstHalf in the Database name text box and click OK . Click Next .

dialog, check the check box for the SQL Server that you’re using. Click the combo box and select . In the dialog, enter in the text box and click . Click . In the Merge Agent Security dialog, click the ellipsis on the far right. In the dialog, select the second radio button to run under the SQL Server Agent service account . Click OK and click Next .

dialog, click the on the far right. In the dialog, select the radio button to run under the . Click and click . In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously . Click Next.

dialog, click the combo box and select . Click Next. In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next .

dialog, stick with the default value of and click . In the Subscription Type dialog, click the Subscription Type combo box and select Client . Click Next .

dialog, click the combo box and select . Click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, click Finish. If everything succeeds, click Close.

To verify that all your settings are correct and that everything is working, open your new CustomerFirstHalf database, right click on the Customers table and Select Top 1000 Rows. The table should be filled with the first half of the products that you find in the ContosoFruit database.

Customer (Second Half) Subscription

Expand the Replication folder in the Object Explorer, right click on the Local Subscriptions folder, select New Subscription to launch the New Subscription Wizard and click Next.

In the Publication dialog, select CustomerSecondHalfShard and click Next .

dialog, select and click . In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor . Click Next .

dialog, select the radio button to . Click . In the Subscribers dialog, check the check box for the local SQL Server that you’re using. Click the Subscription Database combo box and select New Database . In the New Database dialog, enter CustomerSecondHalf in the Database name text box and click OK . Click Next .

dialog, check the check box for the SQL Server that you’re using. Click the combo box and select . In the dialog, enter in the text box and click . Click . In the Merge Agent Security dialog, click the ellipsis on the far right. In the dialog, select the second radio button to run under the SQL Server Agent service account . Click OK and click Next .

dialog, click the on the far right. In the dialog, select the radio button to run under the . Click and click . In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously . Click Next .

dialog, click the combo box and select . Click . In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next .

dialog, stick with the default value of initializing and click . In the Subscription Type dialog, click the Subscription Type combo box and select Client . Click Next .

dialog, click the combo box and select . Click . In the Wizard Actions dialog, check the first check box and click Next .

dialog, check the check box and click . In the Complete the Wizard dialog, click Finish. If everything succeeds, click Close.

To verify that all your settings are correct and that everything is working, open your new CustomerSecondHalf database, right click on the Customers table and Select Top 1000 Rows. The table should be filled with the second half of the products that you find in the ContosoFruit database.

You’ve now scaled out one Customer table shard into two read-only shards that split the number of customers evenly as shown below:

Hopefully, you now see the power of horizontally scaling out SQL Server into shards of partial or complete tables. When you take this shared-nothing architecture into production, you’ll have n SQL Server Subscriber nodes with their own storage, CPU, memory, and networking. Merge Replication is a powerful, supported component of the SQL Server database engine that allows the Microsoft MEAP mobile middleware to meet your performance and scalability needs just like the world’s largest Internet sites. In the next article in the Building Microsoft MEAP series, I’ll show you how build REST web services that connect to the various database shards in order to expose their data out to smartphones and tablets.

Keep scaling!

– Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

Sign Up for my Newsletter and get a FREE Chapter of “Mobile Strategies for Business!”

[mc4wp_form id=”5975″]