Microsoft Dynamics CRM – Filtered Views and SQL Server 2014

Microsoft Dynamics CRM makes extensive use of filtered views – these are standard views in the database, but they prevent unauthorised users (including DBAs) from seeing sensitive data by the way they are coded. During several migrations of Microsoft Dynamics CRM from both the 2011 and 2013 versions to 2015, my project teams have hit performance issues which center on filtered views and the new Cardinality Estimator in SQL Server 2014. The Cardinality Estimator is the part of the database engine that works out how to actually do the work for a particular query, and it was largely based on a code base written for SQL Server 7.0.

SQL Server Management Studio Standard Reports

This week I found, by chance, that on a couple of servers when I tried to run a Standard Report such as Disk Usage in SQL Server Management Studio (SSMS), it would crash. Both SQL Servers were SQL Server 2014, running on top of Windows Server 2012 R2. The actual application crash was that the Visual Basic Command Line Compiler had stopped working.

I had to pause for thought – whilst I’m an experienced database specialist, I don’t normally have to troubleshoot application crashes.

I initially suspected it might be a permissions issue, but it turned out to be a bug with Windows Server 2012 R2 (together with Windows 8)

Kathi Kellenberger’s Pluralsight course : Configuring Kerberos For SSRS

It’s a relatively new course from February 2016, and Kathi explains – and demonstrates – the double-hop problem on SQL Server 2014 in a Azure lab. It’s certainly true that SSRS is the most common occurence where database specialists hit the double-hop problem, but it’s not exclusively a SSRS issue. Kathi also outlines some possible security workarounds (and makes the caveats clear), and then Kathi runs through how NTLM and Kerberos work. My overall conclusion is that this course is a good, yet relatively brief, tutorial about Kerberos for SQL Server professionals

Another reason to sign-up for Visual Studio Dev Essentials

The March update for Visual Studio Dev Essentials has announced that SQL Server 2014 Developer Edition is now free for Dev Essentials members! It was always great value for non-production development and testing, but now – it really shows how Microsoft SQL Server is really gunning for Oracle’s database customers.

Exciting news! Starting today, SQL Server 2014 Developer Edition is available as a free download for Dev Essentials members. Build almost any kind of database application and unlock insights faster with a complete business intelligence solution. SQL Server 2014 was designed to work in hybrid environments, making it easier for you to build back-up and disaster recovery solutions with Azure, as well as delivering a consistent experience across on-premises and cloud.


I’ve had a process for several years that imports approximately 20 million rows into a staging table from a 3GB CSV flat file. As these things do, it has evolved over time. It started as a bulk import statement, but – as errors started to regularly kill the process – it evolvedinto a SSIS package. Without too much difficulty, I was able to set up a data flow task so that rows that generate errors would be diverted. Unfortunately, the package suddenly started failing with an DTS_W_MAXIMUMERRORCOUNTREACHED error.

The column delimiter for column “Column 15” was not found.

Error: 0xC0202055 at Import, Flat File Source [13]: The column delimiter for column “Column 15” was not found.

Error: 0xC0202092 at Import, Flat File Source [13]: An error occurred while processing file “E:\Data\csv-import.csv” on data row 1.
Error: 0xC0047038 at Import, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Microsoft announces SQL Server to run on Linux

In the run up to the Data Driven event, Microsoft stunned the SQL Server community by announcing that they’d be offering SQL Server on a Linux platform by 2017. There was a solid demonstration during the event, but in the following days it became clear that it would initially be just the core database engine, and none of that fancy Business Intelligence (BI) stuff.

Microsoft also announced their “Break free from Oracle” licensing offer – basically, if you sign up before June, you’ll potentially be able to get Microsoft SQL Server 2016 for free as a swap-out of your Oracle licences. Of course, the devil is in the detail – for instance, buying Software Assurance is a part of the offer.

Getting Started With Cloud Databases

Data technologies have never been hotter whether it’s traditional relational databases, NoSQL databases, or Big Data technologies. Cloud has never been hotter either, and it’s the perfect environment to get familiar with old and new technologies – in this guide I look at the major cloud players, and look at what options are available to get started with cloud databases.

Microsoft Azure

There are several ways to get started with Microsoft Azure’s cloud database offerings – try the one month trial from Azure directly; get Azure benefits with Visual Studio Professional with MSDN; get twelve months of Azure benefits with the free Visual Studio Dev Essentials either as software or a cloud version; or pay up!

One thing to watch for, from my experience using previous benefits, is to be careful when using non-Microsoft products which are not open source. My subscription was suspended during one billing cycle because I’d built an Oracle VM, and my MSDN credits could not be used for that product. I had to raise a case with Microsoft to get the issue resolved.

Amazon Web Services (AWS)

Amazon offer a free product offering called AWS Free Tier which allows you to do anything in the cloud for free for 12 months, though usage limits obviously apply!

Google Cloud Platform

Google offer a USD 300 60 day trial, though – unlike Azure or AWS – you won’t be able to do anything with Microsoft SQL Server or Oracle database here. Google Cloud SQL isMySQL versions 5.5 and 5.6 behind the scenes. The product offering is up to 104GB of RAM and 10TB of data storage – so fairly significant!

Oracle Cloud

Oracle do offer their databases in the cloud, or as Database As A Service (DBAAS), but I wasn’t able to find any trials or benefits. And when I stumbled across the monthly cost of Exadata in the cloud, I got more than a little scared!

Kalen Delaney is coming back to SQLBits

Kalen Delaney is coming back to SQLBits XV in May in Liverpool – Kalen’s session will be on the thursday, and is called SQL Server Query Plans – Reuse or Recompile. In Kalen’s words, it will cover the plan cache, how and when SQL Server decides whether to reuse or recompile a plan, and how to influence SQL Server’s choice.

I saw Kalen give a session in 2015 in London at the Excel conference centre – she’s a great speaker, obviously knows her stuff, and is well worth going to see.

SQL Server Licensing

SQL Server licensing isn’t straightforward, and it certainly isn’t as easy as it used to be.  In days gone by, we’d buy a licence for each physical socket no matter how many cores there were, and we’d get High Availability (HA) and Disaster Recovery (DR) generally for free. The release of SQL Server 2012 heralded the introduction of core-based licensing, and tweaks have continued to be made since then – almost always to Microsoft’s advantage.

In the run up to the release of SQL Server 2012, I spent a morning on a SQL Server licensing workshop which was arranged by Microsoft, but was presented by a third party. It was excellent, so I was delighted when a mail came from Microsoft Events offering a webinar on SQL Server licensing in February and March.

I attended the February offering, which was presented by Vicky Lea. Vicky ran through how licensing had changed from SQL Server 2008 R2 to SQL Server 2012, and the methodology for working out your licensing requirements.  Vicky covered the special cases of Virtualisation, Licence Mobility, Software Assurance, the changes to High Availability licensing when SQL Server 2014 was released,  the downgrade and down-edition rights that come with the product licence, and Step Ups which is one of the Software Assurance benefits.

What Port Is My SQL Server Listening On?

Like most application software, SQL Server listens on a specific port for client connections.

What is the default port for SQL Server?

The default port is 1433. It is strongly recommended not to run SQL Server on the default port, as it is well-known. The general guidance is to choose a port number that is much higher, just make sure that it is unused!

How do I find which port my SQL Server is listening on?

There are several methods for determining which port SQL Server is listening on, though ideally this should have been documented at install time. Additionally, if the SQL Server Browser service is running – then you don’t generally need to know, unless you need to open up firewalls to allow database access.

SQL Server Error Log

When SQL Server starts up, it will make an entry in the error log about which ports are being listened to:

Server is listening on [ ‘any’ <ipv4> 50000].

SQL Server Configuration Manager

SQL Server Configuration Manager is the tool for – yes, you’ve guessed it  – configuring SQL Server: services including service account passwords, protocols, and especially the port number(s) for TCP.

Can a SQL Server listen to more than just one port?

Yes, in SQL Server Configuration Manager you can enter the ports that you want SQL Server to listen on separated by commas.

Why might you want to do this? You might want to segregate traffic perhaps.