Monday, 14 March 2016

Two thoughts on the Apple / FBI kerfuffle

1. A cell phone is (and remains) the best surveillance device ever conceived.
2. Apple is legally, morally and practically justified in opposing the order.

Your carrier has a perfect record of your location 24/7 going back years. Everything you do on a smartphone uses the internet and is the opposite of anonymous.

It’s trivially easy to physically locate any person or service you interact with. Every service you use (Facebook, Google, Skype, etc.) keeps **exhaustive** records of everything (messages / searches / calls / etc. ). Your phone now backs itself up automatically (including photos).

What can you actually do on your phone that **only** happens on the phone? Almost nothing. Even a simple note will be saved to the internet.

All of this data is fully accessible to law enforcement. Carriers and services have special help desks for this purpose. Often they do not require a subpoena under a law called “Communications Assistance for Law Enforcement Act (CALEA)”.

So, what exactly does the FBI want? The FBI would like to use San Bernardino as leverage to force Apple to make it easy for them to search phones again. That’s it.

Law enforcement got a free ride on searching phones due to court rulings that they are not subject to 4th amendment protection and searches do not require a subpoena.

iPhones before 2015 (and most Androids still) can be plugged in to a device (from China!) and all the data is “sucked out”. That’s very helpful if you’re the FBI but unfortunately these devices are **widely** available and heavily used by criminals.

Are you happy to keep your credit card details (ApplePay) on a phone if some thief can “suck out” those details in 2 seconds flat? Not likely.

The back door the FBI wants doesn’t exist on recent (more secure) phones, it can’t safely exist (remember the the TSA luggage key?) and is actually not even necessary. They can and do get 99% of the same data via other means.

The only way to ensure security is to never create a way to break it. Once a back door is created for law enforcement a huge wave of criminal effort will be unleashed to find, buy, or bully (think China) access to it.

Thursday, 17 December 2015

The Sound of 2015

Change of format this year - 12 months : 12 albums. The albums says I listened to most in each month.

Taken from my stats. I’ve also made a Spotify playlist of my favorite track from each album.

Wednesday, 31 December 2014

The Sound of 2014

I still listen to music all day, every day during the working week. So here's my personal Top 10 from 2014 in Letterman-style reverse order.

This is available as a Spotify playlist and each track links to the album it comes from on Spotify.

10. Royal Blood - "Come On Over"
    - Best rock album released this year in my view. Of course this is the year that rock gave up.
9. Phantogram - "Black Out Days"
    - Electro pop trip-hop. Check out their back catalogue if you like this.
8. ODESZA - "Memories That You Call"
    - Ibiza style chillout via Seatle.
7. Lia Ices - "Sweet as Ice"
    - Electro folk? Mystical lyrics plus an ethereal voice with a subtle electronic sheen.
6. Bombay Bicycle Club - "Home By Now"
    - British indie at it's best. Eclectic and sublime.
5. HAERTS - "Hemiplegia"
    - An elctro Fleetwood Mac. Kind of like HAIM but better.
4. I Break Horses - "Denial"
    - Downtempo Swedish electronica, dare I say "chillout".
3. RHODES - "Raise Your Love"
    - British indie folk. Better than the much touted Hozier in my view.
2. London Grammar - "Hey Now"
    - Exceptional. The KEXP live video actually sounds better than the album.
1. The Jezabels - "The End"
    - Still the best band you've never heard of.

I'm on as joeharris76 (of course). Stop by and say hi.

Monday, 8 December 2014

Micro-review of InformaticaCloud for Amazon Redshift

I recently finished an evaluation of Informatica Cloud's platform as a candidate to take over our Redshift ETL workload.

I'm posting this 'thumbnail' for the sake of my own memory as much anything else. :)

TL;DR - It's very _close_ to being an ideal solution but, sadly, has some flaws we can't work around right now.


  • Very straightforward agent based architecture. Very simple to install and consume.
  • Reliably syncs/replicates *simple* data from source DBs via flat files and cleans up after itself.
  • Performs simple and complex joins on the data source. This is a *major* weakness for competitors as they can't do this.
  • All functionality is wizard driven and probably consumable by line of business users.


  • Cannot create SQL based transforms without uploading PowerCenter mappings. This is too much to ask of LOB users.
  • Extracts land as plain text on the agent host before gzipping. This is a *very* slow approach. They should pipe directly to gzip.
  • Logging is *extremely* verbose. Every single row is written to the success or error. You'll quickly create many GB of logs.
  • Upsert operations update *every* matching row in Redshift. This is handled as a delete plus and insert within Redshift, which means we then need a very slow vacuum to reclaim space.
  • Finally, the deal breaker, cannot handle `timestamp with timezone` data types. In 2014. Redshift cannot handle these either but I need the ETL platform to be able to work with them.

Friday, 3 January 2014

The Sound of 2013

I listen to music all day, every day during the working week.

Here's my personal Top 10 from 2013 in Letterman-style reverse order.

10. Drenge - Bloodsports
    - 2 man garage punk. Helmet reincarnated.
9. Young Galaxy - Pretty Boy
    - Indie electro pop from Montreal. The whole catalog is excellent.
8. Blackbird Blackbird - It's War
    - Chemical Brothers meets Morrissey with added Ibiza chill. I was hooked after 10 seconds.
7. Hooray For Earth - No Love
    - Indie rock fused with electronica. The Happy Mondays on a downer in Brooklyn.
6. Mr Little Jeans - Oh Sailor
    - Electro pop with a backing choir, also check out the cover of 'The Suburbs'.
5. Say Lou Lou - Julian
    - Swedish electro pop, high-style and ethereal vocals. Should be taped to the cover of Vogue.
4. Other Lives - Take Us Alive
    - Oklahoma folk-tronica. Discovered via the amazing skate video 'India Within'
3. The National - Graceless
    - It's not 'Fake Empire' but it's pretty damn good.
2. Daughter - Youth
    - Haunting folk. Bleak and beautiful.
1. The Jezabels - Endless Summer
    - Best band you've never heard of. INXS plus Texas with a bit of the Smashing Pumpkins on the side.

I'm on as joeharris76 (of course). Stop by and say hi.

Friday, 6 September 2013

Migrating from SQL Server to Redshift

An overview of the work required to migrate our large SQL Server data warehouse to Amazon Redshift. We faced and resolved a number of issues, particularly around the different Unicodes required by each database.

Thursday, 8 March 2012

How To Copy A Standby Database in SQL Server

I ran across a SQL Server problem today where my Standby database was detached and would not re-attach. This problem originally arose because I wanted to make a development copy of log shipping standby database with restarting the log chain from a full backup.

I couldn’t find a complete solution online so I thought I’d share my solution here. A tip ‘o the hat to Paul Randal for his “Hack attach a damaged database” post which got me going in the right direction.

The Problem

I have a log shipping setup whereby the reporting database is kept in Standby mode. I wanted to make an up-to-date copy for a developer to use. So I (foolishly as it happens) tried to simply detach the standby database and make a copy of it.
When I finished copying it and tried to re-attach it I got the error “Cannot attach a database that was being restored.” Uh-oh, this database is on the end of a very long log shipping chain and, although I haven’t really lost anything, rebuilding the chain is going to cost me a lot of time.
Plus, I really need to make new development copies of the standby database quickly and often. This is not a one time process.

How To Copy A Log Shipping Standby

This may not be ‘the right way’ but it works and forget about detaching the DB that is definitely the wrong way to go, although you can recover from it using a variant of this process.
  1. Take the standby DB offline (SQL)
  2. Create a ‘shell’ DB that will become the new target DB (SQL)
    NOTE: that the internal ‘name’ values must match the Standby DB
    ( NAME = 'MyLiveDb', --!!! Must match the Live & Standby name
    FILENAME = 'C:\My\Data\Path\MyNewDevDb.mdf' ,
    SIZE = 3072KB , FILEGROWTH = 1024KB )
    LOG ON
    ( NAME = 'MyLiveDb_log', --!!! Must match the Live & Standby name
    FILENAME = 'C:\My\Data\Path\MyNewDevDb_log.ldf' ,
    SIZE = 1024KB , FILEGROWTH = 10%) ;
  3. (OPTIONAL) Apply TDE encryption to the target DB (SQL)
  4. Backup the target DB (SQL)
    BACKUP DATABASE MyNewDevDb to disk = 'C:\My\Backup\Path\shell.bak' WITH FORMAT;
  5. Restore the target backup in standby mode (SQL) NOTE: The shell DB must match the state of the DB we want to replace it with
    FROM DISK = N'C:\My\Backup\Path\shell.bak'
    WITH FILE = 1
    ,MOVE 'MyLiveDb' TO 'C:\My\Data\Path\MyNewDevDb.mdf'
    ,MOVE 'MyLiveDb_log' TO 'C:\My\Data\Path\MyNewDevDb_log.ldf'
    ,STANDBY = 'C:\My\Backup\Path\ROLLBACK_UNDO_MyNewDevDb.BAK'
    ,NOUNLOAD , STATS = 10 ;
  6. Take the target DB offline (SQL)
  7. Rename (or delete) the target data and log files (CMD)
    RENAME "C:\My\Data\Path\MyNewDevDb.mdf" "DeleteThis.mdf"
    RENAME "C:\My\Data\Path\MyNewDevDb_log.ldf" "DeleteThis_log.ldf"
  8. Copy standby DB’s data and log files, using target’s filenames (CMD)
    COPY "C:\My\Data\Path\MyStandbyDb.mdf" "C:\My\Data\Path\MyNewDevDb.mdf"
    COPY "C:\My\Data\Path\MyStandbyDb_log.ldf" "C:\My\Data\Path\MyNewDevDb_log.ldf"
  9. Take the target DB online (SQL)
  10. Restore the target DB for normal usage (SQL)
  11. Take the standby DB back online (SQL)
  12. Resume log shipping!

Recovering a detached standby DB

If you need to attach a detached SQL Server standby DB you can do so use a slight variation on the steps above. Simply omit steps 1, 8 and 11 and treat the database you want to attach as the target in the other steps. Voila! :-)
I hope this helps someone. Let me know if you find any issues with the steps.

Disqus for @joeharris76