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.

The execute permission was denied on the object xp_sqlagent_enum_jobs

On a SQL Server 2005 pre-production box, I was giving a developer SQL Server Agent permissions to allow them to execute jobs – the new roles in 2005 such as SQLAgentOperatorRole, SQLAgentUserRole, and SQLAgentReaderRole. I used to have to deploy a custom role in SQL Server 2000, but these new roles work just fine. Except the roles didn’t work today on this server.

I reproduced the issue by creating a new SQL login, and giving it the appropriate permissions…. there was nothing in the SQL log. But I did come across a Microsoft KB which talked about permission problems after moving MSDB. Had someone previously moved MSDB, or renamed the server?

I’d seen this before when an old dinosaur SQL Server was cloned for P2V, and then moved to a new domain. The Windows server was renamed, but no one told SQL Server that it’s name had changed. It caused all sorts of problems with job scheduling.

SQL Server 32bit Memory Settings

Today I had to re-familiarise myself with 32 bit memory settings on a legacy server that just hasn’t been migrated to 64 bit as yet – realistically, I haven’t had to think about 32 bit limitations for nearly five years! So, I dug out my old battered copy of Wrox’s SQL Server 2005 Performance Tuning. I read technical books that I don’t own on Safari Online, but my preference is physical!

So, the 32bit architecture limits the set of addresses the processor can access to 4GB. Each process has a total of 4GB of virtual address space (VAS), and this VAS is split evenly in the middle so the kernel has 2GB and user-mode has 2GB. This split can be moved to give the user mode 3GB by using switches like /3GB or /USERVA on Windows 2003. So, on a SQL Server system with 4GB and no switches, SQL Server will typically use 1.7GB for Total/Target Server, and a few hundred MB for other memory operations like linked servers, extended stored procedures etc.

To get past the 4GB limit, Intel&nbsp;physically extended the address bus to&nbsp;36bit, which was called Physical Address Extension (PAE). This allows a 32bit operating system to address up to 64GB of RAM, but Windows Server 2003 must be Enterprise Edition, and it must be Service Pack 1 or later. It’s not all plain sailing – SQL Server can only use this extended memory for the buffer cache, so each SQL Server still needs some memory in that first 2 or 3GB for those linked servers etc.

You can use /3GB and PAE together, and Microsoft support this to 16GB… but it’s more complicated. I like simplicity if possible! And using them both together can throttle the system twice as /3GB reduces the non-paged pool whilst PAE doubles the size of each allocation.

For SQL Server, some configuration may be required. To allow SQL Server to access more than the 2GB memory limit (or 3GB if /3GB or /USERVA is configured), a setting called Address Windowing Extensions (AWE) must be enabled. Hand in hand with AWE is a OS privilege called Lock Pages In Memory – this enables SQL Server to hold on to memory if Windows essentially wants memory back. The potential downside is that other applications on a shared box may start paging instead.
Simple guidelines:

– migrate to a 64 bit architecture as soon as possible!
– on 32bit, if you have 4GB of RAM use /3GB if memory is the bottleneck for SQL Server
– on 32bit, if you have more than 4GB of RAM, use /PAE together with AWE.

Port Query – is SQL Server listening?

From time to time, DBAs will be called in to investigate connection issues – “my application couldn’t connect to the database at 13:30…. was the database ok?”

Management Studio is great for validating connectivity, but it’s not easily portable. Port Query UI (portqry) is a convenient graphical tool that allows ports to be tested to see if SQL Server is listening – and can be connected to from a test workstation or server. It comes as a ZIP download, it doesn’t install anything, and you just need to run the EXE. The added bonus is that it’s not part of the SQL Server suite, so application teams can often be persuaded that it’s legitimate proof that SQL Server really is available and listening.

Once it’s running, you just need to enter the server-name or IP address, and then either provide the TCP/UDP port – or select the SQL Server drop down, which will try the default port of 1433 together with the SQL Server Browser port of 1434. Then just click on the Query button, and the results will be displayed at the bottom.

sqlsrvr.exe isn’t always Microsoft SQL Server

Whilst at a client recently, I was called over as the Windows support team have received an alert that a SQL Server was running at 100% CPU. Strangely, it was on a server-name that wasn’t supposed to host SQL Server, according to the naming standards.

Whilst trying to drive from the back seat over someone’s shoulder, we couldn’t find the SQL Server binaries in the Program Files directory, or much other evidence of SQL Server – no data files or transaction log files; no directory structures; no client or configuration tools. The only “evidence” was TaskManager showing to all and sundry that it was a “SQL Server problem”. Again!

I looked at some tools that remotely list what programs and applications are installed on a server – nope, no SQL Server there. I pointed Microsoft’s MAP tool against the server as well – no joy! Truly frustrating! It turns out is was a version of Sybase, yet again showing the shared ancestry between SQL Server and Sybase!