Tag Archives

21 Articles
MEAP

Building Microsoft MEAP: Scaling Out SQL Server 2012

Posted by Rob Tiffany on
Building Microsoft MEAP: Scaling Out SQL Server 2012

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.

SQL Server + 2 IIS Servers

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.

Super Scale

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.

ContosoFruit

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.

New Publication Wizard

  • 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.
  • 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.
  • 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.
  • Click Next to move to the Publication Database dialog.
  • Select the ContosoFruit database and click Next.
  • In the Publication Type dialog, select Merge Publication and click Next.
  • In the Subscriber Types dialog, select SQL Server 2008 or later and click Next.
  • 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.
  • 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.
  • 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.
  • 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.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • In the Complete the Wizard dialog, enter CustomerShard and click Finish.
  • 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.
  • In the Publication Type dialog, select Merge Publication and click Next.
  • In the Subscriber Types dialog, select SQL Server 2008 or later and click Next.
  • 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.
  • In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table.  Click Next.
  • 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.
  • 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.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • In the Complete the Wizard dialog, enter ProductShard and click Finish.
  • 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.
  • In the Publication Type dialog, select Merge Publication and click Next.
  • In the Subscriber Types dialog, select SQL Server 2008 or later and click Next.
  • In the Articles dialog, expand the Tables tree view and check the Orders check box.  Click Next.
  • In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table.  Click Next.
  • 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:

Add Filter

  • 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.
  • 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.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • In the Complete the Wizard dialog, enter OrderShard and click Finish.
  • 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.
  • In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor.  Click Next.
  • 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.
  • 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.
  • 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.
  • In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • 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.
  • In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor.  Click Next.
  • 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.
  • 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.
  • In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously.  Click Next.
  • In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next.
  • In the Subscription Type dialog, click the Subscription Type combo box and select Client.   Click Next.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • 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.
  • In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor.  Click Next.
  • 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.
  • 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.
  • 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.
  • In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • 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:

Publications and Subscriptions

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.

3 Pubs + 3 Subs

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.

Customers A - 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.
  • In the Publication Type dialog, select Merge Publication and click Next.
  • In the Subscriber Types dialog, select SQL Server 2008 or later and click Next.
  • 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.
  • In the Article Issues dialog you’re informed that a Uniqueidentifier column will be added to your table.  Click Next.
  • 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:

Add Filter

  • 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.
  • 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.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • In the Complete the Wizard dialog, enter CustomerFirstHalfShard and click Finish.
  • 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.
  • In the Publication Type dialog, select Merge Publication and click Next.
  • In the Subscriber Types dialog, select SQL Server 2008 or later and click Next.
  • 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.
  • 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:

Add Filter

  • 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.
  • 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.
  • 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.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • In the Complete the Wizard dialog, enter CustomerSecondHalfShard and click Finish.
  • 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.
  • In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor.  Click Next.
  • 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.
  • 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.
  • In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously.  Click Next.
  • In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next.
  • In the Subscription Type dialog, click the Subscription Type combo box and select Client.   Click Next.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • 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.
  • In the Merge Agent Location dialog, select the first radio button to run all agents at the Distributor.  Click Next.
  • 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.
  • 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.
  • In the Synchronization Schedule dialog, click the Agent Schedule combo box and select Run Continuously.  Click Next.
  • In the Initialize Subscriptions dialog, stick with the default value of initializing immediately and click Next.
  • In the Subscription Type dialog, click the Subscription Type combo box and select Client.   Click Next.
  • In the Wizard Actions dialog, check the first check box and click Next.
  • 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.

Customer First + Second Half

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

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″]

MEAP

Building Microsoft MEAP: Adapters

Posted by Rob Tiffany on
Building Microsoft MEAP: Adapters

In this second article on building Microsoft MEAP, I’ll focus on implementing Gartner’s Enterprise Application Integration Tools critical capability using SQL Server Integration Services (SSIS) to connect to back end systems.

As I mentioned in the Introduction article, one of the top priorities for CIOs today is extending critical data from their backend systems out to the wireless devices used by employees.  This can often be easier said than done.  If your backend ERP, CRM, and other bespoke systems provide efficient, resilient, wireless-friendly connectivity and mobile client apps for smartphones, tablets, and laptops, then you’re in good shape.  Similarly, if your organization has spent the last decade building a mature Service Oriented Architecture (SOA) to expose your backend data sources, then your mobile devices have a way to consume that composite data.  Of course, you need to migrate those bloated SOAP + XML web services to something lighter and faster like REST + JSON.  On the other hand, if your organization is deficient in some of these areas, you need a Mobile Enterprise Application Platform (MEAP) with the adapters needed to connect those backend systems and data sources to Mobile Middleware.

As a recap, let’s take a look at the Gartner critical capabilities that pertain to backend adapters and the tooling needed to make those connections:

Enterprise Application Integration Tools:

  • Gartner Definition:  Tools for integration of mobile server with back end systems, both bespoke & purchased apps or application suites.
  • Microsoft Offering: SQL Server Integration Services (SSIS), Visual Studio SQL Server Data Tools.
  • Value Proposition:  Developers visually compose connections, actions, events and data movement rather than writing separate sets of integration code.  Adapters provide consistent connectivity to dozens of backend systems and data sources.  Microsoft is providing unrivaled, easy to use, drag and drop tools to connect ETL adapters with backend systems and databases.

Integrated Development  Environment:

  • Gartner Definition: Dedicated environment or plug-in for composing backend server & client side logic, including UI.
  • Microsoft Offering: Visual Studio
  • Value Proposition:  As the world’s most widely-used commercial IDE, you’re more likely to find plenty of proficient developers than with any other MEAP offering.  Additionally, developers are more productive since they don’t have to use different or specialized tools to target laptops, tablets, smartphones, servers, or the cloud.  Competing MEAP vendors have unfamiliar native and hybrid SDKs or 4GLs while Microsoft has millions of seasoned developers.
The key takeaway here is that Microsoft provides easy to use Enterprise Application Integration (EAI) Tooling in the form of SQL Server Data Tools in Visual Studio (IDE) and adapter technology in the form of SSIS.  This allows you to pull composite data into SQL Server (Mobile Middleware) for aggregation.  Keep in mind that the value of the EAI technology found in a MEAP vendor’s offering is derived from the following:
  • Must be easy to use and should connect to multiple backend systems in a consistent way.  In other words, if you have to connect to 20 different systems and you’re required to write unique code or connect 20 different ways then your MEAP package has failed.  Anyone can find a way to integrate with any system.  Doing so elegantly and consistently so that your people only have to be trained once is what you’re paying for.  Microsoft provides visual drag and drop tooling to make this task as simple as possible.
  • The more backend packages and data sources you can connect to the better.  It goes without saying that if your MEAP package can only connect to a handful of backend systems, if won’t be very valuable to your enterprise.  That being said, stay on the lookout for MEAP vendors that provide an extensive list of backend systems they can connect to – but connect to all of them via widely different methods.  SSIS can access data from any heterogeneous data source, package, message bus or interface including:
    • Database Systems: Oracle, Teradata, IBM DB2, SQL Server, MySQL, SQL Server Compact, Sybase, Access, PostgreSQL, Informix, FoxPro, Ingres, VSAM, IMS, LDAP, Lotus Notes, ADABAS, ADO, ADO.NET, ODBC, OLEDB (All databases)
    • Packages: SAP, Siebel, Dynamics, Hyperion, Salesforce, SharePoint
    • HTTP (Web Services), FTP, SMTP
    • File, Flatfile, Excel, EDI, XML
    • MSMQ, IBM MQ Series,Tibco Rendezvous, WebSphere, webMethods, SeeBeyond
  • The speed with which the data moves between backend packages and data sources and your SQL Server Mobile Middleware is critical.  Being able to interface your MEAP package with backend systems won’t be good enough if it can’t meet corporate performance SLAs.  Business operations in today’s real-time enterprise move at the speed of light and your MEAP package must do the same.  Luckily, Microsoft is ahead of the pack in this regard with its in-memory solution since it holds the world ETL record for moving in excess of 2 TB of data per hour (650+ MB/second).  It should come as no surprise that SSIS is depended on by more customers than any other ETL solution in the world.
  • Last but certainly not least, since Gartner requires security at every tier of any MEAP solution, EAI data movement between the Mobile Middleware server and backend systems must also be secure.  Microsoft provides the ability to password protect and encrypt all SSIS packages.  Furthermore, once your composite data is aggregated inside SQL Server, it is encrypted at rest.
Now that you know the facts about Gartner’s EAI critical capability and what to expect from Microsoft and other MEAP vendors, it’s time to make things real.  Theory is great, but seeing something in action is better and much more believable.  To keep things simple, I’ll build the EAI critical capability of Microsoft MEAP on my Windows 8 laptop.  I’ve got SQL Server 2012 installed and I’ll use 3 Access databases to represent a backend CRM, ERP, and mainframe.  I figured you’d be more likely to reproduce my examples on your own PC using Access than if I chose to connect to Microsoft Dynamics CRM and SAP.

CRM

To represent customers you might find in a CRM system, I’ve created an Access database with a simple Customers table with a schema that includes Id and Name:

I’ve filled the table with a short list of customers that we’ll use:

I’ve also created a Customers table in SQL Server to serve as the data destination for the CRM Access database.

ERP

To represent products you might find in an ERP system, I’ve created an Access database with a simple Products table with a schema that includes Id, Name, and Quantity:

I’ve filled the table with a short list of products that we’ll use:

I’ve also created a Products table in SQL Server to serve as the data destination for the ERP Access database.

Mainframe

To represent orders you might submit to a mainframe, I’ve created an Access database with a simple Orders table with a schema that includes Id, CustomerId, ProductId, and Quantity:

Since the mainframe is the destination after a mobile transaction is completed, the Orders table is currently empty.  As you might imagine, I’ve created an Orders table in SQL Server to serve as the data source for the Mainframe Access database.

Now it’s time to get started building the SSIS package to perform the data movement to and from our Mobile Middleware.  Since I have SQL Server 2012 installed, all I need to do is launch SQL Server Data Tools and create a new Integration Services Project.  I called my Solution “Adapters.”

In order to connect the 3 Access databases to SQL Server, you’ll need to create a few connections.  Go the bottom-center of the screen and right-click inside the Connection Managers tab area and select New Connection.  In the Add SSIS Connection Manager dialog, select OLEDB and click Add.

In the Configure OLE DB Connection Manager, click New.  In the Connection Manager dialog, select the SQL Server Native Client as the Provider, the name of your server, the appropriate Windows or SQL Server Authentication credentials, the name of the database, and click Test Connection to ensure everything is correct.  If everything checks out okay, click OK twice.

Now it’s time to create Access connections.  Right-click again to create a new OLEDB connection to the CRM database.  Since I’m using Office 2013, I used the Office 15 Access Database Engine OLE DB Provider and pointed to the path on my laptop where my database file resides.  You might use a different Access driver depending what you have installed on your PC.  As before, test your connection and then repeat this process to create Connection Managers for the ERP and Mainframe databases.

Before we move on to create the Data Flows, I want you to view the Properties for the SSIS Package.  Earlier in the article, I mentioned how important it was to secure every tier of any MEAP solution and integration is no different.  If you scroll down to the ProtectionLevel property, you’ll see a variety of ways to encrypt your Package.

One other thing I need you to configure has to do with the use of 32-bit Access drivers in an SSIS system that expects to operate in 64-bit when debugging.  In the Solution Explorer I want you to right-click on Adapters and select Properties.  In the Adapters Property Pages dialog you need to expand Configuration Properties and select Debugging.  You then need to set Run64BitRuntime to False to get things working properly if you happen to be using a 32-bit version of Access on your PC.

Data will move between SQL Server and our 3 Access databases through the use of Data Flow Tasks.  I now want you to drag a Data Flow Task from the SSIS Toolbox and drop it on the open workspace area beneath the Control Flow tab.  Rename it to CRM Data Flow Task.

Double-click on the new CRM Data Flow Task and you will be taken to the Data Flow tab.  In the SSIS Toolbox, expand Other Sources and drag OLE DB Source on to the open workspace.  Double-click on it to bring up the OLE DB Source Editor.  Select your CRM database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select Customers in the Name of the table or the view combo box and then click OK.  To complete the CRM connection to our Mobile Middleware, I want you to expand Other Destinations from the SSIS Toolbox and drag OLE DB Destination on to the open workspace.  Click on the original OLE DB Source and drag the blue arrow to make a connection with the OLE DB Destination.  Double-click on OLE DB Destination to bring up the OLE DB Destination Editor.  Select your SQL Server database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select [dbo].[Customers] in the Name of the table or the view combo box.  Click on Mappings to ensure you have the appropriate linkages between the Available Input Columns and the Available Destination Columns.

If everything looks good, click OK.  At this point you should Save and Build your solution just to verify there’s no errors.  Now it’s time to test this Data Flow.  Click the Play button or hit F5 on your keyboard to try it out.

If your OLE DB Source and OLE DB Destination have green circles with check signs inside, then there’s a good chance your data transferred without issues.  The connecting arrow should display 5 rows.  The final check is to go into SQL Server Management Studio and refresh the Customers table to verify that the 5 customers made it from Access to SQL Server.

The 5 customers made it over so now it’s time to complete the other 2 connections.

Go back to the Control Flow tab and drag a Data Flow Task from the SSIS Toolbox and drop it on the open workspace area.  Rename it to ERP Data Flow Task.  Double-click on the new ERP Data Flow Task and you will be taken to the Data Flow tab.  In the SSIS Toolbox, expand Other Sources and drag OLE DB Source on to the open workspace.  Double-click on it to bring up the OLE DB Source Editor.  Select your ERP database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select Products in the Name of the table or the view combo box and then click OK.  To complete the ERP connection to our Mobile Middleware, I want you to expand Other Destinations from the SSIS Toolbox and drag OLE DB Destination on to the open workspace.  Click on the original OLE DB Source and drag the blue arrow to make a connection with the OLE DB Destination.  Double-click on OLE DB Destination to bring up the OLE DB Destination Editor.  Select your SQL Server database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select [dbo].[Products] in the Name of the table or the view combo box.  Click on Mappings to ensure you have the appropriate linkages between the Available Input Columns and the Available Destination Columns.  If everything looks good, click OK.  Save and Build your solution just to verify there’s no errors and then test this Data Flow.  Look for the green circles with check signs and look inside SQL Server Management Studio to ensure the Products made it over.

The only connection left to make is Orders so return to the Control Flow tab and drag a Data Flow Task from the SSIS Toolbox and drop it on the open workspace area.  Rename it to Orders Data Flow Task.  Double-click on the new Orders Data Flow Task and you will be taken to the Data Flow tab.  In the SSIS Toolbox, expand Other Sources and drag OLE DB Source on to the open workspace.  Double-click on it to bring up the OLE DB Source Editor.  Select your SQL Server database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select [dbo].[Orders] in the Name of the table or the view combo box and then click OK.  To complete the Orders connection to our backend data source, I want you to expand Other Destinations from the SSIS Toolbox and drag OLE DB Destination on to the open workspace.  Click on the original OLE DB Source and drag the blue arrow to make a connection with the OLE DB Destination.  Double-click on OLE DB Destination to bring up the OLE DB Destination Editor.  Select your Mainframe Access database in the OLE DB connection manager combo box, select Table or View in the Data access mode combo box, and select Orders in the Name of the table or the view combo box.  Click on Mappings to ensure you have the appropriate linkages between the Available Input Columns and the Available Destination Columns.  If everything looks good, click OK.  Save and Build your solution just to verify there’s no errors and then test this Data Flow.  Keep in mind that this Data Flow goes in the reverse direction of the first two.  An Order placed on a smartphone or tablet will make its way to SQL Server via Web Services and wireless data networks.  This makes things a little hard to test so we’ll need to insert some dummy data to mock up this scenario.  Launch SQL Server Management Studio and manually insert data into the Orders table.  My Id is an Identity column and I inserted 1, 1, 1 in CustomerId, ProductId, and Quantity.  You can now test this inside Visual Studio and look for the green circles with check signs to verify that things worked.  Last but not least, take a look inside your Mainframe Access database to ensure the Orders made it over.

Congratulations on making it to the end of this exercise!

As I show you how to build Microsoft MEAP, my goal is to illustrate how easy this can be. After walking you through the exercise in this article, the takeaway in integrating your Mobile Middleware (SQL Server) with backend systems (Access databases) is that it’s a Simple, Visual, Drag and Drop operation.

I hope you now have a good understanding of Gartner’s Enterprise Application Integration (EAI) critical capability for MEAP.  I’m also hoping you see how easy it is to perform this EAI to multiple backend systems and data sources using the Microsoft technology your organization already owns.  In the next article I’ll show you how to create .NET Business Objects that model the aggregated data schema you’ve created in SQL Server.  Then I’ll show you how to expose that data to your mobile devices via the ASP.NET Web API.

-Rob

MEAP

Building Microsoft MEAP: Introduction

Posted by Rob Tiffany on
Building Microsoft MEAP: Introduction

After years of showing you how to integrate the principles of Gartner MEAP into your organization’s mobile strategy, it’s now time for me to show you how to roll up your sleeves and actually build the Microsoft Mobile Enterprise Application Platform.

I’ve travelled all over the world assisting Chief Information Officers in the development of their end-to-end mobile strategies.  Time and again, these CIOs inform me that pushing corporate data from their backend systems out to the wireless devices used by their employees is a top priority.  This makes perfect sense because of the productivity and efficiency gains an organization accrues when employees are allowed to work anytime from anywhere. That timely information from backend systems empower employees to make better decisions at the point of activity.  Of course, this works in two directions.  Employees capture data at that same point of activity and it flows from the field to those backend systems.  When data is allowed to flow bi-directionally in real-time, this speeds up the cadence of decision-making and gives the organization a competitive advantage.  We saw this years ago with mobile email.  When you combine wireless data networks, mobile devices, and a mechanism to seamless move critical data, you’ve created the most powerful weapon in business.

The Real-Time Enterprise.  This is where a Mobile Enterprise Application Platform comes into play.

Just in case you’re unfamiliar with MEAP, it represents software and services that allow IT organizations to extend corporate apps and data out to mobile employees and business partners.  This is most often accomplished via reusable mobile middleware components capable of supporting multiple mobile apps, multiple mobile operating systems, and multiple backend systems to maximize your ROI vs. tactical point solutions.  Think of this as a server façade in front of your backend systems and databases.  You might consider using a MEAP solution if your organization doesn’t have a mature SOA infrastructure or if your backend systems aren’t vertically-integrated to provide mobile client apps and connectivity.  Gartner has a technology checklist of critical capabilities that a given MEAP solution must match up with in order to be considered.  Since this series of articles is all about building the Microsoft MEAP solution, I’ll provide you with some guidance as to how Microsoft aligns with each of the critical capabilities:

  • Integrated Development  Environment:
    • Gartner Definition: Dedicated environment or plug-in for composing backend server & client side logic, including UI.
    • Microsoft Offering: Visual Studio
    • Value Proposition:  As the world’s most widely-used commercial IDE, you’re more likely to find plenty of proficient developers than with any other MEAP offering.  Additionally, developers are more productive since they don’t have to use different or specialized tools to target laptops, tablets, smartphones, servers, or the cloud.  Competing MEAP vendors have unfamiliar native and hybrid SDKs or 4GLs while Microsoft has millions of seasoned developers.
  • Application Client Runtime: 
    • Gartner Definition: The client runtime logic for the application, either in native format or packaged within a container.
    • Microsoft Offering:  WinRT, .NET / Silverlight for thick clients and Internet Explorer for thin clients.
    • Value Proposition:  WinRT is supported across Windows 8, Windows RT, and Windows Phone 8.  .NET/Silverlight are supported across Windows, Windows Phone 7.5, Macintosh, Windows Mobile, Windows Embedded, and Symbian S60.  Internet Explorer 10 brings hardware-accelerated HTML5 and JIT-compiled JavaScript to Windows 7/8, Windows RT, and Windows Phone 8.  The only thick client runtime that’s competitive with Microsoft offerings is Java.  The thin client competition takes the form of fragmented WebKit-based browsers.
  • Enterprise Application Integration Tools:
    • Gartner Definition:  Tools for integration of mobile server with back end systems, both bespoke & purchased apps or application suites.
    • Microsoft Offering: Visual Studio SQL Server Data Tools.
    • Value Proposition:  Developers visually compose connections, actions, events and data movement rather than writing separate sets of integration code.  Adapters provide consistent connectivity to dozens of backend systems and data sources.  Microsoft is providing unrivaled, easy to use, drag and drop tools to connect ETL adapters with backend systems and databases.
  • Packaged Mobile Apps or Components:
    • Gartner Definition:  Self standing mobile applications or components.
    • Microsoft Offering:  Cross-platform offerings include Office, Internet Explorer, Bing, Lync, Skype, Tag, .NET Portable Class Libraries, and Exchange ActiveSync (EAS).
    • Value Proposition:  Same business productivity apps available across Windows, Windows Phone, the Web and the Mac.  Corporate and consumer communications are available across all platforms.  Portable class libraries allow developers to reuse code across phones, tablets, laptops, and the web.  The EAS client found in every major smartphone and tablet gives more people around the world access to Microsoft email, calendaring, contacts and device management than any other technology from any other competitor.
  • Multichannel Tools or Servers:
    • Gartner Definition:  Tools that allow for “write once, run anywhere” thick or rich mobile clients, cross compilers or environments or platforms that allow business logic to be supported across thin, thick, and rich mobile architectures.
    • Microsoft Offering:  Tools like Visual Studio allow development of cross-platform thick and thin apps.  Multi-channel transports like HTTP/SOAP/REST/EAS/XML/JSON, OData, and the Sync Framework support communication with any mobile client. Server technologies like Unified Access Gateway (UAG) and Azure securely publish services to any device.
    • Value Proposition:  Microsoft provides more tools, transports, and servers to securely enable any device than all competitors combined.  There’s a strong chance that your organization already owns these technologies.
  • Management Tools:
    • Gartner Definition:  Tools to provision, support, debug, update or decommission mobile apps.
    • Microsoft Offering:  System Center Configuration Manager (SCCM), Windows Intune, Exchange Server, Windows Store.
    • Value Proposition:  Exchange Server is a great BYOD solution that provides policy enforcement to every device that support EAS.  SCCM 2012 and Windows Intune support software distribution + device management of Windows, Windows Mobile, Windows Phone, Symbian, iOS, and Android devices.  Yes, this is the same SCCM product that has been around since the early 1990’s and already manages hundreds of millions of desktops, servers, and laptops with its enterprise-proven capabilities.  If you haven’t bought an MDM package yet, don’t panic.  The Exchange Server that most of the world’s organizations use for email will provide you with a solid baseline of policy enforcement and security to keep you covered.
  • Security:
    • Gartner Definition:  Secure enterprise data on device, while moving through wired & wireless networks, peripherals, & with backend systems & integration packages.
    • Microsoft Offering:  Policy enforced login, AES encryption for data at rest on the device and SSL for data in transit.  Apps tested, digitally signed & securely delivered via Windows Store or privately within enterprise.  Data at rest in client and middleware databases is encrypted.  SSIS integration packages are encrypted and signed.  Perimeter protection is accomplished in the DMZ via Unified Access Gateway with deep packet inspection.  Data leakage is mitigated via Microsoft’s Information Rights Management Server to lock down and/or expire emails and documents.
    • Value Proposition:  Microsoft technologies provide end-to-end security at every tier.  All bases are covered including data at rest, data in transit, perimeter protection, and data leakage prevention.
  • Hosting:
    • Gartner Definition:  The ability to host all development, provisioning, management functions, and optionally corporate data in the cloud.
    • Microsoft Offering:  Windows Intune, Windows Store, Windows Azure, Office 365, Team Foundation Service.
    • Value Proposition:  All Windows 8 and Windows Phone 8 apps are provisioned from the cloud.  Seamless location-based services and push notifications are provided to apps and live tiles from the cloud.  A wide range of mobile clients can access Azure services and storage via interoperable protocols like SOAP, REST, OData, XML, and JSON.  Azure Mobile Services provides rapid cloud development, storage, authentication, and push notifications across multiple mobile platforms.  No other MEAP vendor even comes close to providing this deep level of PAAS, SAAS, and IAAS capabilities as Microsoft’s cloud.

A visual representation of many of these critical capabilities is shown below:MEAP

Looking at the picture above, you see a thin line going across horizontally.  Everything below the line represents client capabilities whereas everything above the line lives in your data center or in the cloud.  On the left side you see both the client and server elements of device management and your integrated development environment illustrated in blue.  Staying below the line you see green blocks that represent thick and thin client runtimes as well as offline storage.  You might be wondering why I included native and web-based offline storage when they aren’t listed as critical capabilities.  In a world where there’s no such thing as a ubiquitous wireless network, you must be able to pre-fetch and store data offline so that your employees can keep working in the absence of connectivity.  Moving above the line, you see that all clients connect to your server infrastructure via a multichannel access gateway.  This is an edge server that provides reverse-proxy and/or VPN services.  Above that you have mobile middleware components that handle data integration, web communication, auth/security, caching, and potential data aggregation.

Now let’s move away from the generic view of a MEAP infrastructure and zoom into why organizations think this stuff is important.  It’s the tidal wave of devices!MEAP2

The use of corporate-liable wireless devices combined with the BYOD phenomenon has made “Mobile” the largest megatrend in the world of enterprise technology.  It’s already the largest megatrend with consumers.  Nothing else even comes close.  Now while I’d prefer that you use Windows Phones, tablets, and laptops, I want to make it easy for you to use your existing Microsoft server stack to support any mobile endpoint.  I realize that there is one large, and many small MEAP vendors in the marketplace.  In fact, the largest of those vendors is making a concerted effort to corner the enterprise mobile market via a number of MDM and MEAP acquisitions.  You should realize that there’s limited value in dropping potentially millions of dollars on unfamiliar server infrastructures and development technologies that aren’t nearly as easy to configure and work with as it says on the sales brochure or in the marketing demo.

Pragmatically speaking, you should use the Microsoft server stack you already own to provide your organization’s MEAP solution.

In the forthcoming articles I’ll be writing, I’ll bring your CIO’s top priority to life:

  • I’ll show you how to configure your own Mobile Middleware that integrates multiple data sources and acts as a server façade.  Microsoft SQL Server forms the core of your system where SQL Server Integration Services (SSIS) adapters connect to dozens of data sources and backend packages.  SQL Server aggregates, caches, and secures the composite data retrieved from those systems to boost client performance while reducing the load on backend systems.
  • I’ll show you how to easily expose this composite data via Web Services in a format consumable by any device.  Business entities built using .NET will model the schema created by the aggregated data.  Internet Information Services (IIS) and the ASP.NET Web API will expose those business entities to mobile clients.
  • I’ll describe how you can horizontally scale out your data tier in your Private Cloud while also providing geo-replication to additional data centers using SQL Server Transactional Replication.  This will be combined with the scaling-out of your web service tier via load-balancing.  If that’s not enough, you can take this high-level of scalability and performance to the next level using Microsoft AppFabric Caching to provide an in-memory distributed cache.
  • A Multi-channel Access Gateway is used to securely publish this data out to the Internet.  I’ll discuss how this task can be carried out by Microsoft Forefront Unified Access Gateway via the same mechanism you’re already using to provide email from your Exchange Servers to mobile devices.
  • Last but not least, I’ll show you how to consume these web services from a Windows 8 tablet app so your employees can work with data and perform transactions offline.  All along the way, you’ll see how I use a single IDE, Visual Studio, to bring every tier of your solution to life whether it be server logic, integration with backend systems, client logic, or UX.

Remember, the infrastructure folks in your IT department already know how to build, configure, and maintain the servers needed to build Microsoft MEAP.  At the same time, your developers get to leverage the .NET and web service skills they’ve been perfecting for the last decade.  The top priority for CIOs is to extend their organization’s critical data from their backend systems out to mobile devices.  I’m going to show you how to build it using the skills your IT folks already have, and the software and servers you already own, with support from the world’s largest software company.

– 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″]

Mobile

Human Extract, Transform, and Load

Posted by Rob Tiffany on
Human Extract, Transform, and Load

HETL (Human Extract, Transform, and Load)

Definition:  When an employee in the field captures data with a pen or pencil & paper, drives back to the office at the end of the workday, and manually transcribes the data into a back-end computer system.

You’re probably thinking this is an antiquated phenomenon that disappeared decades ago with the advent of mobile devices and wireless data networks.  I’m here to tell you that this productivity killer is alive and well in 2012!  Large and small companies all over the world are still wasting time, fuel, money, and accuracy of data.  They’re also misappropriating valuable resources by turning field personnel into data-entry clerks like you used to see in the 1970’s and 80’s.

Luckily, this widespread problem is easily solvable:

  1. Build a native or HTML5 mobile app that’s capable of retrieving data, capturing new data from the employee, saving the data offline, and sending that data back to HQ.
  2. Give employees working in the field a wireless mobile phone/tablet capable of running the app you’ve built.
  3. If the device has 2G/3G/4G capabilities, the data captured in the field can be sent to HQ as soon the employee is in a coverage area.
  4. If the device only has Wi-Fi, the employee can send that data on it’s way by driving to the nearest hotspot or uploading it from home.

That’s it!  Don’t make it harder than it has to be.  Yes, data at rest and data in transit has to be secured but that’s easy to do these days.  Please do your part to but an end to Human Extract, Transform, and Load and convert your organization into a real-time enterprise.

– 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″]

MEAP

Consumerization of IT Collides with MEAP: Android > Cloud

Posted by Rob Tiffany on
Consumerization of IT Collides with MEAP: Android > Cloud

In my ‘Consumerization of IT Collides with MEAP’ article last week, I described how to connect Android smartphones and tablets to Microsoft’s On-Premise infrastructure.

In this week’s scenario, I’ll use the picture below to illustrate how Android utilizes many of Gartner’s Mobile Enterprise Application Platform Critical Capabilities to connect to Microsoft’s Cloud services in Azure:

image

As you can see from the picture above:

  1. For the Management Tools Critical Capability, there is no Cloud-based device management solution, policy-enforcement, or software distribution solution from Microsoft for Android. As I mentioned in last week’s post, consumer software distribution comes from the Android Market and the enterprise equivalent is facilitated via internal web servers and user-clickable URLs. Since Android is a wide-open system, competing markets and app stores are on the rise from Amazon and others.
  2. For both the Client and Server Integrated Development Environment (IDE) and Multichannel Tool Critical Capability, Android uses Visual Studio. Endpoint development consists of HTML5, ECMAScript 5, and CSS3 delivered by ASP.NET via Web Roles. WCF REST + JSON Web services can also be created and consumed via Ajax calls from the browser. On the Cloud side of things, the Windows Azure SDK plugs into Visual Studio and provides Android developers with everything they need to build Cloud applications. It includes a Cloud emulator to simulate all aspects of Windows Azure and AppFabric on their development computer. In scenarios where native development is required by the customers, the Windows Azure Toolkit for Android can be used to allow Java via Eclipse to securely communicate with the Microsoft cloud.
  3. For the cross-platform Application Client Runtime Critical Capability, Android uses the WebKit browser called Chrome to provide HTML5 + CSS3 + ECMAScript5 capabilities. Offline storage is important to keep potentially disconnected Android smartphones and tablets working and this is facilitated by Web Storage which is accessible via JavaScript.
  4. For the Security Critical Capability, Android 3.0 and higher provides hardware encryption based on the user’s device passcode for data-at-rest. Data-in-transit is secured via SSL and VPN. LDAP API support allows it to access corporate directory services. Auth in the Microsoft cloud is handled via the Windows Azure AppFabric Access Control Service (ACS).
  5. For the Enterprise Application Integration Tools Critical Capability, Android can reach out to servers directly via Web Services or indirectly through the Cloud via the Windows Azure AppFabric Service Bus to connect to other enterprise packages.
  6. The Multichannel Server Critical Capability to support any open protocol is handled automatically by Windows Azure. Cross-Platform wire protocols riding on top of HTTP are exposed by Windows Communication Foundation (WCF) and include SOAP, REST and Atompub. Cross-Platform data serialization is also provided by WCF including XML, JSON, and OData. These Multichannel capabilities support thick clients making web service calls as well as thin web clients making Ajax calls. Distributed caching to dramatically boost the performance of any client is provided by Windows Azure AppFabric Caching.
  7. As you might imagine, the Hosting Critical Capability is handled by Windows Azure. Beyond providing the most complete solution of any Cloud provider, Windows Azure Connect provides an IPSec-protected connection with your On-Premises network and SQL Azure Data Sync can be used to move data between SQL Server and SQL Azure. This gives you the Hybrid Cloud solution you might be looking for.
  8. For the Packaged Mobile Apps or Components Critical Capability, Android runs cross-platform mobile apps including Skype, Bing, MSN, Tag, Hotmail, and of course the critical ActiveSync component that makes push emails, contacts, calendars, and device management policies possible.

Samsung-Galaxy-Nexus-UK

While Android 3.0 and higher meets many of Gartner’s Critical Capabilities, it doesn’t fare very well when it comes to cloud-based device management.  While other mobile device platforms also come up short in this department, I’m sure this will change in the coming year.  The tidal wave of CoIT means that device management in the future will look very different from how it did 5 years ago.  Expect a clear separation between corporate apps/data and personal apps/data to be managed.

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″]

 

MEAP

Consumerization of IT Collides with MEAP: iPhone + iPad > Cloud

Posted by Rob Tiffany on
Consumerization of IT Collides with MEAP: iPhone + iPad > Cloud

In my ‘Consumerization of IT Collides with MEAP’ article last week, I described how to connect iPhones and iPads to Microsoft’s On-Premise infrastructure.

In this week’s scenario, I’ll use the picture below to illustrate how iOS utilizes many of Gartner’s Mobile Enterprise Application Platform Critical Capabilities to connect to Microsoft’s Cloud services in Azure:

image

As you can see from the picture above:

  1. For the Management Tools Critical Capability, there is no Cloud-based device management solution, policy-enforcement, or software distribution solution from Microsoft for iOS.  As I mentioned in last week’s post, consumer software distribution comes from the App Store and the enterprise equivalent is facilitated via internal web servers and user-clickable URLs.
  2. For both the Client and Server Integrated Development Environment (IDE) and Multichannel Tool Critical Capability, iOS uses Visual Studio.  Endpoint development consists of HTML5, ECMAScript 5, and CSS3 delivered by ASP.NET via Web Roles.  WCF REST + JSON Web services can also be created and consumed via Ajax calls from the browser.  On the Cloud side of things, the Windows Azure SDK plugs into Visual Studio and provides iOS developers with everything they need to build Cloud applications. It includes a Cloud emulator to simulate all aspects of Windows Azure and AppFabric on their development computer. In scenarios where native development is required by the customers, the Windows Azure Toolkit for iOS can be used to allow XCode/Objective-C to securely communicate with the Microsoft cloud.
  3. For the cross-platform Application Client Runtime Critical Capability, iOS uses the WebKit browser called Safari to provide HTML5 + CSS3 + ECMAScript5 capabilities. Offline storage is important to keep potentially disconnected iPhones and iPads working and this is facilitated by Web Storage which is accessible via JavaScript.
  4. For the Security Critical Capability, iOS provides AES 256 hardware encryption as well as Data Protection based on the user’s device passcode for data-at-rest. Data-in-transit is secured via SSL, VPN, and 802.1X.  Auth in the Microsoft cloud is handled via the Windows Azure AppFabric Access Control Service (ACS).
  5. For the Enterprise Application Integration Tools Critical Capability, iOS can reach out to servers directly via Web Services or indirectly through the Cloud via the Windows Azure AppFabric Service Bus to connect to other enterprise packages.
  6. The Multichannel Server Critical Capability to support any open protocol is handled automatically by Windows Azure. Cross-Platform wire protocols riding on top of HTTP are exposed by Windows Communication Foundation (WCF) and include SOAP, REST and Atompub. Cross-Platform data serialization is also provided by WCF including XML, JSON, and OData. These Multichannel capabilities support thick clients making web service calls as well as thin web clients making Ajax calls. Distributed caching to dramatically boost the performance of any client is provided by Windows Azure AppFabric Caching.
  7. As you might imagine, the Hosting Critical Capability is handled by Windows Azure. Beyond providing the most complete solution of any Cloud provider, Windows Azure Connect provides an IPSec-protected connection with your On-Premises network and SQL Azure Data Sync can be used to move data between SQL Server and SQL Azure. This gives you the Hybrid Cloud solution you might be looking for.
  8. For the Packaged Mobile Apps or Components Critical Capability, iOS runs cross-platform mobile apps including OneNote, Bing, Tag, and of course the critical ActiveSync component that makes push emails, contacts, calendars, and device management policies possible.

While iOS meets many of Gartner’s Critical Capabilities, it doesn’t fare very well when it comes to cloud-based device management.  That being said, this is not a strong area for many mobile endpoints at this stage of the game.

Next week, I’ll cover how Android connects to an On-Premises Microsoft infrastructure.

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″]