One-Stop Shop for SQL Server Scripts

Microsoft recently updated its All-In-One Script Framework, which features SQL Server (and other) scripts designed to address common problems reported by users in forums, support incidents and online communities.

Though primarily targeted at IT pros, the scripts are helpful for developers, too, as pointed out recently by Jialiang Ge, who works at a sister project called the All-In-One Code Framework. "Considering that many developers are writing T-SQL scripts too, we hope that the scripts could be useful to you," he wrote in a MSDN blog post.

He wrote about seven new scripts added to the All-In-One-Script Framework, including:

  • Enroll SQL Server instances on multi server into an existing SQL Server Utility
  • Check SQL Server missing KB2277078 to prevent leak of security audit entries
  • How to retrieve the top N rows for each group
  • Publish report to Reporting Services in SharePoint Integrated Mode
  • Bulk set the Timeout property of the reports that in one specified folder
  • Add one user/group to a specified Reporting Services item
  • Get properties of the objects that in multiple SQL Server instances

Meanwhile, back at the All-In-One Code Framework, targeted specifically at developers, code samples are available via a standalone sample installer or as a Visual Studio extension.

Some sample scripts of interest to data developers include:

  • Import Data from Excel to SQL Server
  • Entity Framework Sample Provider
  • Upload Files Asynchronously Using AJAX into SQL Server Database
  • Bind Image in Gridview Using C# with ASP.NET
  • ASP.NET Dynamic Data Unleashed
  • LinqToSqlExample
  • Stored Procedures
The project also has a "request code sample" service described as "a proactive way for our developer community to obtain code samples for certain programming tasks directly from Microsoft." Project documentation said developers can vote on requests and Microsoft engineers will choose the requests with the most votes and provide appropriate code samples. The two most popular requests at the time of this writing were "Code samples for Orchard Project" and "N-tier Entity Framework 4 end to end sample app."

So take a look at the one-stop shops for code samples and scripts and share your thoughts by commenting here or dropping me a line.

Posted by David Ramel on 10/24/2012 at 1:15 PM0 comments


Azure Mobile Services Now Support Blob, Table Storage Options

Microsoft yesterday announced enhancements to its cloud-based backend for mobile apps, including new data storage options.

Windows Azure Mobile Services (WAMS), a preview announced in late August, provides data storage and other services to developers without the time, talent or inclination to wire up the server-side code themselves. Previously, data access was available through simple management-portal-created SQL Databases, or, as I detailed earlier, an existing database.

Now, WAMS also provides support for Windows Azure Blob and Table storage, Microsoft's Scott Guthrie announced yesterday. "This is supported using the existing 'azure' module within the Windows Azure SDK for Node.js," Guthrie said. He provided an example to show how easy it is to use the new support for Tables:

The below code could be used in a Mobile Service script to obtain a reference to a Windows Azure Table (after which you could query it or insert data into it):

var azure = require('azure'); var tableService =
   azure.createTableService("< account="" name="">>", "< access="" key="">>");

Guthrie pointed to the Windows Azure Node.js dev center for tutorials on using the new data storage options via the "azure" module.

Other services were also announced as Microsoft continues to flesh out its preview offering, including support for iOS apps through the use of native iOS libraries. Previously, only Windows 8 apps were officially supported, though Microsoft partner Xamarin developed an open source SDK for MonoTouch for iOS and Mono for Android, available on GitHub, which also hosts the open source WAMS SDKs and samples.)

One reader expressed surprise that iOS support was added before Windows Phone, the most obvious choice of client to use WAMS. "Holy crap," said the reader. "iOS before Windows Phone? Come on guys. The WP7/8 OS is legit. Give it first rate support with your own products."

As the iOS support reflects a nod toward the ubiquity of Apple mobile devices, other enhancements reflect the pervading need for the requisite "social" services in modern apps. These include support for Facebook, Google and Twitter authentication and the ability to send e-mails and SMS messages from WAMs apps. Also, server deployment to the West region was added (previously only the East region was available).

As WAMS continues to evolve, with Windows Phone support probably coming soon, so does the entire Windows Azure ecosystem. "We'll have even more new features and enhancements coming later this week--including .NET 4.5 support for Windows Azure Web Sites," Guthrie said. "Keep an eye out on my blog for details as new features become available."

What do you think of the new data storage options available in the do-it-yourself cloud backend for mobile apps? Comment here or drop me a line.

Posted by David Ramel on 10/17/2012 at 9:03 AM1 comments


IndexedDB Featured in New Web Development Open Standards Site

Further evidence that NoSQL database technology has triumphed over its relational counterpart in Web development was illustrated earlier this week in the unveiling of a new site aiming to provide a one-stop resource for Web developers using open technologies.

The World Wide Web Consortium's (W3C) Web Platform Docs features IndexedDB as the database technology of choice for client-side storage of substantial amounts of structured data.

Web Platform Docs was announced Monday by Microsoft and other major Web development players who partnered with the W3C to develop the wiki-style site, part of WebPlatform.org. The "alpha" release of Web Platform Docs includes "hot topics" such as HTML5, CSS, Video, File API, Media Queries and more in addition to IndexedDB.

IndexedDB, or the Indexed Database API, is a NoSQL database technology that uses key/value pairs as records with an index over the records for high-performance queries. It was initially proposed as a standard by Oracle in 2009, according to an entry on Wikipedia.

IndexedDB competed for a while with the Web SQL API, similar to SQLite, but the W3C gave up on that in November 2010, stating, "The specification reached an impasse: all interested implementors have used the same SQL backend (Sqlite), but we need multiple independent implementations to proceed along a standardisation path."

Some two years later, browser makers still need to catch up with the winner, IndexedDB. The lack of consensus and agreement on a formal standard has led to uneven support among browsers for different competing technologies. The site caniuse.com, which reports on technology compatibilities, lists IndexedDB as having full support among only about 18 percent of browsers, according to "global usage share statistics," while Web SQL is listed as having about 45 percent. However, IndexedDB was reported to be partially supported by about 31 percent of browsers, for a total of about 48 percent. Firefox is reportedly the only browser with full support in its "current" version.

Microsoft came on board with the standard in March 2010 and supports it with the new Internet Explorer 10 browser coming with Windows 8. According to Firefox browser maker Mozilla, which backed IndexedDB early on and which provides extensive documentation on the technology, the asynchronous IndexedDB API is also supported by the latest (or upcoming) versions of Chrome and Firefox browsers, but not Opera or Safari. (IndexedDB also has a synchronous API, but it's hardly used, which probably accounts for the lack of "full support" listed by caniuse.com.) Opera and Safari support Web SQL, along with the current version of Chrome (and upcoming versions).

The lack of a universally accepted standard has obviously caused problems for Web developers. "It's costly and inefficient for them to spend precious hours consulting multiple resources to understand how to employ Web technologies in a way that functions across browsers, operating systems and devices," said Microsoft's Jean Paoli and Michael Champion in their announcement of Web Platform Docs. Of course, you Web developers have known this for a while now. As one developer said on the hacks.mozilla.org site almost two years ago, commenting on Mozilla's support for IndexedDB in Firefox 4, "I think the way Mozilla are heading is correct. If we don't get this right now, before [Web apps] begin to supplant client-side apps, we're just going to be in one whole kludge of a mess that can't be undone easily."

So consider the new Web Platform Docs as an effort to further address the "kludge of a mess." The site is looking for user input and further development and is pretty bare-bones right now. The IndexedDB page just lists some 80 subpages for methods, properties, events and so on. The content borrows heavily from the MSDN Windows Internet Explorer API reference site. Still, drilling down into individual items can lead to a lot of question marks where content is supposed to go, and some are entirely empty.

So register, log on and help out on the wiki. You can start with the getting started guide about how you can contribute.

The "Web platform stewards" who helped out on the site, in addition to the W3C, Microsoft and Mozilla, include Adobe, Apple, Facebook, Google, HP, Nokia and Opera.

In addition to API documentation, the site features tutorials, forums, chat and a blog. The "Concepts" page includes articles ranging from "The history of the Web" and "How does the Internet Work" to "Mobile JavaScript best practices" and "WebSocket security."

Many Web developers expressed enthusiasm for the new initiative, writing comments on the introductory blog post such as this: "Great work to promote and develop the Web. It's one more step to move the Web forward."

Let's hope so.

What do you think about the new one-stop shop for Web development? Do you see it fleshing out into a vital resource? Do you plan to contribute? Share your thoughts by commenting here or dropping me a line.

Posted by David Ramel on 10/10/2012 at 1:15 PM2 comments


A Hobbyist Tackles Windows Azure Mobile Services

So everything's going mobile. We'll all hook into the cloud. Now it's touch-happy Windows 8 and an emphasis on Windows Store apps built with JavaScript and HTML5. It's inevitable, and I get that. But what's a hobbyist programmer like myself going to do, after spending a lot of time trying to learn the Microsoft .NET Framework and finally getting to the point where I can create interesting desktop applications?

Start over.

I don't like JavaScript. Never have. I don't get it--all those functions within functions and spaghetti code I can't figure out. C#/.NET seems a lot more organized and understandable.

But that's just me. More important, what about you, the professional developer making a living in the Microsoft ecosystem?

Well, the company is trying to smooth the transition. Take, for example, the new Windows Azure Mobile Services (WAMS) preview, which I've been playing around with. To recap, this is a Microsoft effort to simplify back-end development for your mobile cloud apps, targeting developers who want to focus on the client side of things and not worry about the nitty-gritty details of interacting with a database and such.

I found a couple things interesting about this initiative. First of all, it's yet another indication of Microsoft's attempt to be part of the transition from a world of PC desktops to mobile devices hooked into the cloud, obviously, along with becoming a good open source citizen.

Second, it's yet another indication of Microsoft's goal to simplify programming, making it more accessible to the not-professionally-trained masses. It kind of feeds into the whole "can anyone be a programmer" debate, which recently garnered more than 760 comments on Slashdot in response to an ArsTechnica.com article.

I decided to see if WAMS delivers, because, of course, if I can do it, anyone can do it. Well, I can do it; it's that easy. I started with some beginner tutorials available at the WAMS developer site, complete with a link to a free Windows Azure trial account to get you started. You also have to enable the WAMS preview in the account management portal. And you need to download the WAMS SDK, which, though clearly targeted at the mobile arena, could only build Windows 8 apps at the time I started playing around with it--though Windows Phone, Android and iOS support was promised soon. (Update: Microsoft partner Xamarin announced an open source SDK for MonoTouch for iOS and Mono for Android, available on GitHub, which also hosts the open source WAMS SDKs and samples.)

The first tutorial, "Get started with Mobile Services," is a simple "TodoList" app, letting you view, add and check-off/delete items from a list of things to be done. First you have to create a service. You need to fill in specific things such as the URL of your Windows Store app, database to use and region (though, curiously, the "Northwest US" region illustrated in the tutorial isn't available yet--you can only use "East US"). In this tutorial you create a new database server and table, instead of use an existing one.

After setting up the service, you create a new app to use it. The management portal includes a quickstart to do this. The quickstart can also guide you through the process of connecting an existing app, which basically just requires adding some references and a few lines of code to connect to your database table, which you make via the portal.

There are also tutorials available for getting started with data, validating and modifying data using server-side scripts, adding paging to queries, adding authentication, adding push notifications and more. These include downloadable projects for C# or JavaScript apps. (Yes, C# and .NET aren't going away; I exaggerated a little bit earlier, but you know what I mean.)

These beginner tutorials all involved setting up a super-simple new database or using a built-in data source such as a collection for your data. I usually check out a bunch of different tutorials when investigating a new technology, mixing and matching and trying different things until I usually get it to work right through sheer brute force, trial-and-error. I wondered about the use of an existing database. I couldn't find nearly as much guidance for that scenario, so I thought I'd explore it further.

I turned to the trusty AdventureWorks example database. WAMS uses ordinary Windows Azure SQL Databases, so I used the SQL Database Migration Wizard to generate a script to build the database in the cloud. You just need the info to connect to the server you set up via WAMS.

Once the wizard is done and your database is visible from the WAMS portal--you can't use it. You have to do a few different things to get it working. That process is described by Microsoft's Paul Batum in response to a Sept. 6 reader query in the WAMS forum. Basically you have to connect to the database management portal, reached from the regular Windows Azure management portal, to alter the schema so WAMS can use it. I wanted to use the AdventureWorks HumanResources.Employee table, and the WAMS service I set up was called "ram," so I ran this "query" from the database management portal:

ALTERSCHEMA ram TRANSFER HumanResources.Employee

That changed the schema of just that one table, of course, as you can see in Figure 1.

Changing the schema of an existing database table to match your WAMS service.
[Click on image for larger view.]
Figure 1. Changing the schema of an existing database table to match your WAMS service.

That would be quite tedious to do for every table, but there's probably some batch command or something that can change them all. I also had to change the existing primary key "EmployeeID" column to a lowercase "id," which WAMS requires for everything to work correctly, such as the browse database functionality (I'm not sure if anything else is broken). But WAMS still didn't know about the database, so I had to use the portal to "create" a table named "Employee," exactly as demonstrated in the tutorials where you set up a new database from the portal. After a few seconds, WAMS recognized the database and populated the new table with records. If your primary key column is "id," you can browse the database, as shown in Figure 2.


[Click on image for larger view.]
Figure 2. Browsing your database table via the management portal.

Having an existing database at the ready, I used the portal "Get Started" page to grab the information I needed to connect to WAMS from an existing app (it also shows you how to create a brand-new app, which involves creating a database table and downloading a prebuilt Visual Studio solution all set up to use it). This was fairly straightforward. Figure 3 shows the steps and code for C# apps, while Figure 4 shows the process for JavaScript apps. Heeding the winds of change, I took the JavaScript route.


[Click on image for larger view.]
Figure 3. The steps to build a C# app.

[Click on image for larger view.]
Figure 4. The steps to build a JavaScript app.

With the MobileServiceClient variable "client" obtained from the WAMS portal as shown in Figure 4, I simply had to grab the table, read it and bind the results, assigned to the dataSource property of a WinJS.Binding.List, to the ListView's itemDataSource:

var empTable = client.getTable('Employee') 
  .read() 
   .done(function (results) { 
     empItemsListView.winControl.itemDataSource = 
       new WinJS.Binding.List(results).dataSource; 
 });

The read function uses one of four server-side scripts (JavaScript) that WAMS provides for insert, read, update and delete operations (because there's no System.Data namespace to use).

You can customize the scripts as you wish. For a contrived, impractical example, to query the AdventureWorks Employee table and return only three "Production Technician – WC10" employees who are female and single, I changed the read script to this:

function read(query, user, request) { 
  query.where({Gender: 'F', MaritalStatus :'S', Title : 
    'Production Technician - WC10'}) 
  .take(3); 
   request.execute();
}

Note that a mssql object is also available for situations where a more complex SQL query might be needed. With that object, the read script above could be written as:

function read(query, user, request) { 
  mssql.query("select top 3 * from Employee 
   where Title = 'Production Technician - WC10' 
   and Gender='F' 
   and MaritalStatus = 'S' ", [], { 
     success: function(results) { 
       request.respond(statusCodes.OK, results); 
     } 
   });
}

Normally, of course, the server-side scripts would be used for validation, custom authorization and so on. Such query customization as in my contrived example would be handled from the client. For example, the functionality of my contrived example would be duplicated in the MobileServices.MobileServiceClient's getTable function from the client, like this:

var empTable = client.getTable('Employee') 
   .where({ Title: 'Production Technician - WC10', MaritalStatus: 
      'S', Gender: 'F' }) 
   .take(3) 
   .read() .done(function (results) { 
     empItemsListView.winControl.itemDataSource = 
       new WinJS.Binding.List(results).dataSource; 
 });

WAMS uses a REST API, so the above function call emits the following GET request header to the server, as reported by Fiddler, the free Web debugging tool:

GET /tables/Employee?$filter=(((Title%20eq%20
'Production%20Technician%20-%20WC10')%20
and%20(MaritalStatus%20eq%20'S'))%20
and%20(Gender%20eq%20'F'))&$top=3 HTTP/1.1

That returns the JSON objects shown in Figure 5, as reported (and decrypted) by Fiddler.


[Click on image for larger view.]
Figure 5. The JSON objects returned by WAMS, as seen in the Fiddler Web debugger.

The resulting ListView display when I hit F5 in Visual Studio is shown in Figure 6.


[Click on image for larger view.]
Figure 6. The final product: database information displayed in a ListView.

I didn't explore the tutorials much beyond the basics because I was primarily interested in the new aspect of connecting to an existing database, and this proved the concept. But for mobile apps, obviously, push notifications are important and, as mentioned, some tutorials are available through the portal to cover that functionality and user authentication. The push tutorial, however, uses C#, not JavaScript. One reader commented on the push notification tutorial, "Where is the JS version of this documentation? I am a HTML developer." There was no reply.

Not to be outdone, other readers in the .NET camp complained about the lack of C# code, specifically for server-side scripting, in the WAMS support forums. In reply to the question of whether or not C# or another .NET language would be supported for scripting, WAMS guru Josh Twist replied: "we have no firm plans right now but certainly haven't ruled this out. Again, we're listening to customer feedback and demand on this (you're not the first person to ask) so thanks for posting." Several other readers also weighed in on the subject, with one saying: "I agree. This seems kinda mismatched. The client allows script or C#. But the server side is JavaScript only. Seems the server side would be even a better fit with C#."

There were also some requests for Visual Basic tutorials. In fact, that was the post with the most total views in the support forum. Twist replied, "This is on our list of things to do." (Hmm, I wonder if that "to do" pun--all the tutorials are "to do" lists--was intended.) Microsoft's Glenn Gailey replied with links to the Visual Basic versions of the "Get started with data" tutorial and the WAMS quickstart project.

I'm sure such issues will get ironed out as the WAMS preview continues and user feedback is collected, so give it a try and let Microsoft know what you think. As a hobbyist, I'm certainly giving it the thumbs up. In fact, I was gratified that Twist mentioned the hobbyist as one of three distinct personas to whom WAMS is relevant. Twist listed these three roles in his introduction to WAMS on his The Joy of Code site. He said Microsoft research found that about two-thirds of developers were interested in the cloud but suffered from "cloudphobia," in that "they wanted backend capabilities like authentication, structured storage and push notifications for their app, but they were reluctant to add them" for lack of time, expertise and so on. In addition to the hobbyist, the other two developer types he mentioned were the "client-focused developer," targeted by WAMS, and the "backend developer," who is already experienced in writing server code.

So I'm looking forward to seeing how WAMS matures (hopefully before my 3-month trial Windows Azure subscription expires). As Twist said in a Joy of Code post, "we're working on making it even easier to build any API you like in Mobile Services. Stay tuned!"

What do you think about WAMS? Share your thoughts by commenting here or dropping me a line.

Posted by David Ramel on 10/03/2012 at 9:03 AM0 comments


SQL Server Data Tools Updated for Visual Studio 2012

Microsoft recently updated SQL Server Data Tools (SSDT) with support for Visual Studio 2012, improved LocalDB functionality and new SSDT Power Tools.

SSDT, if you're unfamiliar with it, is a hodgepodge of functionalities targeted at easier database project development from within Visual Studio. For example, one main feature is that data devs no longer have to switch back and forth between SQL Server Management Studio and Visual Studio, working entirely in the latter.

The September 2012 update comes in versions for Visual Studio 2012 and Visual Studio 2010. The big news, of course, is that SSDT now plays nicely with Visual Studio 2012. I wrote earlier about the problems users (including me) were having getting the two to work together. "This release of SSDT supports the Visual Studio 2012 shell," wrote Janet Yeilding in the team blog post announcing the update. "SSDT September 2012 contains several bug fixes to the SSDT version that shipped in Visual Studio 2012 and this release can be applied as an update on top of Visual Studio 2012 Professional, Premium, and Ultimate Editions.

The new SSDT release also includes configuration improvements for LocalDB, a streamlined, simplified version of SQL Express that speeds up data development by obviating the need for developers to set up full SQL Express instances on their machines. "We received feedback that SSDT's policy of creating a new LocalDB instance for each SSDT solution was too verbose, so we created a single LocalDB instance called Projects to host all the project debug databases," Yeilding said. "We've also enhanced SQL Server Object Explorer by surfacing the default LocalDB instance and enabling the deletion of LocalDB instances."

Other improvements include an updated Data-Tier Application Framework, support for non-standard ANSI_NULLs and QUOTED_IDENTIFIER properties, numerous bug fixes and new companion SSDT Power Tools.

The new SSDT Power Tools release, which works only with the new SSDT main release, also offers brand-new support for Visual Studio 2012. "For the first time, this Power Tools release provides a version of the power tools for Visual Studio 2012 in addition to the version for Visual Studio 2010," the team blog said. The new Power Tools come in separate installations for Visual Studio 2012 and Visual Studio 2010.

Besides the Visual Studio 2012 compatibility, the new Power Tools release has new functionality allowing the creation and deployment of Data-Tier Applications (*.dacpacs files), described in the blog post as "the core artifact of the DAC Framework."

Posted by David Ramel on 09/26/2012 at 9:03 AM5 comments


Microsoft Readies SQL Server 2012 SP1 CTP4

Microsoft today released a new Community Technology Preview for SQL Server 2012, Service Pack 1. The CTP4 includes improved AlwaysOn Availability Group migration, better query capabilities over XML data and a better SQL Server Management Studio (SSMS) for Express users.

The AlwaysOn Availability Group cross-cluster migration lets users move to a higher-level version of Windows Server cluster, which keeps intact service-level agreements when upgrading to the higher-level version, such as Windows Server 2012 cluster.

The new Selective XML index can boost query performance over XML data, resulting in faster indexing of big XML data workloads, along with improving scalability by lowering storage costs of an index, the company said.

Finally, users of Express editions now get a full-featured edition of SSMS, matching the functionalities in the SSMS included with full editions of SQL Server. The Express editions include SQL Server Management Studio Express, SQL Server Express With Tools, SQL Server With Advanced Services and SQL Server Express COMP.

The last update, CTP3 was released in July, including support for self-service business intelligence functionality in Office and SharePoint 2013 Preview.

Posted by David Ramel on 09/20/2012 at 1:15 PM0 comments


Cloud Devs Favor Windows Azure (Survey)

Microsoft has long been trying to get developers to store their data in the Windows Azure cloud, and the effort seems to be paying off. More cloud developers use Windows Azure than any other platform, according to a new Evans Data survey.

The survey costs money to actually view, of course, so all we can report to you are the highlights from a news release (and even it requires registration). It states that 36 percent of developers actively targeting a cloud used Windows Azure. The next most popular platform was Google Storage, with 29 percent, and Amazon Web Services closely followed at 29 percent.

"Microsoft was very aggressive with its introduction of Azure to the development community a few years ago and that has paid off," said Evans Data CEO Janel Garvin. "Additionally, the large established MSDN community and the fact that Visual Studio is still the most used development environment are huge assets to Microsoft in getting developers to adopt the Azure platform," she said. "However, cloud platform use is still very much fragmented with lots of players laying claim to small slivers of share. It will take more time before a clear landscape of major cloud vendors shakes out."

For those willing to pony up the money, the survey's table of contents indicates a section of the survey is devoted to "big data and database technology," including types of databases being used, best tool suite for big data and more.

One strange thing I found in the news release was that 27 percent of cloud coders who develop in a cloud service end up deploying their apps to another service. Slightly more than 50 percent deployed their apps to that same service in which they developed. I would've expected that percentage to be higher.

Evans Data said the Cloud Development Survey is conducted twice per year, and more than 400 developers responded.

Why do you think Windows Azure is the most popular platform? Why on earth would 27 percent of developers use one service for their actual development and then deploy their apps to a different service? Share your thoughts by commenting here or dropping me a line.

Posted by David Ramel on 09/13/2012 at 1:15 PM1 comments


Data Devs Demand Local Access in Windows Store Apps

Stop me if you've heard this one: Microsoft introduces a new technology and developers complain about lack of local database access.

Yes, it happened last year with Windows Phone. And Microsoft responded with SQL Server Compact Edition in its "Mango" update.

Now it's happening with Windows 8. "We should at least have the ability to connect to an embedded database like the one they added to the WP7 Mango update," said one developer on the customer feedback site for Visual Studio. This comment was under a heading of "Make System.Data available to Metro style apps," with 163 votes as of this writing. But there are plenty more likewise sentiments around the Web:

  • "I personally would have liked to have seen a desktop Metro app that could connect to a SQL Express Database for instance but it's not currently in the cards without jumping through hoops," said a reader on stackoverflow.com.
  • "I'm stunned to find out that there is no way [to communicate with *any* SQL Server instance]--how are people meant to build LoB apps if they can't communicate with their databases?" asked a reader on a Microsoft forum site.
  • "WinRT is moving in the *wrong* direction by *removing* the System.Data namespace," said a reader on itwriting.com.

Well, you get the idea.

Basically, in your Windows Store (formerly called Metro) apps, you get your database access via the cloud/network/service. But some developers complained about that model, citing intermittent connectivity problems and the like.

And there are some options for local access. For example, IndexedDB "provides Metro style apps using JavaScript with a way to easily store key-value pairs in a database."

And for non-JavaScript apps, there are a few other file-based options for local storage. You can also check out "siaqodb--local database for Metro style apps." And the Windows Dev Center lists some "data access and storage APIs [that] are supported for developing Metro style apps."

Those options certainly aren't on par with SQL Server, of course. As Microsoft's Rob Caplan explained on a forum posting, "There aren't any SQL-like databases provided in-box, but you can use a 3rd party database such as SQLite."

Indeed, SQLite seems to be the most popular option. Tim Heuer has written extensively on how to use it for Windows Store apps. Some developers worried about passing Windows Store muster with apps built with SQLite, but Heuer reported in June that, "Yes, SQLite will pass store certification as long as compiled correctly. The current binaries on the SQLite site aren't the ones built for WinRT, but you can build it yourself and use it." And that same month, the SQLite site reported "SQLite version 3.7.13 adds support for WinRT and metro style applications for Microsoft Windows 8."

So that's probably your best workaround for right now. But stay tuned. As one stackoverflow.com reader said: "an embedded Microsoft SQL CE is not supported. There has been no announcement yet as to its support--but like Windows Phone, we can only assume this support is in the pipeline."

What do you think? Is this a big problem? Do you know of other workarounds? Should data developers just be patient and wait for a "Mango"-like update to Windows 8? Comment here or drop me a line.

Posted by David Ramel on 09/05/2012 at 1:15 PM10 comments


Microsoft Eases Mobile Data Access in the Cloud

The recent announcement of Windows Azure Mobile Services included some interesting stuff for you data developers.

As explained by Scott Guthrie, when Windows Azure subscribers create a new mobile service, it automatically is associated with a Windows Azure SQL Database. That provides ready-made support for secure database access. It uses the OData protocol, JSON and RESTful endpoints. The Windows Azure management portal can be used for common tasks such as handling tables, access control and more.

Guthrie provided a C# code snippet to illustrate how developers can write LINQ queries--using strongly typed POCO objects--that get translated into REST queries over HTTP.

The key point about all this is that it enables data access to the cloud from mobile or Windows Store (or desktop) apps without having to create your own server-side code, a somewhat difficult task for many developers. Instead, developers can concentrate on the client and user UI experience. That greatly appeals to me.

In response to a reader query about what exactly is "mobile" about Mobile Services, Guthrie explained:

The reason we are introducing Windows Azure Mobile Services is because a lot of developers don't have the time/skillset/inclination to have to build a custom mobile backend themselves. Instead they'd like to be able to leverage an existing solution to get started and then customize/extend further only as needed when their business grows.

Looks to me like another step forward in the continuing process to ease app development so just about anybody can do it. I'm all for it!

When asked by another reader why this new service only targets SQL Azure (the old name), instead of also supporting BLOBs or table storage, Guthrie replied that it was in response to developers who wanted "richer querying capabilities and indexing over large amounts of data--which SQL is very good at." However, he noted that support for unstructured storage will be added later for those developers who don't require such rich query capabilities.

This initial Preview Release only supports Windows 8 apps to begin with, but support is expected to be added for iOS, Android and Windows Phone apps, according to this announcement. Guthrie explains more about the new product in a Channel9 video, and more information, including tutorials and other resources, can be found at the Windows Azure Mobile Services Dev Center.

What do you think of this new Microsoft offering for mobile data developers? Comment here or drop me a line.

Posted by David Ramel on 08/29/2012 at 1:15 PM1 comments


Free eBooks for Data Developers

When Microsoft blogger Eric Ligman recently posted a collection of free eBooks, he found the topic so popular that he followed up with even more.

"The response and feedback I am receiving about it is incredibly positive about how much you liked it," he wrote.

So I thought I'd check out what free offerings Microsoft has that would be interesting to data-related developers. Here are a few representative examples from his first post:

  • Transact-SQL Data Manipulation Language (DML) Reference
  • Data Analysis Expressions (DAX) Reference
  • SQL Server 2012 Tutorials: Analysis Services - Multidimensional Modeling

There aren't as many data-related resources in his second post, but you'll surely find the SQL Server 2012 Developer Training Kit useful. There are also development titles available for data mining, Windows Azure, Dynamics CRM, SharePoint, Windows Phone, LightSwitch and more with data-related components.

They come in a variety of downloadable formats, with many available in your choice of PDF, EPUB and Mobipocket, and some in Word (DOCX) and Open XML Paper Specification (XPS).

Everybody loves free stuff, of course, and reader Anthony expressed the views of many when he commented "Thanks, Eric! … really good gesture. It helps many a MS technology developer." So please go ahead and share your favorite free information sources for data developers. Comment here or drop me a line.

Posted by David Ramel on 08/23/2012 at 1:15 PM4 comments


Entity Framework Peeves? Code it Yourself!

A lot of data developers were frustrated with missing features of the Entity Framework as it continued to evolve in the past few years, such as the oft-requested enum support, for example.

Well, that support is included in the upcoming EF 5 release, of course. But beyond that, Microsoft -- in yet another implementation of its turnabout embrace of the open source movement -- has put its flagship object-relational mapping source code up for grabs on CodePlex, the company announced recently.

On the open source software hosting site are the EF runtime and Power Tools, with more to come. And developers have been busy, with changes being made right up until Monday, with a code contribution "Making IQueryable implementation on ObjectQuery explicit again," and 26 forks.

CodePlex reported almost 106,000 page views and 2,475 software downloads, with 560 people following the project.

So if there's something you don't like about EF, you can now do more than submit a bug report a request a feature -- you can code that feature yourself!

As Scott Guthrie noted in his announcement of the open source move: "Community contributions will also be welcomed, so you can help shape and build Entity Framework into an even better product."

I'd be interested to see how much non-Microsoft code makes it into release software, though. This obviously isn't a weekend project for a hobbyist hacker. I can't imagine many people passing muster and meeting Microsoft's strict coding requirements. As the company states on its "Ways to Contribute" CodePlex page:

Note that all code submissions will be rigorously reviewed and tested by the Entity Framework Team, and only those that meet an extremely high bar for both quality and design/roadmap appropriateness will be merged into the source.

Anyone up to the challenge? I'd love to hear from contributors to the project and share the nuts and bolts of the process with readers (Microsoft lists 13 fairly involved steps to follow in order to contribute code). If you've contributed or tried to, or know someone else who has, please comment here or drop me a line.

Posted by David Ramel on 08/16/2012 at 1:15 PM0 comments


Really? Still Fighting About SQL vs. NoSQL?

I was quite surprised to see a recent Slashdot post titled "SQL vs. NoSQL: Which is Better?" My first thought was: "Really? Hasn't this already been settled?" The politically correct answer to the question, of course, goes along the lines of: "Neither is better. They are separate tools. As with all tools, each addresses specific problems and should be used by programmers according to their needs ...." And so on.

I haven't combed through the 300-plus comments on the post yet, but I'm willing to bet a bunch of them pretty much say the same thing.

The post references an extensive Slashdot article by experienced programmer Jeff Cogswell, who writes: "Tech pros debate the merits of SQL vs. NoSQL. It's a fight worth examining on the programming level."

So this gets down to the nuts-and-bolts of the programming aspect, kind of a different take. He discusses using the two in Node.JS, in C# and much more. He likes MongoDB, but concludes basically with my politically correct (and I thought, by now, universally accepted) answer above, which I wrote before reading his article. He writes:

So I use both. For some projects, I use Oracle, MySQL, or SQL Server (yes, I've used all three on different projects) for clients who have large amounts of data that fit well into a tabular structure, and who will be doing queries that pull back thousands of records per query. (Think financial applications.) For software that's more oriented towards pulling individual objects (such as an online organizer tool, or an online content management system that I'm presently working on), I opt for something like MongoDB.

Now I look at the post comments and the very first one reads: "SQL and NoSQL are different, with different use cases."

Well, duh!

So why are we still debating this issue? Please take a look at the post and Cogswell's article and share what you think. Does focusing on the coding add anything to the debate? Comment here or drop me a line.

Posted by David Ramel on 08/07/2012 at 1:15 PM5 comments


.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.