There's a series on Azure SQL Database from Jovan Popovic on the SQL Server Database Engine Blog. Jovan has written posts on why database management is easier, the scalability of the platform, and a great one that claims the database engine can't die. I don't know that I quite believe that, especially as the guarentee is 99.99% availability. I'd expect 100% if you really think the engine can't die on you. In any case, Jovan clearly has some pride in his work on Azure SQL Database.
What I think is interesting in the post on the ever living engine is this sentence: "Azure automatically handles patching, backups, replication, failure detection, underlying potential hardware, software or network failures, deploying bug fixes, failovers, database upgrades and other maintenance tasks." This notes that all of these operations are completed in less than 0.01% of the database life, hence the 99.99% guarentee. While that's not 0, it's close, and more importantly, this is something that to which DBAs ought to pay attention.
These are often the tasks that many organizations will hire someone to complete. These tasks are becoming less of a time sink as organizations move to infrastructure as code or cloud computing, though they don't disappear entirely. However, these tasks are mundane, tedious ones in many cases that should be solved once and then deployed easily to multiple instances.
Azure SQL Database and SQL Server share the same code base. Most features get built and tested in Azure and then will get merged into a release for a CU or new version of SQL Server. This means that as Microsoft learns how to better build these features, they will migrate them to our boxed SQL Server versions. With success stories in Azure and strong marketing, I'd bet that more and more management will be questioning whether they need more people, or even any people to handle these tasks in the latest versions of SQL Server.
Don't panic if you're a DBA working on SQL Server 2008/RS, 2012, 2014 or other older versions. Those editions still require your time and things will change slowly for plenty of companies. They won't want to upgrade too many instances at once, especially when there are potential vendor costs as well. You will have a job for some time, and I don't think that lots of those older instances are disappearing anytime soon.
That also shouldn't mean that you rest on your existing skills and don't learn anything new. You ought to be sure you are beginning to learn more about PowerShell, Azure, automatic indexing, and more. Improve your skills and potentially give you more career options. Even if you don't change jobs, you'll enjoy the learning.