Book Review: Chris Webb’s “Power Query for Power BI and Excel”

I’ve been experimenting with Power Query and the rest of the Power BI suite in Excel (desktop edition) since it was originally released. But, like most folks that aren’t completely centered in that part of the data-based offerings, the plethora of new functionality and new releases made it difficult to keep up. The online help has improved, but the distinction between Office 365 Power BI  and Power BI desktop and the fast release cadence (updates appear almost monthly), made it difficult to write books (which require pre-production time). Having met Chris Webb at a few conferences, when I heard he stepped up to this challenge with a Power Query book entitled “Power Query for Power BI and Excel“, I was interested in reading.

The book weighs in at 272 pages, and I thought it might take a couple of weeks to do it justice. Once I started however, I was hooked and consumed it in a couple of days. Chapter 1 was a nice introduction that told the story of Power Query’s reason for existence, and described the differences between the experience with different versions (Office 365 Power BI vs Excel 2013 vs Excel 2010). It wouldn’t be too big of a stretch to call this an incantation of SSIS-like functionality for self-service BI. Reading quickly through chapters 2-4, about data sources-transformations-destinations was a thorough demonstration of the functionality of the product, and clarified exactly what was supported. I was introduced to the “Excel Data Model” concept here as well.

It was the chapter on M that was the gem of the book for me. I’d realized that you could extend your Power Query queries with M functions, but didn’t realize that every query was actually a script written in M. Chapter 5 describes M the language and how you can use it to not only embellish Power Query queries, but to even write them by hand. M code that comprises Power Query queries looks vaguely reminiscent of Hadoop Pig scripts, although obviously the surface area isn’t the same. I was impressed by the author’s treatment in “Introduction to M”.

The chapter on Power Query in Power BI Office 365 told me what I wanted to know about how Office 365 extends the functionality of Excel desktop Power Query into a shared, more enterprise-y offering (e.g. query sharing), as well as how it integrates with Sharepoint. And the Multiple Queries and Power Query Recipes chapters helped get me into the “thinking of solving problems with Power Query” mindset.  The first sentence of the recipes chapter “…when you are learning about a new tool it isn’t enough to know what each button does when you press it, or what each function returns when you call it” was right on target.

I had a few minor quibbles. I’d like to have seen a mention of the exact version of Power Query (version number and release date) that the book covers. I knew that new releases are coming out monthly, and was working with the version that appeared a few days ago, but looking at a screenshot that said “Apply and Close” when my version said “Close and Load” (as well as other differences) in Chapter 1 was a bit disconcerting. But that’s more of a “don’t constantly change the GUI on something that’s supposed to be consumer-focused” comment on the product. Or maybe it looks different in the Office 365 version, I didn’t know.

And about the product…I was also surprised, product-wise, that they didn’t support OLE DB (at least for the Analysis Services OLAP provider), or even ODBC, although you can shoehorn these in via the vanilla Excel “data sources” tab. I guess it was to keep the GUI rich and consistent, functionality-wise, but if you can do it for OData… I did especially like the non-traditional data sources like HTML tables/pages and Facebook, although a generic JSON data source would also be useful. Maybe I just didn’t see where the generic JSON functionality lives. The “data sources” book chapter did make the level of support very clear, though.

Overall, I’d recommend this highly recommend this book if you’re getting started, or even if you think you’re pretty far along with this tool. The M and Office 365 Power Query chapters themselves may be worth the price of admission.

Cheers, Bob (@bobbeauch)

New Azure services and evolution of the Service/SQL Server relationship

Today, two new service offerings for the Azure platform were introduced, DocumentDB and Azure Search. These are exciting in themselves and worth a look-see but, to me, they are also occurrences of the phasing out of the “database as an application server” concept, formalized in the SQL Server 2005 timeframe. At least, in the cloud. Did everybody already notice, or was it too subtle?

Services moving inside the database (where they are more tightly integrated with the data they use) predates SQL Server 2005. Rather than rely on the OS for scheduling and alerting activities, SQL Server has always had its own facility (and separate Windows service) for this, known as SQL Agent. A mail component is included in the database as well. But possibly the first big service to be moved into the database was Microsoft Search Server. It was originally grafted on, but over time came to be completed integrated in SQL Server as the fulltext search feature. SQL Server 2005 was the watershed for this concept, including the XML/XQuery, Service Broker, event and query notifications and HTTP endpoints, with SQLCLR providing a programming substrate to be used in conjunction with T-SQL for the new services. This concept was formalized in the Dave Campbell’s paper “Service Oriented Database Architecture App-Server Lite”. Interestingly, these features are often artifices over relational constructs (e.g. XML and fulltext indexing are relational side-tables). There was even lots of work put into using SQL Server as a substrate for the Windows file system (WinFS).

The first move away from this concept of “database as substrate for all” was the removal of HTTP endpoints and it’s replacement by what finally came to be called the OData protocol. OData serves data as either an XML format (AtomPub) or JSON format, which is why, although it’s often requested, it’s doubtful we’ll see “native JSON support” inside SQL Server soon.

Then….to the cloud…

One of Microsoft’s first PaaS offerings, now known as Azure SQL Database, solidified what was to come. Azure SQL Database is a relational database engine only, shipped without most of the “app-server” services inside SQL Server proper. Features that do not exist inside Azure SQL Database include not only the features mentioned above (XML/XQuery survives, but XML indexes, as far as I’m aware, do not), but other nice infrastructure features such as replication. These are to be offered by “the platform”, as in PaaS.

DocumentDB and Azure Search are just the latest pieces of the platform. SQL Agent functionality can be implemented using Azure scheduling and notification services. Service Broker is implemented/subsumed by Azure Service Bus. OData services are part of the service layer, not the database layer. JSON is tightly coupled in DocumentDB, JSON documents subsuming XML documents in many use cases, e.g. document databases rather than XML databases.

A discussion of what precipitated the move from application servers and service-based architectures to database as application server to separate services with service coordination programmed into each application is a whole other set of blog entries…or discussions in bars.  As is the comparing-and-contrasting of how this is being done in other ecosystems, e.g. cluster as substrate for all and a central resource negotiator (YARN) that manages the cluster. Note: SQL Server (and likely SQL Azure Database) uses it’s own Resource Governor to divvy-up the resources. And there’s always the question of who (application and administration-wise) manages and orchestrates the integration of these disparate services; now that’s a occupation that will certainly be in demand.

As far as SQL Server “proper” goes, DBAs need not worry about having less to do. The latest versions now integrate a column-based “database” and its optimizations, and an in-memory “database” (with different storage and isolation paradigms) into the mix. But these are data engines rather than services. Also note the cross-pollination between different parts of “the platform”; the XVelocity engine also lives quite nicely in SQL Server Analysis Services and in Excel. And there’s already rumblings of the implementation of DocumentDB using lessons learned with SQL Server’s in-memory implementation. So far, it’s interesting that neither columnstore nor hekaton have yet to make an appearance in Azure SQL Database. The same “redistribution, integration, and evolution of services” is going on in the ETL/Analysis/DataMining/Machine Learning Microsoft ecosystem too.

One thing about the technologies surrounding software; we always “live in interesting times”.

Cheers, Bob (@bobbeauch)

Don’t drop empty Azure SQL Database servers

In a previous blog entry, I mentioned changes in SQL Azure Database’s new tiers. Another one, that I think is indirectly related to the new tiers, is the disappearance of a message that used to appear (on the portal) when you deleted the last database on a particular Azure SQL Database server. That message asked “Do you want to delete the server as well?”. Lately, this message doesn’t appear.

I’d always answered “no” to the message in the past; having a server hanging around without databases didn’t cost you anything and if I restored (imported) the database in future, I didn’t want to change all my connection strings. The server would, it was said, go away after a certain number of days (60?) without databases.

With the introduction of the new tiers and restore/recovery there’s a better reason not to delete servers with no databases. Your collection of “Dropped but restorable databases” (the PowerShell property for these is RestorableDroppedDatabases) is tied to the server. When the server is deleted, you can’t restore them any more. And, as far as I know (the cost was likely amortized up-front) servers with only RestorableDroppedDatabases still don’t cost you money.

Because the Azure Portal has more mention of “create a new database”, the server concept has sort of taken a back seat. A server in Azure SQL Database is like a “virtual SQL Server instance”. It consists of a master database (similar in concept, but not completely in implementation to SQL Server’s master), an associated administrative login (as well as other logins you’ve added) and a set of firewall rules. In addition to associated user databases. For a complete list of associated properties (and methods and events), use the following lines of code in PowerShell, after getting a credential:

$ctx = New-AzureSqlDatabaseServerContext -ServerName “myserver” -credential $cred
$ctx | get-member

Currently (AzurePS 0.83), attempting to retrieve all the property values returns an exception (you get the same exception trying to access the RecoverableDatabases property so it might be that), so you’ll need to retrieve the values that you want by name.

So don’t forget that servers are important in the new tier world, and dont drop ‘em unless you don’t care about (or you don’t have any, because of time limits) RestorableDroppedDatabases.

Cheers, @bobbeauch

A few tweaks to Azure SQL Database’s new tiers

One of the things that make Azure SQL Database difficult to keep track of is that details of some database behaviors can change, usually without notice or announcement. There were supposed to be announcements on one of the Azure blogs, but that hasn’t happened for Azure SQL Database in over a year, except for the “big announcements” this April. There is no way to confirm that “things used to work this way, and now they don’t”. This is particularly difficult with preview features that are more likely to change during the preview.

That being said, there are a few changes that have happened with respect to the new Basic/Standard/Premium tiers preview. The first one is that you can mix “new tier” and “old tier” databases on the same server now. Previously they didn’t mix; a server could either contain old or new tier databases, but not both. I currently have a server with a Web edition and a Basic edition database. I first the server after the preview started (PowerShell identifies these as “V2″ servers); I don’t have an “old tier” server (V1?) to test out whether it supports the new tiers. Using an “old tier” database on the same server as a “new tier” database doesn’t convey “new tier” behaviors. That is, deleting the “new tier” (Basic edition) database makes it available for restore under the “Deleted Databases” portal tab. Deleted the “old tier” (Web edition) database doesn’t.

Another change is in the implementation of restore/recovery of databases (and deleted databases). Not only have the Basic and Standard tiers “length of available backup” changed (Basic from “most recent daily backup” to “point in time within 7 days” and Standard’s point-in-time from 7 to 14 days), but all editions (including Basic) support point-in-time restore. This also means that the Start-AzureSqlDatabaseRestore (for editions that support point-in-time) now works on all new editions, whereas Start-AzureSqlDatabaseRecovery (for editions that don’t support point-in-time) doesn’t seem to work at all, with any edition.

Finally, the “Automated Export” feature (where you want to backup on a schedule and also keep your bacpac file) is now available for the new tiers as well as the old. Check it out under the “Configure” tab on any SQL Azure Database, old tier or new.

Cheers, @bobbeauch

Extended Events In Azure SQL Database. For real.

I’ve just returned home after teaching two weeks of SQLskills Immersion Events. Total post-event exhaustion for two days. Trying to produce some nice demos on the new SQL Azure Database tiers and services for my SQL Azure Database A-Z preconference ssession at SQLTuneIn. And then this happens…

About a year or so ago, after attending a TechEd talk on XEvents in Azure SQL Database (ASD, the PaaS offering), I looked for and found metadata and blogged about it. The feature never seemed to come to fruition and after a while, I stopped looking.

This morning, I received email from Egbert Schagen, a person I’d met in my conference travels. He let me know that XEvents now appeared to work in ASD. They appeared in the SSMS object explorer. Egbert said he’d seen no announcement for it, and by web search he came across my old blog entry. He thought I might like to know. And he was right.

And so they DO work. I tried this in an Standard S1 Edition database I had and Egbert said they also worked in Web/Business edition. As with almost everything ASD, these event sessions exist at a database level, rather than in master.

There are 12 pre-configured event sessions. It doesn’t look like you can create your own. The sessions are:

The event sessions with an asterisk can be turned on and you can “Watch Live Data” in SSMS. The rest of them look like they require a credential because they write to Azure blob storage. More on that as I try and figure through this with the metadata. It appears you can only run one event session at a time. The second concurrent one always produces a “system is currently busy” message on attempting to start it.

I’m not sure what the “official” status is, but for now it looks like you *can* trace in Azure SQL Database. Great! Thanks, ASD developers. Thanks, Egbert.


Installing and Running HDP 2.1 on Windows

And now, for something completely different. Hadoop.

I’ve been playing with everything I can get my hands on (and can find time for) with Hadoop for a while now. Have tried Hortonworks on CentOS (seems like the Hortonworks “reference” platform), Hortonworks HDP on Windows, and HDInisght. I even think I may have a clue about what big data is and why it matters. But that’s a topic for another time. It’s intruiging that Hadoop on Linux has a built-in GUI dev tools (like Hue) but the Windows’ versions (put out only by Hortonworks/Microsoft) have no GUI except for the NameNode/YARN/HBase status pages, and almost mandate you work on PowerShell. I thought the Linux folks were the bigger command-line wonks. I haven’t tried any other distos (e.g. Cloudera) yet (or tried building it myself for that matter).

Over the weekend, I installed Hortonworks new GA distro for Windows. The main reason I installed it was to try out Hive 0.13 (with vectorized query – think SQL Server columnstore batch mode, ORC files are the columnstore) and Hive under Tez. Think of Tez as a more flexible, powerful, faster MapReduce. I did a single-node install for simplicity. Ran into some problems and anomolies that I’ll mention here. But did get most of what there is working. I say “what there is” because the reference diagram at mentions all the things I do see, but the Windows distro doesn’t include Accumulo (another fast data storage offering based on BigTable) and Solr (a search offering). Perhaps the CentOS distro does. And, of course, no Hue. And, although there’s an exe and it’s in the list of Windows services, HWI (Hive Web Interface) doesn’t start up. That’s been reported by others too.

BTW, this version isn’t available on HDInight yet. Maybe soon. Microsoft allocated some engineers to Hive 0.13 (and other phases of the Stinger Initiative), so I’m pretty sure they want it in HDInsight too.

There were the usual potholes in the docs, if you’re trying to follow the docs by rote. First, the docs warn you not to use path names with spaces in the Java or Python installs ( Good idea in any case (don’t have to put quotes around names in command lines) but especially with Unix ports to Windows. But they warn you in the Python directions and Python 2.7 doesn’t install in “Program Files”. Java does. And one of the instructions says you should add (step 5c) e.g C:\Java\jdk1.7.0_45\bin as the value for JAVA_HOME. Don’t use the “bin”. Step 6b is wrong too; the path should include the “bin” directory. Easy to fix. The install reports “JAVA_HOME is not set” if you mess it up. Ask me how I know…I should have been paying attention.

If you choose “Run Hive Under Tez” you must peform Section 6.1 “Setting up Tez for Hive”. But there’s a glitch there too. Step 5: “Copy the Tez home directory on the local machine into the HDFS /apps/tez directory” is wrong too.
%HADOOP_HOME%\bin\hadoop.cmd dfs -put %TEZ_HOME%* /apps/tez
should be:
%HADOOP_HOME%\bin\hadoop.cmd dfs -put %TEZ_HOME%/* /apps/tez
(note the slash after %TEZ_HOME% and before the *)

Until you do that, Hive fails starting up the command line app with /apps/tez/lib not found.

And its likely that step 6 should be:
%HADOOP_HOME%\bin\hadoop.cmd dfs -rmr -skipTrash /apps/tez/conf
because there is no conf3 directory.

With the right copy command the Tez smoke test and Hive under Tez run just fine. I did have trouble with the HCatalog and Hive smoke tests at first (something about hadoop user needing a GRANT, GRANTs are expanded in Hive 0.13), but after enough tries (3 reinstalls of the distro and 7-8 runs of smoke tests) it just started working. Don’t know exactly what I did to make that start happening. So all the smoke tests (including optional components) succeed.

So THANKS folks! Lots more to try. There’s a lovely Hive/Tez/vectorized query tutorial (do it sans-GUI) that shows the new features off. And I’m glad that I can specify precision/scale for decimal types. And all other Stinger features and additional ecosystem components too.

BTW, I ran this on Windows 2012 R2 OS, even though the docs say only Windows 2008 R2 an 2012 OS are officially supported. It lists these under minimal requirements.

Hopefully this information will be helpful to someone else.

Cheers, @bobbeauch

Last week in Azure SQL Database – Part 5 – Wrapup

This post contains miscellaneous information about the current/future state of Azure SQL Database (AST). You know I couldn’t write just one more blog post when I said I would in Part3, didn’t ya’? This post has some properties of a rant in some places, but I’m genuinely interested. I try not to judge technologies, just tell people how they work…in detail. This post covers:
Additional metrics in the new tiers
ASD database functionality status
Sync Services status
Scale-out status

The new tiers contain 3-4 new metrics that can be turned on and observed in the portal. Select a existing new-tier database, choose “Monitor” then at the bottom, choose “Add Metrics”. New additonal metrics are:
CPU Percentage %
Log Writes Percentage %
Physical Data Reads Percentage %

Old “Additonal Metrics” are
Blocked by Firewall Count
Throttled Connections Count
Storage Megabytes

Original Metrics are:
Deadlocks Count
Failed Connections Count
Successful Connections Count

I said 3-4 new ones because I think “Storage Megabytes” was in the old tiers as well. There may be PowerShell properties to control and monitor these too. None of the additional metrics show up in Azure PowerShell 2.3, or at least I can’t find them.

Next, about new functionality (database functionality) in ASD. It’s been a while since there’s been any new (visible) database functionality in ASD. The new tiers don’t provide any; @@version is the same as the old tiers, as is the database metadata. Sequences and SQL2012 windowing functions are still missing. Event sessions were announced with fanfare at TechEd a year or two ago, then metadata appeared, but the feature hasn’t appeared.

The last new functionality tidbit that I remember is the addition of compression (ROW and PAGE) about a month ago. I tried this and it works. However, without a sys.partitions metadata table (it’s “not found”) it’s impossible to see what existing tables/indexes have been compressed. On a whim, I looked in sys.tables….and found some metadata fields from SQL2014! (referring to in-memory OLTP). Of course, in-memory OLTP isn’t there either, but this begs the question: what version of SQL Server is this based on, anyway?

Then there’s Sync Services. It still exists, still works (or can be configured) on the new tiers, but it’s still preview…for the last approximately 2 years. What I once referred to, in one of my more rude moments, as “eternal beta”. No word on it’s GA date or it’s fate yet either. Geo-replication may provide an alternative for Premium customers, but for non-Premium and sync to on-premises/SQL Server, we’re still using either Sync Services (preview) or Export/Import.

Finally, about Federations being deprecated… The replacement for federations, according to this page: is Custom Sharding. But it’s description: “Design custom sharding solutions to maximize scalability and flexibility for resource intense workloads. These solutions use application code across multiple databases.” leaves a lot to be desired. What does the custom code (that replaces a built-in feature) do, exactly?? I’d heard there was to be “prescriptive guidance” but, so far, I see no guidance at all, except “write your own”. Maybe, some SAMPLE CODE? Especially because the placement point for Azure SQL Database is “New applications that are designed to scale-out”.

Granted federations had a lot of missing features (fan-out queries and ALTER FEDERATION MERGE are two that come to mind) and had its drawbacks, but (MHO…) you can’t replace a built-in feature with (no, yet) prescriptive guidence unless no one’s using it currently. But some folks ARE using it. I’ll need to keep updated as things develop in this area. I’m hopeful that things will develop….

Wrapup: I like the new tiers. Love the new utility features. Wonder about the database features going forward. But these were not MEANT to be announced last week. There were enough announcements to get used to. ;-)

Cheers, @bobbeauch

Last week in Azure SQL Database – Part 4 – Performance Levels

In this post I’ll address perhaps the most important of all the announcements, performance levels.

The 6 new ASD tiers provide different levels of performance and different resource guarentees/reservations. There is a chart here: that lists performance levels (among other things) and there is a different chart here: that gives more details on performance, predictability, and resource guarantees (like max worker threads and max sessions).

The part concerning predictability is useful because ASD servers (physical servers in datacenters, not SQL Server master database they call “servers”) are shared. Because servers are shared, there is a syndrome that affects predictability called the “noisy neighbor” syndrome. Imagine someone who shared a physical server with you is performing a database stress test…maybe during your business’ peak time…

Performance is defined in a new, curious, unit of measure called a DTU or Database Throughput Unit. DTUs are meant to allow comparison between the new tiers. Currently, there is no direct comparison between the new tiers and the old (Web/Business) tiers, possibly because there was no performance SLA in the old tiers at all.

DTUs are based on throughput with a described benchmark (The Azure SQL Database Benchmark, see This benchmark and how they run it are described in a nice level of detail. However, it would be better if the source code and running instructions could be released in future. Unless it’s already been released, and I missed it.

For now, the DTU is a nice way to compare the tiers and a known benchmark is a nice thing but, to reiterate, there’s no way to ensure that you’re getting your bang-per-buck. And remember, at this point, any “smoke tests” you do on your own are being performed against a preview, not production. With Basic, sometimes it seems to take “a long while” (a nebulous term) to connect the first time to a new Basic tier database. After that, it’s faster (another nebulous term). Others have reported that Basic/Standard is slower than Web/Business on self-invented performance test. It would be nice, before the new tiers go GA, if they run the benchmark on a traditional Web/Business database (maybe a few times and take the average, but post all detail runs) just to assuage the fears of folks before they need to convert to the new tiers. MHO… After everyone’s converted, we can start talking about DTUs again, and they become more interesting and meaningful.

BTW, to get information about your databases (new or old tiers) in T-SQL, just use this query. It has all of the tier and objective information. There is some redundancy in the metadata, so start with SELECT * and choose the information you’d like to see:

select, o.description, st.*, do.*
from slo_objective_setting_selections sl
join slo_service_objectives o
on sl.objective_id = o.objective_id
join slo_dimension_settings st
on sl.setting_id = st.setting_id
join slo_database_objectives do
on o.objective_id = do.current_objective_id
order by;

Cheers, @bobbeauch

Last week in Azure SQL Database – Part 3 – HADR preview service for premium

This post is about a new SQL Azure Database feature called a “Business Continuity Feature”, called “Disaster recovery/geo-replication”. This feature was announced last week as a preview. For the Premium tiers, this is a lovely feature that include “Active geo-replication” (their term) and cmdlets (and portal) for controlling it. For Basic and Standard tiers, you can use “Database copy + Manual export” according to the chart here: By now, you know what “Database copy + Manual export” means (BACPACs, CREATE DATABASE AS COPY OF).

Terms concerning geo-replication are documented here: and there’s an entire docs section devoted to how to use it. A few points need to be called out:
1. Secondaries are readable.
2. You can create secondaries in the same region/different server or different region.
3. The target server must have an available Premium database quota to create the active secondary. You start with a quota of 2 and can “request” (from Azure support?) to have your quota increased.
4. The secondaries are always transactionally consistent but can run behind the primary.
5. Secondaries must be the same performance level (P1,P2,P3) as the primary. This has charge repercussions, as you could imply (nowhere is it directly stated but it seems obvious) that each secondary costs the same as the primary.
6. Each primary and secondary consists of a database that is itself replicated (passively and unseen by you) 3 times, because this is the design of ASD. And there is no info that this internal design has changed.

You start a continuous copy to the secondary using the portal or the (soon-to-be-released) PowerShell cmdlet Start-AzureSqlDatabaseCopy. This starts a seeding process, and eventually catches up to close to the primary. You can monitor this. Your can stop or cancel a continuous copy by using portal, the DROP DATABASE command on the copy, or Stop-AzureSqlDatabaseCopy. You need to use Stop-AzureSqlDatabaseCopy -ForcedTermination parameter to cancel the operation while it is still in progress or stop the replication immediately. See

There are two ways to failover, planned or forced. Planned is performed only on the primary. Forced can be performed on the primary or secondary. Their terms for the types of types of disaster recovery that can be designed are:
1. Active-passive compute with coupled failover
2. Active-active compute with decoupled failover
3. Active-passive compute with decoupled failover

I know some folks in the disaster recovery biz have a problem with the terms active-active and active-passive, so perhaps these terms will change in future. There are a few recovery scenarios documented, I’d like to try these with the PowerShell cmdlets, when they appear.

This is an excellent feature for Premium edition but, to reiterate, nothing new in this space is currently announced for Basic and Standard, past the original, built-in and internal, 3 database replicas as with the original ASD. A change to the internal single-database design was not announced.

In the next post (which will be the last post, if I can make it short enough), I’ll talk about performance levels in the tiers and comment on the current state of ASD functionality.

Cheers, @bobbeauch

Last week in Azure SQL Database – Part 2 – New preview services

Note: Well that was quick. I’ve updated this blog entry (same day) to reflect clarifications provided by a member of the Azure SQL Database team. Thanks for these excellent clarifications. For now (I may go back and change this later) changes from the original blog post are indicated with italics.

The last post in this series was about the new tiers/pricing in Azure SQL Database (ASD). This post will be more exiting, as it covers the new services that come with the new tiers. I’m talking about what the announcement (and docs) call “Business continuity features”. To summarize these features are Self-Service Restore and Disaster Recovery – Geo-Replication.

Although the docs and the chart on show these features as available on all new tiers, currently, these services only appear on premium. And the PowerShell cmdlets mentioned in the docs aren’t in Azure PowerShell 2.3. I was told the cmdlets “will be out this(Apr 28) week”. See the chart for how the new features are implemented on different tiers.

One final thing about using “CREATE DATABASE .. AS COPY OF” and the preview. Last year’s Premium preview created a copy that was in a “disabled premium” state. The new preview will create a copy at the same level, so, for example, “CREATE DATABASE .. AS COPY OF” with a P2 database will create a P2 database. This has charge repercussions.

First, Self-Service Restore. Microsoft keeps (and has always kept) database backups at their data center. I’m guessing these are “traditional” database backups (not BACPACs). BUT, you can’t use their backups, because Backup and Restore are not supported on ASD. Export and Import are supported. Self-service restore is a way you can have them use THEIR backups to restore an ASD database. There are two flavors of self-service restore:
1. Restore a copy of a currently existing database as of a point-in-time. Perhaps you deleted a table, for example, or some data with a miscoded SQL DELETE statement. It happens…
2. Restore a copy of a database you deleted by mistake. Or that you want back. The database doesn’t currently exist now.

I’ve heard both of these referred to as “oops recovery”. I’m thrilled with this service, even though you and I have never made a mistake, right? ;-)

Use the portal (see or the PowerShell cmdlets Start-AzureSqlDatabaseRestore (for a Standard or Premium Edition database) or Start-AzureSqlDatabaseRecovery (for a Basic database, because it doesn’t have point-in-time recovery), and a restore request will be submitted for you. To restore a deleted Standard or Premium database, just restore to a point-in-time before you deleted it. There is no SLA on *how long* the request will take to process. I couldn’t even get a ballpark figure, because it depends on the size of the database and the amount of recent activity. You can, however, get information about the status of the restore operation. You can even get this a T-SQL with the sys.dm_operation_status metadata table.

Unlike “CREATE DATABASE .. AS COPY OF”, self-service restoring a database produces a database of the same tier, but the lowest performance level in that tier. For example, restoring a P3 database creates a P1 database as a copy. This lessens the charge repercussions, but you do, of course, pay for at least one day of the copy.  If you’re manually just using it to recover a table, don’t forget to delete the restored copy when you are done. The database that’s created with a restore request can have the same name or a different name as the original and is always created on the same logical server, same data center. So to use a restored copy, you’ll need to change connection strings to point to the new database name or choose the same name when you when you submit the restore request. You may also want to increase the performance level, if you want to use the copy in place of the original afterwards.

If you’re the kind of person who wants their own backup (to Import the data on-premises, for example) you’ll still need to use Export/Import and BACPACs. The backup/restore capability is not available to you. BTW, if you used the Automated Export service (in preview itself) with Web/Business (it produces BACPACs to Azure Storage on a schedule), this is NOT available currently on the new tiers (at least that I could see, on the portal). No announcement when/if it will be.

To reiterate, the level of self-service restore (length of retained backups and point-in-time or not) is dependent on the service tier. See the chart referred to above. Also, here’s a clarification of what “Most recent daily DB backup in past 24 hours” means for Basic tier. For each database the service manages several types of backups: full backup created once a week, differential backup created once a day and transaction log backup created every 5 minutes. The first two are also copied to the Azure storage and that is what we refer to as “daily backups”. The actual time those backups are created differ therefore we can only guarantee that they will not be older than 24 hrs.  Consequently, if a database is recovered using Start-AzureSqlDatabaseRecovery the data loss (RPO) will be less than 24 hrs.

As this post is getting too long, I’ll save disaster recovery – geo-replication for another post.

Cheers. @bobbeauch