Blog Archive

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.

 

 

 


Lync Translator – Klingon vs Welsh



Can you Confused peoplespeak Klingon?  Nope, nor can I.  Unless I eat a hot chilli and then I emit sounds which could be mistaken for Klingon.

Lync can translate from and into not just one dialect but two dialects of Klingon.

Lync 2010 client application (and 2013 of course) has a little-known conversation translation feature which is great fun to try but also very useful if you need to collaborate with people who don’t share your language.  Let’s take an example of a local council.  A newly arrived Romanian strolls into the council office reception and sees a kiosk where they can select help with various councily things.  The resident selects their preferred language, selects their requirement (e.g. housing), is connected via Lync to someone in the appropriate team who can instant message (IM) in English whilst the resident IMs in their preferred language.  Or Klingon.  [Disclaimer: I am not in any way stereotyping Romanians, housing applicants, council employees or Klingons.]

In the early days of this feature, Welsh translation was not an option.  I asked Microsoft HQ why they had chosen to offer Klingon before Welsh and they came back with the perfectly reasonable answer that more people in the world spoke Klingon than Welsh so it was a higher priority.  Sorry Cardiff.

The feature does require enabling (if you want to try it yourself you can see one method on this blog page) but is fun to try.  Microsoft is not promising that you’ll be seen as fluent but it overcomes language barriers quite effectively.

Turn on Lync Conversation translator

 

Start Lync Conversation translator

 

Lync Conversation translator preview

The new Skype for Business will go even further by translating the audio in conversations.  Skype Translator is in preview currently and only works with English and Spanish but is a promising feature.

Sign up for the Skype translation preview at Microsoft.com.  You can also read our blog post on licensing Skype for Business and of course take one of our Office 365 or Lync Server training courses and gain your Microsoft qualification.


Office Demos to Make Customers Cry



User jumping for joy

We don’t mean make them cry in a bad way.  Let me set the scene.  Microsoft have between 1-1.5 billion users of Office applications worldwide.  The Office developers work really hard to make the applications intuitive so users can just get on and use them without needing to take a day’s training course in Outlook or Excel.  However there’s a downside.  Users tend to use new versions of Office in the same way they used the previous version so they don’t really see any immediate improvement and that’s part of the reason we see customer inertia; no-one takes the time to point out some of the fantastic things they can now do.

With new Office versions, there’s really two buckets of goodness – firstly, a bunch of new features and secondly, improvements.  The improvements might include a single button which does what used to take the user 5 minutes and twenty clicks in the previous version.  Or perhaps an improvement will stop users swearing so much!  New features are great too but I find they’re best learnt organically; take a couple of minutes each day to look at a new feature and if it will help, then practice it but don’t try to learn every new trick in Office from day 1.

So we’re starting a series of very quick demos that you can easily emulate either to learn from or to repeat if you have customers that use Office.  Welcome to number 1.

Excel Flashfill

1. Download and open the sample Excel file.  Once you’ve downloaded that, pat yourself on the back for ensuring you had up-to-date anti-malware installed and you can confidently download files from the great unwashed Internet.

2. You’ll see a simple table with five columns.  Column 2 (Data) holds concatenated strings which we need to split out into the correct columns, so the Manager column will hold names such as John, Jenny and Bill and the Category column will hold the type of expense such as Advertising, Events and Digital Marketing.  Some Excel users will look in the help for a string function that will work.  Others will look in the ribbon and perhaps try out Text to Columns.  Most users will see there’s only twenty table rows and just type or copy and paste.  That’s how errors occur; one Bill might be typed with three l’s in it; we won’t notice and the reports will be wrong.

Excel flashfill demo step 1

 

Excel flashfill3. Click in cell D4 and type John.  Hit ENTER to go to cell D5 and type Jenny.  As soon as you’ve typed Jen you should see Excel volunteer the rest of the rows.  It’s as if a little Excel intern has been watching and is now stepping into take over your work.  How lovely.  Hit enter to accept the Flashfill.

4. Now click in cell E5, type Events and hit ENTER.  Hit CTRL+E to force Flashfill to evaluate the pattern at this point and you should see the suggestions.  Press ENTER to accept.

5. Flashfill can do smarter things too.  Click in cell F5 and type John heads up events.  Then hit enter to move down into cell F6 and start typing Jenny heads up advertising.  Flashfill will complete the rows for you, including respect to your capitalisation of the category name (keeping it lowercase).

Customers have asked at what point Flashfill is checking.  You can be reassured that this is nothing scary.  We’re all happy with autofill; type in 1 in a cell and 2 underneath and you can have Excel continue the numbering pattern t0 3, 4, 5, etc.  Flashfill is just an extension of autofill, that’s all.

Flashfill step 3Sometimes Flashfill will not guess correctly first time in which case you ignore the greyed out suggestions and keep typing rows.  Your little Excel intern will keep watching and at some point will guess the correct pattern at which point you just hit ENTER to save lots of typing!  Sheet 2 includes an example of this.  Click in cell C2 and type Adriana from Germany.  Go to cell C3 and type Billy from United Kingdom.  Flashfill is suggesting incorrect matches at this point but you just ignore it and keep typing in the rows.  You will need to get down to row 5 (Damien from Germany) before the suggested pattern is correct and at this point you can accept it by hitting ENTER.

Flashfill is available as a Ribbon command too (on the Data tab) and is a feature of Microsoft Excel 2013 and Office 365 ProPlus.

Subscribe to our Twitter feed for more hints and tips, take one of our Office courses or let us run an Office Buzz Day for your users.


PowerPoint Presenter View Says No



PowerPoint Presenter ViewAn attendee had an issue with a PowerPoint file and could not choose ‘Use Presenter View’ as it was greyed out and disabled.  What’s more, when the slideshow was started, it didn’t take up the whole screen but annoyingly, played in a window and left the system tray icons at the bottom of the display.  How is a speaker supposed to present their awesome slideshow on plans for World Domination v1.0 if they can’t even get PowerPoint working?  As you can imagine, we use PowerPoint quite a lot so were happy to point out the issue.

First thing to check of course is whether the dual monitor option in Windows is set up correctly and in this case it was.  The second cause is a feature of PowerPoint that is less well-known because most slideshows are presented live by a speaker.  An alternative is to set-up self-running presentations, either in full screen (for example on kiosk displays) or in a window.  These will typically use recorded timings for each slide and animations so they play and advance in a timely manner and might even loop continuously.  To set your PowerPoint deck up to run automatically, choose Set Up Slide Show in the Slide Show ribbon tab.

Set up slideshow button

 

 

This will open a window where you can set your play options.

Set up PowerPoint slideshow

 

 

 

 

 

In our attendee’s example, the Show Type was set to Browsed by an individual which meant the show would play in a resizable window and not full screen or presenter view.  Kiosk mode plays the show full screen and uses timings to advance the slides; users cannot click or touch to advance.  The default is Presented by a speaker and for some reason this deck had that option changed.  Once we’d selected the correct show type, the Use Presenter View checkbox was active again and the world is happy.

Of course, if you’d like to know more hints and tips on Microsoft PowerPoint or any Office application, we’d love to hear from you.

 


Save the Photographs!



My wife takes digital photos and downloads them onto her laptop.  Then she deletes them from the camera.  If you have ever taken a hard-drive apart, on those rainy days to avoid doing dull IT tasks, you’ll know how fragile they are.  Lots of memories on those small platters of film.  No matter how many external hard drives I buy, or funny-shaped USB sticks to persuade her to back up her files, she never does.  So it’s with great anticipation that Microsoft Azure Backup Vaults now support Windows client OS (Windows 7 and Windows 8) as well as Windows Server.

Windows Client backup to Azure

 

If you’re a techie, you might be thinking why didn’t I set up a home active directory domain on an HP Microserver, back-up the client devices that log-on, including my wife’s laptop, and then back the server and thus the client files up to Azure?  Well, I did but the big blue light on the front on the Microserver started to annoy me at night because it was really bright so I keep turning it off and that’s not really a good infrastructure decision.

Let’s walk through how to set your home pc to back-up to the Azure cloudy goodness.  A couple of basics first; you will need an Azure subscription and this back-up solution will cost you money.  However, backups are compressed, encrypted, triple-replicated inside Microsoft’s datacenters and once you’ve set up your back-up schedule, it’s automatic.  And think of your photographic memories safe and sound (although of course you should periodically test your recovery processes as well).

Log onto www.azure.com and go to the Azure portal.  Select Recovery Services and create a new Backup Vault.  Once you have your vault, the dashboard will show you the next steps to take.

Azure backup vault dashboard

 

The client machine needs to register with the Azure Backup Vault.  A few months ago this required creating a certificate but now Azure simply provides a credentials file to download and save onto your local machine.  Next, download the Microsoft Azure Recovery Services (MARS) agent onto the local pc and run it.  The Azure portal provides two options for the backup agent; pick the first option (Windows Server or System Center Data Protection Manager or Windows Client).  The setup wizard is going to ask about proxy settings and will also download the pre-requisites but typically on a home network, you’ll be able to breeze through accepting the defaults.

Azure backup agent installation

 

Azure backup agent installation

 

Azure backup agent installation

 

The final step allows you to Proceed to Registration (or you can Close the setup but if you’re ready to schedule the backup, you may as well proceed to register your pc with the backup vault).  The backup agent will ask for the vault credentials file that you downloaded and then you’ll need to specify a passphrase.  This will be used for encrypting the data before transfer to Azure.  Note the data is encrypted on the client device and stored in Azure encrypted.  Microsoft do not hold the passphrase so it’s vital this is kept safe and secure otherwise you’ll only be able to restore encrypted data.  In fact, the agent will not let you proceed to the next step until the passphrase is saved to another location.

Azure backup agent

Once you’ve registered the server (the agent still refers to your client pc as a server; can’t change everything overnight), the Azure Backup app will be started and you can set your backup schedule.

Azure backup app

The wizard is pretty straight-forward to navigate; you select the items from you local pc to backup (note that Azure will only backup the data that changes after the initial backup).

Azure backup app

The next step is to set up the frequency of the backup, i.e. when it will run, and how long Azure will retain the backups for.  A recent change was the increase of the maximum retention period to 3,360 days; essentially about 9 years so this is an archiving solution as well as a disaster recovery.  However, remember your costs.  Even though Azure backup will compress the data before storage, the more backups you keep, the more storage you’ll use and the higher the monthly cost.  There’s also a limit of 120 recovery points so you may need to balance the frequency with the retention range or you’ll get an error message when you hit the Next button.  For example, the screenshot below is trying to backup once a week and keep these recovery points for 9 years which would easily exceed 120 points.  However, if I set the frequency to monthly (4 weekly to be exact), I’d be fine.

Azure backup app

There’s also a size limit of 1700GB per volume to each backup operation (so if you’re backing up files from multiple drives, you have around 1.7TB from each).  You can stop the backup or change the items to backup and then schedule by clicking Schedule Backup in the main Azure Backup application window.

Recovering items is also straightforward; you can specify the recovery point (date) to restore from and which items you want to restore.  You can also restore these items to the original location or a new location.

Back in the Azure portal, you can see the registered server (or in this case client pc) and also view the protected items and the recovery points.  You can register up to 50 machines against each backup vault and as of December 2014, you can have up to 25 backup vaults per Azure subscription.

Azure backup protected items

And just as a final part to this blog, we have to thanks one of our trainers, Thomas Lee who scored this coup by asking nicely for it!


Licensing Gotcha #1 – OWA



Microsoft Licensing ClarityIf you’ve dealt with Microsoft Licensing, you’ll know there are lots of exceptions, gotchas, myths and riddles.  We want to clear some of those up in our blog posts so number one is a good place to start.  These all assume basic Microsoft licensing knowledge but if you need to top this up with some free training, look at our licensing courses or contact us.

Microsoft Exchange Server is licensed in a server and CAL (client access licence) model.  The CALs can be per-user or per-device.  These CALs provide the user or device rights to access the Exchange server but you need a client to read and send email, deal with calendar entries and so on.  The obvious client is Outlook; part of the Office family of applications.  In the past the Exchange CAL included an Outlook licence but this was no longer included after Exchange Server 2003 and since then, Outlook needs to be purchased separately.

Perhaps you don’t have Outlook?  That’s ok because the Exchange CAL provides the rights to access e-mail, calendar, contacts and tasks through either Outlook Web App (OWA) or through a mobile device via Exchange ActiveSync.

Typically a user will access their mailbox from a number of devices.  This is fine if you have deployed Exchange per-user CALs; the user can log into OWA from pretty much any internet connected device (hotel kiosks, airport lounge machines, home, work, internet cafés, etc.) and the user can synchronise to any supported mobile device.

If you have deployed per-device CALs then the user can only use OWA from licensed devices and can only synchronise from licensed mobile devices.

Hopefully you see the gotcha here.  Exchange is brilliant at providing access anywhere, anytime and on any device but only if you licence per-user.  For organisations that have per-device Exchange licensing, anywhere access becomes extremely restricted.


IE Auto-Scaling Annoys Me



Like many people, I work on multiple monitors.  Take now for example; I’m writing this blog post on my main monitor and Jeremy Kyle is playing in Internet Explorer on the second monitor (is he the father and the brother?  Who really cares?).

My monitors are set to different resolutions and when I drag an Internet Explorer window from my main monitor to the second monitor, it automatically scales to 150% which makes it unusable.  This ‘helpful’ behaviour is actually extremely annoying to me but there is an easy way to prevent this from happening.

Right click your desktop and select Screen Resolution.  Click Make text and other items larger or smaller and then check the box for Let me choose one scaling level for all my displays.

Stop Internet Explorer auto scaling

 

You may need to sign out of Windows and back in again for some bizarre reason but that should solve the issue.

Now, back to Jeremy Kyle, “My teenage son picks his nose too much”.


Dude, where’s my data?



Office 365 Logo

A play on the film title from “Dude, where’s my car?” in which, well actually, we can’t remember the plot but we do remember the title and something about a scene with tattoos.

Putting movies to one side, customers often want to know where their mailboxes reside in Office 365 and Exchange Online.  If my billing address is in Europe I can assume my data centres are located in Dublin and Amsterdam but can I verify that?  Also, the mailboxes for individual users and resources might be in either of those locations.  Here’s where a little PowerShell comes in handy.

There’s a useful getting started with Powershell resource on the Microsoft website.  You can also search the web for myriad explanations but be aware many of these will be out of date especially if you’re using Windows 8.1.

Windows 8.1 includes PowerShell by default so on the Start screen, type PowerShell, right-click Windows PowerShell in the search results and select Run as Administrator.  I’ll explain why you need to run it as admin in a moment.

How to run Windows powerShell

 

So many memories of the old DOS window.  PowerShell doesn’t speak Office 365 by default so we have to teach it by installing the Windows PowerShell cmdlets for Office 365 management and deployment.  Your keyboard should now be bristling with all the power you have at your fingertips.

1 – Set the PowerShell execution policy.  The default setting for execution policy is restricted which would not allow PowerShell to run scripts at all; secure but not very useful.  RemoteSigned tells PowerShell it can run scripts on the local machine but any scripts downloaded from the Internet must be signed by a trusted publisher.  This is also why you need to run PowerShell as administrator; in order to change this setting.

Set-ExecutionPolicy RemoteSigned

2 – Set your Office 365 admin credentials.  A popup window will appear where you type in your Office 365 admin user and password.  This will also tell the PowerShell cmdlets which Office 365 tenant you want to connect to.

$cred = Get-Credential

3 – In order to perform administrative tasks on Office 365 you can connect to the online service using the credentials you supplied.

Connect-MsolService -cred $cred

4 – You can list all the Office 365 admin commands.  Note that Exchange, SharePoint and the other cloud services have their own set of commands.

Get-Command –Module msonline

5 – To connect to Exchange Online, type the following commands.  You don’t need to change anything here; again, the tenant to connect to is specified by the login ID in the credentials.

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $cred -Authentication Basic -AllowRedirection

Import-PSSession $Session

6 – Return your mailbox information to see where they’re located.  If the server name begins with db you’re in Dublin and if it’s am you’re in Amsterdam.  Happy travels!

Get-Mailbox