Mobile Apps Must Work Offline Because Wireless Connectivity isn’t Ubiquitous

Albuquerque

Intermittent wireless connectivity requires mobile apps to follow sync patterns using pre-fetched data via APIs and offline local storage.

If the network isn’t available, it’s pretty hard for your native app to call web APIs or for your web app to load new web pages. On the Ethernet connected desktops of the past, developers didn’t concern themselves with this issue. In our wireless connected mobile society, ignoring this issue leads to a poor user experience. Most of the time, devices are connected via 2G/3G/4G wireless data networks whose reliability is driven by cell tower density, the number of devices connected to a given tower, wireless frequencies, bandwidth and the number of buildings in the area.

Rather than assuming everything will “just work,” developers of successful apps assume “nothing works.” For starters, mobile apps must take advantage of platform APIs that detect the existence of network connectivity. Once this is established, an app must not only download the data it needs at that given moment but enough data to get through the day. Depending on the amount and complexity of this data, it should be stored locally on the device in a mobile database or as serialized files. From then on, the app should only use the local data to perform its tasks rather than reaching out to servers. Changes made by the user to this local data should be tracked so that only deltas are sent to backend systems when it’s time to upload. Extensive error handling and “sync retries” are needed to ensure reliability. Employees can work in airplane mode or when roaming internationally without using data.

Increase revenue and improve user productivity by using sync to create apps that keep working whether the Internet is available or not. App downtime on a sales call in front of a customer is not an option. Has your company made the move to apps that work offline?

Learn how to digitally transform your company in my newest book, “Mobile Strategies for Business: 50 Actionable Insights to Digitally Transform your Business.”

Book Cover

Click to purchase a copy of my book today and start transforming your business!

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

Looking to the Future of Cross-Platform Mobile Data Sync with SQL Server

Zumero

If you’ve used Merge Replication to sync SQL Server Compact data on mobile devices with SQL Server in the past and you’re looking for a cross-platform solution to take you into the future, take a look at Zumero.

As many of you know, I spent most of the first decade of the 21st century building massive-scale mobile enterprise solutions for the world’s largest companies. The backbone of many of those architectures was based on the Merge Replication technology built into SQL Server that allowed mobile devices running the embedded SQL Server Compact database to sync data and take it offline for use with mobile apps. This was a great solution that took care of all the bi-directional, mobile-to-server data movement, conflict resolution and filtering without writing any code allowing development teams to focus on their apps. Unfortunately, the journey for this Microsoft technology arrived at the end of the road with SQL Server 2012 and SQL Server Compact 3.5 SP2. It’s no longer supported on the more recent versions of those products.

For those of you who need to keep synchronizing mobile data with SQL Server, the folks over at Zumero have a solution you should investigate. They smartly took a dependency on SQLite as the mobile database since it already runs on iOS, Android and Windows. The Zumero server runs on an Internet Information Server (IIS) to broker communications between devices and your SQL Server database. This architecture should look familiar to those of you who have built mobile Merge Replication infrastructures in the past.

Zumero Architecture

They’ve even gone so far to provide you with a migration document that will help move you from SQL Server Compact and Merge Replication to their Zumero offering. If this solution matches the scenario you’re targeting, I would encourage you to perform your own due diligence and see if Zumero meets your needs.

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

Empower your Demanding Mobile Line of Business Apps with SQLite and Offline Data Sync on Windows

Presenting at TechEd in Houston, watch my session: “Empower your Demanding Mobile Line of Business Apps with SQLite and Offline Data Sync on Window.”

Most mobile apps require the ability to store data locally to deal with the realities of a disconnected world where ubiquitous wireless networks are non-existent.  While many consumer apps get by with saving light amounts of information as small files, the data requirements of mobile line-of-business apps is significantly greater.  With Windows 8.1 and MSOpenTech’s Portable Class Library for SQLite, .NET developers can build structured data storage into their apps.

In this video, I walk you through creating local databases and tables and show you how to work with offline data.  I also demonstrate a new data sync capability in Microsoft Azure Mobile Services which uses SQLite for local data storage and change tracking.  It even detects data conflicts during a sync so your can resolve them either programmatically or interactively.  There’s no faster way to build robust mobile apps to meet your most demanding enterprise needs.  If you know me, you know that I’ve been in the mobile data sync business for well over a decade and I’ve designed and developed many of the world’s largest architectures.

Where you may have used SQL Compact in the past, now you can use SQLite.  Likewise, where you’ve used the Sync Framework or Merge Replication before, take a look at Azure Mobile Services today.  It might be time to move your mobile enterprise application platform to an mBaaS architecture.  Either way, you still sync data with SQL Server.  I’m really excited about the next chapter in this journey where SQLite runs across all mobile platforms and synchronizes with cloud and on-premise data stores via Azure Mobile Services.

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

Keeping Windows 8 Tablets in Sync with SQL Server 2012

Windows 8 Book Front

I’m pleased to announce that my newest book, “Keeping Windows 8 Tablets in Sync with SQL Server 2012,” is now available for sale.

Spending a decade travelling the globe to help the world’s largest companies design and build mobile solutions had taught me a few things.  Large organizations are not interested in constantly running on the new technology hamster wheel.  They prefer to leverage existing investments, skills, and technologies rather than always chasing the next big thing.  Don’t believe me?  Take mobile and the cloud for example:

  • In 2003 I was building Pocket PC solutions for large companies that wirelessly connected apps on those devices to SAP.  I assumed mobile was going mainstream that year.  I was wrong.  I was early.  Mobile apps wouldn’t explode until the end of the decade with the iPhone 3G.
  • In 2004, my partner Darren Flatt and I launched the first cloud-based mobile device management (MDM) company to facilitate software distribution and policy enforcement on early smartphones and handhelds.  Early again.  MDM didn’t get big until the end of the decade.
  • At PDC in 2008, my company launched our cloud offering called Azure.  We skipped directly to the developer Nirvana called Platform as a Service (PaaS).  I spent a few years doing nothing but speaking and writing about Windows Phones communicating with Web Roles.  Turns outs companies wanted to take smaller steps to the cloud by uploading their existing servers as VMs.

Being early over and over again taught me how the real world of business operates outside of Redmond and Silicon Valley.  Businesses need to make money doing what they do best.  Where appropriate, they will use technology to help them improve their processes and give them a competitive advantage.  So let’s cut to the chase and talk about why I wrote my new book:

  • Tablets and Smartphones are taking over the world of business and outselling laptops and desktops.  This is a well-known fact and not speculation on my part.
  • There are 1.3 billion Windows laptops, tablets, and desktops being used all over the world.  Windows 7 is in first place with Windows XP in second.
  • Companies run their businesses on Microsoft Office combined with tens of millions of Win32 apps they created internally over the last 2 decades.  Intranet-based web apps also became a huge force starting in the late 90s.
  • Tools like Visual Basic, Access, PowerBuilder, Java, and Delphi made it easy to rapidly build those Win32 line of business apps in the 90s and helped ensure the success of Windows in the enterprise.
  • Many of those developers moved to VB and C# in the 2000s to build .NET Windows Forms (WinForms) apps that leveraged their existing Visual Basic skills from the 90s.
  • Some businesses built Service Oriented Architecture (SOA) infrastructures of Web Services based on SOAP and XML over the last decade in order to connect mobile devices to their servers.  Most business did not, and instead opted for out-of-the-box solutions that didn’t require them to write a lot of code so they could get to market faster.
  • While the “white collar” enterprise recently started building business apps for the iPhone and iPad, the “blue collar” enterprise has been building WinForms apps for rugged Windows Mobile devices using the .NET Compact Framework and a mobile database called SQL Server Compact for over a decade.
  • Most businesses run servers in their own data centers.  Many of them are using virtualization technologies like Hyper-V and VMware to help them create a private cloud.
  • Of the businesses that have dipped their collective toes in the public cloud for internal apps, most of them are following the Infrastructure as a Service (IaaS) model where they upload their own servers in a VM.  Just look at the success of Amazon and the interest in Azure Infrastructure Services.

So the goal of my new book is to help businesses transition to the tablet era in a way that respects their existing investments, skills, technologies, enterprise security requirements, and appetite for risk.

Windows 8 Book Front

Since I’ve been involved in countless mobile projects where companies used the Microsoft data sync technologies already baked into SQL Server and SQL Server Compact, I decided to illustrate how to virtualize this sync infrastructure with Hyper-V.  With an eye towards existing trends that are widely embraced, this gives businesses the flexibility to use this proven technology in a private, public, or hybrid cloud.  Companies authenticate their employees against the same Active Directory they’ve used for over a decade.  I’m deadly serious about security and you’ll be glad to know the technology in this book handles it at every tier of your solution with Domain credentials plus encrypted data-at-rest and data-in-transit.  You also have the option of synchronizing mobile data with any edition of SQL Server 2005, 2008 or 2012 using Microsoft sync technologies that takes care of all data movement plumbing.  Your development team avoids writing thousands of lines of code to create web services, sync logic, change tracking, error handling, and retry logic.  With Microsoft lowering risk to your project by taking care of the server backend, security, and data sync technologies, your team can focus on building the best possible Windows 8 tablet app for the enterprise.

Speaking of tablet app development, it’s important to show you a path that doesn’t force you to learn all-new tools or programming languages, frameworks, or paradigms.  As a developer, you get to keep using Visual Studio along with the Desktop WinForms skills you’ve mastered over the last decade.  Better still, you can accomplish everything using the free version of Visual Studio 2012.  While you might be thinking Windows 8 tablet solutions must be created via Windows Store apps, this is not the case.  Instead, I show you how to apply Modern UI principles to Desktop WinForms apps that are full-screen and touch-first.  Concepts like content over chrome, use of typography, and UI elements with large hit targets are all covered in detail.  I also respect your investment in Windows 7 laptops and tablets by ensuring your touch apps are backwards compatible and keyboard + mouse/trackpad friendly.

Windows 8 Book Back

If you’re looking to build a new Windows 8 tablet app using what you have and what you know, this book is for you.  If you’re looking to port an existing Windows XP or Windows Mobile WinForm app to a Windows 8 tablet, this book empowers you with the skills to make your porting effort a successful one.

The takeaway is you don’t have to scrap your existing investments to participate in the tablet revolution.  I purposely made the book low-cost, hands-on, short, and to-the-point so you can rapidly build mobile solutions for Windows 8 tablets instead of wasting your time with theory.  Click here to take “Keeping Windows 8 Tablets in Sync with SQL Server 2012” for a spin so you can start building mobile apps for the world’s first and only enterprise-class tablet today.

Stay in Sync!

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

Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode III

SQL Server Compact

Back in my first article, I showed you where to find Microsoft’s latest updates to the SQLCE and RDA technologies so you can begin synchronizing data with the new SQL Server 2012 (Denali) database.

Just imagine, you now have mobile sync components that give you the flexibility to to work with SQL Server 7, 2000, 2005, 2008, and 2012 from your devices.  I’d say you have both your legacy and state-of-the-art bases covered.  In the second article you built both the server and client databases so now you’re ready to sync some data.

As I may have mentioned before, Remote Data Access (RDA) is the fastest and easiest way for your mobile devices to synchronize data with SQL Server – and then take it offline in SQL Server Compact.  It works on the simple premise of pulling and pushing data to and from SQL Server via the Server Agent which is running on the middle-tier IIS application server.  The Server Agent is able to communicate with SQL Server via an OLEDB connection string which is passed to it from your device application code:

string rdaConnection = @”Provider=SQLOLEDB;” +

“Data Source=Machinename\\SQLExpress;” +

“Initial Catalog = ContosoBottling;” +

“User Id = sa;” +

“Password = P@ssw0rd;”;

You’ll use this connection string over and over whether your pulling or pushing data so keep it handy.  Data is retrieved on a table-by-table basis using the Pull method of the SqlCeRemoteDataAccess object.  You would put the example code below in a method to retrieve a list of Distribution Centers from SQL Server:

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess())

{

rda.InternetUrl = “http://localhost/rda/sqlcesa35.dll”;

rda.LocalConnectionString = “Data Source=ContosoBottling.sdf”;

//Drop Table

DropTable(“DistributionCenters”, rda.LocalConnectionString);

//Pull Table

rda.Pull(“DistributionCenters”,

“SELECT DistributionCenterId, Name FROM DistributionCenters”,

rdaConnection,

RdaTrackOption.TrackingOnWithIndexes,

“ErrorTable”);

}

Notice that the mobile device connects to the Server Agent on IIS by pointing to it via a URL.  After that, you assign a connection string that points to the local path of your SQLCE database.  For now, I want you to ignore the DropTable method, because I’ll cover it in a sec.  The Pull method is where the magic happens.  In the first parameter, you pass in the name of the local table you want to create as an argument.  This typically matches the name of the table you’re retrieving from SQL Server.  In the second parameter, you pass a standard SQL statement or call to stored procedure.  This is how you filter the data you want to download to the device.  I don’t want to see any SELECT *’s and I do expect to see appropriate use of the WHERE clause to reduce the amount of data downloaded.  Remember, this filtering allows you download lookup tables that apply to everyone, as well as tables with data that uniquely pertain to a specific user.  In the next parameter you pass in the OLEDB connection string I displayed at the beginning of the article.  The following parameter is where you decide if you want SQLCE to track changes or not, as well as whether to create the same indexes found on the server.  Indexes are typically always a good thing except for very small tables.  Download-only data won’t need change-tracking but your transactional stuff will.  This amazing feature allows offline users of your app to keep working in the absence of a network connection.  In the last parameter you specify the name of a table to auto-create to track any sync errors that may arise.

Server Explorer

 

 

 

 

 

 

After executing this code, I connected to the new SQLCE ContosoBottling bottling database on my Windows laptop using the Server Explorer in Visual Studio as shown above.  You can see that the ErrorTable and DistributionCenters tables were created locally.

So now let’s talk about that DropTable method.  RDA works on the premise of downloading complete table snapshots.  Unlike Merge Replication that downloads incremental changes from SQL Server, RDA re-downloads the entire table in order to make SQLCE aware of any server changes.  The catch is that you have to drop an existing local table before downloading an updated version from SQL Server.  Here’s how you do it:

private void DropTable(string tableName, string connectionString)

{

using (SqlCeConnection cn = new SqlCeConnection(connectionString))

{

SqlCeCommand cmd = cn.CreateCommand();

cmd.CommandText = String.Format(“SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘{0}'”, tableName);

cn.Open();

if((int)cmd.ExecuteScalar() == 1)

{

cmd.CommandText = String.Format(“DROP TABLE {0}”, tableName);

cmd.ExecuteNonQuery();

}

}

}

You can see that I use SqlCeConnection and SqlCeCommand objects in order to query the INFORMATION_SCHEMA.TABLES database object.  If the return value of the query is 1, then you know that a table already exists.  This result leads you to execute a DROP TABLE statement so that the existing table is gone before the new one is downloaded.

Local Query

 

Right-clicking on DistributionCenters and selecting Show Table Data reveals that the Seattle and Redmond distribution centers and their associated uniqueidentifiers were downloaded to SQLCE from SQL Server 2012.

Right about now, I know you’re thinking that this whole process of dropping a table and re-downloading a new one in order to keep a mobile database up to date sounds wasteful.  I get it.  I also get all the heavyweight processes that are required by Merge Replication to figure out server changes for each device that synchronizes with SQL Server.  You have to weigh your options.  For instance, in boosting Merge Replication performance and scalability, one of the keys to success is maintaining a low Subscription Expiration value.  This value determines how long a mobile user can go without synchronizing her data before her subscription expires, which requires her to re-download an entire database from scratch.  Keeping a low value ensures that SQL Server doesn’t track too much performance-degrading metadata.  It also means that users might have to synchronize more frequently than business rules dictate.  The great thing about RDA is that the notion of a subscription doesn’t exist since it downloads table snapshots to keep mobile clients up to date.  This means users can download data to their devices and remain disconnected for an indefinite amount of time while capturing new data out in the field.  No expiration or degraded performance on SQL Server 2012.  This leads to infinitely greater scalability for your system.

In the most common mobile scenarios I see in business, laptops/devices download the data needed to perform work for a given day via Wi-Fi or cradled Ethernet.  Unless each of your downloaded tables are 100+ MB a piece, this shouldn’t be a big deal at these types of network speeds.  Most organizations roll their own web services to do the same thing and they don’t bat an eye at the amount of data they have to re-download with each web method call.  Heck, most companies I work with allow their devices to take all night to download the data needed for the next morning.

So after a user has spent some time in the field capturing new data or changing/deleting existing data, it’s time to push those tracked changes back up to SQL Server 2012.  This is the simplest code of all:

SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();

rda.InternetUrl = “http://localhost/rda/sqlcesa35.dll”;

rda.LocalConnectionString = “Data Source=ContosoBottling.sdf”;

rda.Push(“DistributionCenters”, rdaConnection, RdaBatchOption.BatchingOn);

For each table that you tracked changes for, you need to use the SqlCeRemoteDataAccess object and the Push method.  The first parameter should look familiar since it’s the name of the tracked table that you had previously Pulled.  The second parameter is the same OLEDB connection string we used in the Pull method.  The last one allows you to specify batching of uploads.  This feature gives you the transactional, all-or-nothing functionality of a message queue.  If any of the table data uploads fail, the whole transaction is rolled back.  This is a great feature to ensure data integrity.

Before you run this code, I want you to go back to the local SQLCE query result from the Server Explorer in Visual Studio and change the Distribution Center Name column from Redmond to Bellevue.  I have to prove that this great change tracking feature actually works after all.  Once you’ve made the change and hit the tab key to save it, go ahead and run your Push code.  If everything works as expected, the local change you made should be pushed up to SQL Server.  We need some proof, so open up SQL Server Managment Studio:

Object Explorer

 

Right-clicking on dbo.DistributionCenters and clicking Select Top 1000 Rows should return the result you see in the figure above.  Happily, the local SQLCE change from Redmond to Bellevue is reflected in the result on SQL Server 2012.

The circle is complete.

If you’re ready to go deep on this sync technology to build enterprise apps that run on Windows tablets and laptops, click this link to check out and purchase my book, “Keeping Windows 8 Tablets in Sync with SQL Server 2012.”

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

Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode II

SQL Server Compact

In my last article, I walked you through finding, downloading, installing, and configuring SQL Server 2012 Express, SQL Server Compact 3.5 SP2 CU6, and the Sync Server Tools.  With that series of tasks completed, you’re now capable of performing data synchronization with a mobile Windows client.

Open SQL Server 2012 Management Studio and connect to the local SQL Express instance.  You’ll quickly notice the new Visual Studio 2010 IDE look and feel.  Since you’re going to need a database to sync with, right-click on the Databases folder in the Object Explorer and select New Database.  Type ContosoBottling in the Database name text box and click OK.  I want you to create three simple tables for the purposes of this article:

DistributionCenters
Column PK Data Type Nulls Defaults RowGuid
DistributionCenterId Yes uniqueidentifier No newid() Yes
Name nchar(20) Yes No

 

Routes
Column PK Data Type Nulls Defaults RowGuid
RouteId Yes uniqueidentifier No newid() Yes
DistributionCenterId uniqueidentifier Yes No
Name nchar(20) Yes No

 

Drivers
Column PK Data Type Nulls Defaults RowGuid
DriverId Yes uniqueidentifier No newid() Yes
RouteId uniqueidentifier Yes No
FirstName nchar(20) Yes No
LastName nchar(20) Yes No

If you read my last book on Enterprise Data Replication, these tables that support the operations of a delivery driver should look familiar to you.  Since RDA doesn’t support the Identity Range feature of Merge Replication, you’ll be using GUIDs for your primary keys to ensure uniqueness.  Since the offline data capabilities of sync technologies from all vendors are based on the notion of optimistic concurrency, having a globally unique primary key that won’t collide with inserts and updates made by one or more devices is critical to success.  Now it’s time to fill these tables with some sample data to get started:

Right-click on DistributionCenters and select Edit Top 200 Rows.  Type Seattle for the Name in the first row and Redmond for the Name in the second row.  Allow the DistributionCenterId uniqueidentifier values to be automatically created.  It should look something like this:

Distribution Center

 

 

 

 

Now it’s time to tackle the Routes table.  Each Distribution Center will have multiple routes that it supplies products to.  Right-click on Routes and select Edit Top 200 Rows.  Like before, allow the RouteId uniqueidentifier values to be automatically created.  The eight rows of data I want you to enter should be as follows:

  1. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Magnolia.
  2. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Ballard.
  3. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Fremont.
  4. The DistributionCenterID should equal the related Seattle value from the DistributionCenters table and the Name should equal Wallingford.
  5. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Kirkland.
  6. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Bellevue.
  7. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Issaquah.
  8. The DistributionCenterID should equal the related Redmond value from the DistributionCenters table and the Name should equal Sammamish.

It should look something like this:

Routes

 

Last but not least, we have the Drivers.  Each of these folks will be assigned to a particular route on any given day.  Right-click on Drivers and select Edit Top 200 Rows.  For each row, allow the DriverId uniqueidentifier values to be automatically created.  I’ll just have you enter a couple of drivers for this table:

  1. The RouteId should equal the related Magnolia value from the Routes table and the FirstName should equal Dave and the LastName should equal Bottomley.
  2. The RouteId should equal the related Kirkland value from the Routes table and the FirstName should equal Khalid and the LastName should equal Siddiqui.

It should look something like this:

Drivers

 

Now that your sample database has some data inside, it’s time to build a sample Windows app so fire up Visual Studio 2010.  Create a Windows Forms or WPF application and call the Solution SimpleSync.  The first thing I want you to do is go to the Solution Explorer, right-click on References, and add a reference to System.Data.SqlServerCe.  To make sure you’re working with the newest bits based on Cumulative Update package 6, in the Add Reference dialog, click the Browse tab and navigate to C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop\System.Data.SqlServerCe.dll.

With those pieces in place, it’s time to write some code.

Unlike Merge Replication that automatically creates a SQL Server Compact database for you during the initial sync, with RDA you’ll need to create it in code with the SqlCeEngine object.  So before your app can start synchronizing data, you’ll need to first create a database with the following code:

if (!File.Exists(“ContosoBottling.sdf”))
{
using (SqlCeEngine sqlEngine = new SqlCeEngine())
{
sqlEngine.LocalConnectionString = “Data Source=ContosoBottling.sdf”;
sqlEngine.CreateDatabase();
}
}

The first thing the above code does is to check and see if a SQL Server Compact database already exists.  If not, then the SqlCeEngine object is instantiated.  The LocalConnectionString property is set to the path of where you want the database to reside.  In this case, I didn’t enter a path so the database will be created in the same folder as the app’s exe.  Keep in mind that a number of other parameters can be used for this property to support password protection and encryption among others.  Next, you just call the CreateDatabase() method and that’s all there is to it.  You will now have an empty shell of a database that typically weighs in at 20 KB.

With a local database created, you can begin retrieving data.  In my next article I’ll discuss how to RDA filters and pulls both data and indexes, enables local change-tracking, and pushes new data and updates back to SQL Server.

If you’re ready to go deep on this sync technology to build enterprise apps that run on Windows tablets and laptops, click this link to check out and purchase my book, “Keeping Windows 8 Tablets in Sync with SQL Server 2012.”

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

Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode I

SQL Server Compact

Now that SQL Server 2012 has been released, some of you might be wondering if SQL Server Compact is capable of synchronizing with it.  With the release of Cumulative Update Package 6 for SQL Server Compact 3.5 Service Pack 2, the answer is a resounding yes!

Build number 3.5.8088.00 adds support for replication with SQL ServerDenali” which is pretty awesome in my book.  For those of you keeping score at home, check out Erik’s Everything SQL Server Compact blog to see a running total of improvements to SQLCE via Cumulative Updates.  After that, click on the Cumulative Update link at the beginning of this article and head on over to the Microsoft Support page to get started.

At the top of the page it says Hotfix Download Available and beneath that says View and request hotfix downloads so click on that.  Keep in mind that my link was defined by my U.S. English IE9 browser so the page I navigated to shows me checkboxes to download x64 Server Tools for IIS and both x86 and x64 versions for Windows.  Your experience may be different depending on where you live.  Luckily, there’s a link that says Show hotfixes for all platforms and languages.  Check the checkboxes to select your language, the Server Tools, Windows, and Windows Mobile/Embedded platforms that you’re looking for.  Afterward, type in your email address and play the Captcha game in order to have links to the bits you need sent to you.

The next thing you’re going to need is a copy of SQL Server 2012.  To keep things simple with this series of articles, I won’t dive into security and I decided that I’ll use my x64 Windows 7 laptop as the database server, middleware, mobile platform and use Remote Data Access (RDA) as the sync transport.  I think you’ve probably already heard enough about Merge Replication from me so I decided to mix it up a bit with my old friend RDA.  Remember, RDA does not require any configuration on SQL Server, it is not invasive to the schema of the server database, and it’s amazingly fast and scalable.  With over 650 million copies of Windows 7 deployed, this is by far the most widely used, occasionally-connected mobile platform in the world, so I don’t feel bad about not writing another Windows Phone article.  Navigate your browser to download the free SQL Server 2012 Express with Advanced Services since we won’t be needing support for Replication.  You might need to navigate elsewhere if you’re not targetting U.S. English.  Sorry about that.  Once you’ve downloaded the exe, install the product and make sure you can login via SQL Server Management Studio.

At this point, go ahead and install the 32 and 64-bit versions of the SQL Server Compact 3.5 SP2 runtimes that you downloaded as appropriate.  Remember, on a 64-bit OS, you must install both the x86 and x64 versions in order to have smooth sailing with out favorite embedded database.

I already have IIS installed on my x64 Windows 7 laptop so I’m in good shape to install the x64 Server Tools.  Unzip your CU 6 update and click SSCEServerTools-ENU.msi to begin the Server Tools installation.  As a refresher, you must have IIS 6 Management Compatibility enabled to make things work with IIS 7.5.  During the install make sure all the System Configuration Checks are successful, and that you select SQL Server “Denali” to synchronize with. With the Server Tools installed, I want you to create a local folder on your computer and call it SnapshotShare and Share it with Everyone to keep things simple.  It’s a little silly since RDA doesn’t use a Snaphot Share, but the installation Wizard may no let you proceed without it.

I know many of you have followed the screenshot-filled installation routines in my books so I’ll keep the pictures microscopic this time around.  Click Windows Start, navigate to Microsoft SQL Server Compact 3.5, and select Configure Web Synchronization Wizard.

Welcome to the Configure Web Synchronization Wizard | Click Next

 

 

 

 

Subscriber Type | Select SQL Server Compact | Click Next

 

 

 

 

Web Server | Create a new virtual directory | Click Next

 

 

 

 

Virtual Directory Information | Type RDA in the Alias textbox | Click Next | Click Yes to create a folder | Click Yes again

 

 

 

 

Secure Communications | Select Do not require secure channel (SSL) | Click Next

 

 

 

 

Client Authentication | Select Clients will connect anonymously | Click Next

 

 

 

 

Anonymous Access | Default IUSR account of IIS will be used | Click Next

 

 

 

 

Snapshot Share Access | Enter path to the shared folder I told you to create | Click Next | Click Yes

 

 

 

 

Complete the Wizard | Verify the choices you made | Click Finish

 

 

 

 

Configure Web Synchronization | You should have 9 successes | Click Close

 

 

 

 

Congratulations!  You’re done.

Test your Server Tools installation using Internet Explorer and navigate to this address: http://localhost/rda/sqlcesa35.dll.  Your browser should display “Microsoft SQL Server Compact Server Agent” if all went well.  Your configuration tasks are almost complete, but I need you to bring up SQL Server Management Studio to do one more thing for me.  In SQL Server, create a new login called NT AUTHORITY\IUSR  with ContosoBottling as the default database so devices can anonymously connect to IIS and SQL Server to sync.  I apologize for not having you build out a network full of servers and for not having you use Windows auth against Active Directory.  Remember, when it’s time to go to production, you’ll do this the secure way.

You’ve accomplished a lot by following along through this article and all the pieces are in place to create an occasionally-connected solution for yourself, your company, or your customers.  In the next article, we’ll build a sample database and start writing some code in Visual Studio 2010.

If you’re ready to go deep on this sync technology to build enterprise apps that run on Windows tablets and laptops, click this link to check out and purchase my book, “Keeping Windows 8 Tablets in Sync with SQL Server 2012.”

Stay in sync,

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

Sync Framework v4 is now Open Source, and ready to Connect any Device to SQL Server and SQL Azure

Sync

Microsoft has brought the power to synchronize data with either SQL Server on-premise or SQL Azure in the cloud to the world of mobility.

The profound effects of the Consumerization of IT (CoIT) is blurring the lines between consumers and the enterprise.  The fact that virtually every type of mobile device is now a candidate to make employees productive means that cross-platform, enabling technologies are a must. If you’ve ever synched the music on your iPhone with iTunes, the calendar on your Android device with Gmail, or the Outlook email on your Windows Phone with Exchange, then you understand the importance of sync.  In my experience architecting and building enterprise mobile apps for the world’s largest organizations over the last decade, data sync has always been a critical ingredient.

The new Sync Framework Toolkit found on MSDN builds on the existing Sync Framework 2.1’s ability to create disconnected applications, making it easier to expose data for synchronization to apps running on any client platform.  Where Sync Framework 2.1 required clients to be based on Windows, this free toolkit allows other Microsoft platforms to be used for offline clients such as Silverlight, Windows Phone 7, Windows Mobile, Windows Embedded Handheld, and new Windows Slates.   Additionally, non-Microsoft platforms such as iPhones, iPads, Android phones and tablets, Blackberries and browsers supporting HTML5 are all first-class sync citizens.  The secret is that we no longer require the installation of the Sync Framework runtime on client devices.  When coupled with use of an open protocol like OData for data transport, no platform or programming language is prevented from synchronizing data with our on-premise and cloud databases.  When the data arrives on your device, you can serialize it as JSON, or insert it into SQL Server Compact or SQLite depending on your platform preferences.

The Sync Framework Toolkit provides all the features enabled by theSync Framework 4.0 October 2010 CTP.  We are releasing the toolkit as source code samples on MSDN with the source code utilizing Sync Framework 2.1.  Source code provides the flexibility to customize or extend the capabilities we have provided to suit your specific requirements. The client-side source code in the package is released under the Apache 2.0 license and the server-side source code under the MS-LPL license.  The Sync Framework 2.1 is fully supported by Microsoft and the mobile-enabling source code is yours to use, build upon, and support for the apps you create.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now some of you might be wondering why you would use a sync technology to move data rather than SOAP or REST web services.  The reason has to do with performance and bandwidth efficiency.  Using SOA, one would retrieve all the data needed to the device in order to see what has changed in SQL Server.  The same goes for uploading data.  Using the Sync Framework Toolkit, only the changes, or deltas, are transmitted over the air.  The boosts performance and reduces bandwidth usage which saves time and money in a world of congested mobile data networks with capped mobile data plans.  You also get a feature called batching, which breaks up the data sent over wireless networks into manageable pieces.  This not only prevents you from blowing out your limited bandwidth, but it also keeps you from using too much RAM memory both on the server and your memory-constrained mobile device.  When combined with conflict resolution and advanced filtering, I’m sold!

I think you’ll find the Sync Framework Toolkit to be an immensely valuable component of your MEAP solutions for the enterprise as well as the ones you build for consumers.

Keep Synching,

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

Performance and Memory Management Improvements with Windows Embedded Handheld

A lot has changed since the launch of Windows Phone in the Fall of 2010.

Microsoft now has a compelling phone platform that targets consumers inside and outside the office.  One thing that that hasn’t changed is the widespread use of Windows Embedded Handheld to solve tough enterprise mobility problems.  It should be no surprise that over 80% of enterprise handhelds shipped are running Windows Mobile or Windows Embedded Handheld.  They include support for barcode scanning, RFID reading, rugged hardware, every type of wireless, full device encryption, complete over-the-air software distribution and device managment support, FIPS compliance, and both capacitive touch and stylus operation.  On the application platform side of the equation, they have rich support for WinForm development using Visual Studio and the .NET Compact Framework, C++ and a full-featured database with built-in sync capabilities via SQL Server Compact.  They can easily communicate with WCF SOAP and REST web services running on Windows Servers on-premise or with Azure in the cloud.  Support for Merge Replication means faster time to market to get device synchronizing with SQL Server with almost no coding.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Since Windows Embedded Handheld uses an advanced version of the operating system kernel used by Windows Mobile 6.5.3, many of the techniques and best practices I’ve taugh customers and developers all over the world still apply.  While it still uses the slotted memory model found in Windows CE 5 with 32 processes and 32 MB of memory per process, you’ll find that numerous enhancements and tuning has taken place to give your line of business apps more of what they need.  I’m talking about more memory per process and improved performance.

A recent Gartner report recommends that organizations should stay with Windows Embedded Handheld as the best mobile platform for enterprise line of business needs.  Great devices are available from OEMs like Intermec, Motorola, Psion, and Honeywell just to name a few.  I hope this video helps you with any memory management or performance issues you may need to deal with in your enterprise mobile apps.

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