Connecting to SQL Server Express remotely

I'm beginning to become more of a power user....

The section below is copied from http://www.datamasker.com/SSE2005_NetworkCfg.htm (just copying it in case the original disappears)

Although by the time I become a proper power user I'll hopefully be on the full monty - not the express!

Here's the instructions:

Here's a quick summary of the actions you need to take. The first three actions have a separate page which provides more details and some screen shots of the procedure.

1. [Link] Enable the TCP/IP protocol using the Surface Area Configuration Utility
2. [Link] Make sure the TCP/IP protocol is enabled in the SQL Server Configuration Utility
3. [Link] Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address - but this is non-standard for named instances. See sqlexpress's WebLog for details.
4. Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall. Chris D. sent in a note which might help.
5. Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports (thanks P.C.) that starting and stopping the SQL Server and Browser software is not enough.

Essential reading if you are trying to run DNN5 with a proper SQL Server Express Connection String...

This blog post has just saved me lots of horrible experimentation

http://www.dotnetnuke.com/Community/Blogs/tabid/825/EntryId/1202/DNN-SQLExpress-SQL-Server-Management-Studio.aspx

Basically I've been running using "File" access - and now it's definitely time to move up to "proper" access. For a start it will make my backup process 100 times easier :)

Post is reproduced here - just so it can go missing in any reorg at DNN:

Recently I rebuilt my laptop and decided to only install SQL Express and not the full SQL Server 2005.  The issue I can across occurs when you attach to the DNN mdb file within SQL Server Management Studio.  Once you have done that the connection string does now work and you DNN site cannot connect to the database. And no matter what you do you cannot correct it even by detaching it, restart SQL Express, resetting IIS, etc.  I finally had to scrap the database and do it all again.  Below is the way to setup DNN and SQL Express if you want to also access the database from within SQL Server Management Studio.

1) Rename the Database.mdb to another name.  (This is optional).

2) Open SQL Server Management Studio and do the following 

• Attach the database 
  

1. Right click on the database folder and choose attach.
2. Click Add button and point to the database you want to attach to.
3. Click on the "Attach As" column and give it a more friendly name.  For this blog purpose we will change it to DotNetNuke_ModDev
4. In the lower screen delete the line that references the ".ldf" file.  This will be created and you will get an error that it cannot be found if you do not delete it.
5. Click on the "Current File Path" for the ".mdf" file and update it so it points to the location of the DNN database you are attaching to.
6. Click Okay and the database should be attached correctly.

• Set up permissions
  • Adding Login
  1. Click on the security\logins folder.
  2. If the ASPNet user exist you can skip this section.
  3. Right click login folder and choose "New Login"
  4. Click the "Search" button, then the "Advance" button, and then the "Find Now".  This will bring up a list of user on the computer and select the ASPNet account.
  5. Make sure Windows authentication is checked and click "OK" to add user.
     

•  Adding Database User
1. Click on "Databases\Security\Users folder.
2. If the ASP.Net user exist you can skip this section
3. Right click the user folder and choose "New User"
4. Click on the "..." button next to the Login Name, then click on the "Browse" button.
5. Check the ASPNet user and click "OK" button.  Click the OK button on the Select Login screen and this will add the user.
6. Under database role membership check one of the following combinations.  Option #2 is more secure.  Click "OK" button when done.
   
• Permission Options #1
  • dbowner
• Permission Options #2
  • db_datareader
  • db_datewriter
  • db_ddladmin
  • db_securityadmin

• Setting Database Permissions (Only needed it Option #2 is chosen above)
  
1. Right click on the database you attached to and choose "Properties"
2. Click on "Permissions" under select a page (upper-left of screen).
3. Under Explicit permission for {Database Name}, make sure that the Grant box is checked for Execute.

3) Update the connection strings in the DNN web.config file.  Do not forget to use the same connection string in the <ConnectionString> and <AppSettings> section.

FROM
Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Database.mdf;

TO
Data Source=.\SQLExpress;Integrated Security=True;User Instance=False;Database=DotNetNuke_ModDev;

4) Access the DNN web site to run the installation process.

You should now have a working DNN site and a SQL Express database that you can access/manipulate using SQL Server Management Studio and still be able to access it from the web site as well.

Validation of viewstate MAC failed / The state information is invalid for this page and might be corrupted

I'm hitting some of these problems on runsaturday at the moment.

These definitely aren't being caused by machinekey errors - this is occurring on a single PC.

Some of these might be occurring due to some specific DotNetNuke behaviour - especially when the page makeup changes during a postback.

However, in one particular case - I have one specific Mac user who (regardless of whether he uses Firefox or Safari) just occasionally seems to randomly hit one of these problems).

This has me quite stumped.... but I'm still looking :)

Here's one discussion that seems to be particularly relevant - http://forums.asp.net/t/955145.aspx?PageIndex=4

Still trying to understand IIS7....

This excellent post has at least solved one of my queries - I simply could not understand why IIS7 didn't let me select .NET 3.5 - the answer is in the runtime... http://www.west-wind.com/Weblog/posts/289139.aspx

Problems using ThreeSharp with bucket names containing DOTs

I'm trying to set up a bucket name at the moment (for public access) and keep hitting:

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel

One solution seems to be not to include "config.Format = CallingFormat.SUBDOMAIN;" - but this obviously won't help people with European buckets.

Azure downtime report

Using host-tracker to check my Azure sites.

Here are the stats from Feb - I think all downtime must be from Azure itself - I haven't taken the site down at any point.

I can also get a response time report if anyone is interested

Stuart

Stacka
  http://www.stacka.com
   Total uptime:99.71% Downtime:2 hour(s) 13 min(s)
       Monthly uptime:99.75% Downtime:1 hour(s) 39 min(s)
           Day 2009-02-28 Uptime:100.00%
           Day 2009-02-27 Uptime:100.00%
           Day 2009-02-26 Uptime:100.00%
           Day 2009-02-25 Uptime:100.00%
           Day 2009-02-24 Uptime:96.00% Downtime:57 min(s) 37 sec(s)
           Day 2009-02-23 Uptime:100.00%
           Day 2009-02-22 Uptime:100.00%
           Day 2009-02-21 Uptime:100.00%
           Day 2009-02-20 Uptime:97.12% Downtime:41 min(s) 30 sec(s)
           Day 2009-02-19 Uptime:99.95% Downtime:41 sec(s)
           Day 2009-02-18 Uptime:100.00%
           Day 2009-02-17 Uptime:100.00%
           Day 2009-02-16 Uptime:100.00%
           Day 2009-02-15 Uptime:100.00%
           Day 2009-02-14 Uptime:100.00%
           Day 2009-02-13 Uptime:100.00%
           Day 2009-02-12 Uptime:100.00%
           Day 2009-02-11 Uptime:100.00%
           Day 2009-02-10 Uptime:100.00%
           Day 2009-02-09 Uptime:100.00%
           Day 2009-02-08 Uptime:100.00%
           Day 2009-02-07 Uptime:100.00%
           Day 2009-02-06 Uptime:100.00%
           Day 2009-02-05 Uptime:100.00%
           Day 2009-02-04 Uptime:100.00%
           Day 2009-02-03 Uptime:100.00%
           Day 2009-02-02 Uptime:100.00%
           Day 2009-02-01 Uptime:100.00%

clouddotnet
  http://www.clouddotnet.com
   Total uptime:98.83% Downtime:8 hour(s) 54 min(s)
       Monthly uptime:99.86% Downtime:55 min(s) 44 sec(s)
           Day 2009-02-28 Uptime:100.00%
           Day 2009-02-27 Uptime:100.00%
           Day 2009-02-26 Uptime:100.00%
           Day 2009-02-25 Uptime:100.00%
           Day 2009-02-24 Uptime:98.23% Downtime:25 min(s) 29 sec(s)
           Day 2009-02-23 Uptime:100.00%
           Day 2009-02-22 Uptime:100.00%
           Day 2009-02-21 Uptime:100.00%
           Day 2009-02-20 Uptime:98.08% Downtime:27 min(s) 43 sec(s)
           Day 2009-02-19 Uptime:99.82% Downtime:2 min(s) 32 sec(s)
           Day 2009-02-18 Uptime:100.00%
           Day 2009-02-17 Uptime:100.00%
           Day 2009-02-16 Uptime:100.00%
           Day 2009-02-15 Uptime:100.00%
           Day 2009-02-14 Uptime:100.00%
           Day 2009-02-13 Uptime:100.00%
           Day 2009-02-12 Uptime:100.00%
           Day 2009-02-11 Uptime:100.00%
           Day 2009-02-10 Uptime:100.00%
           Day 2009-02-09 Uptime:100.00%
           Day 2009-02-08 Uptime:100.00%
           Day 2009-02-07 Uptime:100.00%
           Day 2009-02-06 Uptime:100.00%
           Day 2009-02-05 Uptime:100.00%
           Day 2009-02-04 Uptime:100.00%
           Day 2009-02-03 Uptime:100.00%
           Day 2009-02-02 Uptime:100.00%
           Day 2009-02-01 Uptime:100.00%

DotNetNuke, Roles and Invalid value for 'encrypted ticket parameter'

On http://www.RunSaturday.com I was using DNN's roles for friends... then one day I couldn't log on.

The reason? I had too many friends :)

Basically the problem was that DNN was inserting a very long string into a cookie....

To work around this I'll need to either change my code to not use the DNN roles structure or insert an HTTPModule to tweak their Roles behaviour.

Here's the DNN thread: http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/111/threadid/234880/scope/posts/threadpage/7/Default.aspx

I was sure I'd already blogged this - colors (colours) darker and lighter

From http://www.csharp411.com/lighten-and-darken-colors-in-net/ (and other sources) you can use ControlPaint to help with lightening and darkening colours.

Integrating Twitter

I've been playing with www.twitter.com quite a lot over the last week or two.

On the send side, I used the tweetsharp project to send tweets - if you're a www.runsaturday.com user then you'll have seen this - if not, then you can't see it! - http://code.google.com/p/tweetsharp/

On the browse side, I used twitter.js to show people's tweets on their runsaturday profile - see http://www.runsaturday.com/Profile/TabId/82/UserId/3/Default.aspx for an example - http://remysharp.com/2007/05/18/add-twitter-to-your-blog-step-by-step/

Both libraries come recommended

Starting to think about internationalisation

Someone's emailed me from Martinique.

Yes, I had to lookup were that was! (It is on Google Maps but not on all of them - e.g. not on Google static maps!)

Anyways, it's got me thinking about internationlisation (i18n) for runsaturday. I think it should be possible to do this... I might have a go soon...

Need to go back over my code first and find out which modules have strings naughtily hardcoded... but then I think it should be possible to translate the existing site quite quickly - maybe in less than a week....

It must be done! But then so must a lot of other things....

Stuart

Preprocessor directives must appear as the first non-whitespace character on a line

Dodgy error from the compiler....

"Preprocessor directives must appear as the first non-whitespace character on a line"

What this actually meant was I'd tried to use two DataBinding expressions inside the same property -

                <asp:Label ID="UserName" runat="server" Text='<%# Eval("UserName") %><%# Eval("TeamMemberNameText") %>'></asp:Label>

To solve it I just pulled it down to:

                <asp:Label ID="UserName" runat="server" Text='<%# Eval("UserAndTeamMemberNameText") %>'></asp:Label>

Replacing the far too secure random password generation in DotNetNuke

When using my facebook connect authentication plugin (on http://www.snowcovered.com/Snowcovered2/Default.aspx?tabid=242&PackageID=13359), the password generated by DNN is far, far too secure for most users.

It looks like ASH783934_w3w-r - i.e. not very easy to remember.

I wanted to replace it - so used a class like....

    public class SlodgeDNNMembershipProvider : DotNetNuke.Security.Membership.AspNetMembershipProvider

    {

        static List<string> PasswordBase = new List<string>()

        {

            "fishfinger",

            "rabbit",

            ... lots of other simple keywords,

            "racing"

        };

 

 

        public override string GeneratePassword(int length)

        {

            // length ignored - hope this does not hurt the SQL layer!

            return GeneratePassword();

        }

 

        public override string GeneratePassword()

        {

            Random r = new Random();

            int index = r.Next(PasswordBase.Count);

            if (index >= PasswordBase.Count) // according to the intellisense help this should not happen

                index = PasswordBase.Count - 1;

 

            int number = r.Next(100);

            return string.Format("{0}{1:00}", PasswordBase[index], number);

        }

    }

And then inserted this into the web.config layer using:

            <members defaultProvider="AspNetMembershipProvider">

                  <providers>

                        <clear/>

                        <add name="AspNetMembershipProvider" type="SlodgeDNNMembershipProvider.SlodgeDNNMembershipProvider, SlodgeDNNMembershipProvider" providerPath="~\Providers\MembershipProviders\AspNetMembershipProvider\"/>

                  </providers>

            </members>

Seemed to work first time - which is always suspicious!

Notes on YetAnotherForum.net speed

I've been using YetAnotherForum on runSaturday and have found it very good.

However... I have also had some problems - which I think I've started to solve...

Here are my notes - as submitted to http://forum.yetanotherforum.net/yaf_postsm31803_Problems-with-slow-forum-not-on-godaddy--and-my-solution.aspx#post31803

I've been using this on my DotNetNuke site - http://www.runsaturday.com - and I've had some problems with slowness - similar I think to some of the GoDaddy problems much discussed here.

My speed problems (I think) were:
 - that my mail server is occasionally slow to react - so the sending of emails is often slow... and this seems to be done on the forum main thread.
 - that the user images returned from the forum are not sent with caching information - so they are requested far too often by clients.

To work around these problems:

1. I've added this code to the mail sender in forumpage.cs - I've not fully tested it works yet - but the speed improvement seems OK:

        const double NumSecondsBetweenMailSendAttempts = 600;
        static DateTime MailLastSent = DateTime.Now;
        class MailSendLock
        {
            private int i = 0;
        }
        static MailSendLock MailSendLockObject = new MailSendLock();

        private void TriggerMailIfNecessary()
        {
            if ((DateTime.Now - MailLastSent).TotalSeconds > NumSecondsBetweenMailSendAttempts)
            {
                lock (MailSendLockObject)
                {
                    if ((DateTime.Now - MailLastSent).TotalSeconds > NumSecondsBetweenMailSendAttempts)
                    {
                        MailLastSent = DateTime.Now;
                        System.Threading.ThreadPool.QueueUserWorkItem(new WaitCallback(SendMailThread));
                    }
                }
            }
        }

2. I've added this code to the resource.ashx.cs file:

        private static void AddCaching(HttpContext context)
        {
            context.Response.Cache.SetExpires(DateTime.Now.AddDays(7.0));
            context.Response.Cache.SetCacheability(HttpCacheability.Public);
            context.Response.Cache.SetValidUntilExpires(false);
        }

        public void ProcessRequest( HttpContext context )
        {
            if ( context.Request.QueryString ["r"] != null )
            {
                // resource request
                GetResource( context );

            }
            else if ( context.Session ["lastvisit"] != null )
            {
                if ( context.Request.QueryString ["u"] != null )
                {
                    GetResponseLocalAvatar( context );
                    AddCaching(context);
                }
                else if ( context.Request.QueryString ["url"] != null && context.Request.QueryString ["width"] != null && context.Request.QueryString ["height"] != null )
                {
                    GetResponseRemoteAvatar( context );
                    AddCaching(context);
                }
                else if ( context.Request.QueryString ["a"] != null )
                {
                    GetResponseAttachment( context );
                    AddCaching(context);
                }
            }
            else
            {
                // they don't have a session...
                context.Response.Write( "Please do not link directly to this resource. You must have a session in the forum." );
            }
        }

I've been incredibly busy

Sorry I've not posted much here for a short while.

I've been really very busy indeed writing Javascript of all things.

Lots of Google Maps code - for the output take a look at the new Course Mapper on RunSaturday - see:

http://www.runsaturday.com/Maps/tabid/98/Default.aspx

and:

http://www.runsaturday.com/Maps/tabid/99/course/1/Default.aspx

And you can also see it used in action in this iframe example: http://lodge.stuart.googlepages.com/examplepage

Reflection on my own base classes - DNN 4 vs DNN 5 pain

So DotNetNuke5 is lovely... and so was DotNetNuke4....

However, someone has really upset me today by completely changing the base class functionality of AuthenticationConfigBase - which means that it's really hard to make an authentication module which will work in both DNN4 and in DNN5. Basically you get haunted by MissingMethodException's surrounding GetPortalSetting and PortalController.GetPortalSettingsDictionary.

To get around this, I had to reflect on the Config's own base class to find out what to use...

Here's the code.... I'm sure it could be more efficient - but it doesn't matter too much for where it is (it's not loaded very often)

Load settings:

        // Methods

        protected Config(int portalId)

            : base(portalId)

        {

            this._ApplicationKey = Null.NullString;

            this._Enabled = Null.NullBoolean;

            this._IncludeHelp = Null.NullBoolean;

            this._SecretKey = Null.NullString;

            this._LocationXDReceiver = Null.NullString;

 

            bool tryDnn4Methods = false;

            try

            {

                Dnn5LoadSettings(portalId);

            }

            catch (Exception /*e*/)

            {

                tryDnn4Methods = true;

            }

 

            if (!tryDnn4Methods)

                return;

 

            try

            {

                Dnn4LoadSettings();

            }

            catch (Exception e)

            {

                throw new ApplicationException("Cannot initialise Facebook Connect config", e);

            }

        }

 

        private void Dnn4LoadSettings()

        {

            if (!bool.TryParse(HackGetValueForDnn4("FacebookConnect_Enabled"), out this._Enabled))

                this._Enabled = false;

 

            this._ApplicationKey = HackGetValueForDnn4("FacebookConnect_ApplicationKey");

            this._SecretKey = HackGetValueForDnn4("FacebookConnect_SecretKey");

            if (!bool.TryParse(HackGetValueForDnn4("FacebookConnect_IncludeHelp"), out this._IncludeHelp))

                this._IncludeHelp = false;

            this._LocationXDReceiver = HackGetValueForDnn4("FacebookConnect_LocationXDReceiver");

        }

 

        private void Dnn5LoadSettings(int portalId)

        {

            this._Enabled = PortalController.GetPortalSettingAsBoolean("FacebookConnect_Enabled", portalId, Null.NullBoolean);

            this._ApplicationKey = PortalController.GetPortalSetting("FacebookConnect_ApplicationKey", portalId, Null.NullString);

            this._SecretKey = PortalController.GetPortalSetting("FacebookConnect_SecretKey", portalId, Null.NullString);

            this._IncludeHelp = PortalController.GetPortalSettingAsBoolean("FacebookConnect_IncludeHelp", portalId, Null.NullBoolean);

            this._LocationXDReceiver = PortalController.GetPortalSetting("FacebookConnect_LocationXDReceiver", portalId, Null.NullString);

        }

 

        private string HackGetValueForDnn4(string valueToGet)

        {

            Type type = this.GetType();

            System.Reflection.PropertyInfo info = type.GetProperty("ModuleSettings", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.SetProperty | System.Reflection.BindingFlags.Instance);

            if (info == null)

                throw new ApplicationException("Cannot initialise Facebook Connect - no ModuleSettings found");

 

            System.Collections.Hashtable hashTable = (System.Collections.Hashtable)info.GetValue(this, null);

            string value = (string)hashTable[valueToGet];

            if (value == null)

                value = string.Empty;

 

            return value;

        }

 

Save settings:

        internal void HackSetValueForDnn4(string valueToSet, string value)

        {

            ModuleController controller = new ModuleController();

            Type type = this.GetType();

            System.Reflection.PropertyInfo info = type.GetProperty("AuthenticationModuleID", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.SetProperty | System.Reflection.BindingFlags.Instance);

            if (info == null)

                throw new ApplicationException("Cannot initialise Facebook Connect - no ModuleSettings found");

 

            int authenticationModuleID = (int)info.GetValue(this, null);

 

            controller.UpdateModuleSetting(authenticationModuleID, valueToSet, value);

        }

 

        public static void UpdateConfig(Config config)

        {

            try

            {

                Dnn5UpdateSettings(config);

            }

            catch (Exception /*e*/)

            {

            }

 

            try

            {

                Dnn4UpdateSettings(config);

            }

            catch (Exception e)

            {

                // ignored

                //throw new ApplicationException("Cannot save Facebook Connect config", e);

            }

        }

 

        private static void Dnn4UpdateSettings(Config config)

        {

            config.HackSetValueForDnn4("FacebookConnect_Enabled", config.Enabled.ToString());

            config.HackSetValueForDnn4("FacebookConnect_ApplicationKey", config.ApplicationKey);

            config.HackSetValueForDnn4("FacebookConnect_SecretKey", config.SecretKey);

            config.HackSetValueForDnn4("FacebookConnect_IncludeHelp", config.IncludeHelp.ToString());

            config.HackSetValueForDnn4("FacebookConnect_LocationXDReceiver", config.LocationXDReceiver);

            ClearConfig(config.PortalID);

        }

 

        private static void Dnn5UpdateSettings(Config config)

        {

            PortalController.UpdatePortalSetting(config.PortalID, "FacebookConnect_Enabled", config.Enabled.ToString());

            PortalController.UpdatePortalSetting(config.PortalID, "FacebookConnect_ApplicationKey", config._ApplicationKey.ToString());

            PortalController.UpdatePortalSetting(config.PortalID, "FacebookConnect_SecretKey", config.SecretKey.ToString());

            PortalController.UpdatePortalSetting(config.PortalID, "FacebookConnect_IncludeHelp", config.IncludeHelp.ToString());

            PortalController.UpdatePortalSetting(config.PortalID, "FacebookConnect_LocationXDReceiver", config._LocationXDReceiver.ToString());

            ClearConfig(config.PortalID);

        }

 

Facebook Connect for DotNetNuke finally documented...

Hi All

I've finally documented the facebook connect auth system I produced for dotnetnuke.

The doc is up on http://www.cirrious.com/Research/FacebookConnect/tabid/59/Default.aspx - pdf format to follow soon

I'll be releasing the software for a nominal fee through snowcovered and through DNN showcase in the very near future!

Got to get some sleep now!

Stuart

Back on dnn user names again

This change (for Jules) worked:

update dnn_users set username='JulesR', displayname='JulesR'  where username='JROSE02'
update aspnet_Users set username='JulesR' ,loweredusername='julesr' where username='JROSE02'
update yaf_user set name = 'JulesR' where name='JROSE02'

My previous post on this had a typo!

At least I hope the change worked....

White Paper Draft - Azure development

Got distracted this morning - was supposed to be working on http://www.runsaturday.com on people search - but instead wrote a paper on Azure and stacka:

Cloudtime - Adventures in Microsoft Azure

View more documents from cirrious. (tags: asp.net development)

Another twist on stacka

Since it really really snowed last night and today....

I got inspired to spin out another version of stacka.

This time http://www.snowdotnet.com - a place to show your snowmen off :)

Not going to plug it too hard - I think I might have friend new website apathy by now....

However, one interesting techie point to make:
- because I've run out of webspace, this app is not running purely on Azure
- instead the storage is 100% azure
- but the web app is running on my GoGrid server
- and the async worker role has been abandoned - instead I'm doing the picture processing synchronously.

Anyone want to post any pictures, please do :)

Experiments with Azure - changing the structure of live data

Before I start......

Sorry if this blog post is a bit too brief and too technical. I could write about this topic all day and still not get it covered - so I've gone brief and techie. You will probably need at least a little experience in Azure (or GAE or EC2) to understand this...

On with the post....

The problems...

I wanted to update my http://www.stacka.com and http://www.clouddotnet.com Azure apps - especially adding recent comments and recent ratings to the front page.

And so the problems started.... it was very revealing about my data structure....

Firstly, I had organised my comments and my ratings (partition key and row key) so that they were easily accessible in time order from an individual "stacka" (or site in the couddotnet case):
- I had the PartitionKey as StackId
- I had the RowKey as a reverse time index (a bit like Steve Marx's blog examples - see http://blog.smarx.com)

Problem 1: Now because there is no "order by" allowed in the Linq for Azure table storage - so the order returned is the partition key, row key order... this presented a challenge in how to get all my data back in the right order

Secondly, I had not stored simple data like Stack title (site title for clouddotnet) in the rating and comment entries

Problem 2: Because there is no "join" allowed in the Linq for Azure table storage (and nor is Contains allowed) then listing comments/ratings alongside their stack/site names was going to be really slow.

Thirdly, I wanted to present a random set of Stacks (or sites) - rather than just the latest

Problem 3: How do you pick a random set when there's no order by, no count, etc available?

To solve these problems....

I had to go back to my data schema - which in Azure, of course, is just the public properties of my data classes.

For problem 1:

• I've bodged it....
• While my data size is so small, I'm actually pulling back all the comments and ratings into app memory and sorting them there. To help prevent some slowness I'm using the HttpContext.Current.Cache cacheing with a one minute absolute time expiration)  
  
• What I need to do in the longer term is either to change the PartitionKey and RowKey so I can get results returned by time while still being able to search quickly by stack - I think this will be simple enough to do - but I think it will require a change in table name.
  
• (An alternative in the long term would be to add another table to act as an index - but I think in this case that is not needed)

For problem 2:

• I had to add on the public property StackTitle to the CommentRow class - and similarly for the RatingRow class.
• I then had to write some code to update the existing data to include these StackTitle properties - I did this in a simple Windows Form app that I ran on my local PC - it used the same class libraries as the real ASP.Net app - and upgraded the data live without any users noticing - very easy.
  
• Then I changed the ASP.Net code to use the new structures and deployed the new code :)
  

For problem 3:

• I've bodged it....
• While my data size is so small, I'm actually pulling back all the stacks and selecting a random set there
• What I need to do in the longer term is to create separate lists of random items - I think I would do this in a worker role - maybe creating a new random list once every minute - and storing up to 10 random list in the azure table storage at any one time? This would be simple enough to do - and not too bad on processing or redundancy.
  

Some side notes:

• Azure storage is a bit of a mindf*&k - you have to stop thinking relational - you definitely have to stop thinking normalised.
• Do not read this and think - "this is horribly inefficient"!
• Azure storage (like Amazon's SimpleDB and like Google's BigTable) is very very efficient
• It's just that you have to keep thinking about efficiency in terms of speed of retrieving data for the user - not in terms of number of bytes stored or in terms of duplicated data.
• It's a classic MIPs versus Memory trade off - and in the world of the cloud, then MIPs and Memory are both cheap, but when it comes to responding to the user then you cut MIPs at the expense of Memory.
  
• Changing the schema was really simple
• Working out what is actually now stored in the data store is a bit tricky - at one point I added some rows to my live data store with an extra column... those cells will live on for all time now - but I can't work out right now how to actually work out which rows have those cells.
• The use of a desktop tool to upgrade the data was beautifully simple (and it used https - so it was secure)
• While the bodges might:
• They're a good programming approach - as long as I know they work and as long as I know that they can be changed, then it's actually a solution that is scalable in development effort terms?
• They're a p1ss poor programming approach... there is definitely an argument that it's better to do the fix right first time...
  

And the conclusion:

You can see the results on:

• Before the upgrade the page looked like - http://www.stacka.com
• After the upgrade the front page looked like - http://www.clouddotnet.com
  

Well, I think it was worth it anyway :)

Can I have some spam with my cloud?

So it looks like I've got to add some admin functions to clouddotnet and to stacka.....

e.g. this is the first spam entry already on clouddotnet:

http://www.clouddotnet.com/cloud/samujjal/samujjal

Kind of funny I suppose...