I recently had to install SQL Server 2016 again on my Windows 10 machine. This also meant that I had to install and configure MDS as well. Last time it didn’t went so smoothly: Master Data Services error – Cannot read configuration file due to insufficient permissions. However, this time I had already applied Service Pack 1, so I had another error when I tried to launch the MDS website.

The error this time: “The ‘tempDirectory’ attribute must be set to a valid absolute path”. If you can’t see the error, it’s possible you have to enable them in the web.config file of MDS. Typically you can find this configuration file in the folder “C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication”. The customErrors attribute should be changed to the following:

<customErrors mode="RemoteOnly" />

Anyway, apparently a temp folder is missing. Cody Konior already warned for this bug. The error message at the website is kind enough to provide you with the exact location of the error in the web.config file. Funny that the comment above explains that the Configuration Manager is responsible for this temp folder. Someone has not done its job properly.

The resolution is simple: create a temp folder with the name specified in the web.config file at the specified location. Now you are greeted with the following error:

Of course the MDS Application Pool account doesn’t have write access to the newly created temp folder. This is also easy to fix:

Note that I assigned permissions to the MDS_ServiceAccounts group. I manually added the MDS Application Pool account to this group, because MDS also forgets to do this. Now you can finally browse to the MDS website!

SQL Server 2016 service pack 1 has been released and it is a mayor one! You can download it here.
Why, you ask, is it so important? Because a lot of Enterprise Edition database engine features are now available in the other editions as well. In other words, you can now enable compression and use columnstore indexes in Standard Edition. This is great news for existing customers who can now implement faster databases for no extra cost.

This is fantastic news, of course. A bit less fantastic news was that Power BI Desktop on premises probably won’t be for SQL Server 2016, but rather for SQL Server vNext. I can’t deny that I’m quite disappointed. More information can be found here. But enough about the bad news. SQL Server 2016 SP1 is not only great for customers that don’t have Enterprise Edition, but there are also some enhancements and bug fixes:

• the list with release information
• there are some performance improvements for SSAS
• SSRS has some nice enhancements as well. They also have a blog post about the future of SharePoint integration.

The official announcements:

• SQL Server 2016 Service Pack 1 (SP1) released !!!
• SQL Server 2016 Service Pack 1 generally available

In short, SQL Server 2016 SP1 is a great opportunity for people who have already SQL Server 2016 but don’t have Enterprise Edition installed. Furthermore, it’s a good reason to upgrade to SQL Server 2016 if you haven’t already.

I was preparing a demo for a session about the new features of Master Data Services in SQL Server 2016. In that demo, I created a business rule to test out the extension with custom scripts. When the business rule was finished, I tried to publish it. But, I was greeted with the following error: “300 – A database error has occurred. Contact your system administrator”.

Not the most helpful error message, especially when you are the admin yourself. The MDS team promised that in SQL Server 2016, troubleshooting would be easier. So I went and checked out their blog post What’s New in Master Data Services – SQL2016 CTP2 (May) Release, where they have a section Improved Troubleshooting and Logging.  There it is mentioned the web application logs to the following folder: C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication. In the log file over there, I could find the following – actual – error message:

VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’

Apparently during the publishing of a business rule, MDS wants to run a stored procedure called udpValidationIsRunning. This procedure wants to check the system view sys.dm_broker_activated_tasks and apparently the necessary permissions are lacking.

The fix is luckily quite easy: just run the following statement to grant the permission to the MDSAppPool security group:

USE MASTER;
GRANT VIEW SERVER STATE TO [yourmachine\MDSAppPool];

Now the business rule can be published without an issue.

UPDATE:

it’s possible to logs are located at the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services\WebApplication\MDSTempDir. This might have been changed in a later CU.

In preparation of my upcoming webinar on the new features of Master Data Services 2016, I installed MDS on my system. Otherwise it would be quite hard to give demos, of course. The installation itself went quite smooth, no remarks there. I also enabled Internet Information Services (IIS) on my system, as well as a number of required prerequisites. Of course I forgot a few, but to be honest, the MDS Configuration Manager has become quite good at telling you which ones are missing.

Side note: Matt Smith has a PowerShell script in Github that will install all of the prerequisites for you!

@Ko_Ver https://t.co/tCrXeZlgAN

— Matt Smith (@matt40k) October 23, 2016

Next I had to install and configure the MDS database, which was quite painless, as usual. But then comes the tricky part: adding the MDS website. First I created a new website using the MDS Configuration Manager, but that did not work. At all. For some reason, IIS searched for the MDS website at …\inetpub\wwwroot\MDS, instead of at the MDS installation folder in Program Files. So I deleted the website and assigned MDS to the default website. That also didn’t work, but this time I got a different error:

The error seemed quite clear: Cannot read configuration file due to insufficient permissions. Just to be sure, I added the user MDSAppPool – created in the MDS Configuration Manager for the MDS Application Pool – to the Administrators group on the machine. A brute-force solution, but since it’s on my own machine for demo purposes, I didn’t really care. Of course it didn’t work. Then I assigned full control permissions for the MDSAppPool user on the folder C:\Program Files\Microsoft SQL Server\130\Master Data Services. Didn’t work. Used the browser in Administrator modus. Also didn’t work. Checked IIS settings and discovered that Windows Authentication was not enabled. So I enable it, but the error persists. This is the point where it all starts to get frustrating. Adding MDSAppPool to the IIS_IUSRS group doesn’t work. Giving that group full control on the MDS directory either.

As a last attempt, I went directly to the web.config file and assigned MDSAppPool full control directly on that file, for which I had to pass numerous UAC verifications. But lo and behold, the MDS website worked!

Conclusion: if you encounter a permission issue on the web.config file, directly assign permissions to it for the MDS application pool user. Adding the user to the admin group does not work, for whatever reason.

The 2016 edition of the Belgian SQL Server Days is over. It was a blast – as usual – and it was very nice meeting up with the #sqlfamily again. I had a session about the new features in SSRS 2016. I was so excited that I could do a full hour session on Reporting Services’ new features. I had to wait only like 7 years to be able to do so. Anyway, the session went okay and there weren’t that many questions. I always assume that’s because I did a kickass job at explaining stuff, if there are no questions

You can find the slides from my presentation on Onedrive:

All of the demos are turned into MSSQLTips.com articles, so you can find more information about them over there.

I also attended some other sessions:

• The SSIS design patterns precon by Matt Masson. It was really great meeting up with Matt. He has been in a lot of Microsoft teams: SSIS, MDS, DQS and Power Query. He did a solid precon, but at the end I kind of lost my interest (I already read the book by Matt and Andy Leonard about SSIS Design Patterns, so I already knew most of the stuff). Once Matt mentioned XML patterns, it was time to pack it up I did learn some interesting nuggets, like for example how the SSIS data flow treats LOB columns (badly).
• The Advanced MDX Tips and Tricks session by Chris Webb. Chris is an utmost authority on MDX, so I couldn’t miss this one. He also stated that this would be his very last MDX session ever (session, not training!). Unless he comes back like an aged rockstar, that is. It was a very interesting session and I learned a lot. Chris did a great piece on subselects (damn you SSRS and Excel), partitioning and the unknown UNORDER() function. Too bad there wasn’t more time.
• Deep Dive into Data Modeling using Power BI Desktop and SQL Server Analysis by Kasper De Jonge. Kasper has worked both on the SSAS team and on the Power BI team, so I was very excited to attend his session. He did a very thorough explanation of row level security (RLS) and bi-directional cross filtering (BI-DI). He had some warnings about over-filtering your model using BI-DI, so you shouldn’t blindly enable it on every relationship, especially in more complex models. He finished off with a part on DAX variables, which are a great addition to the language.
• The Power BI happy hour. Chris did a great demo about using Power BI and your brainwaves to do predictions and you could tell he really put some work in it. Joey D’Antoni talked about beer and cycling, Nico Jacobs about Power BI running for president and Wesley Backelant ended the day with a nice Power BI dashboard about his music influences. Great stuff.

I had a great time and can’t wait till next years’ edition.

Here is an overview of the articles I published in the third quarter of 2016.

• What’s New in SQL Server Integration Services 2016 – Part 1
• What’s New in Integration Services 2016 – Part 2
• Installing new SQL Server sample databases: WideWorldImporters
• Install SQL Server 2016 Sample Database: Wide World Importers Data Warehouse
• Generate more data for the Wide World Importers sample SQL Server databases
• Create a basic KPI in SQL Server Reporting Services 2016
• Advanced KPI Configurations in SQL Server Reporting Services 2016

Up next are articles about the great new features in Reporting Services 2016, combined with the webinars of course.

I’ll be delivering two webinars for MSSQLTips.com in the coming months. The first one is about the – amazing – new features in Reporting Services 2016.

SQL Server Reporting Services is Microsoft’s corporate reporting tool. The SQL Server 2016 release marks one of the biggest evolutions of the product since it’s initial release. In this webcast we’ll give an overview of all those new changes. A couple of highlights:

• New chart types
• The parameter grid
• The brand new report portal
• The integration of Datazen, which means SSRS now supports mobile reports
• and more…

Join us in this session to discover why SSRS 2016 will blow your mind away!

This webinar will be hosted on Wednesday, October 26, 2016 at 6PM UTC (be careful with daylight savings time during that period
You can register here. If you can’t catch my session at the SQL Server Days next week, this webcast is the perfect opportunity to still learn about this subject.

The other webinar is about another tool of the SQL Server stack which finally has gotten some love: Master Data Services.

SQL Server Master Data Services is Microsoft’s master data management solution. The SQL Server 2016 release brings MDS to a higher level: usability and performance have been improved, security is more flexible and there are tons of new features. Some highlights of this webcast:

• How to sync entities
• Slowly Changing Type 2 support
• The new security and performance features
• How to use change sets
• and much more…

Join us in this demo-packed session to find out how MDS 2016 will improve your master data

The webcast will be held at Wednesday, November 09, 2016 at 8PM UTC. You can register over here.

Just a quick blog post about an error I recently encountered when trying to create a mobile report for SSRS 2016, using the Mobile Report Publisher. I created the following query on top of the Wide World Importers data warehouse:

SELECT
	 c.[City]
	,c.[State Province]
	,c.[Country]
	,c.[Sales Territory]
	,c.[Region]
	,c.[Subregion]
	,c.[Location]
	,[Latitude]		= c.[Location].Lat
	,[Longitude]	= c.[Location].Long
	,u.[Customer]
	,u.[Category]
	,u.[Buying Group]
	,[Order Date]	= [Order Date Key]
	,[Picked Date]	= [Picked Date Key]
	,e.[Employee]
	,[WWI Order ID]
	,[Package]
	,[Quantity]
	,[Unit Price]
	,[Tax Rate]
	,[Total Excluding Tax]
	,[Tax Amount]
	,[Total Including Tax]
 FROM [Fact].[Order]			f
 JOIN [Dimension].[City]		c ON f.[City Key]			= c.[City Key]
 JOIN [Dimension].[Customer]	u ON f.[Customer Key]		= u.[Customer Key]
 JOIN [Dimension].[Employee]	e ON f.[Salesperson Key]	= e.[Employee Key]
 WHERE	[Order Date Key] >= '2016-01-01'
	AND	[Order Date Key] <= '2016-12-31';

I created a shared dataset and stored it on the report server. Then I tried to create a new mobile report using this dataset. I was quickly greeted with the following error:

The error message “The JSON SharedDataSet Table renderer cannot parse the supplied report” doesn’t exactly tell you what’s going on. Apparently it is having issues with the Location column, which is of the geography data type. If you remove it, the dataset will be imported in the mobile report editor. There’s no documentation of which data types are supported or not in the mobile reports. I included the column in the first place to find out if the Mobile Report Publisher could handle it and plot the data on a map. It seems not.

This week SQL Server 2016 Cumulative Update 1 was released and it contains a lot of updates/fixes for SSIS, SSAS, SSRS and MDS. If you are already working on SQL Server 2016, I’d urge to install this update as soon as possible.

Anyway, one of the particular updates that caught my attention was Adds the adjusted buffer size to the BufferSizeTuning event when AutoAdjustBufferSize is enabled in SSIS 2016. In simple terms, it allows you to log  the size of the data flow buffer set automatically by the AutoAdjustBufferSize property. This property basically automatically calculates the buffer size needed to reach the amount of rows in the buffer specified by DefaultBufferMaxRows. Therefore, it ignores  the DefaultBufferSize property. Unfortunately, this new property is set to false by default.

Back on topic: since the size of the buffer is calculated automatically for you, it might be interesting to know its final size, especially when doing some performance tuning. There are two options to get the log information:

• Run the package using the Verbose logging level.
• Add a custom log provider to the package and log the new custom BufferSizeTuning event of the data flow.

Let’s take a look at the first one. When you execute a package in the SSIS Catalog, certain information is logged automatically to the Catalog. Which information is decided by the logging level. In order to view the size of the data flow buffer, you need to use the Verbose logging level. Keep in mind that this logging level should be used for debugging only, since it generates quite some logging overhead! I ran a very simple package and it already resulted in over 6400 rows of logging. When you take a look at the All Messages drillthrough of the All Executions report in the catalog, you can find the necessary information under the message type NonDiagnostic.

In the screenshot above, you can see that the buffer was adjusted to 50MB.

The other method is to enable logging inside the SSIS package itself. The data flow has a new custom event, called BufferSizeTuning:

When we run the package, the following message is logged:

The buffer size of buffer type 0 is automatically adjusted to 50160000 bytes.

Now, when we change the DefaultBufferMaxRows property from its default value of 10,000 to 20,0000, we can see the following message being logged:

The buffer size of buffer type 0 is automatically adjusted to 100320000 bytes.

In other words, the buffer sized doubled to 100MB to incorporate the increase in the number of rows per buffer. By the way, the package ran only a few seconds faster although more memory was used.

Conclusion: a very nice trick to the tool belt for some SSIS performance tuning. Don’t use the Verbose logging level too much though.

Here is an overview of the articles I published in the second quarter of 2016, which were all focused on the new changes for Master Data Services in SQL Server 2016:

• Approval Workflow in SQL Server Master Data Services 2016 using Change Sets – Part 1
• Approval Workflow in Master Data Services 2016 using Change Sets – Part 2
• How to Merge a Conflict in SQL Server 2016 Master Data Services
• Member Revision History in Master Data Services 2016 – Part 1
• Member Revision History in Master Data Services 2016 – Part 2
• Using Data Compression in Master Data Services 2016
• Add a Custom Index in Master Data Services 2016

Very happy that MDS has gotten quite some love in the 2016 release.

In the pipeline: What’s new in SSIS 2016 and some articles about the new sample database Wide World Importers.