SQL Server Express and Developer – How far can we use free versions of SQL Server? – Dirceu Resende
Views:
29.138 views
Reading Time:
6
minutes
Hello everybody!
After much reading and answering questions in Whatsapp and Telegram groups about limitations of SQL Server Express and Developer, I decided to write this article explaining about these two free versions *** of SQL Server and trying to answer any questions.
SQL Server Developer Edition
Is SQL Server Developer Edition free anyway?
On 31 / 03 / 2016, it was announced that from SQL Server 2014, the Developer version would be free for program members. Visual Studio Dev Essentials (see the official post here).
A little later, this benefit eventually extended to all users, and you can download the latest version of SQL Server Developer. accessing this link here.
Where to download previous versions of SQL Server Developer Edition?
If you want an older version of SQL Server Developer, access this link here, as shown below (must have a program account Visual Studio Dev Essentials:
Can I use SQL Server Developer Edition in production environments?
This incredible benefit that Microsoft has made available to IT pros, allowing anyone to download and install SQL Server Developer, applies only to study, test, and / or development environments. Under no circumstances use SQL Server Developer in production environments, as your installation will be considered PIRATE / ILLEGAL and you may be heavily punished ($$$) by Microsoft.
And how does Microsoft know that you are using SQL Developer in production?
In cases of complaints or customers who have contracts, Microsoft conducts in-person or remote audits of companies (companies are informed and follow the audit) and consultants analyze the entire environment for pirated software or other licensing problems (including Azure) , so in one of these audits, your company may end up being fined for irregularities.
Although the SQL Server Developer documentation tells us that we can use the Developer version in testing and development environments, we should be very careful about that:
- If your development / testing environment does not have actual production data (it was not replicated or randomly generated or masked), you can rest easy and use the Developer version.
- If your development / testing environment is a copy or sample of the production environment, you may or may not have licensing issues by using the Developer version. In this case, the recommendation is to contact your sales representative at Microsoft and request formal guidance on how to proceed and if using the Developer version applies to your scenario (formalize all communication), as I have seen cases where this did not generate problems and others ended up generating.
What are the differences between the Developer version and Enterprise?
This is a very common question among IT professionals and students who are starting to study SQL Server. Technically, they are the same version. The Developer edition is complete and has all the features we found in the Enterprise edition of SQL Server. This gives everyone the opportunity to know and study all the features that SQL Server offers, such as Analysis Services, Reporting Services, Integration Services, Master Data Services, Data Quality Services, etc., at no cost.
The big difference between the Developer version and the Enterprise version is licensing. While the Developer version is free (with the rules above), the Enterprise version is paid (per core or per server).
If you would like to test the Enterprise version for your company for 180 days (in which case you can test with actual production data) and evaluate whether it is worthwhile to purchase the license, you can download the Trial version of SQL Server. on this link and start using it.
SQL Server Express Edition
Is SQL Server Express Edition really free?
Yes! Since it was released on 2005, the Express version of SQL Server is and always has been free. You can download the latest version of SQL Server Express. accessing this link.
Where to download previous versions of SQL Server Express?
If you want an older version of SQL Server Express, access this link here, as shown below (must have a program account Visual Studio Dev Essentials:
Can I use SQL Server Express in production environments?
Yes! SQL Server Express can be used in production environments without any software licensing issues. However, it has several technical limitations (I will explain below) that may make SQL Server Express unviable for larger and more complex systems.
What are the differences between SQL Server Express and Enterprise?
Unlike the Developer and Enterprise versions, where only the licensing form changes, the Enterprise and Express versions have several technical differences between them, as the Express version has some limitations (both hardware and feature). , which vary according to the version of the DBMS:
Hardware Limitations
- Maximum database size limit: 10 GB (SQL Server 2008 R2 to SQL Server 2017) and 4 GB (SQL Server 2008 and earlier). This limit applies only to data (logs are not considered), although have a form not recommended to exceed this limit
- Maximum RAM memory limit used: 1 GB (1410 MB as of SQL 2016). This limit is only for the Buffer Pool (cache)
- Maximum CPU limit: 1 CPU. If the server has 8 quad-core processors, only 1 quad-core processor will be used (that is, in this example, 4 cores of the 32 available on the server will be used)
My SQL Server Express uses more than 1GB of memory
I see many reports on the internet about people questioning the SQL Server Express memory limit, because in the task manager, the SQL process is using more than 1 GB. So how is this possible?
The answer to that is that this 1GB limit (or 1410MB as of SQL Server 2016) only applies to the buffer pool memory area, which is the main memory area and also responsible for caching queries to reduce queries. disk I/O operations.
However, SQL Server has other memory areas besides the buffer pool. Starting with SQL 2016 SP1, SQL Express can consume an additional 352MB for columnstore object segments (per instance) and an additional 352MB for OLTP in-memory objects (Hekaton) per database. And there are other SQL Server memory areas that have no limitations, such as MEMORYCLERK_SQLCLR, even in the Express edition.
To view memory consumption by area, you can run this query here:
1
2
3
4
SELECT
type
,
SUM
(
pages_kb
)
/
1024
AS
MemoryMB
FROM
sys
.
dm_os_memory_clerks
GROUP
BY
type
ORDER
BY
2
DESC
Software Limitations
- You do not have SQL Agent, meaning you cannot use SQL Server Jobs (if you need to schedule tasks, you must use the OS scheduler)
- It does not have the Database Mail interface, that is, to send emails from SQL Server you will not have an interface (GUI or Wizard) to assist you in the configuration. Everything will have to be done via T-SQL code (see more details here)
- You do not have Analysis Services
- Don’t have Reporting Services
- Don’t have Integration Services
- Not supporting some other features such as: Backup Compression, AlwaysOn, Database Snapshots, Rebuild Online, Backup Encryption, Resource Governor, Transparent Data Encryption (TDE), SQL Profiler, Database Tuning Advisor (DTA), SQL Server Data Tools, Tools MDX, R and Python Integration
It is worth mentioning that there is no limitation on the maximum number of users specific to SQL Server Express (it will follow the same limitation as the other versions – 32.767 simultaneous connections). However, due to the hardware limitations seen above, it is unlikely that this bank will support all this number of open connections. The maximum number of users supported by SQL Server Express ends up being defined by the hardware limitations and the way the application was built.
For a full list of Express limitations, see the links below:
If you would like to learn more about the SQL Server Express Advanced Services extension, which adds some more features to Express, such as Data Tools and Reporting Services, check out my post. SSRS – Reporting Services in your company without paying anything? Meet SQL Server Express with Advanced Services.
In summary, we can consider SQL Server Express as a free and “lite” version of SQL Server Enterprise, due to the limitations of the Express version. However, SQL Server Express is still a DBMS robust enough to support small applications, with a wide variety of management (Windows Authentication, Backups) and development (SQLCLR, XML, JSON, Full-text search) features
I hope you enjoyed this post and see you next time!
Strong hug!
