| Comments

I’ve previously posted a few things about SQLite including a HOWTO on how to build from their source code.  If you still want to build your own DLL from their source code that is totally fine, but not necessary in most every single case I’ve seen in app usage.  One of the challenges I noted is that since SQLite is a native component and if you are a managed (.NET) app you can’t be architecture neutral anymore (AnyCPU).  What this means is that you have to build your app for each architecture you want to support: x86, x64 and ARM.  The flow of this using SQLite3.dll was that you would have to package, change the DLL, re-package. 

Not anymore.

In working with the great folks on the SQLite team, they’ve packaged the binaries up (for Windows 8 apps) in a nice installer using the Extension SDK format.  What this means is you now add a ‘reference’ to the SQLite binary and based on the architecture being built for your package, it will pull in the right DLL without you having to manage that yourself.  Here’s some step-by-step…

Installing the SQLite for Windows Runtime package

The first thing you want to do is install the package.  You can do this from within Visual Studio itself in all editions.  From the Tools menu, choose Extensions and Updates and then choose the Online section (on the left of the dialog) and search for ‘sqlite’ in the search term.  This will show you the SQLite for Windows Runtime package:

Visual Studio Extensions dialog

Click install my friends.  You will be prompted to restart Visual Studio which you should do.  Go ahead…I’ll wait.

Using the new package in your C#/VB app

Now that you have the SQLite for Windows Runtime package installed in your Visual Studio environment, you want to use it.  In a managed (.NET) app you would do the following steps.

First, create your app (e.g., a Blank XAML app is fine).  Once within your app, use the Add Reference mechanism to get to the next step.  Now you will not be browsing for any DLL directly like you would in a traditional .NET.  What we are adding here is a reference to the Extension SDK…not the library itself, a small but important distinction.  Once in the Add Reference dialog choose the Windows\Extensions view (see on left) and you’ll see SQLite for Windows Runtime.

To correctly use this in a managed app you’ll need to select that *and* the C++ runtime as seen below:

Add Reference dialog

The reason for this is to ensure that your app declares the right dependencies that are needed for the app to run.  The likelihood of the C++ runtime not being on a Windows machine where your app will be installed is extremely rare, but you need to declare this anyway.  Failure to do so will fail your app certification tests.

Now with this involved you can grab a managed wrapper to call the SQLite APIs as I’ve previously described in my HOWTO video.  I personally recommend the sqlite-net library (available via NuGet) to make this easier for you.

NOTE: sqlite-net is available as source in C#.  If you are using a VB app, you would first need to compile the sqlite-net source in a separate DLL so you can just add a reference to that in your VB project.

Using the sqlite-net library you can perform tasks using a model similar to LINQ2SQL where you can have types represent database entities:

   1: public sealed partial class MainPage : Page
   2: {
   3:     public MainPage()
   4:     {
   5:         this.InitializeComponent();
   6:         LoadData();
   7:     }
   8:  
   9:     public void LoadData()
  10:     {
  11:         var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");
  12:         using (var db = new SQLite.SQLiteConnection(dbPath))
  13:         {
  14:             db.CreateTable<Person>();
  15:  
  16:             db.RunInTransaction(() =>
  17:                 {
  18:                     db.Insert(new Person() { FirstName = "Tim", LastName = "Heuer" });
  19:                 });
  20:         }
  21:     }
  22: }
  23:  
  24: public class Person
  25: {
  26:     [SQLite.AutoIncrement, SQLite.PrimaryKey]
  27:     public int ID { get; set; }
  28:     public string FirstName { get; set; }
  29:     public string LastName { get; set; }
  30: }

Now you just need to specify your architecture for your app (x86, x64, ARM) and when you build, the appropriate sqlite3.dll will be packaged in your app automatically.  This also happens during the packaging step for the store so the right item is included for each architecture-specific package.

WARNING: Do not package in DEBUG mode as you will fail certification.  Ensure that you build/package in RELEASE mode prior to submitting to the store or running the app certification toolkit (also referred to as WACK in some places).  You will get false positives if you are compiled in DEBUG mode.

This should make your development much easier without having to  swap out DLL files each time.

Using the new package in your C++ app

If you are a C++ developer you will do the same steps for installing and adding as a reference to your app.  In the C++ project system there is no 'Add Reference’ menu on the project context menu, but you will choose References and then the Add Reference button shows up.

Once you have the reference to the SQLite SDK then as a C++ developer you can just #include the header and go to work:

   1: #include <sqlite3.h>

Since C++ projects are already architecture-specific you don’t have to worry about the AnyCPU situation because there isn’t one!  You’ll get IntelliSense on the API by just including the header.  The Extension SDK mechanism already includes the C++ props file to help the project system know where to get the header for development and the lib for linking when building.  Most C++ developers will interact with SQLite using the native APIs and not need any additional wrapper library.

Using the new package in your JavaScript app

If you are using JavaScript/HTML to developer your app, you will follow the same flow as the C#/VB flow.  Add a reference to both the SQLite SDK as well as the C++ runtime (to declare the dependency).  As to accessing SQLite in your app, you’ll need a WinRT wrapper library to do that.  The one that seems to be gaining favor is the SQLite3-WinRT library on GitHub.  I have not personally used this, but seen a lot of people using this.  It allows you to use the JavaScript programming model in a familiar way:

   1: var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\db.sqlite';
   2: SQLite3JS.openAsync(dbPath)
   3:   .then(function (db) {
   4:     return db.runAsync('CREATE TABLE Item (name TEXT, price REAL, id INT PRIMARY KEY)');
   5:   })
   6:   .then(function (db) {
   7:     return db.runAsync('INSERT INTO Item (name, price, id) VALUES (?, ?, ?)', ['Mango', 4.6, 123]);
   8:   })
   9:   .then(function (db) {
  10:     return db.eachAsync('SELECT * FROM Item', function (row) {
  11:       console.log('Get a ' + row.name + ' for $' + row.price);
  12:     });
  13:   })
  14:   .then(function (db) {
  15:     db.close();
  16:   });

If using JavaScript this might be the way to go for your app.

Summary

I’m very glad the SQLite team worked to get this deployment package out there.  I think for some Microsoft developers, using SQLite is fairly new and this SDK package will make it easier to ensure you have the right bits at the right time.  Of course you are free to do it your own way, but I think this will ease the process a little bit.

Why no NuGet? Well, the NuGet infrastructure right now doesn’t support some of these semantics around native components to deal with headers, linking and architecture-specific deployments.  We’ll continue to work with them to see if we can drive these changes into that platform.

So please feel free to download via the Visual Studio ‘Extensions and Updates’ option from within VS, download directly from the Visual Studio Gallery, or download from the SQLite site themselves.  Once installed, once an update is available, VS will notify you that an update is available and you can install it.

Hope this helps!

| Comments

At the “Developing Windows 8 Metro style apps with C++” event that happened on 18-May-2012, we saw and heard some very interesting things.  If you were watching live then hopefully you didn’t see how I tried to work through my presentation while my disk was suspiciously guzzling every last byte until it eventually ran out of space!  But I digress…

During the keynote presentation by Herb Sutter, we brought up several customers that are well-known in the native code world to talk about their experiences with Metro style apps and C++/Cx.  In particular hopefully this one caught your eye:

SQLite case study slide

That’s right, the team for SQLite was there to discuss how they were able to take their existing Win32 codebase and ensure that it worked well on Windows 8 as well as for Metro style apps.

SQLite is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. SQLite is currently found in more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen().– Source: http://www.sqlite.org/about.html

Dr. Richard Hipp, the founder of SQLite, was on hand to announce the availability of the experimental branch they’ve been working on as well as that when the Release Preview of Windows 8 is made public that he will merge this code to the main trunk for SQLite, making it supported by them.  This is a really great thing for developers as SQLite has been a proven embedded database for numerous platforms and many, many customers.  The team prides themselves on testing and has millions of test cases that are validated each release.

As a Windows (and perhaps more specifically .NET) developer, you may not have had to build any lib from Open Source before of this type (i.e., native code) and since a binary is not being provided yet until Release Preview for Windows 8, I thought I’d share my tips on building the experimental bits, adding them to your projects and then using them with a client library.

UPDATE: I created a HOWTO video demonstrating the following steps of building and using from a C# Metro style app.

Building SQLite from source

If you are looking for the sqlite3.dll with this WinRT support anywhere on the sqlite.org site, you won’t find it.  You will have to build the source yourself. UPDATE:Since the origination of this post the SQLite team has released a version already compiled for 32/64-bit.  I highly recommend you get the code from the source rather than from any third party site.  Microsoft has worked with the team at SQLite to ensure compatibility and store certification.  For most .NET developers who have never grabbed native code source from an Open Source project and had to build it before, the maze of knowing what you should do can be confusing.  I’ve put together a cheat sheet on building SQLite from source for a Windows (and .NET developer) and put it on my SkyDrive: Building SQLite from source.  The OneNote I have has the details you need for the tools that will be required. 

In a nutshell you’ll need:

  • Visual Studio (easiest way to get the C++ compiler)
  • ActiveTcl
  • Update for gawk.exe
  • Fossil (the source control system used by SQLite)

Once you have these, you are ready to build SQLite.  Again, I’ll defer to my instructions on the details of setup.  Once your setup is complete, from a developer command prompt you’d run:

   1: nmake -f Makefile.msc sqlite3.dll FOR_WINRT=1

The result of this will give you basically 4 files that are of likely most importance to you: sqlite3.c, sqlite3.h, sqlite3.dll, sqlite3.pdb.

NOTE: The resulting pdb/dll that is built will be architecture specific.  If you used an x86 command prompt then that is what you have.  Be aware of this (as noted later in this post).

At a minimum you’ll want sqlite3.dll if you are a .NET developer, but as a native code developer you will likely be more interested in the others as well.  After this step, you now have a Windows Store compliant version of SQLite to include in your applications.

Runtime versus client access

Now at this point is where I’ve seen some confusion.  Folks are asking How do I include this, don’t I need a WinMD file to reference?  Let me diverge a bit and explain a few things.

The result of compiling the binary above produces primarily one thing…which I will call the “Engine” in this post.  This is the SQLite runtime and logic required to create/interact with SQLite database files.  This is NOT, however, an access library, which I will call the “Client” in this post.  If you are a managed code or JavaScript developer, at this point, all you have is the Engine, the database runtime.  You have no Client to access it.

Now, if you are a C++ developer you are probably okay at this point and don’t care much about what I have to say.  You have the header and are likely saying I’ve got the header, get out of my way.  And that is okay.  For C++ developers I think you’ll likely be accessing the database more directly through the SQLite APIs provided in the header.

I call out this distinction because this step provides you with the database engine you need to create a database and have it be store-compliant.  So if you are a JavaScript or .NET developer, what are you to do?  Stay tuned…let’s first get the Engine included in our app package.

Including SQLite in your app package

As I noted above, as a native code developer, having the header, lib and c file you may be okay and don’t care to read this.  I  personally think, however that I’d always just want the binary from my vendor rather than always include source in my files.  That said, the SQLite build process does product the amalgamation (sqlite3.c) you can just include in your native code app.

If you choose to go the binary file route (sqlite3.dll) then you need to simply follow a few principles to ensure that it is included in your package when you build your app/package.  These principles are simple:

  • include the architecture-specific binary
  • ensure the sqlite3.dll is marked as Content in your project
  • ensure you note that you now have a native code dependency (not needed if you are already a C++ Metro style app)

These two items will ensure that when you build (even for debug via F5) or when you package for the store, that the Engine is included in your package.  Marking as content is simply ensuring that after you add the file to your project, ensure the file properties note that it is content.  In .NET apps this is making the Build Action property Content.  In JavaScript applications ensure the Package Action is marked Content.

Declaring the native code dependency means you simply add a reference to the Microsoft C++ Runtime Library via the Add Reference mechanisms in .NET and JavaScript applications.  By doing this (and again, this is a requirement of including SQLite in your app) you now cannot be architecture-neutral. This means no more AnyCPU for .NET.  When producing a package you’ll have to produce architecture-specific packages before uploading to the store.  Not to worry though as Visual Studio makes this incredibly easy.  The one thing you’ll have to remember though is that you’ll have to change the sqlite3.dll before building the packages as the DLL is architecture-specific itself.

Now this all should be easier right?  Wouldn’t it be nice if you could just Add Reference to the Engine?  I personally think so.  I’ll be working with the SQLite team to see if they will adopt this method to make it as easy as this:

SQLite Extension SDK

In doing so, you as a developer would just add a reference to the Engine and then during build time Visual Studio (well MSBuild actually) will do all the right things in picking up the architecture-specific binary for your app.  No fiddling on your part.  This method also makes it easier for C++ developers as well as a props file would automatically be merged to include the .lib for linking and the header file for development.  This method uses the Visual Studio Extension SDK mechanism that was introduced in Visual Studio 11.

NOTE: Again note that as a managed (.NET) app I’d also have to ensure that my package includes the Microsoft C++ Runtime package in order for this to work and pass store certification.

Native code developers may scoff at this approach, but I could get started in 2 steps: Add Reference, #include.  No tweaking of my project files at all because the Extension SDK mechanism in VS does all this for me behind the scenes.

So why don’t I just give you the VSIX to install and accomplish the above?  Well simply, because SQLite is not my product and we’ve had a good relationship with their team and I want to make sure they understand the benefits of this method before jumping right in.  I hope that they will like it as I think it makes it *really* simple for developers.

Accessing the Engine from your app

Great, you’ve compiled the bits, you’ve understood how to ensure sqlite3.dll gets packaged in your application…now how do you use it!!!  Here’s the assessment of where we are at for Metro style apps as of the writing of this post.

C++ app developers: I think most C++ developers will get the header file (sqlite3.h) and be okay on their own with SQLite.  At this stage for them there doesn’t appear to be a real huge benefit of a WinRT wrapper to use the Engine.

.NET developers: I’ve messed around with a few libraries and believe the sqlite-net project to be the most favorable for what I believe most use cases will be for SQLite and Metro style apps.  This is a .NET-only library (not WinRT) but is basically a “LINQ to SQLite” approach.  The Mono team uses this one as well.  The necessary .NET 4.5 Core changes are already included in the project on github.  So you just need to get the SQLite.cs file and include it in your project.  Using this library allows you to write code like this:

   1: public sealed partial class BlankPage : Page
   2: {
   3:     public BlankPage()
   4:     {
   5:         this.InitializeComponent();
   6:  
   7:         string dbRootPath = Windows.Storage.ApplicationData.Current.LocalFolder.Path;
   8:         using (SQLiteConnection db = new SQLiteConnection(Path.Combine(dbRootPath, "mypeople.sqlite")))
   9:         {
  10:             db.CreateTable<Person>();
  11:  
  12:             db.RunInTransaction(() =>
  13:                 {
  14:                     for (int i = 0; i < 10; i++)
  15:                     {
  16:                         db.Insert(new Person() { FullName = "Person " + i.ToString() });
  17:                     }
  18:                 });
  19:         }
  20:     }
  21: }
  22:  
  23: public class Person
  24: {
  25:     [AutoIncrement, PrimaryKey]
  26:     public int ID { get; set; }
  27:     public string FullName { get; set; }
  28:     public string EmailAddress { get; set; }
  29:     public double Salary { get; set; }
  30: }

This is clearly just a sample, but demonstrates the simplicity of the library. 

NOTE: In the snippet above you do want to make sure you are creating your database in a path that is accessible from the AppContainer.  The best place is in the app’s ApplicationData location.  When specifying a path to SQLite in Open() for creation, give an explicit path always to ensure you aren’t relying on a temp file creation.

Some may ask about System.Data.Sqlite and this cannot be used because of the dependency of ADO.NET which is not a part of the .NET Framework Core profile.

Now this leads us to JavaScript developers.  Currently, there is not easy way for you to access this.  The Developer and Platform Evangelism team are working on some samples that are not quite complete yet.  JavaScript developers will need a WinRT library in order to access/create/query the Engine.  There are some out there (I haven’t played around with any of these) that would be good to see if they meet your needs.  Here are some pointers:

At the C++ event we talked with the SQLite team about a WinRT client library and will continue to talk with them to see if this is something of interest.  SQLite has a great history of working with the community and have a desire to continue this.  In the meantime, there are options for you to get started.  Also note, that since these are WinRT libraries they could also be used from C++ and .NET in Metro style apps.  At this point though it is my personal opinion that existing .NET libraries for .NET offer more value (i.e. LINQ) than how these WinRT ones exist.

Summary

This was a great announcement that the SQLite team made for Metro style app developers.  WinRT provides some existing local storage mechanisms which you should explore as well, however none that have structured storage with a query processor on top of it.  I’m really glad that the SQLite team was able to make a few diff’s to their code to accommodate a few store compliance areas and continue to offer their great product to this new class of applications.  It is very simple to get started by ensuring you have the Engine and picking your Client of your choice and write your app using SQLite for some local/structured storage!

Hope this helps and stay tuned for the release preview of Windows 8!

| Comments

I should have known better honestly.  I’ve had one strike with cloud billing catching me by surprise and I’m not sure why I’m shocked it happened again.  This time, however, I thought I really did plan it out, pay attention to things and asked what I thought were the right questions.  Unfortunately I didn’t get the full answers.  This time I was stung by my shiny new SQL Azure service choice.

UPDATE 12-APR-2012: Based on comments I've received I feel the need to clarify that I'm not bashing Azure or cloud services in general here.  I don't think anywhere I indicated Azure was a crap product or that I hated it at all.  In fact, I indicated I was completely happy with the service offering.  My frustration was *only* with the fact that the pricing was unclear to me based on how I researched it...that is all and nothing more.  As many have pointed out, cloud services like Azure are extremely important in the marketplace and the ability to scale real-time with minimal effort is an exceptional feature.  *FOR ME* I currently don't have those needs so I couldn't justify the charges beyond what I had planned...that is all, nothing more.  My experience with SQL Azure was a positive one as a product.  Quick setup, familiar tools to manage, worry-free database management, great admin interface and a reliable data storage solution.  My architecture, however, just didn't prove ideal currently with my site not being in Azure as well.  When VM roles come out of beta I will be sure to evaluate moving sites there and plan better.

A while back I heard about the change in price for some Windows Azure services and the one that piqued my interest was the SQL Azure.  At the time it hit me right as I needed to move around some of my hosting aspects of my site.  The lure of the $5/month SQL Azure database (as long as it was < 100MB) was appealing to me.  The SQL server aspect of my site has always been a management headache for me as I don’t want to have to worry about growing logs, etc.

Stung by marketing

I followed the announcements to the http://www.windowsazure.com site and read the descriptions of the services.  I was immediately convinced of the value and heck, it was a service from my company so why shouldn’t I give it a try and support it?  When I began to set it up, however, there were questions being asked during setup and I started to get concerned.  I asked around about if this $5 fee was really the only fee.  I didn’t want to get surprises by things like compute time.  Perhaps I wasn’t asking specific enough questions, but all answers I got was that signs pointed to yes, that would be my only fee.

NOTE: As of this writing yes I am a Microsoft employee, but this is my own opinion and I realize that peoples’ expectations and results vary.  This is only my experience.  I’m not only an employee but also a customer of Microsoft services and in this instance a full paying customer.  No internal benefits are used in my personal Azure hosting accounts.

Yesterday I learned that wasn’t the case.  I received my first Azure billing statement and it was way more than I expected.  Yes my $5 database was there as expected, but also was suddenly “Data Transfer” charges of $55.

Trying to make sense of billing

I immediately tried to make sense of this billing.  I immediately remembered that I had created a storage account as well for a quick test and perhaps I forgot to disable/delete that service.  I logged into the management portal and saw that my storage account was properly deleted and nowhere to be seen.  But how to make sense of these charges from the past week then?  Luckily Azure provides detail usage download data so I grabbed that.  The CSV file I download did indeed provide some detail…perhaps too much as some of it I couldn’t discern, namely the one piece that I had hoped would help me: Resource ID.  This ID was a GUID that I thought pointed to a service that I used.  It did not, or at least that GUID was nowhere to be seen on my Azure management portal.

I contacted the billing support immediately to help.  I was able to talk with a human fairly quickly which was a plus.  The gentleman explained to me that I had a lot of outgoing data leaving the Azure data centers and that was the source of the costs.  He asked if I knew if anything was connecting to my SQL Azure instance externally.  Well, duh, yes it was my site!  He went on to explain that this constitutes “Data Transfer” and I’m billed at a per GB rate for any data that leaves the Azure data center. 

I took a deep breath and asked where this was documented in my SQL Azure sign-up process.  We walked through the site together and he agreed that it wasn’t clear.  After being put on hold for a while, I was assured I would receive a credit for the misunderstanding.  Unfortunately for Azure, the damage was done and they lost a customer.

Where the failure occurred

For me the failure was twofold: me for not fully understanding terms and Azure for not fully explaining them in context.  I say “in context” because that was the key piece that was missing in my registration of my account.  Let me explain the flow I took (as I sent this same piece of internal feedback today as well) as a customer once I heard the announcement about the SQL Azure pricing changes:

  • I received notice of updated SQL Azure pricing
  • I visited the site http://www.windowsazure.com for more information
  • I clicked the top-level “PRICING” link provided as that was my fear
  • I was presented with a fancy graphical calculator.  I moved the slider up to 100MB and confirmed the pricing on the side (no asterisks or anything)
  • I notice a “Learn more about pricing, billing and metering” link underneath the calculator and click it to learn more
  • I’m presented with a section of 10 different options all presented at the same level giving the appearance as unique services.
  • I choose the Database one and again read through and confirm the charge for the 100MB database option.
  • I click the “More about databases” link to double-verify and am presented with another detailed description of the billing

Not once during that process was context provided.  Not at any of the steps above (3 different pricing screens) was there context that additional fees could also apply to any given service.  Data transfer, in fact, doesn’t even describe itself very well.  As I was assured in asking folks involved in Azure about my concern on pricing, this “Data Transfer” wasn’t brought up at all.  I’m not sure why at all it is listed along side services and almost presented as a separate service as it appears all Azure services are subject to data transfer fees.  This is not made clear during sign up nor marketing of the pricing for each service.  SQL Azure should clearly state that the fees are database *plus* any additional fees resulting from data transfer.  Heck Amazon does this with S3 which also makes it so confusing to anticipate the cost of billing there as well…but at least it is presented that I need to factor that into my calculation.

I’m to blame, so why am I whining

I said I’m to blame as well for not understanding better what I’m getting into.  It is unfortunate because I really did like the service and felt an assurance of more reliability with my database then I had before.  The management portal was great and the uptime and log management was something I didn’t have to think about anymore. 

So why, you might ask, am I complaining about a service fee for something that was providing me value? 

NOTE: You may ask why I didn’t just move my site within Azure as well so that no data would be leaving the data centers.  This is a fair question, but unfortunately my site won’t run on any Azure hosting services and additionally I manage a few sites on a single server so it is cost prohibitive to have multiple Azure hosting instances for me right now.

Well it is simple.  I’m not made of money.  This blog has no accounting department or annual budget and such, I have to be smart about even the smallest cost.  I already have sunk costs into the server that hosts this site as well as a few others.  A $5/month database fee was nothing and justifiable easily with the value I was getting and the minor additional cost.  $50 (and growing) just wasn’t justifiable to me.  It was already at the same cost as my dedicated server and just no longer made sense for my scenario here.  In this instance I’m the “little guy” and need to think like one.  Perhaps cloud services are not for me.

Summary

So what did I learn?  Well, I really need to understand bandwidth and transfer data better for the sites I have.  Unfortunately this isn’t totally predictable for me and as such if I can’t predict the cost then it isn’t something that I should be using.  If you are considering these types of services regardless of if they are from Azure or Amazon (or whomever) you need to really plan out not only the service but how it will be used.  Don’t be lured by those shiny cost calculators that let you use sliders and show you awesome pricing but don’t help you estimate (or alert you) to that some of those sliders should be linked together.

I think Azure (and other similar services) have real customer value…there is no doubt in that.  For me, however, it just isn’t the time right now.  The services, based on my configuration needs, just don’t make sense.  Had I had a clearer picture of this when signing up, I wouldn’t have been in this situation of frustration.  Choose your services wisely and understand your total usage of them.  For me it currently doesn’t make sense and I’m moving back to a SQL Express account on my server.  Yes I’ll have to manage it a bit more, but my costs will be known and predictable.

Hope this helps.

| Comments

I run my site on Subtext which has been around for 6+ years in some form (Subtext is a fork of .Text from way back).  As a part of the framework, there was initially built-in capabilities for tracking referral traffic.  On each view of the application, it would tick a referral note and you could see this in the statistics view of the admin pages.

As the standards (for lack of a better term) of tracking Page Views, Referrals, etc. moved to more proven/consistent reporting like Google Analytics (or other platforms) these type of platform tracking became worthless to me.  I never checked them because, frankly, I didn’t believe them anyway.  The problem is that Subtext is still tracking this information for me and taking up valuable little bytes in my database.

For Subtext, specifically, contributors have created scripts and maintenance pages to help manage some of these referrals that may not matter to folks and are just taking up space.  I am one of those people.  In my recent migration to SQL Azure I wanted to take advantage of 100MB pricing.  Surely my blog was not bigger than that.  To my surprise my blog was 650MB in size. 

What!?

I hadn’t run my database maintenance script in a while and decided to run that which purges the referral tracking.  It got down to 35MB.  Yeah baby.  In fact this topic has been discussed on a few times on the Subtext developer mailing list and even tracking as a issue for the project.  In the meantime I wanted to solve it myself for my blog.

In Subtext there is a stored proc that runs to get some of the entry tracking data called subtext_TrackEntry.  Within that proc is where it looks to see if it is a referral and adds that data.  I simply altered my proc on my end to be like this (keeping in the old function just so that I know what I did in case I needed to revert back):

   1: ALTER PROCEDURE [dbo].[subtext_TrackEntry]
   2: @EntryID INT, @BlogId INT, @Url NVARCHAR (255)=NULL, @IsWeb BIT
   3: WITH EXECUTE AS CALLER
   4: AS
   5: -- Removing the referral tracking
   6: -- if(@Url is not NULL AND @IsWeb = 1)
   7: -- BEGIN
   8: --    EXEC [dbo].[subtext_InsertReferral] @EntryID, @BlogId, @Url
   9: -- END
  10: EXEC [dbo].[subtext_InsertEntryViewCount] @EntryID, @BlogId, @IsWeb

Now I’m no longer tracking referrals because my analytics package is doing that for me already.  My database is now representative of things that matter to me, rather than things I just want to clean up.  If you are a Subtext user and never knew that referral logging was wasting your database (and you are using an analytic package to track that anyway), then I hope this helps! 

| Comments

For the past 6 years I’ve run this blog on the Subtext project (an Open Source software project which is an ASP.NET blog framework).  It has served me very well with being flexible and allowing me to customize things that I want.  It is based on SQL Server and uses stored procedures and relational database “stuff” to accomplish the goals of the design. 

Recently I saw the news from Scott Guthrie about the reduction in pricing on some Windows Azure products, introducing a 100MB pricing option for SQL Azure which you can read about here and here.  I thought this would be a good time to start looking at moving some of my infrastructure of my blog “to the cloud” so I started looking at the details…after all, $5/month seemed reasonable for my database.

Now, I’m only talking about the database portion here…not the ASP.NET application.  Right now I’m happy being in complete control of my own server and have no need for moving the .NET site at this time.  Perhaps in the future, but given the pricing on app hosting, it just doesn’t make sense for my little site here.  I have, however, had some issues with the database infrastructure over the past year with some latency, outages and just age of the server it is hosted on for my site (not the same as my web app).  Because of this I was investigating SQL Azure.

I’m happy to say that this site now runs the data side on SQL Azure…but the process was not without hiccups.  I wanted to share some frustrations I had so that you might be able to avoid them.  My comments below are related to migrating an existing SQL Server database to SQL Azure and not creating a new one from scratch.

My environment

To set the stage, it is fair to note my particular environment for migration.  My database resides on a Windows 2003 server and is a SQL Server 2005 instance/database.  My web application also resides on a Windows 2003 server and is configured in a virtual LAN to have access to the database infrastructure server. 

Yes I realize these are not the “latest” in server products, but I also don’t think they are too old relatively speaking.  They work to my needs and I hadn’t needed any of the new features for a long while (I’ve been desiring to move to Windows 2008 and ASP.NET MVC for Subtext though).

Getting the necessary tools

I chuckled a bit at how many client tools I actually needed to complete this process of moving things to the ‘cloud’ during this process.  I’ve had experience in the past moving SQL databases around using SQL Management Studio (SSME) and other script methods.  I thought this would be very similar using the import/export capabilities nicely provided in SSME. 

I was wrong.

In doing this I started on the Windows Azure web site for some data.  Now, I clearly didn’t navigate deep enough (more on that in a moment) because I wasn’t finding what I was looking for in a “migrating an existing database to Azure” article.  So I did what anyone else would do and searched the web for ‘migrate sql to azure’.  I was presented with a first top choice titled Migrating Databases to SQL Azure.  This provided me with a few options, but no really good end-to-end example.  Truth be told, I tried a few of these and was completely frustrated because the details were not complete.

In the end I found what I needed in the following tools that were essential to me.  Here were the tools that you will need:

The last 2 bullets (direct connectivity) are needed to ensure you can do this from your machine.  The last one – access to your SQL Azure server – isn’t entirely intuitive that you need it set up in advance, but you do.  Another thing to note is that in order for the SQL Azure Migration Wizard to work, your SSME installation must be 2008 R2 SP1.  The link above is to SP1 but I could not find a download for just the SSME tool for R2 SP1, so I downloaded the full SP1 of SQL Express With Tools and just installed the management studio.

Setting up the connections

Once you configure your Windows Azure account, you’ll need to create a SQL Azure server.  This initially confused (and concerned) me because I only wanted my 100MB database account and not anything that will bump up my compute time costs.  However I’ve been assured this just represents the “instance” of your DB and not a compute server.  So you’ll need to configure that first.

To do this you’ll login to your account at https://windows.azure.com and select the subscription server you set up for SQL Azure.  You’ll then want to add a firewall rule to your server.  The “Add” button shows your current IP address so you can add just that if you’d like.

SQL Azure Server Configuration

This takes a few minutes to propagate so I’d do this first.  Once you have this you can configure this connection in SSME to connect to.  Your server name is on the right hand side of this screen (blurred for my account) and is something like XXXXXX.database.windows.net.  In SSME you will connect to this as XXXXXX.database.windows.net,1433 as the server name.

Start by adding a connection in SSME for your current database and your SQL Azure server you just configured.  Do not create any database yet at this time on SQL Azure.

Exporting the current database schema

I tried a few different methods, but by far the easiest was the Data-tier Application method.  To do this go to your existing database in SSME and right-click, choose Tasks and then Extract Data-tier Application:

Extract Data-tier Application

This will create a “dacpac” file via the wizard you will be presented with.  Essentially this extracts the schema and objects for you.  Now why this instead of just a normal TSQL script?  Your mileage may vary, but this was the only method I had real success with in my configuration.

Creating the database from the DACPAC

Once you have the exported .dacpac file go back to SSME and on your SQL Azure instance right-click and choose Deploy Data-tier Application:

Deploy Data-tier Application

This will create the database and schema for you, but not any data.  This is another wizard that walks you through this process.  Once complete you should have a new SQL Azure database matching the schema from your original one.

Migrating the data

Once I had the new schema in my SQL Azure database I was ready to move the data.  This is where the SQL Azure Migration Wizard comes into play.  Launch that tool and you will be asked to choose a source and destination target.  For the source, connect to your original database and after specifying the connection information (I chose just the db, not Master and was fine), click the Advanced button and change to Data only:

Migrating data only

You will then start the process and notice that it is basically doing bcp.exe commands for you to extract the data:

Migrating data

Once this is done you will select the destination – your SQL Azure DB that was just created.  Now since SQL Azure may not have the same features as your source database there may be some conflicts.  The migration wizard tool will stop you on errors on the bcp import commands and give you a chance to resolve/continue those conflicts.  As an example, some of my clustered indexes didn’t transfer over in the schema creation (no idea why) and I needed to re-create those before two tables could be imported.  No big deal, but it was cool that the import was “paused” for me with a Retry function so that I could do this without starting all over.

Migration clean-up

There were a few things that didn’t migrate well for my Subtext experiment here.  First, even though stored procedures in my source database had correctly identified some parameters as ‘out’ it seems they didn’t transfer well.  I’m not sure if this is an issue with the Data-tier Application export or something in SQL Azure, but it required me to go back and ALTER those procs with the correct flag of OUTPUT.  Luckily I could actually do this through the Silverlight application for managing my database just fine after the database was configured:

Alter stored procs in portal

Logins also didn’t transfer, but users did.  My web app doesn’t use an admin user so I wanted to make sure I had a correct login for that.  Through SSME connected to SQL Azure, there is not the GUI interface for doing these and you have to use all TSQL scripts.  The commands in SSME when connected to SQL Azure DB will generate the template for you and you just put in the right values.

Subtext uses some system stored procedures in some admin screens that I use and sp_spacesed is not available.  Luckily others have seen this and I just needed to modify some areas to use similar scripts.

General frustrations

Some of you may be looking at this and wondering why I had so much trouble.  Why didn’t I just read this document (don’t you hate me for putting that last) that walked me through similar steps (minus migration wizard)?  That would be a good question.  That document didn’t show up in search for me anywhere and it is under the “Develop” section of the Azure information site.  I didn’t think to look for as I wasn’t developing anything just yet.  I only found out about that link from my friend Peter Laudati.  It would have saved me some time, but not most.  The first link on that site shows to download SSME R2 RTM…but the migration wizard requires SP1 and without it you’d see an error message about some missing SQL types (Smo). 

Why didn’t I just use the migration wizard for schema *and* data…why two steps?  That’s a good question.  Frankly I don’t know why the migration wizard itself didn’t work for me for the wholesale schema+data approach.  It could be my SQL2005 version or something.  But for me, it just didn’t work.  The steps above were the only paths that worked for me and my SQL2005 database to migrate.

Summary

While I was successful in finally migrating my database, discovering the proper steps wasn’t as in-my-face as it should be.  There are pieced together areas about migrating, but the MSDN article I expected to be a more full-featured end-to-end example.  I remember there was a big push for Access->SQL Server and there was an “upsizing wizard” provided from Microsoft to move that data.  I wish that Azure had something more one-stop like this.  The migration wizard seems like a first approach, but didn’t work as smooth as a one-stop solution for me…hopefully it does for others.  It would have also been nice to have this actually integrated into the tools of the Azure portal.  Let me provide a connection to my existing database and just magically create the Azure one for me – that would have been awesome.

Once my migration was complete, everything in SQL Azure is working with my app as expected and the tools are familiar to me to do any maintenance on the data that I need.  I like the Silverlight management interfaces via the web that I can get a snapshot of my datbase at anytime and they even display query performance on the dashboard which is cool.  I don’t have access to the log files nor am I able to run DBCC commands anymore, but I’m trusting that SQL Azure is more efficient than my own DBA skills of old and that my database will be managed effectively with regard to these items. 

Hope this helps.