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 SimpleVisual, 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

Consumerization of IT Collides with MEAP: Windows > Cloud

In my Consumerization of IT Collides with MEAP article last week, I described how to connect a Windows 7 device to Microsoft’s On-Premises servers.

Whether you’re talking about a Windows 7 tablet or laptop, I showed that you can follow the Garter MEAP Critical Capabilities to integrate with our stack in a consistent manner.  Remember, the ability to support multiple mobile apps across multiple mobile platforms, using the same software stack is a key tenant to MEAP.  It’s all about avoiding point solutions.

If you need a refresher on the Gartner MEAP Critical Capabilities, check out: http://robtiffany.com/meap/consumerization-of-it-collides-with-meap-windows-on-premises

In this week’s scenario, I’ll use the picture below to illustrate how Mobile versions of Windows 7 in the form of slates, laptops, and tablets utilize some or all of Gartner’s Critical Capabilities to connect to Microsoft’s Cloud infrastructure:

image

As you can see from the picture above:

  1. For the Management Tools Critical Capability, Windows 7 uses Windows Intune for Cloud-based device management and software distribution.
  2. For both the Client and Server Integrated Development Environment (IDE) and Multichannel Tool Critical Capability, Windows 7 uses Visual Studio. The Windows Azure SDK plugs into Visual Studio and provides developers with everything they need to build Cloud applications.  It even includes a Cloud emulator to simulate all aspects of Windows Azure on their development computer.
  3. For the cross-platform Application Client Runtime Critical Capability, Windows 7 uses .NET (Silverlight/WPF/WinForms) for thick clients. For thin clients, it uses Internet Explorer 9 to provide HTML5 + CSS3 + ECMAScript5 capabilities. Offline storage is important to keep potentially disconnected mobile clients working and this is facilitated by SQL Server Compact + Isolated Storage for thick clients and Web Storage for thin clients.
  4. For the Security Critical Capability, Windows 7 provides security for data at rest via Bitlocker, data in transit via SSL, & Authorization/Authentication via the Windows Azure AppFabric Access Control Serivce (ACS).
  5. For the Enterprise Application Integration Tools Critical Capability, Windows 7 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. Crosss-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. Cross-Platform data synchronization if provided by the Sync Framework. 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 knocked out of the park with 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, Windows 7 runs cross-platform mobile apps include Office/Lync/IE/Outlook/Bing.

As you can see from this and last week’s article, Windows 7 meets all of Gartner’s Critical Capabilities whether it’s connecting to Microsoft’s On-Premises or Cloud servers and infrastructure.  They great takeaway from the picture above, is Windows 7 only needs to know how to integrate its apps with WCF in the exact same way as is does in the On-Premises scenario.  Windows developers can focus on Windows without having to concern themselves with the various options provided by Windows Azure.  Cloud developers just need to provide a WCF interface to the mobile clients.

When an employee walks in the door with a wireless Windows 7 Slate device, you can rest assured that you can make them productive via Windows Azure without sacrificing any of the Gartner Critical Capabilities.

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

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://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″]

Consumerization of IT Collides with MEAP: Windows > On-Premises

The Consumerization of IT is an unstoppable force where employees are bringing every kind of mobile device imaginable into the office expecting to be productive.

Over the course of the next 20 articles, I’ll describe how IT professionals can use the principles of Gartner MEAP to connect any type of mobile device to Microsoft’s On-Premises and Cloud servers.

Gartner specifies the following Critical Capabilities that must be addressed in order for a given product or stack of products to be considered a Mobile Enterprise Application Platform (MEAP):

  • Integrated Development Environment

    A dedicated environment or plug-in for composing backend server and client side logic, including UI and UX

  • Application Client Runtime

    The client runtime logic for the application, either in native format or packaged within a container.

  • Enterprise Application Integration Tools

    Tools for integration of mobile server with back end systems, both bespoke and purchased apps or application suites.

  • Packaged Mobile Apps or Components

    Self standing mobile applications or components.

  • Multichannel Tools or Servers

    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.

  • Management Tools

    Tools for provisioning, supporting, debugging, updating or decommissioning mobile applications.

  • Security 

    Tools for ensuring the security and privacy of enterprise data on board the device, while transiting through wired or wireless networks, through peripherals, and with backend systems and integration packages.

  • Hosting

    The ability to host all development, provisioning, management functions, and optionally corporate data.

    In this first scenario, I’ll use the picture below to illustrate how Mobile versions of Windows 7 in the form of slates, laptops, and tablets utilize some or all of Gartner’s Critical Capabilities to connect to an On-Premise Microsoft infrastructure:

image

As you can see from the picture above, Windows 7:

  1. For the Management Tools Critical Capability, Windows 7 uses System Center Configuration Manager (SCCM) 2007 for on-premises device management and software distribution.
  2. For both the Client and Server Integrated Development Environment (IDE) and Multichannel Tool Critical Capability, Windows 7 uses Visual Studio.
  3. For the cross-platform Application Client Runtime Critical Capability, Windows 7 uses .NET (Silverlight/WPF/WinForms) for thick clients.  For thin clients, it uses Internet Explorer 9 to provide HTML5 + CSS3 + ECMAScript5 capabilities.  Offline storage is important to keep potentially disconnected mobile clients working and this is facilitated by SQL Server Compact + Isolated Storage for thick clients and Web Storage for thin clients.
  4. For the Security Critical Capability, Windows 7 provides security for data at rest via Bitlocker, data in transit via SSL+VPN, data in the database via RSA/AES, & Authorization/Authentication via Active Directory.
  5. For the Enterprise Application Integration Tools Critical Capability, Windows 7 can reach out to servers directly via Web Services or indirectly via SQL Server or BizTalk using SSIS/Adapters/Sync to connect to other enterprise packages.
  6. The Multichannel Server Critical Capability to support any open protocol directly, via Reverse Proxy, or VPN is facilitated by ISA/TMG/UAG/IIS.  Crosss-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. Cross-Platform data synchronization if provided by the Sync Framework.  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 Server AppFabric Caching.
  7. While the Hosting Critical Capability may not be as relevant in an on-premises scenario, Windows Azure Connect provide an IPSec-protected connection to the Cloud and SQL Azure Data Sync can be used to move data between SQL Server and SQL Azure.
  8. For the Packaged Mobile Apps or Components Critical Capability, Windows 7 runs cross-platform mobile apps include Office/Lync/IE/Outlook/Bing.

It should come as no surprise that Windows 7 has a compelling and complete MEAP story to address the issues surrounding the Consumerization of IT (CoIT) when an employee walks in the door with a wireless Windows 7 Slate device.

Next week, I’ll cover how Windows 7 connects to the Cloud.

Rob

Sharing my knowledge and helping others never stops, so connect with me on my blog at http://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″]