Blog Archive

Azure Direct to Azure in Open

Moving vanThere are three main routes to purchasing Microsoft Azure services:

1 – Direct through Azure.com and your credit card is billed monthly in arrears for the services you use.  Can result in a scary bill if you’re unsure of the cost of the Azure services.

2 – Purchase an Azure ‘top-up’ via an IT reseller through the Open volume licence channel.  Just like a mobile phone top-up; the top-ups are available in multiples of $100 and if your credit runs out, your services stop until you top-up again.

3 – Purchase via an Enterprise volume licence agreement.  You can read more detail in an earlier blog post about How to buy Azure.

Imagine you are an IT reseller.  You might actually be one in which case, not so tricky.  You have a new customer who has been running some infrastructure on Azure and purchased the services direct.  You’ve worked hard to persuade them that you can offer a nice managed service with single billing.  To effect this, you’ll need to move them from direct into Open licensing.  How do you do this?

Call Ghostbusters Support

First, you need to create the new Azure in Open subscription and also make sure that the service administrator is the same on both the subscriptions.  This will involve the following steps:

  1. Log into https://account.windowsazure.com using the Live ID of the account owner or delegate for the new Open subscription
  2. Once you log in, you should be at the Subscriptions page of the Account tab but if not, click on the Account tab at the top of the page.
  3. Select the subscription for which you want to change the Service-Administrator
  4. Click on Edit Subscription Details on the right hand side of the page
  5. Change the Service Administrator to the same as the customer’s direct subscription

Then phone Azure billing support to ask them to migrate the existing services across.  Billing support is included in all Azure subscriptions.

Not all services can be migrated from one subscription to another but here is a list of services that should be ok to move:
Virtual Machines
Cloud Services
CDN
Web sites
Media Services
Service Bus
Storage
Multi Factor Authentication
Traffic Manager
Mobile Services
Virtual Network
Access Control Service

Some services can be migrated easily by the partner or customer (self-service migration):
VSO
SQL DB
Multi-Factor Authentication

Finally, some services cannot currently be moved:
Azure Active Directory
BizTalk Services
HD Insight
Backup 
Hyper-V Recovery Manager
Azure Store
Import / Export
Scheduler
Management Services 
SQL Reporting
Caching

If in doubt, support will be able to advise but this should give you an idea of what’s possible.


How to Price Azure Backup

Safe backupAzure Backup is a great feature for simple disaster recovery to the cloud.  As with many of the Azure services, it improved and can now retain backups for a silly 99 years.  The pricing model originally depended on the total storage that was backed up but it was a lot more expensive than simple Azure storage and that made long-term retention uneconomical.  Pricing changed in April to reflect a more logical, but harder to understand, model.

Azure Backup differs from Azure Storage because it’s a service which includes bandwidth for transferring the data, the backup agent, compression and encryption.  You could simply run an on-site backup and copy up the backup files to Azure storage but you would not have encryption, you would need to manually perform the upload to cloud and if you wanted to restore any files, you might incur bandwidth charges.

The Azure pricing calculator is rather confusing but essentially when pricing Azure Backup you have the following two steps:

First, determine what you are protecting and how large each instance is.  You might be protecting a Virtual machine (this could be on-premises Hyper-V or an Azure virtual machine, Windows or Linux).  You might be protecting a Windows Server (perhaps running a server application or just a file server).  Finally, you might be protecting a Windows client machine as we blogged about previously.  Note that instances should all be 64-bit and some workloads, especially application servers like SQL Server or Exchange, will require System Center Data Protection Manager.

How to calculate Azure backup cost

Small and medium are pretty easy to calculate.  If you have large instances, you will be paying £6.109 (prices as of May 2015) per 500GB so a 1.3TB backup would cost you £18.33 per month.  A simple protection estate could be:

Instance Size Cost
 Windows Server 300GB £6.109
 Windows 7 laptop  45GB £3.0545
 Linux virtual machine 30GB £3.0545

And the cost for those would be £12.22 per month.  So that takes care of the backup service; the agent, compression, encryption and bandwidth.

Next we need to calculate the cost of the storage.  Microsoft have wisely brought this in line with the standard Azure Storage costs and you have the choice of locally redundant where your backup files are replicated three time within a single datacentre (e.g. Dublin) or zone redundant where they are replicated three times in one data centre and then three times in geographically paired datacentre (e.g. Dublin and Amsterdam).

How to calculate Azure backup cost

 

We’ve put a typical price per GB in the table above.  The actual figures vary with the amount of data you store and you can view current prices on the Azure Storage Prices.  Determining the amount of storage is a bit of a guessing game as it depends on how much the data changes (the churn), how many restore points you want to keep and the level of compression that can be achieved.  A file server with lots of Word documents will be compressed far more than a file server containing hundreds of .jpg images because the jpeg format is already compressed.  Azure will only charge for the actual storage used so your estimate doesn’t need to be accurate.  In our example, we might use the following factors:

1- the total storage of 375GB

2 – locally redundant storage because we only want an archiving and backup solution to replace tape-drives

3 – 20% of the data changes between backups

4 – 10% compression (this is conservative; a typical compression should be around 30-40% depending on the type of data being backed up)

5 – a backup every week

6 – retention period for the backups of 1 year (for a maximum of 52 backups stored after a year)

Our back-of-a-napkin calculation would be 375GB initial backup + 52 further backups would just be the data changes at 75GB (20% of 375GB).  Total of 4.275TB, with compression at 10% this comes down to 3.8475TB.

So after 1 year (at which point we will have a rolling 52 backups retained), our monthly cost might be £53.87 (for storage at a rough £0.014 per GB) + £12.22 (for the protected instances) = £66.09

For more technical information about Azure, sign up for one of our courses and gain your professional qualification.

 


Excel – Paste into Visible Cells Only

Microsoft Excel

 

Sometimes Microsoft Excel is just too helpful.  Like American shop assistants to an English shopper (I’m not being xenophobic, I’m just not used to lots of people asking if I want help finding things (try shopping in Reading on a Saturday afternoon).

Anyone who has tried to paste data in a filtered Excel spreadsheet knows this.  Excel will also paste the data into the hidden (filtered out) cells.  It obviously thinks it’s being helpful but it’s really not.

There have been many suggested workarounds but it comes down to using Paste into Excel Visible Fields or a Paste into Excel Visible fields only with code.

There are two solutions that we use.  If you’re running Excel 2013 or above, you can utilise Flashfill.  For earlier versions, you might be able to use the Fill function..

Let’s look at Fill first.  Here is our example sheet:

Excel Paste into Visible cells only

 

A nice simple table with numbers in column B, whether they are odd or even in column A and the square of the number in column C.  What I would like to do is filter on odd numbers (because I am a little odd), copy the square and paste those into the new column D.

Let’s try to do that in the most obvious way and see what happens.

Filter the table to show only odd numbers.  Select all the squares in column C and copy.

Excel Paste into Visible cells only

 

Click in cell D2, right-click and select Paste Values.  But wait!  Only half of the values are shown.  That’s because Excel is being over-helpful and pasting into the hidden, filtered-out rows as well as the visible rows.  It would be lovely if there was a ‘Paste Values into Visible Cells’ option but you’ve already spent an hour searching the internet to discover there just isn’t.

 

Excel Paste into Visible cells only

 

If we clear the filter, we can see exactly that behaviour.  Our five selected cells have been pasted into the interim rows.

Excel Paste into Visible cells only

 

 

 

 

 

 

Using Fill to Successfully Paste into Visible Cells Only

Let’s go back to our filtered table.  However this time select the cells and the column next to it.

Excel Paste into Visible cells only

Now go up to the ribbon (Home tab) and click Fill and Fill Right.  Obviously if your destination column is to the left then feel free to hit Fill Left instead.

Excel Paste into Visible cells only

 

And voila, unlike the previous attempt, we are seeing all five desired values.

 

Excel Paste into Visible cells only

 

And just to be sure, let’s clear the filter condition to make sure nothing has been copied into the hidden rows.

 

Excel Paste into Visible cells only

 

Bingo.  We have our desired outcome.  Obviously this only works in the same sheet and if your columns are adjacent left or right to the cells you wish to copy.  If there are columns in between, you can hide those columns and this method will still work; Excel doesn’t paste into hidden columns in the same way it pastes into hidden rows.  In the screenshot below, I moved column A between the source column and the destination.  I filtered on Odd numbers in the same way, then hid column C.  Select Columns B and D and use the Fill Right method and as the screenshot works, once I unhide column C and clear the filter, everything still works out ok.

 

Excel Paste into Visible cells only

 

Flashfill Will Only Update Visible Cells

In Excel 2013, we have the lovely Flashfill feature which we blogged about previously.  Flashfill will also help but it’s not relevant for Excel versions earlier than 2013 (or Office 365 ProPlus if you ‘re in the cloud).

You can filter on odd numbers, type 1 in the first cell of the destination column, type 9 in the next cell down, hit Enter and then CTRL + E to force Flashfill to take over.  All the desired cells will be copied and if you clear the filter condition, you’ll see that the hidden rows haven’t been touched.  This is why we love Flashfill!

If you’d like more hints and tips, subscribe to our newsletter or better still, sign-up for one of our courses.

 


Modern Attachments in OWA

I’ve just been sent an email with a PowerPoint template attached.  All would be fine except this is the fourth version of the template file.  I’m lucky that I don’t need to worry about storage limits in my inbox but I still don’t like multiple versions flying about and duplicated search results.

We all know we should put the file in a shared location and send a link to so we don’t need to worry about different people having different versions.  But it’s never been easy.

Modern Attachments with Outlook Web App

If the file is on OneDrive for Business (we’re assuming you don’t put business files in OneDrive), I can easily attach them to an email and the sharing is done for me.  I don’t need to go into the ODfB folder and share.

Below I have a document stored on my ODfB but shared with no-one.

OneDrive Modern attachments

 

 

In Outlook Web App (OWA), I compose my email in the normal way and insert attachment.

 

OneDrive Modern attachments

 

OneDrive for Business shows me recent files.  This is a fairly new enhancement along with the ‘shared with me’ view.  I select the file I want to attach.

 

OneDrive Modern attachments

 

The all-important question; do I want to attach this file and endure the pain of resending it every time something changes.  Or do I want the simplicity of sharing the file via OneDrive?

 

OneDrive Modern attachments

 

Within the email, I can use the dropdown on each attachment to change the permissions from the default of edit.

 

OneDrive Modern attachments

 

And once I send the email, I can see that the share and permissions have been set for me automatically back in ODfB.  Nice.

 

 

OneDrive Modern attachments

 

An upcoming feature of ODfB is expirations on shared links.  That means I could share a file or folder for a week and the permissions will automatically revoke after that time.  How this will surface in the attachment process, I don’t know.

Currently, this feature is only available via OWA but it will be included in the rich Outlook client sometime in 2015 (no timeline) and in the mobile Outlook apps for iOS and Android before July 2015.

 

 

 


OneDrive for Business Roadmap

OneDrive for Business logoI thought we’d have the Swedish logo for this post.  We like a bit of international flavour.  Did you know that Excel FlashFill in Danish is Hurtigudfyld?

Back in the real world, we get a lot of questions around OneDrive for Business (ODfB), the perceived reliability of the synchronisation engine and feature requests such as being able to select individual folders for synchronisation as you can do with plain old OneDrive (and just to clarify, this is not something that ODfB can do at the moment).

Back in the Mists of Time (well, 8 years)

A little bit of background often helps people to understand the situation we’re in at the moment.  SharePoint 2001 and 2003 were the early versions of the collaborations and portal tools we know in Office 365 and on-premises today.  They were good at document management and collaboration but had limitations for people that frequently worked offline.  To fill that gap, Microsoft bought a product called Groove in 2005 which allowed sites to be used both offline and between internal and external users.  When I used to demo Groove I asked people what they’d do if they needed to transfer a 50Mb file to a customer in another country; too big to email so it would be a case of posting it or setting up an FTP site.  We all think Dropbox or OneDrive now but they simply didn’t exist back then.

Groove was renamed to SharePoint Workspace before being discontinued and replaced with ODfB.  ODfB doesn’t have all the functionality of SharePoint Workspace 2010 but it does allow for offline synchronisation and sharing with external users.  Don’t get me started on Windows Live Mesh; I loved that but it was in turn replaced by OneDrive.

The reason I’m reaching back into the dim mists of time is that ODfB continues to use the Groove synchronisation engine (Groove.exe) to keep your online and offline files up to date and this is all about to change.

Cloud ODfB vs On-premises ODfB

Before I go into the OneDrive roadmap, let’s also consider for one paragraph, the difference between ODfB in Office 365 and the on-premises SharePoint ODfB.  Although these are called the same the gap in functionality will widen as Microsoft have stated in their “cloud-first engineering model”.  The cloud version of ODfB is already more advanced with features such as shared-with-me, responsive pages, integration with Delve, quick command bars, context menus and drag/drop support.  While many of these changes should be included in the next on-premises release, many will not.  Specifically those related to unique cloud technologies such as Delve.  Also, because Microsoft are running the cloud ODfB, they are able to keep up with market demands for features such as unlimited storage, easy mobile access without puncturing your firewalls, external guest sharing, etc..  These are unique values to a cloud offering and on-premises deployments will never be able to compare.

Big Changes in the New ODfB

The first big change is already available in the mobile clients.  At present on the pc there are two sync applications; OneDrive and OneDrive for Business.  Confusing and it leads to terrible jokes like OneDrive is really TwoDrive.  Ha ha, ahem.  The mobile sync application is a unified app which lets users connect to both OneDrive and ODfB.  This will be coming for rich clients too.  The back-end services will not be merged however: OneDrive will remain consumer and ODfB will remain enterprise-led with service level agreements and so on.  Think Hotmail vs Exchange online; one is consumer and the other is an enterprise service but I can connect to both using a unified client.

The next generation sync client will be in preview sometime after July 2015 for the pc and Mac and should go on to general release in October-December.  There will also be a unified web client.  We’ve already blogged that the storage limit of 1TB is being increased to unlimited storage over this year and the 20,000 file limit is also removed for general release of the next generation sync app along with support for files larger than the current 2GB restriction although this is likely to remain limited at 10GB per file.

Additional features are being rolled out now and existing Office 365 tenants will start to see these soon.  Too many to mention in a single blog post but you can keep up to date via the Office 365 roadmap.  There are some great compliance features being surfaced in an Office 365 admin centre area called the compliance center (sic) so keep an eye out for that in your tenant.  These include:

Auditing and reporting – the ability to view what users are doing in ODfB within a certain date range
Data Loss Prevention (DLP) and eDiscovery – notification and control if certain data is uploaded, e.g. files with credit card numbers.
Mobile Device Management (MDM) – tighter integration with Intune
Information Rights Management (IRM) – protection for ODfB files and libraries
Sync controls – being able to block certain pcs from being able to sync files offline
Data retention policies – for example, auto delete or archive files that haven’t been modified for 6 months
Encryption at rest – data store in ODfB will be encrypted
Compliance – ISO, EU Model and other compliances; a key differentiator between OneDrive and ODfB

The compliance center screen is shown below.

Office365 Compliance Centre

 

Microsoft wants customers to be confident about the importance of ODfB and the effort they’re putting into getting it right.  We all swear (a lot) at ODfB synchronisation right now and sometimes it’s embarrassing to discuss this with customers but the roadmap is exciting and the future looks bright.

If you’d like to learn about SharePoint and ODfB in more technical depth, have a look at our courses.  We use the best trainers so not only do you learn the course material but we can provide the most up-to-date information about the technologies.