Wrapping a Mobile API around Your Enterprise & Taking Microsoft SQL Server 2014 Data Offline w/ NoSQL via Universal Apps for Windows

At TechEd New Zealand, I presented a session on how to integrate a company’s backend systems into SQL Server 2014 and deliver that data out to mobile devices via Web APIs to support the operations of occasionally-connected apps on mobile devices using NoSQL tables.

Enterprise mobility is a top priority for Chief Information Officers who must empower employees and reach customers by moving data from backend systems out to apps on mobile devices.  This data must flow over inefficient wireless data networks, be consumable by any mobile device, and scale to support millions of users while delivering exceptional performance.  Since wireless coverage is inconsistent, apps must store this data offline so users can be productive in the absence of connectivity.  In this video, I’ll teach you how mashup disparate backend systems into high-speed, SQL Server 2014 in-memory staging tables.  I boost the speed even further through the use of natively-compiled stored procedures and then link them to fast and scalable REST + JSON APIs using the ASP.NET Web API while employing techniques such as in-memory caching.  On the device, I’ll show you how your apps can work with offline data via in-memory NoSQL tables that use LINQ to support the same CRUD operations as relational databases.  You’ll walk away from this session with the ability to deliver flexible server solutions that work on-premises or in Azure and device solutions that work with Windows Phones, Tablets or Laptops.

– Rob

How to Create In-Memory Database Tables in SQL Server 2014

Getting data off disk drives and into RAM is the biggest game changer for relational databases in decades and SQL Server 2014 brings it to the masses.

RAM is cheap and it’s finally time to reap the benefits of 64-bit computing.

SQL Server In-Memory OLTP, also know at Hekaton is here and it’s ready to transform your business.  Unlike other recent entries to the in-memory database space, SQL Server 2014 integrates this new technology directly into the database engine instead of being a separate add-on. Additionally, existing SQL Server DBAs and developers will feel right at home building memory-optimized databases with same SQL Server Management Studio they’ve used for years.  Not having to retrain your staff is pretty cool.

Benefits to using SQL Server 2014 include:

  • In-memory execution for low-latency data retrieval vs. disk-bound I/O
  • Elimination of contention (locks, latches, spinlocks) from concurrent data Inserts and Updates due to optimistic concurrency control (row versioning without using TempDB)
  • Disk I/O reduction or elimination depending selected data durability (more on this later)
  • 5x – 25x performance improvement and the equivalent throughput of 5 – 10 scaled-out database servers

Create a Memory-Optimized Database

  • Create a normal database in SQL Server Management Studio

Create Database

  •  Add Memory Optimized Data Filegroup to ensure data durability

Memory Optimized Filegroup

  • Add a FILESTREAM Data file type with Unlimited Autogrowth/Maxsize

Filestream Data

Create Memory-Optimized Tables

  • Right-click on the Tables folder of the database you just created and select New | Memory Optimized Table… to get a starter SQL script
  • Create and execute CREATE TABLE scripts to create one or more tables where MEMORY_OPTIMIZED=ON (example in a sec)
  • Set DURABILITY=SCHEMA_ONLY for staging tables to prevent transaction logging and checkpoint I/O (this means only the schema but no actual data will be saved to disk in the event of a server restart)
  • Set DURABILITY=SCHEMA_AND_DATA for standard tables (this saves the schema and in-memory data to disk in the background with the option to delay durability for better performance by not immediately flushing transaction log writes)

Here’s an example of a SQL script to create a memory-optimized Customer table with an Id, FirstName and LastName column:

USE TechEDNZ2014
GO
CREATE TABLE [dbo].[Customer] (
    [Id] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000) DEFAULT (NEWID()), 
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50)
) 
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

 

Create Natively-Compiled Stored Procedures

Just when you thought performance couldn’t get any better, SQL Server 2014 rewrites the book on stored procedures.  Your T-SQL code now compiles to C DLLs which minimizes code execution time to further boost performance and scalability.  Furthermore, they significantly reduce CPU usage on your SQL Server box due to the need for fewer instructions to execute.

Here’s an example of a SQL script to create a natively-compiled stored procedure to retrieve data from the memory-optimized Customer table you just created:

USE TechEDNZ2014
GO
create procedure [dbo].[CustomerSelect]
with native_compilation, schemabinding, execute as owner
as 
begin atomic with
(
    transaction isolation level = snapshot, 
    language = N'English'
)
    SELECT [Id], [FirstName], [LastName] FROM [dbo].[Customer];
end
GO

 

I’m hoping by now you’re feeling the need for speed.

I’ve heard plenty of reports from companies that upgraded from previous versions of SQL Server to SQL Server 2014 that they instantly doubled their performance.  This is before converting disk-based tables to in-memory tables which is pretty incredible and well worth the upgrade on its own.  Just knowing that you can jump from a 2x performance increase to anywhere from 5x to 25x is mind boggling.

Most of you know me as a mobile strategist, architect and developer.  Being a mobile guy doesn’t mean I don’t think about the server.  In fact in all the large-scale enterprise mobile solutions I’ve designed for Fortune 500 companies, I figure I spend more than 70% of my time ensuring that servers are fast and can scale.  With SQL Server 2014 being the heart of most enterprise systems, just imagine how delighted all your mobile users will be when their apps become dramatically more responsive.

– Rob

TechEd North America 2014: Empower your Demanding Mobile Line of Business Apps with SQLite and Offline Data Sync on Windows 8.1

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

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.

Rob

NoSQL

In this week’s episode of “Inside Windows Phone,” Matthijs Hoekstra and I discuss the use of NoSQL.

When building mobile apps for the enterprise, an offline data store is absolutely essential so that users can keep working in the absence of wireless connectivity.  In addition to SQLite and SQLCE, NoSQL is a great option to consider in order to achieve your goals with local data storage.

Below are some code snippets I created to get you started:

Define Table schema (Entity)
public sealed class Customer
{
    public int CustomerId { getset; }
    public string FirstName { getset; }
    public string LastName { getset; }
}

Define Table (Generic List to hold collection of Customer objects)
public static List<Customer> Customers { getset; }

 

Create Table
Customers = new List<Customer>();

 

Save Table
public static async Task SaveChanges<T>(T collection, String tableName)
{
    var file = awaitApplicationData.Current.  LocalFolder.CreateFileAsync(tableName, CreationCollisionOption.ReplaceExisting);
    var outStream = await file.OpenStreamForWriteAsync();
    var serializer = new DataContractJsonSerializer(typeof(T));
    serializer.WriteObject(outStream, collection);
    await outStream.FlushAsync();
    outStream.Dispose();
}
 
Calling the Save Table Method
await SaveChanges<List<Customer>>(Customers, “Customer”);
 
Load Table
public static async Task<T> LoadTable<T>(string tableName)
{
    StorageFile file = awaitApplicationData.Current.
LocalFolder.GetFileAsync(tableName);
    Stream stream = await file.OpenStreamForReadAsync();
    DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(T));
    T table = (T)serializer.ReadObject(stream);
    stream.Dispose();
    return table;
}
 
Calling the Load Table Method
Customers = await LoadTable<List<Customer>>(“Customer”);
 
INSERT Customers
Customers.Add(new Customer
{
    CustomerId = 1,
    FirstName = “Andy”,
    LastName = “Wigley”
});
 
UPDATE Customers
foreach (var item in Customers.Where((c) => c.CustomerId == 2))
{
    item.FirstName = “Mike”;
}
 
DELETE Customers
Customers.RemoveAll((c) => c.CustomerId == 2);
 
SELECT Customers
var query = from c in TableService.Customers
            select c;
CustomersList.ItemsSource = query.ToList();

Have fun!
Rob

SQLite WinRT for Windows and Windows Phone: Data Definition

SQLite WinRT allows Windows and Windows Phone developers to create and manipulate database objects through the SQLite Data Definition Language (DDL).

SQLite supports DDL statements including CREATE, ALTER, and DROP to work with objects like Tables, Indexes, Triggers, and Views.  This is complimented by simplified, dynamic data types including INTEGER, NULL, REAL, TEXT, and BLOB.  Admittedly, this takes some getting used to by those of you who use database engines with static typing.  Here’s a quick explanation:

  • INTEGER: A signed integer that is flexibly stored in 1, 2, 3, 4, 6, or 8 bytes depending on the size of the value you INSERT.
  • NULL: Nothing to see here.
  • TEXT: A variable-sized text string stored using UTF-8, UTF-16BE or UTF-16LE encoding.
  • REAL: A floating point value stored as an 8-byte IEEE floating point number.
  • BLOB: Stores whatever you INSERT into it.

Lets cut to the chase so you can see how to create a new SQLite database:

private async void OpenCreateDatabase()
{
    var db = new SQLiteWinRT.Database(Windows.Storage.ApplicationData.Current.LocalFolder, "Contoso.db");
    try
    {
        await db.OpenAsync();
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        var result = SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);
        throw new Exception("Failed to create database " + result);
    }
}

Walking through the code above, you quickly notice that I created an async function.  This is because I use await when opening the database asynchronously on a worker thread to keep things fast and fluid.  The first line of code is flexible enough to either create new database called Contoso.db or use an existing one which make our lives easier.  You’ll also notice that I’m placing the database in the LocalFolder used for storage by my Windows or Windows Phone app.  SQLiteWinRTPhone is used for Windows Phone and SQLiteWinRT is used for Windows tablets, laptops, and desktops.  I’m wrapping the aforementioned code to open the database in a try/catch block specifically designed to deal with COM exceptions since our WinRT code is interoperating with native C++ code from SQLite.  You’ll use GetSqliteErrorCode in order to retrieve the HResult from SQLite and then throw the error up the stack using a standard Exception object.

With your database created, the next thing you’ll want to do is create one or more tables.  The structure for the CREATE TABLE statement can be found at http://www.sqlite.org/lang_createtable.html.  Below, I’ll show you the code to create a typical Parent/Child table relationship between a Product and ProductDetails table:

private async void CreateTables()
{
    var db = new SQLiteWinRT.Database(Windows.Storage.ApplicationData.Current.LocalFolder, "Contoso.db");
    try
    {
        await db.OpenAsync();
        string sql = @"CREATE TABLE IF NOT EXISTS Products
                       (ProductId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                       Name TEXT)";
        await db.ExecuteStatementAsync(sql);

        sql = @"CREATE TABLE IF NOT EXISTS ProductDetails
                (ProductDetailId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                Color TEXT,
                Quantity INTEGER,
                ProductId INTEGER,
                FOREIGN KEY(ProductId) REFERENCES Products(ProductId) ON DELETE CASCADE)";
        await db.ExecuteStatementAsync(sql);
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        var result = SQLiteWinRT.Database.GetSqliteErrorCode(ex.HResult);
        throw new Exception("Failed to create tables " + result);
    }
}

In the code above, I use a sql string variable to create two different DDL statements. While they both start out with CREATE TABLE, we also have the luxury of using IF NOT EXISTS before the table name.  This means if the table already exists, this DDL statement is harmless and no operation will be performed.  The next part of the statement creates one or more columns and constraints.  In both tables I create an integer-based Id column that serves as the primary key which enforces uniqueness.  SQL Server DBAs and developers who like to use Identity columns will be happy to see the AUTOINCREMENT constraint that automatically increments the value of the integer each time a new row is inserted.  Other columns and their data types are separated by commas.  Last but not least, the ProductDetails Child table enforces a referential integrity constraint with the Products Parent table.  FOREIGN KEY points to the local ProductId column and REFERENCES the ProductId in the Products table.  ON DELETE CASCADE ensures that when a Product is deleted, associated ProductDetails are also deleted.

Once you’ve created tables, you’ll want to speed up your queries by creating indexes, prepackage SELECT statements into views, and trigger various actions whenever an INSERT, UPDATE, or DELETE occurs:

Sometimes you need to make changes to an existing table.  SQLite allows you rename tables and add columns via ALTER TABLE: http://sqlite.com/lang_altertable.html.  If you find you no longer need certain indexes, views, triggers, and tables, you can drop them:

No matter which DDL operation you need to perform, you’ll follow the same pattern I showed you in the CREATE TABLE code sample above.  You’ll piece together the appropriate statement in a string variable and then call the ExecuteStatementAsync() method.

Now that you know how to use SQLite WinRT to work with SQLite’s Data Definition Language, you can build any data structure that you mobile app needs.  In my next article I’ll show you how to work with SQLite’s Data Manipulation Language (DML) to bring you database and app to life.

– Rob