Rob Tiffany on Intermittent wireless connectivity requires #mobile apps to follow sync patterns using pre-fetched data via APIs and offline local storage.
Read More Rob Tiffany on 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.
Read More Rob Tiffany on Presenting at TechEd in Houston, watch my session: “Empower your Demanding #Mobile Line of Business Apps with SQLite and Offline Data Sync on Window.”
Read More Rob Tiffany on 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:
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:
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.
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.
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 https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany
[mc4wp_form id=”5975″]
Rob Tiffany on 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.
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.
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:
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.
-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
[mc4wp_form id=”5975″]
Rob Tiffany on 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:
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:
It should look something like this:
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:
It should look something like this:
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:
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.
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
[mc4wp_form id=”5975″]
Rob Tiffany on Build number 3.5.8088.00 adds support for replication with SQL Server “Denali” 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.
Stay in sync,
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
[mc4wp_form id=”5975″]
Rob Tiffany on 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.
Read More Rob Tiffany on It should be no surprise that over 80% of enterprise handhelds shipped are running Windows Mobile or Windows Embedded Handheld.
Read More Rob Tiffany on 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: https://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:
As you can see from the picture above:
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 https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany
[mc4wp_form id=”5975″]
