SQL Server Features Forgotten But Not Gone

Robert Sheldon looks at five cobweb-ravaged parts of SQL Server:

Policy-Based Management (PBM) has a PR problem, even worse that DQS and MDS. A lot of people don’t know what it is, or if they do, are not sure if it’s still supported. Microsoft doesn’t even mention the feature in its list of Editions and supported features of SQL Server 2016. The PR problem is so bad, in fact, that I’ve come across community forum postings asking whether PBM still exists.

Despite its silent role, PBM is still a supported feature and appears to be available to all SQL Server editions, although I suspect it caters primarily to organizations with a large number of similar SQL Server implementations.

Microsoft introduced PBM in SQL Server 2008 to provide a policy-based system for managing one or more SQL Server instances. You can, for example, use policies to grant permissions on user databases, disable lightweight pooling, or choose an authentication mode. You can also target policies at specific SQL Server instances, databases, tables, or indexes.

Quick hits:  Data Quality Services and Master Data Services have been lapped by the competition and aren’t really competitive products anymore.  Policy-Based Management is still useful, and I wish it’d get some internal love to extend roles & features.  Management Data Warehouse is a tire fire that you should avoid at all costs.  Service Broker is useful in the right circumstances, but it needs a few big changes to become a great broker.  Unfortunately, I don’t see big changes happening and so there are superior alternatives for data flow (like Apache Kafka).  But read Robert’s take on these features.

Missing MDS Temp Directory

Koen Verbeeck ran into an error when reinstalling Master Data Services on his Windows 10 box:

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:

Read on for the solution.

Handling MDS Configuration Errors

Koen Verbeeck looks at setting up MDS and conquers some configuration file permission issues:

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.

Read on for the solution.

MDS Installation Pre-Requisites

Cody Konior explains pre-requisites for installing Master Data Services for SQL Server 2016:

Microsoft’s list of MDS prerequisites doesn’t match their PowerShell installation script “sample”.

  • The sample installs the Application Server component which is not listed in the requirements.

  • The sample installs the Application Server NET 4.5 component even though it’s not in the requirements (it lists .NET Framework 4.5 Advanced Services, but a look through other versions of the OS would indicate that this is the plain .NET Framework 4.5 Features category; I suspect someone misread this as “Application Services”).

  • The sample installs additional “Application Development” components that are not listed in the requirements.

  • The sample doesn’t install the recommended Dynamic Content Compression component.

Basically, the sample needs updated and the documentation fixed.  Click through if you’re planning to use MDS.

MDM Is Hard

Knut Juergensen gives an overview of Master Data Management:

The sad reality in many companies is that there is no MDM, or that it exists but is implemented and managed poorly. Often, this is due to lack of managerial-level understanding of its real value and, subsequently, a lack of investment.

I’ll recount some of the problems that we encountered with our MDM system, at least partly due to this lack of understanding and investment from management. Although the example is specific to engineering manufacturing, I know that similar fundamental flaws affect other MDM systems in other environments.

The primary master data in this case comprises the parts and products used in our assembly lines, which are provided and created by our in-house and external design engineers. A core issue with our MDM system is the source of this master data.

Knut gives a good explanation of what MDM is, how it works, and then an example of how it doesn’t work.  Read the whole thing.

Installing Apache Falcon

Awanish at Edureka shows how to install Apache Falcon on your Hadoop cluster:

Apache Falcon is a framework for managing data life cycle in Hadoop clusters. It establishes relationship between various data and processing elements on a Hadoop environment, and also provides feed management services such as feed retention, replications across clusters, archival etc.

Let us first discuss how to setup Apache Falcon. Run the below given command to download git repository of Falcon:

Command: git clone https://git-wip-us.apache.org/repos/asf/falcon.git falcon

Falcon comes as part of the Hortonworks Data Platform; Cloudera has its own alternative.

Master Data Services Workflow

Reza Rad shows us how to introduce workflow concepts into MDS 2016:

This feature introduced in SQL Server 2016 CTP 3.0. You can save your pending changes in a set called Change Set. This Set then can be modified or deleted or applied. You can also add the functionality of Requirement for Approval for the change set, which then requires an entity administrator to approve or reject the change set.  This is a great feature for MDS and would help on the work flow process of making changes in master data. Let’s now have a closer look at how this feature works in action.

I don’t use Master Data Services, but I could see this being very useful at a large company with a lot of people modifying master data.

Master Data Services Bug

Kenneth Nielsen has found a bug in Master Data Services:

Something is not right here, there are some inconsistency in the way MDS behaves in regard to attribute management.

  • Changes to an attributes length is not supported in web interface
  • Changes to an attributes length is supported in Excel Add-In
  • Changes to an attributes length is limited to max 1000 in Excel Add-in
  • New attributes support a length of max 4000 in web interface
  • New attributes support a length of max 1000 in Excel Add-in
  • It is not at all possible to change an attributes length once it is set to 4000

With SQL Server 2016 CTP 3.1, Nielsen found a couple of these have been corrected, but not all of them.

Update, 2016-01-04:

Kenneth notes that the bug will be fixed in CTP 3.3.  Very nice.


September 2017
« Aug