Tag Archives

14 Articles
Windows 8

Keeping Windows 8 Tablets in Sync with SQL Server 2012

Posted by Rob Tiffany on
Keeping Windows 8 Tablets in Sync with SQL Server 2012

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 https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

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

[mc4wp_form id=”5975″]

Sync

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

Posted by Rob Tiffany on
Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode III

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 https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

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

[mc4wp_form id=”5975″]

Sync

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

Posted by Rob Tiffany on
Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode II

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 https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

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

[mc4wp_form id=”5975″]

Sync

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

Posted by Rob Tiffany on
Simple Mobile Sync with SQL Server 2012 and SQL Server Compact: Episode I

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 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.

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 https://robtiffany.com , follow me on Twitter at https://twitter.com/RobTiffany and on LinkedIn at https://www.linkedin.com/in/robtiffany

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

[mc4wp_form id=”5975″]

SQL Server

Reducing SQL Server Sync I/O Contention :: Tip 5

Posted by Rob Tiffany on
Reducing SQL Server Sync I/O Contention :: Tip 5

When it comes to delivering server solutions with Windows Server and SQL Server, speed is your friend (as we used to say in the submarine service).

More speed means more things can happen in a given period of time. If more things can happen in a given period of time, then you can derive greater scalability. Duh, Winning!

Okay, this stuff is obvious so let’s move on.

Have you ever noticed when you’ve finished installing your shiny new Windows Server 2008 R2 box, the default Power Plan is set to “Balanced?” Guess what kind of performance and scalability you get when you decide to “Go Green” and save the world with a “Balanced” power plan? Needless to say, you’re not making the most of the high-powered CPUs you just paid big money for.

So how does this relate to SQL Server and reducing I/O contention?

Would it surprise you to know that the amount of time your CPU’s spend processing your queries could actually double with a Balanced plan? If it takes more CPU time to execute a query, then imagine all those Merge Agent processes locking and blocking each other as they try to perform DML operations on the change tracking tables.

So what’s the takeaway here?

Set your Windows Server 2008 R2 power plan to High Performance! If you’re part of a Windows Domain and you need to make this setting stick, have your sys admin enforce this setting on all your SQL Servers via Group Policy.

Go fast or go home because your users care about performance.

-Rob

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

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

[mc4wp_form id=”5975″]

Windows Phone 7

Windows Phone 7 Line of Business App Dev :: Improving the In-Memory Database

Posted by Rob Tiffany on
Windows Phone 7 Line of Business App Dev :: Improving the In-Memory Database

About a month ago, I wrote an article intended to help you fill some of the gaps left by the missing SQL Server Compact database.

Since your Windows Phone 7 Silverlight app is consuming an ObservableCollection of objects streaming down from Windows Azure and SQL Azure, it makes sense to organize those objects in a database-like format that’s easy to work with.  If you’ve ever worked with Remote Data Access (RDA) in the past, the notion of pre-fetching multiple tables to work with locally should look familiar.

In this case, each ObservableCollection represents a table, each object represents a row, and each object property represents a column.  I had you create a Singleton class to hold all these objects in memory to serve as the database.  The fact that Silverlight supports Language Integrated Query (LINQ) means that you can use SQL-like statements to work with the multiple, ObservableCollections of objects.

If you’re wondering why I have you cache everything in memory in a Singleton, there’s a few reasons.  For starters, it makes it easy to query everything with LINQ with the fastest performance possible for single and multi-table JOINs.  Secondly, I don’t represent a Microsoft product group and therefore wouldn’t engineer an unsupported provider that can query subsets of serialized data from files residing in Isolated Storage.  Finally, I don’t want you to accidentally find yourself with multiple instances of the same ObservableCollection when pulling data down from Azure or loading it from Isolated Storage.  Forcing everything into a Singleton prevents you wasting memory or updating objects in the wrong instance of an ObservableCollection.  An inconsistent database is not a good thing.  Don’t worry, you can control which tables are loaded into memory.

So what is this article all about and what are the “improvements” I’m talking about?

This time around, I’m going to focus on saving, loading and deleting the serialized ObservableCollections from Isolated Storage.  In that last article, I showed you how to serialize/de-serialize the ObservableCollections to and from Isolated Storage using the XmlSerializer.  This made it easy for you to save each table to its own XML file which sounds pretty cool.

So what’s wrong with this?

Saving anything as XML means that you’re using the largest, most verbose form of serialization.  After hearing me preach about the virtues of doing SOA with WCF REST + JSON, using the XmlSerializer probably seems out of place.  Luckily, the DataContractJsonSerializer supported by Silverlight on Windows Phone 7 gives you the most efficient wire protocol for data-in-transit can also be used to save those same .NET objects to Isolated Storage.  So the first improvement in this article comes from shrinking the size of the tables and improving the efficiency of the serialization/de-serializing operations to Isolated Storage using out-of-the-box functionality.

While going from XML to JSON for your serializing might be good enough, there’s another improvement in the way you write the code that will make this much easier to implement for your own projects.  A look back to the previous article reveals a tight coupling between the tables that needed to be saved/loaded and the code needed to make that happen.  This meant that you would have to create a SaveTable and LoadTable method for each table that you wanted to retrieve from Azure.  The new code you’re about to see is generic and allows you to use a single SaveTable and LoadTable method even if you decide to download 100 tables.

Enough talk already, let’s see some code.  Launch your ContosoCloud solution in Visual Studio and open Database.cs.  I want you to overwrite the existing code with the code shown below:

using System;
using System.Net;
using System.Windows;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.IO.IsolatedStorage;
using System.Runtime.Serialization.Json;

namespace ContosoPhone
{
sealed class Database
{
//Declare Instance
private static readonly Database instance = new Database();

        //Private Constructor
private Database() { }

        //The entry point into this Database
public static Database Instance
{
get
{
return instance;
}
}

        //Serialize ObservableCollection to JSON in Isolated Storage
public void SaveTable<T>(T tableToSave, string tableName)
{
if (tableToSave != null)
{
using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
{
using (IsolatedStorageFileStream stream = store.CreateFile(tableName + “.txt”))
{
DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(T));
serializer.WriteObject(stream, tableToSave);
}
}
}
else
{
throw new Exception(“Table is empty”);
}
}

        //Deserialize ObservableCollection from JSON in Isolated Storage
public T LoadTable<T>(T tableToLoad, string tableName)
{
using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
{
if (store.FileExists(tableName + “.txt”))
{
using (IsolatedStorageFileStream stream = store.OpenFile(tableName + “.txt”, System.IO.FileMode.Open))
{
DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(T));
return (T)serializer.ReadObject(stream);
}
}
else
{
throw new Exception(“Table not found”);
}
}
}

        //Delete ObservableCollection from Isolated Storage
public void DropTable(string tableName)
{
using (IsolatedStorageFile store = IsolatedStorageFile.GetUserStoreForApplication())
{
if (store.FileExists(tableName + “.txt”))
{
store.DeleteFile(tableName + “.txt”);
}
else
{
throw new Exception(“Table not found”);
}
}
}


//Declare Private Table Variables
private ObservableCollection<Customer> customerTable = null;

        //Customer Table
public ObservableCollection<Customer> Customers
{
get { return customerTable; }
set { customerTable = value; }
}
}
}

 

Looking from top to bottom, the first change you’ll notice is the new SaveTable method where you pass in the desired ObservableCollection and table name in order to serialize it as JSON using the DataContractJsonSerializer.  The next method down the list is LoadTable where you pass in the same parameters as SaveTable but you get back a de-serialized ObservableCollection.  The last new method in the Database Singleton is DropTable which simply deletes the serialized table from Isolated Storage if you don’t need it anymore.

So how do you call this code?

Bring up MainPage.xaml.cs, and find the click event for Save button.  Delete the existing XmlSerializer code and replace it with the following:

try
{
Database.Instance.SaveTable<ObservableCollection<Customer>>(Database.Instance.Customers, “Customers”);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

The code above shows you how to call the SaveTable method in the Singleton with the appropriate syntax to pass in the ObservableCollection type as well as actual ObservableCollection value and name.

Now find the click event for the Load button, delete the existing code and paste in the following:

try
{
Database.Instance.Customers = Database.Instance.LoadTable<ObservableCollection<Customer>>(Database.Instance.Customers, “Customers”);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

This code looks pretty much the same as the SaveTable code except that you set Database.Instance.Customers equal to the return value from the method.  For completeness sake, drop another button on MainPage.xaml and call it Drop.  In its click event, paste in the following code:

try
{
Database.Instance.DropTable(“Customers”);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

For this code, just pass in the name of the table you want to delete from Isolated Storage and it’s gone.

It’s time to hit F5 so you can see how things behave.

phone7

When your app comes to life in the emulator, I want you to exercise the system by Getting, Adding, Updating and Deleting Customers.  In between, I want you to tap the Save button, close the app, reload the app and tap the Load button and then View Customers to ensure you’re seeing the list of Customers you expect.  Keep in mind that when you Save, you overwrite the previously saved table.  Likewise, when you Load, you overwrite the current in-memory ObservableCollection.  Additionally, Saving, Loading, and Dropping tables that don’t exist should throw an appropriate error message.

So what’s the big takeaway for these tweaks I’ve made to the in-memory database?

While switching serialization from XML to JSON is a great improvement in size and efficiency, I truly believe that making the SaveTable and LoadTable methods generic and reusable will boost developer productivity.  The new ease with which you can Save and Load 1, 10 or even 1,000 tables makes this more attractive to mobile developers that need to work with local data.

So where do we go from here?

You now have some of the basic elements of a database on Windows Phone 7.  You don’t have ACID support, indexes, stored procedures or triggers but you have a foundation to build on.  So what should be built next?

To help ensure database consistency, I would add an AutoFlush feature next.  SQL Server Compact flushes its data to disk every 10 seconds and there’s nothing to prevent you from using the SaveTable method to do the same.  A timer set to fire at a user-specified interval that iterates through all the ObservableCollections and saves them will help keep your data safe from battery loss and unforeseen system failures.  The fact that your app can be tombstoned at any moment when a user taps the Back button makes an AutoFlush feature even more important.

Anything else?

At the beginning of this article I mentioned RDA which is a simple form of data synchronization.  It’s simple because it only tracks changes on the client but not the server.  To find out what’s new or changed on the server, RDA requires local tables on the device to be dropped and then re-downloaded from SQL Server.  With the system I’ve built and described throughout this series of articles, we already have this brute force functionality.  So what’s missing is client-side change tracking.  To do this, I would need to add code that fires during INSERTS, UPDATES, and DELETES and then writes the appropriate information to local tracking tables.  To push those changes back to SQL Azure, appropriate code would need to call WCF REST + JSON Services that execute DML code on Windows Azure.

I hope with the improvements I’ve made to the in-memory database in this article, you’ll feel even more empowered to build occasionally-connected Windows Phone 7 solutions for consumers and the enterprise.

Keep coding!

-Rob

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

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

[mc4wp_form id=”5975″]