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:
- SQL Server 2005 Express
- SQL Server 2008 Express
- SQL Server 2008 R2 Express
- SQL Server 2012 Express
- SQL Server 2014 Express
- SQL Server 2016 Express
- SQL Server 2017 Express
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!
and the WEB version, does it have limitations like EXPRESS? I noticed that it has a low cost (I've seen servers asking $20 per month to use)… I think there should be a version with monthly payment and with the right to upgrade resources without having to install everything again…
Hello, I would like to know if there is a possibility to extend the 180 days. And if the system is formatted and reinstalled from scratch with another HD, for example, can I use those 180 days again? or is some sql record on the hardware .. Thanks
Great explanation, thanks Dirceu my question was clarified.
Dirceu, thanks for the publication, but I had a question. Is the Developer version completely free or does it have any restrictions besides not being able to install in production? For example, can I install this version for use in an approval environment containing production data via bkp / restore?
a question, then the Developer is the same as the Licensee, has all the tools, no size limit etc. (only caveat regarding the use of untrue data)? that's right?
and another question, if I install Developer on Ubuntu, then to migrate to the licensed version is easy and simple without losing data, or does it give you a headache?
Thank you
The Developer edition has no limitations, so it is practically the same as the Enterprise version.
It is easy and simple yes, you will only have to install the licensed version and migrate banks, users, logins, etc. .. As if it were a normal migration .. If you install the trial version and want to migrate to the licensed version, then it is easier, just convert the license into the sql server installer and use the edition upgrade option
** Under no circumstances use SQL Server Developer in production environments, **
Okay, and how does microsoft know if i'm sending out real data or if i'm using made up data?
They spy on me and steal my information ????
Enio, Microsoft goes to companies in person to do this type of verification.
My doubt I can have a bank larger than 10Gb in sql developer
Congratulations on the article, very enlightening.
Here in the company we are just in doubt about the use of a copy of the production base with scrambled data in the development environment for bug simulation.
Do you know if there is an official Microsoft channel where I can answer this question?
Very interesting! I went to download microsoft sql today and saw these two apparently free options. I realize that I downloaded the correct one (express), because I just want to use it for studies and small applications, without having problems afterwards with microsoft. Thanks for the article!
Very good article ...
A doubt…
Is the size limit per bank?
Can I have 5 banks of 9 GB each?
Thank you
That's right. The limit is per bank and you can have 5 banks of 9gb each if you want.
Hug!
Congratulations on the article!
Excellent Dirceu. Thank you for the explanation!
Thanks for stopping by and feedback 🙂
Hi Dirceu, thanks for the article, it helped when I searched for previous versions of Developer.
Att,
Gustavo
You're welcome, Gustavo.
Glad to hear that.
Hello Dirceu, good afternoon!
Thanks for the explanation, I can now decide which will be the best option for my studies.
Att
Edimilson Jr
Thank you, Edimilson.
I hope I have been helpful to your studies.