English
Magyar
Microsoft SQL Server, commonly known as MSSQL, is one of the most widely used database management systems in enterprise software. Many accounting, invoicing, stock management and enterprise resource planning programs are built on it, and it is the natural companion to .NET-based web applications. If you run or develop such software, sooner or later you will need to decide which edition to use, what size server is required, and how to manage backups and maintenance.
This guide will help you with the following: it explains how SQL Server works and its different editions, compares it with MySQL, summarises the new features of SQL Server 2025 and the support periods for each version, and then covers server sizing, security, backups and maintenance.
What is MSSQL?
SQL Server is Microsoft’s relational database management system (RDBMS). It stores data in tables, describes the relationships between tables using keys, and uses T-SQL (Transact-SQL), Microsoft’s extension of the SQL language, for queries. The first version was released in 1989; today it runs on Windows and, since 2017, on Linux as well, and is also available in Docker containers and on Microsoft’s cloud (Azure SQL).
How does it work?
SQL Server consists of two main parts:
- The relational database engine receives queries, analyses them, and the query optimiser selects the fastest execution plan based on statistics.
- The storage engine handles the actual writing and reading: it manages data files, indexes, locks and transactions.
The key to speed is memory. SQL Server keeps frequently used data pages in memory, in what is known as the buffer pool, so it does not need to access the disk for the majority of queries. For this reason, SQL Server by default attempts to allocate all available memory; this is not an error, but part of its operation (it is advisable to set an upper limit; see below). Data integrity is ensured by the transaction log: every change is first recorded in the log, so that following a power cut or crash, the database can be restored to a consistent state.
What is T-SQL used for?
T-SQL extends standard SQL with variables, control structures, error handling, stored procedures, functions and triggers. This allows part of the business logic to run directly within the database, resulting in less network traffic and enabling simpler access control.
DECLARE @MinAr INT = 5000;
SELECT Nev, Ar
FROM Termekek
WHERE Ar > @MinAr
ORDER BY Ar DESC;
Plans: which one should you choose?
| Publication | Price | What is it for? | Main limitations |
|---|---|---|---|
| Express | Free, even for production use | Small applications, websites and internal tools | Max. 50 GB per database (from SQL Server 2025; previously 10 GB), approx. 1.4 GB of memory for the cache, max. 4 cores; no SQL Server Agent |
| Standard | Subject to licence | Most small and medium-sized business applications | From SQL Server 2025, a maximum of 32 cores and 256 GB of memory; high availability is limited |
| Enterprise | Requires a licence; the most expensive | Large, critical systems | No meaningful restrictions; full Always On, online indexing operations, advanced encryption |
| Developer | Free, for development and testing purposes only | Development and testing environment | Cannot be licensed for production use |
Express is sufficient for a well-written website or a small registry program for years, particularly since SQL Server 2025, which raised the database size limit to 50 GB. The bottleneck tends to be the memory limit: if the actively used data does not fit into the cache, which is roughly 1.4 GB, Express starts to slow down, regardless of the server’s size. Express also lacks SQL Server Agent, so scheduled backups must be handled using the Windows Task Scheduler.
The Standard plan is sufficient for most companies: it supports a larger ERP system or online shop and includes the most important security features. The Enterprise plan is required when continuous availability (multi-server ‘Always On’ availability groups), very large amounts of memory or specialised features become business requirements.
In SQL Server 2025, Microsoft discontinued the previous Web edition and made the Standard Developer edition available to developers, enabling the development environment to accurately replicate the limitations of the Standard edition.
Licensing in a nutshell
Paid editions can be licensed under two models:
- Processor-core based: you pay per server core; the number of users doesn’t matter. This is what you need if your database is accessed by an uncountable number of users, such as visitors to a public website or online shop.
- Server + CAL (Standard only): alongside the server licence, you purchase a client access licence (CAL) for each user or device. This can be a more cost-effective option for a clearly defined, small group of internal users.
There are specific terms and conditions for using your own licence on a rented server (VPS), which are typically tied to Software Assurance or a subscription licence. Before purchasing, ask your Microsoft licence reseller which option is best suited to your situation. No licence is required for the Express and Developer editions.
Versions and support
| Version | End of general support | End of extended support |
|---|---|---|
| SQL Server 2025 | The latest version (since November 2025) | – |
| SQL Server 2022 | January 2028 | January 2033 |
| SQL Server 2019 | February 2025 (expired) | January 2030 |
| SQL Server 2017 | October 2022 (expired) | October 2027 |
| SQL Server 2016 | July 2021 (expired) | July 2026 (expired) |
SQL Server 2016 reached end of support in July 2026 and no longer receives security patches. If you are still using this version, you should plan to upgrade; even the 2017 version will only receive updates until October 2027. For new installations, it is advisable to choose SQL Server 2025 or 2022, provided your application’s manufacturer supports them.
What’s new in SQL Server 2025
- Vector data types and vector search: semantic search of text, images or documents directly within the database, which is useful for applications based on artificial intelligence, such as chatbots that work with users’ own documents.
- Native JSON data type and new JSON functions, as well as regular expressions in T-SQL.
- Optimised locking: fewer locking conflicts even with many concurrent writes.
- Higher limits for Express and Standard plans: the Express plan supports a 50 GB database, whilst the Standard plan supports 32 cores and 256 GB of memory.
- Stricter encryption defaults for the connection between clients and the server.
MSSQL or MySQL?
| Criteria | SQL Server | MySQL / MariaDB |
|---|---|---|
| Licence | Express is free; the more advanced versions are paid | Open-source, free |
| Typical environment | Windows Server, .NET, ASP.NET, enterprise software | Linux, PHP, Python, Node.js, WordPress |
| Control Panel | SQL Server Management Studio (SSMS), VS Code | phpMyAdmin, MySQL Workbench, HeidiSQL |
| Hosting | Do you need a Windows VPS or a Linux VPS? | Available on all shared hosting plans |
| Strengths | Advanced tools, T-SQL, deep integration with the Microsoft ecosystem | Low resource requirements, extensive support, free |
Here, too, the choice is determined by the software: if your application is built for SQL Server, there’s no question about it. If you’re running a PHP-based website or WordPress, MySQL or MariaDB is the natural choice, and shared hosting is sufficient for this – you don’t need your own server. We’ve written about MySQL and MariaDB in our knowledge base articles: What is MySQL?, What is MariaDB?
What sort of server do you need for SQL Server?
SQL Server does not run on shared hosting; it requires its own server. There are two options:
- Windows VPS: the familiar environment, with a graphical interface, manageable via remote desktop, and SSMS can be run directly on the server. Most business software written for Windows requires this. We have a separate guide on renting a Windows VPS.
- Linux VPS: SQL Server runs on Linux (Ubuntu, Red Hat Enterprise Linux, SUSE) or in a Docker container, on a licence-free operating system and with lower resource requirements. It’s a good choice if your application also runs on Linux, or if you only need a server for the database. A KVM VPS with at least 2 GB, or preferably 4 GB, of RAM is recommended for this.
Memory
Memory is the most important resource. Work it out like this:
- Windows Server requires 2–4 GB of RAM for its graphical interface,
- Ideally, the SQL Server cache should be large enough to hold frequently used data (the ‘working set’),
- And if anything else is running on the server (business software, IIS), that needs space too.
aWh’s smallest Windows VPS plan starts with 8 GB of RAM, which is sufficient for an Express edition and a small Standard database; for larger databases, it is worth choosing the 12 or 24 GB plan.
Set the maximum memory for SQL Server (max server memory) so that there is still enough left for the operating system and other programs. On an 8 GB server running only SQL Server, this is roughly 5–6 GB; on a 24 GB server, it could be 19–20 GB.
Processor and storage
The number of processor cores matters for concurrent queries, whilst performance per core is important for the response times of individual queries. When it comes to storage, NVMe SSDs are virtually essential: writing the transaction log, indexing operations and backups all involve a large number of disk operations. aWh’s Windows VPSs run on NVMe SSDs.
Some tried-and-tested settings:
- Data files, log files and backups should, if possible, be stored on a separate drive or at least in a separate folder.
- The tempdb should be allocated several data files of the same size (the installer now automatically recommends this).
- The automatic growth of the database should be specified in fixed MB, not as a percentage, to prevent a large database from growing enormously all at once.
Security
- Do not expose port 1433 to the internet. The default TCP port for SQL Server is 1433, which automated bots constantly try to access. On the Windows firewall, only allow access from IP addresses where it is actually required (for example, from the web server), or access it via a VPN or via Remote Desktop from within the server. Changing the port alone does not provide protection.
- Use Windows authentication wherever possible. If you’re also using SQL authentication, disable or rename the built-in
saaccount and give it a long, unique password. - Least privilege. Every application should connect using its own user account, only to its own database, and only with the necessary permissions. A web application should not run with system administrator (sysadmin) privileges.
- Encrypted connection. Clients must connect via TLS; SQL Server 2022 and 2025 are imposing increasingly strict requirements in this regard, which means a valid certificate is required on the server.
- Data-level protection. Transparent Data Encryption (TDE) encrypts data files and backups (available in the Standard edition since 2019), whilst Dynamic Data Masking and Row-Level Security restrict who can view which parts of the data.
- Updates. Make sure you regularly install the cumulative updates (CUs) for Windows and SQL Server as well, not just for the operating system.
Backup
It is not enough to simply copy the SQL Server database as a file: whilst the server is running, the files are not consistent. You must back up the database using SQL Server’s own backup function.
Types of backups:
- Full: the entire database.
- Differential: data that has changed since the last full backup.
- Transaction log: all changes made since the last log backup. It only works in ‘full’ recovery mode, and allows you to roll back to a specific point in time (for example, the minute before an accidental deletion).
How should you plan this? First, decide how much data loss your business can tolerate (this is the RPO). If it’s no more than 15 minutes, you’ll need to run log backups every quarter of an hour. A typical plan: a full backup weekly, a differential backup daily, and log backups every 15–60 minutes. For smaller databases, a daily full backup and frequent log backups are also sufficient.
Scheduling: in the Standard and Enterprise editions, SQL Server Agent runs backup tasks; many people use Ola Hallengren’s free, widely used maintenance scripts for this. In the Express edition, use the Windows Task Scheduler to run the sqlcmd command.
Where? Never just on the same server. Copy your backups to another location as well (another server, cloud storage), and keep them going back several days or weeks. aWh’s daily backups for Windows VPS protect the entire server, but only go back one day and are not time-stamped; this does not replace your own database backups.
Give it a go. Regularly restore a backup to a test database. The backup you haven’t tested is just a hope.
Maintenance
- Indexes: modifying data causes indexes to become fragmented. Schedule regular index maintenance (use ‘REORGANIZE’ for minor fragmentation and ‘REBUILD’ for more severe cases), but don’t run everything every day: on modern NVMe storage, fragmentation matters less than it used to, whilst the timeliness of statistics matters all the more.
- Statistics: the query optimiser bases its decisions on these; keep them up to date, particularly after large data imports.
- Integrity check: run a
DBCC CHECKDBcheck regularly to detect any corruption caused by a disk failure before the corrupted data is included in your backups. - Monitoring: keep an eye on CPU and memory usage, free disk space, log file size and slow queries. The Query Store (enabled by default since SQL Server 2022) shows which queries have slowed down and why.
Control Panels
- SQL Server Management Studio (SSMS): Microsoft’s free, comprehensive management tool for Windows. Queries, permissions, backups, performance monitoring – all in one place.
- Visual Studio Code with the MSSQL extension: a simpler alternative for querying and development, running on Windows, macOS and Linux. Microsoft phased out the previous Azure Data Studio in February 2026 and now recommends this instead.
- sqlcmd: a command-line tool for scripts and scheduled tasks.
Summary
SQL Server is a reliable, well-equipped database management system that is particularly at home in Windows and .NET environments. For smaller applications, the free Express edition is sufficient, and since SQL Server 2025, its limitations have been relaxed; for larger systems, the Standard edition is required, whilst for critical systems requiring continuous availability, the Enterprise edition is necessary. The key to performance is adequate memory and NVMe storage; the key to security is a closed port 1433 and the principle of least privilege; and the key to peace of mind is regular backups stored off-site and tested.
aWh’s Windows VPS plans, featuring licensed Windows Server, NVMe SSD storage and at least 8 GB of RAM, provide a solid foundation for an SQL Server database. If you’re not sure what size server you need for your software, get in touch and we’ll help you out.
Frequently Asked Questions
Is MSSQL free?
The Express edition is indeed suitable for production use, including business applications. Its limitations are: a maximum of 50 GB per database (from SQL Server 2025; 10 GB for earlier versions), approximately 1.4 GB of memory for the cache, and a maximum of 4 processor cores. The Developer edition is also free, but only for development and testing. The Standard and Enterprise editions require a licence.
What is the difference between SQL and MSSQL?
SQL is a standard query language used by almost all relational database management systems. MSSQL (Microsoft SQL Server) is a specific database management system that uses T-SQL, its own extension of the SQL language.
Can MSSQL run on Linux?
Yes, since SQL Server 2017, it has officially supported the Ubuntu, Red Hat Enterprise Linux and SUSE Linux Enterprise distributions, and can also be run in a Docker container. Some Windows-specific features (such as certain integration services) are not available on Linux.
Which port is SQL Server running on?
The default instance listens on TCP port 1433, whilst the SQL Server Browser service helps you locate named instances on UDP port 1434. Do not make either of these accessible to the wider internet: only allow access from the necessary IP addresses via your firewall.
How do I back up my MSSQL database?
Using SQL Server’s built-in backup function: in SSMS, right-click on the database, select Tasks › Back Up, or use the BACKUP DATABASE command in T-SQL. To perform regular backups, schedule the task (using SQL Server Agent; for the Express edition, use the Windows Task Scheduler), and copy the backups to another location as well.
How much memory does an SQL Server need?
SQL Server will run with as little as 2 GB, but on Windows Server, at least 8 GB is recommended for a usable database server, and the more memory you have, the more data it can hold in memory. The Express edition uses roughly 1.4 GB as a cache, so having more memory won’t make it any faster.
How can I upgrade from the Express edition to the Standard edition?
Using the Edition Upgrade feature in the SQL Server installer, with a valid Standard licence key. Your databases will remain intact; only the edition’s restrictions will be lifted. Before doing so, make a full backup and check whether the Standard edition’s licensing model (core-based or CAL) suits your usage.
Do I need to update my old version of SQL Server?
If a version has reached the end of extended support (as has been the case for SQL Server 2016 since July 2026), it will no longer receive security patches, so it is worth upgrading to a newer version. Before doing so, check whether your application’s manufacturer supports the new version, and test the update on a copy of your system.