SMP versus MPP architecture in the context of Azure SQL Data Warehouse

A Symmetric Multi-Processing (SMP) system is a computing architecture in which all the processors share the same operating system, memory, and disk storage and are connected via a system bus. 

As shown in the image above, SMP is a single-bus system where there is a common way to access resources (such as storage, for example) – this is one of the main architectures bottlenecks of an SMP system that events it from scaling. All the traditional SQL Server suite of products including SQL server 2005, 2008, 2012 etc. use SMP architecture. 

A Massively Parallel Processing (MPP) system, on the other hand, uses a "shared nothing" approach. In this architecture, each processor has its own set of resources (memory, disk storage, operating system) and each processor is fully independent and isolated from other processors. As you can guess, there is no single point of contention in this architecture and hence, it is able to scale massively. 

All the nodes in the system communicate with each other via a high-speed communication system. An MPP system can be thought of as a set of SMPs lined up and programmed to perform a single task in a parallel and coordinated fashion.
Examples of SQL Server products that are able to leverage MPP architecture: Microsoft Analytics Platform System (formerly SQL Server Parallel  Data Warehouse), Azure SQL Server Data Warehouse.

Now let’s try to put this concept in the context of Azure SQL Data Warehouse:

In an SMP architecture
- There is a single instance of SQL Server shared by all the resources (CPU, memory, disk storage)
- Though multiple CPUs work together to execute individual tasks concurrently (using application threading), the main bottleneck is that memory, disk storage etc. will be shared by all the CPUs.

In an MPP architecture (which Azure SQL Data Warehouse is built on)
- Each node runs its own instance of SQL Server and processes only the rows on its own disks - for example, in a 4-node MPP system, there will be 4 instances of SQL Server processing queries in parallel. If a table has 1 Million rows, considering the same 4-node example, each node will store 250,000 rows. This means, in theory, a query that takes 1 minute on an SMP system will take 15 seconds on an MPP system with 4 nodes
- When a query is executed (say a simple select statement), all the nodes in the system work in parallel to retrieve data from their respective disks and coordinate with each other to satisfy the request
In other words, the system distributes data and query processing across the nodes, thereby increasing parallelism and eliminating bottlenecks that are inherent in an SMP architecture.

Power BI Desktop vs. Power BI Service

There seems to be some confusion around the difference between the Power BI Desktop and Power BI Service (aka PowerBI.com) and when to use which. Please note that though Power BI Desktop and Power BI Service are different applications meant for different purposes and are referred to as "companions applications" by Microsoft, they do share some commonalities and hence the reason for confusion. Below are some of the key differences:

- Power BI Desktop is mostly about content authoring whereas Power BI Service is mostly about consumption, collaboration, and administration. IMO, this is the main difference between the two applications.

- Though both Power BI Desktop and Power BI Service provide a freeform, drag-and-drop canvas for creating stunning visualizations, there are a few things that are more “richer” in one than the other and/or only available in one and not in the other application. Below are a few examples:

• We can’t create data models (datasets) using the Power BI Service, we can only ‘consume’ the data models created using Power BI Desktop (or Excel add-ins for that matter).
• Content Packs can only be created and shared using the Power BI Service
• Ability to setup data-driven alerts and create annotations are only available on mobile devices and hence only possible in the Power BI Service
• Quick Insights (an interesting feature of Power BI ) is only available in Power BI Service
• Natural Langauge Query (also called Q&A) is only available in Power BI Service
• Calculated fields can only be created in Power BI Desktop

Hope this is helpful! 

Introduction To Power BI

At a very high level, Power BI is a stack of two "companion" applications - Power BI Desktop and Power BI Service.

Power BI Desktop (formerly Power BI Designer): A standalone self-service BI tool/application that is freely downloadable from the Power BI site (available in 32-bit & 64-bit mode). Power BI Desktop is a data modeling and content authoring tool that unifies the power of Power Query, Power Pivot, and Power View.

Below are some of the important tasks we can do using Power BI Desktop:

• Discover, connect, extract, transform data from public as well as corporate data sources (this was done using Power Query).
• Create data models (this was done using Power Pivot).
• Create interactive, drag-and-drop visualizations and explore/navigate geospatial data on a map in just a matter of a few clicks (this was done using Power View and Power Map).
• Publish/Deploy data models and visualizations to Power BI Service (more on this below) with a single click. Power BI Service is where the published content will be available for other users in the organization.

Power BI Service: Power BI Service (aka PowerBI.com) is a cloud-based Business Analytics Service (SaaS offering) that provides a simple, interactive, and user-friendly interface to create, view, share dashboards/visualizations, interact with your data and collaborate across your organization. Users interact with the service using a web browser or a mobile application.

                

Below are some of the important tasks we can perform with the Power BI Service:

• Connect to a variety of data sources (on premise or cloud).
• Build rich, interactive reports and dashboards. 
• Interact with data using Natural Language Query (also called as Q&A) – This feature allows users to ask questions in plain English and Power BI will respond to the question with an interactive chart or table. Power BI will interpret the semantics of the question and display data in the form of a visual. This visual can be tweaked to fit the need or can be added to an existing dashboard.
• Leverage Content Packs – For popular SaaS systems such as SalesForce, Zendesk, GitHub, Dynamics CRM etc., Microsoft Power BI team has built a set of data models, dashboards, and reports and published it to the Power BI Service so that users can consume the models/dashboards/reports. This pre-packaged collection of data models, dashboards, and reports is called Content Pack. Simply put, Content Packs provide out-of-the-box connectors to popular SaaS sources with pre-built data model, reports, and dashboards; these dashboards and reports can be customized (“personalized”) if required to fit the needs. 
• We can also create our own “Organizational Content Packs” and publish them to the Power BI Service. More on this here.
• Stay connected from anywhere – users don’t need to be on the corporate network, all they need is a browser (or Power BI app if using a mobile device) and an internet connection.
• Upload Excel files (either stored locally or on OneDrive), PBIX files (build using Power BI Desktop) and create visualizations based on the data in the files.
• Share content (dashboards etc.) with other users in the organization. Users just need a web browser to access and interact with the visualizations. Users with mobile devices can access the Power BI content using a browser on the mobile device or use Power BI app (native apps available for iOS, Windows, and Android devices).
• Setup data-driven alerts and create annotations on mobile devices and sher with other users.
• Schedule data refresh for online and on-premises data sources. By default, datasets from content packs are automatically updated once a day, but can also be refreshed manually or based on a schedule.

If you are still not clear on what Power BI Desktop is for and what Power BI Service is for, Power BI Desktop vs. Power BI Service might help.

Extensibility: Power BI is an extensible platform and provides the following ways to extend what is available out-of-the-box.

• REST API: Power BI’s REST API library makes it a fully extensible platform for its customer to customize pretty much every component of Power BI, including connecting to custom data sources, enabling real-time data streaming from data sources to Power BI, and integrating other line-of-business applications with Power BI. 
• Open Source Visualizations: Microsoft has released an open source visualization project on GitHub to enable Developers to start building custom visualizations. This is an excellent move towards extensibility as customers will be able to extend out-of-the-box visuals and are not limited by what’s available within Power BI.
• Custom Visuals Gallery: Microsoft lets Power BI community create and deploy custom visualizations to https://app.powerbi.com/visuals/, where other community members can download the visuals from and use it, free of charge.

Hope this is helpful.
Note: Images referenced in this blog post have been taken from Jen Underwood's Power BI Technical Reference Deck
Related links:

Power BI Basic Concepts 

Power BI Pricing 

Power BI Licensing Revisited 

BACKUP DATABASE is terminating abnormally - Process 4424 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process

One of my clients recently encountered this error (they are using SQL Server 2014). Their database backup job would fail with the following error:

Command: BACKUP DATABASE [Databas_Name] TO DISK = N'E:\Backup\Database_Name\FULL\DB_Name_FULL_20151013_210000.bak' WITH CHECKSUM, COMPRESSIONMsg 3013, Level 16, State 1, Server SERVER-NAME, Line 1  BACKUP DATABASE is terminating abnormally.

Also, the error log, referenced a dump file

Stack Dump being sent to E:\Databases\MSSQL10.MSSQLSERVER\MSSQL\LOG\SQLDump0169.txt     

I opened up the dump file and saw the below message. As you can guess, there is nothing useful on what the error message is about and how to resolve it.

This file is generated by Microsoft SQL Server  version 12.0.2000.8  upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request. 

SqlDumpExceptionHandler: Process 4424 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Exception Address = 000007FEF0A68D40 Module(sqlmin+0000000002C88D40)                                        

Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION                                                      

Access Violation occurred reading address FFFFFFFFFFFFFFFF                                                      

As you can guess, there is nothing useful on what the error message is about and how to resolve it. I found a KB article that talks about this error and it suggested installing a Cumulative Update.  

Resolution: Since SQL Server 2014 SP1 was available, I was more comfortable installing SP1 instead of a Cumulative Update. We installed SP1 and the issue got resolved. 

Hope this helps. If you encountered this error and used to a different method to fix the issue, please let me know (via comments)

Excel Timeline Error: We can’t create a Timeline for this report because it doesn't have a field formatted as Date.

You might get the error below when trying to insert a Timeline filter in an Excel Pivot table that is built off of an SSAS (SQL Server Analysis Services) Cube. Timeline filter is a new feature of Excel 2013 that can be used as a ‘visual filter’ to filter data in pivot tables.

We can’t create a Timeline for this report because it doesn't have a field formatted as Date.

It appears that the Timeline filter requires the MemberValue property of the key column in the date dimension to be a date field (date or datetime both works). So to resolve this issue, just point the ValueColumn property of the key column of your date dimension to a date field as shown in the screen shot below.

Also, please note that Timeline filter also requires the Type property of the date dimension to be set to Time. Hope this is helpful.

Could not find a log backup file that could be applied to secondary database

Error: Could not find a log backup file that could be applied to secondary database 'Database Name'.(Microsoft.SqlServer.Management.LogShipping) 

Error: The file 'F:\Database_Transaction_Log\Database_20130405231501.trn' is too recent to apply to the secondary database ‘Database Name'.(Microsoft.SqlServer.Management.LogShipping)

Error: The log in this backup set begins at LSN 2045263000000026000001, which is too recent to apply to the database. An earlier log backup that includes LSN 2045263000000023900001 can be restored. Searching for an older log backup file. Secondary Database: 'Database Name'

A few of my clients encountered this issue recently and needed help. Basically, they've setup Log Shipping on one of their production databases to copy transactions to a secondary server (for reporting purposes). The setup was working fine except for the fact that Log Sipping will break sporadically with the above error messages, and they had to ‘reset’ Log shipping to resolve the issue. Just by looking at the error message, I made a guess that this must be due to transaction log backups that are being performed “outside” of Log Shipping – in other words, I thought there must be a process separate from Log Shipping that is performing transaction log backups. The reason this is going to be an issue is because when applying/restoring transaction log backups on to the secondary server, Log Shipping is not going to be able find the log backup that was taken “outside” of Log Shipping – this means that there will be a ‘break’ in the chain/sequence of transaction log backups and hence a successful restore won't be possible. Below are three different scenarios I ran into with different clients.

Client/Scenario #1: This scenario was simple and it was easy to identify and resolve the issue: this client had a maintenance Job (outside of Log Shipping) that was backing up the transaction log on a regular basis. We simply disabled the maintenance job and the issue got resolved.

Client/Scenario #2: In this scenario, there was nothing within SQL Server and outside of Log Shipping that was backing up the transaction log. After a couple of discussions, I found out that they are using Avamar for backups and recovery purposes so I obviously wanted to know if they are using Avamar for transactions log backups. The answer was “No, We just do full backups”. But I wanted to confirm so we took a closer look at some of the settings in Avamar and found one that says “Force incremental backup after full backup” under the “Options” tab in the “Edit Dataset” window (shown in screenshot below). It sounded like this option does transactions log backups but to be 100% sure, we reached out to Avamar technical support and they confirmed that the setting below DOES transactions log backups. The support also said that if you include a database in Avamar’s backup plan, by default, it will perform transaction log backups as well (if the database is in FULL recovery model). 

Long story short, we deselected the option "Force incremental backup after full backup" so Avamar stopped performing  transaction log backups and Log Shipping continued to work without any issues.

Client/Scenario #3: This scenario is pretty much the same as that of Scenario #2, except that this client uses Veeam for backup and recovery. As in scenario #2, we explored some of the options in Veeam and found a similar setting (though not as obvious) called “Enable application-aware image processing” as shown in the screen shot below (the one on the left). Veeam technical support did confirm that this setting performs full as well as transaction log backups but if we just need to do full backups (without transactions log backups), we need to deselect the option “Backup logs periodically” under the advanced settings shown in the screen shot below on the right.

So we left the option “Enable application-aware image processing” selected but deselected “Backup logs periodically” and that took care of the issue with Log Shipping.

Even if you are not using Log Shipping, it is not a good idea to have two different process backup the transaction log of a given database as it can cause issues and confusion at the time restoring those backups. I hope this is helpful. If you've experienced this error and the reason/resolution was different than what I posted above, please leave a comment with your solution. Thanks.

Error when opening SQL Server Maintenance Plan: Value cannot be null

One of our clients just called me with an issue they were having when opening a Maintenance Plan in SQL Server Management Studio. Below is a screen shot of the error message(and the error text)

Value cannot be null. Parameter name: component (System.Design). Microsoft SQL Server Management Studio is unable to load this document: Error loading XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.

As soon I saw the error(and the server version), I realized that this is a SQL Server 2012 instance and  the client (a Network Admin) was trying to edit the maintenance plan using SQL Server 2008 R2 (Management Studio). I had him try using SQL Server 2012 Management Studio and it worked without any issue. SQL Server Tools in general, are not forward compatible and even if they let us connect to a later version of the software, it is not a good idea to make any changes (I wouldn't even recommend opening anything). Hope this helps.

On a side note, if you try doing something similar with Analysis Services(open SSAS 20012 database in on-line mode using BIDS 2008 R2 etc), you’ll get the error message below. 

Deserialization failed: The 'AllowedRowsExpression' element in the 'http://schemas.microsoft.com/analysisservices/2011/engine/300/300' namespace is unexpected.

So the key takeaway of this post is to always try using the right version of the tool when connecting to SQL Server, SSAS, or SSRS for that matter. Hope this helps.