SQL Server Express and Reporting Services
I’m a fan of SQL Server Express edition. It’s a fabulously functional free database that lets you work with up to 10GB of data per database. One aspect that’s commonly misunderstood though, is that it also supports a limited version of SQL Server Reporting Services.
There are many scenarios where Express edition is the perfect fit. The Microsoft sales and marketing teams have had an ongoing strange relationship with Express edition because it’s free. Much of the documentation tried to suggest that it was for hobbyists, and para-professionals. But Express edition has a great role to play in professional systems too. For example, if you needed a small database to run on a series of point of sale devices, it could well be the right answer, particularly if it’s then connected to one of the higher editions as a central SQL Server system.
I used to use it many years ago as an easy way to provide a system test system for clients using one of our applications. That allowed them to get established quickly without SQL Server licensing. When they had been using it for a while, and their databases grew in size, that was the easiest upgrade you’d ever convince customers to do.
Reporting
What wasn’t understood well early on, is that reporting was also needed. I remember arguing passionately about why Express edition needed to include some form of Reporting Services. The marketing teams didn’t at first understand that if a software house needed a reporting solution, they weren’t going to use something different on Express to what they used on other editions. They’d simply use a different reporting tool where they only had to write the code once.
Express edition regularly helped us to sell paid SQL Server licenses.
Fortunately, the teams eventually saw the light on this one, and a version of Express with Advanced Services was released. It included a limited form of Reporting Services. It was perfectly functional, but didn’t allow you to report on any data, except from the Express edition it was running from. (This avoided Microsoft providing a free reporting tool for the world). I thought that was a great option.
So where it is now?
Lately though, I’ve been asked where Reporting Services for Express went. It’s not in that Advanced Services option any more. Other good things are, like full text, but not Reporting Services.
The reason for that, is that since SQL Server 2016, Reporting Services has become a separate install. It’s not directly bundled with SQL Server. And that applies to Express edition as well.
You need to install Reporting Services separately. It still understands deployment against Express edition.
You’ll find details of installing it here.
One thing to note, is that since the 2016 change, multiple instances of Reporting Services on a single machine are not supported. (You could do that previously).
Isn’t it all about Power BI now?
Power BI is outstanding, but there are many scenarios where Reporting Services is still the best tool. Standard reports from Express edition is a great example.
If you need to learn more about Reporting Services, we’ve got a course to help you learn to use it properly. You’ll find details of it, and our other courses, here: https://training.sqldownunder.com .
2021-02-16