I think SQL Server as a Box will stay around for a long time, -even though we do Azure. OLTP Load as an example. Limitations as you mention.
As the SQL Server Version's has changed over the year's, there has been new areas to learn, -going from cocommandlinend Query Analyzer to management Studio, -get Dynamics management views. Sure there has been a lot to learn with every New Version.
For the current Version, Hyper-V, SMB 3 and Storage Spaces, In Memory solution's, "hybrid cloud", tuning on Hyper-V for SQL Server (I run around50 always on installations on Hyper-V With Virtual drives.., ehm, files:))
New Security models and .........., I always been thinking that beeing a DBA involves hardware and Storage, -understand NUMA and SPLAT, Volume alignment, lun.. And not to forget Clustering. Active Directory and certificates.
But if we look at the DBA role as you describe, sure we will loose in the long run