Migrating Sitecore Databases to SQL Azure

Hosting Sitecore Databases on SQL Azure

Before beginning this process it is worth ensuring Microsoft SQL Server Management Studio (SSMS)  2008 R2 SP1 is installed on your machine as this release has brought a lot of enhancements which allows you to work with SQL Azure in a similar way to when you connect to a SQL server. The first advantage of this version of SSMS is that you can connect to your SQL Azure subscription within the object explorer. Although the functionality available is a subset of what you are used to, there are quite a few useful features:

  • Provides access to the “Databases” and “Security” areas of the tree
  • Drill down into the database schema
  • Create/Delete tables
  • Run SQL queries
  • Create new user logins (via script)

Creating Databases with Sitecore Schema and Data

There are a number of ways available for migrating databases to SQL Azure as described on MSDN (http://msdn.microsoft.com/en-us/library/windowsazure/ee730904.aspx). I found using a combination of these methods worked best:

  1. Creating the database schema using SQL Azure Migration Wizard (http://sqlazuremw.codeplex.com/)
  2. Exporting/Importing the data using SQL Scripts created by SQL Server Management Studio

Creating Database Schema

As mentioned above, the SQL Azure Migration Wizard was very handy at doing this, it provides an easy to use wizard to generate a SQL Azure compatible script of the database and then connect to the SQL Azure subscription and run the script it has created.

Before starting, you will need to edit a stored procedure within the core database on your local SQL server due to SQL Azure not supporting all of the functionality that SQL Server does. If you run the wizard, will see the following error:

StoredProcedure [dbo].[aspnet_Membership_GetNumberOfUsersOnline] — Deprecated feature ‘Table hint without WITH’ is not supported in this version of SQL Azure.

This is simply fixed by adding the “WITH” keyword within the SELECT statement so that it reads the following:

SELECT  @NumOnline = COUNT(*)

FROM    dbo.aspnet_Users u WITH (NOLOCK),

dbo.aspnet_Applications a WITH (NOLOCK),

dbo.aspnet_Membership m WITH (NOLOCK)

This is the only incompatible aspect of the databases which I came across so once this is updated, you can follow the wizard through creating your database schema scripts:

  1. Select the radio button “SQL Database” within the “Analyze/Migrate” section of the window
  2. Enter the server details of the SQL Server which your databases are located
  3. Select the Core/Master/Web databases to be migrated
  4. When presented with selecting the database objects to script, click advanced and under the “Script Table / Data” option, select “Table Schema Only”.

At this point, the wizard allows you to include scripting the data as well as the database schema. If you select this option, the data is copied into a dat file for each table and then the bcp utility (http://msdn.microsoft.com/en-us/library/ms162802%28v=sql.105%29.aspx) uses these files to bulk copy the data to the SQL Azure subscription. However, I found that the command the migration tool generated did not successfully carry out the bulk copy which is why I used SSMS to generate scripts of the data (below).

  1. Once the script has been created successfully, the wizard then provides the functionality to connect to your SQL Azure instance. You will just need the server name (SERVER.database.windows.net) and username/password to connect. After connecting to the subscription, the wizard provides an option for you to select an existing database or create a new one.
  2. After selecting (or creating) the database to run the script against, the script is executed and you are left with the schema of the database on your SQL Azure database

Exporting/Importing Data

As mentioned previously, a combination of the migration wizard and the bulk copy utility can be used to import the data into the SQL Azure database. I found the wizard couldn’t successfully carry this out but if you edit the commands to work, it would be a lot quicker to import large amounts of data rather than the scripting approach. To do this, you would need to use the wizard to generate you the dat files and then in a command prompt run the following query:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe “TABLENAME” in “FILE LOCATION OF DAT FILE.dat” -E -n -b 10000 -a 16384 -q -S “SQLSERVERDNS” -U “USERNAME@SQLSERVER” -P “PASSWORD” -d “DATABASENAME”

However, due to the manual process of setting up these commands, I instead used SSMS to generate scripts to copy the data following the instructions at http://msdn.microsoft.com/en-us/library/windowsazure/ee621790.aspx:

  1. On your SQL Server database, right click and select all of the objects
  2. Select the wizard option to script to a file and then click advanced:
    1. Change the value of “Script for the database engine type” to “SQL Azure Database”
    2. Change the value of “Convert UDDTs to Base Types” to “True”
    3. Change the value of “Types of data to script” to “Data only”
    4. Open the file generated in SSMS and change the connection to your SQL Azure subscription, select the database to import into and run the script

Conclusion

Sitecore doesn’t support running the CMS site in Windows Azure but now that our data has been created in the cloud, it’s functioning without any issues. It feels like the initial migration to SQL Azure could be more friendly but the additional SQL Azure features brought to SQL 2008 R2 SP1 management studio certainly helps with running queries and visualising the database schema without having to do this through the browser interface.

Energising Yourself

Years ago, I used to hate coming to work. I felt consistently tired, sleepy and unproductive. I was continuously distracted by emails or other people. I did a lot of research and tried lots of things. Some worked, and some didn’t, however I did find myself progressively doing more work in less time. This ultimately let me enjoy my work more, and produce better quality work as a result. Over the years I’ve taken the bits that really worked and either expanded on them or reduced them which have resulted in the list below. They may not work for you, but I hope you give them a go; they really do work for me…

Increasing productivity

Real productivity comes from not just working more hours but from creative solutions that shorten schedules. Think about how you can remove barriers that distract you, decrease your efficiency or make work dull. Flag these impediments to your team at your stand-up and try to remove them as soon as possible.

Make a commitment

Write down somewhere (electronic or paper) what you’re intending on doing for the day. Include meetings in this list and estimate each one in hours. Make sure you give yourself ample time for lunch. At your daily stand-up, commit to your team all the things that you intend to finish for the day.

Review your commitment

At your stand-up, talk about what you committed to yesterday and what you’ve achieved. If you missed your commitment, explain to the team why. Someone may be able to help you out.

Iterate your day into 25 minute time-boxes

An adult attention span peaks at about 20 minutes for full concentration. The general idea is to split your day up and review your progress every 30 minutes. Get yourself a timer capable of counting down from 25 minutes and work solidly on 1 item for the entire period. No distractions. The extra 5 minutes is the most important thing, it’s necessary to readjust your concentration. So go get a glass of water, use the bathroom or talk to a colleague about something important – it doesn’t really matter, as long as you get away from the task at hand to something unrelated for a couple of minutes. You’ll feel refreshed for the next time-box. This might seem counter-intuitive at first but it really does work.

After each time-box readjust your priorities and start a new time-box. If it makes sense to continue what you’re doing, do it. If the priority now is to work on something else or you feel like you need a change, then do that instead. Try your best to complete tasks within your time-box where possible.

Long meetings

In my experience, meetings should be broken up into time-boxes. Use your timer to break up the meetings into 55 minute sessions with a 5 minute comfort break in-between. Don’t wait for people to return in the 5 minutes, set expectations that you’re going to continue regardless of whether they’re back or not. Make sure people don’t go check their emails or use their computers. It’s a comfort break! People will feel refreshed and more focused. Something to eat in meetings really helps too. I still remember the moment when I first started providing biscuits and fruit in meetings. People were noticeably more awake, especially on Monday mornings. Try it – it works!

Deal with Email

Try to deal with email immediately. Emails can be put into several categories. When an email comes in make a call:

  • If it will take you less than 60 seconds to deal with – do it.
  • If it takes longer than 60 seconds to deal with and can wait until the end of your time-box, wait until then to deal with it. I tend to find most things can wait until the end of your time-box to do.

If it can’t wait until the end of the time-box, you can drop the time-box. Terminate the time-box and start a new one, dealing with the urgent issue.

If you are getting too many emails to deal with in this way, dedicate a time-box to deal with emails and close your email application for the other time-boxes. This will allow you to concentrate on your work without being distracted.

Organise your email

Once you have dealt with an email, either move it to an archive sub-folder if you need to keep the contents or delete it entirely. The goal is to have only items in your inbox that you need to action. This requires an initial amount of discipline but I found it extremely effective – I effectively have a natural email to-do list that’s self-emergent.

Try to avoid using outlook rules if possible, I found that automatically moving email to a subfolder usually results me missing the email. I personally prefer to read the email then use an outlook quickstep to move it to the correct archive folder.

Work at a sustainable pace

If you work 7.5 hours a day you should get through 15 time-boxes in a day. It’s better to be refreshed and do your work well, rather than worry and be tired all the time. If there is more work to do in the day than you have time to do, you may need to improve your processes or negotiate less work. There are obviously times where extra work is necessary, but working at a sustainable pace leaves scope for short bursts when you really need it.

Increase your focus – Cross-brain activities

Early in the morning, have you ever felt like your brain hasn’t quite engaged yet? One technique I used to teach for engaging the brain and focusing the mind is ‘Cross-brain activities’. The idea is that you pick an activity that engages both hands where your focus crosses your body . For me, this is juggling. The ball moving from one side to the other engages both left and right of your brain and kicks your brain into gear. If you can’t juggle (or can’t be bothered to learn), bending down and touching your right foot with your left hand and vice-versa 5-10 times works quite well too. The results are staggering, especially first thing in the morning. It’s ideal for exams or solving complex logic problems, even if it does look a little silly.

Open a window

It may seem obvious, but fresh air (weather permitting) energizes a room. Hot, stuffy rooms encourage tiredness and are counter-productive.

Relax

If you rush around in the mornings getting ready, try getting up 10 minutes earlier. This may seem counter-intuitive at first, but rushing around less in the mornings reduces stress and allows you to focus your mind before you start work. After work, try to leave work behind. It can be good to reflect to a partner at the end of the day how your day went, but try to avoid checking emails and doing work at home if possible. If you must work at home in the evenings, try to stop thinking about work at least 2 hours before you go to sleep to let your mind wind-down. You’ll sleep better and be alert the next day.

Eat well

Make sure you have a sensible breakfast. Cereal or toast works well as it’s full of long-lasting energy that will last until lunch. Avoid cereal bars if possible. For lunch try and have something high in protein (chicken and nuts for example) and complex carbohydrate (bread, rice, pasta). Chicken pasta is really good. The complex carbohydrate gives you energy and the protein helps muscle recovery, making you feel more alert and less drained. Try to leave the building for lunch if possible to have a visual break from work and get some fresh air.

Keep drinking water

Dehydration is a common cause of tiredness, headaches and being generally unproductive. Try to drink hydrating liquids such as water rather that diuretic such as tea or coffee. The caffeine in coffee might seem like a good idea in the short term, but your body will get used to it and you’ll soon find yourself ‘needing’ a coffee in the morning to wake up. Try cross-brain activities (listed earlier in this article) instead for a sustainable wake-up activity.

Be positive

If you notice someone do great work, tell them! It feels good to be appreciated for the great work you’ve done, and your colleagues will appreciate that you’ve noticed. You don’t need to force your good work down people’s throats. If you do good work, they will find out themselves. If something goes really wrong, ask for help and deal with it as a team. Make a note to bring it up at your team retrospective so you and your team can learn from mistakes and fix any process issues.

Take a step back and look at how good you actually are

Lastly, it’s very easy to forget all the great work you’ve done. It’s very easy to fall into the trap of just talking about the bad stuff. Try to focus on all the great things you’ve achieved. Look at all the amazing software you’ve made and be proud of yourself and your team.

Summary

I appreciate these techniques and advice won’t work for everybody. The nature of continuous improvement suggests that this list will change over time, and I’m sure you will have your own spin on things I’ve suggested. I do hope that you’ve got something out of it and comments are always welcome.

Who’s afraid of the Cookie Monster…? – facing up to the ICO requirements

In IT circles at the moment, there is a frantic buzz as companies scramble to show compliance with the so-called “Cookie Directive” – implemented in May 2011, the Information Commissioner’s Office (ICO) of the UK gave an initial indication that they would allow a 12-month “grace period” for organisations to bring their websites in line.

That grace period ends on May 26th, and in true Agile/Lean/Just-in-time style, this means that many of these organisations are now looking to show that they are complying with the new requirements.  The threat of fines up into 6 figures might have something to do with it – this new “cookie monster” has potentially very sharp teeth!

The aim of the “Cookie Directive” was to reflect a European Union requirement (the ePrivacy Directive) that all sites which use technology such as cookies must only do so on the basis of having collected “informed consent” from visitors to the site.  The two key points from this are:

1) the requirement that users must be presumed to have refused permission unless they explicitly “opt in” – in contrast with previous legislation that allowed sites to presume they had received permission unless they explicitly “opt out”

2) the requirement that users can only be presumed to have given “informed consent” when they understand what they are doing.

The first requirement, in combination with the ICO’s advice that it is not acceptable to rely on browser settings alone (at least for the moment – there is talk that this may be revised as the big browsers release new versions reflecting the requirements…), is meaning that organisations have to think actively about how to go about gaining consent in an explicit manner.

The second requirement, by contrast, is making organisations work out how to state clearly, and in language comprehensible to non-technical website users, what cookies they have and what they do with them.  This is having the beneficial side-effect of educating site owners as to what they are actually using cookies for (and perhaps introducing them to what they could be using them for…).

The loose nature of the guidance provided thus far by the ICO has led many organisations to start looking down the path of “implied consent” – namely that the user, by virtue of their actions, can be judged to have given consent without explicitly ticking a box.  Whilst the line of argument around browser settings has been closed off, at least for the moment, there is some thought going into an approach involving flagging up visibly and clearly to a user that a site uses cookies, with the opportunity to get complete details, and then including a message along the lines of “By continuing to use the site, you consent that you accept our use of cookies as described”.

Whether this is classed as compliant by the ICO is one of many questions that remain to be answered, but as noted by Dave Evans, their Group Manager for Business & Industry, in an interview with EConsultancy.com:

“We will enforce the law proportionately. We’ll look at the risks if and when customers complain to us. If a websites’ cookie and privacy is a risk to many people, we may then take action … not all cookies are equal, and our enforcement approach will bear this in mind”

As with so many things in life, there are as many possible approaches as there are people looking at the challenge, but the International Chamber of Commerce (ICC) offers some interesting guidelines into cookie classification and suggested approaches to consent in their ICC UK Cookie Guide – the suggested approach to generating an “industry best practice” in there offers hope that once some approaches are shown to be compliant and work well, users will be able to expect some level of similarity in experience, all of which helps with the aim of them being more informed when making their decisions.

In the meantime, we can all exercise our creativity in working out how to provide the necessary information and gather consent from users, without excessive adverse effects on user experience.

A few examples of how it has been done so far:

– The ICO’s own site has a fairly basic interface

– Fife Council have a rather robust approach to highlighting the content inline, but their header bar gives fairly clear information as to what cookies they use and what the options are

– As might be expected of a large corporate entity, BT have a fairly graphically involved explanation of what is used.

I can’t use CAPTCHA – does that mean I’m not a human?

Am I alone in absolutely hating any form that makes me fill out CAPTCHA?  I consider myself an intelligent, literate person but struggle to read the strange words and numbers that CAPTCHA presents me with.  I wonder how many people fail to complete forms simply because of these horrible barriers?

One of our teams was pondering this problem the other week and came across this nifty alternative for people to prove they are in fact humans.  http://www.areyouahuman.com/.

I absolutely love this and think it’s a much better alternative and likely to make people fill in forms lots just to play another game – they quote a 40% increase in completions – a marketing dream!!

We’re going to be offering this alternative to CAPTCHA to our clients in the future so if this is something you’d like to see on your website, please let us know.  Works on mobiles and with flash or HTML5 and claims good reliability and scalability so looks like a safe option.

One last thing – do read the instructions on the games carefully – I initially struggled trying to put non-food objects in the fridge and to pin flying objects to the ground.  Clearly I’m not as intelligent as I’d like to think I am!