Introduction
If you are not able to spend money on your small databases, SQL Server Express edition is the solution for you. This edition will provide you with a basic storage option and this article is to examine what maintenance tasks you need to perform to manage SQL Express effectively.
Maximum Database Size
The major limitation in the SQL Express database is that the storage limit which is 10 GB. However, it is important to note that this limitation is on the data file and the log file is excluded from this limitation. This means log files can be in any size.
Following is the Disk Usage report of the Express database which describes the limitations on the data and log files.
You will see that database is 24 GB which is clearly over the 10 GB limitation.
Since there is no limitation for the Express database transaction log file, you can leave the express database recovery model in either full or bulk logged. Most of the users configure the recovery model of the database in Express edition as Simple to reduce the transaction log file size. With this configuration, you are dropping the options of transactional log backup and importantly you will not have the option of point in time recovery. These configurations leave databases in a risk state.
Since log file size will not count towards the database size, you can leave the database recovery model as Full or bulk-logged without impacting important recovery features as shown in the below figure.
What data to store in Express
Since you have a limitation of 10 GB for data, at the design stage itself, you need to look at what type of data that you are storing in your database. You need to look at database distribution strategies to maintain the 10 GB limit. You can distribute your data from a functional perspective. For example, if you are running an Accounting system, you can create different databases for Accounts Payable, Accounts Receivable, Inventory and General Ledger domains, etc. Since the upper limit for the number of databases in a SQL Server Express edition is more than 32,000, you can create multiple databases in an express instance.
Similarly, you can design your database so that data is kept for only a specific time period preferably for a year. Whatever the option that you follow, it must be taken at an earlier stage of the system design in order to design and develop the relevant application.
Apart from the design strategies, you need to look at the data archiving strategies. In the standard or enterprise edition, you can simply archive the data into the same database. However, in the SQL Server Express edition, it is essential to archive the data into a different database. When data is in another database, the restoring process is much complex. Therefore, in the Express databases, you need to focus on design as well as for the archiving process of data.
Data Types
You need to choose data types very carefully so that you can limit disk usage. For example, if you can use tinyint instead of int, you will be saving 3 bytes per row. Similarly, you should not use Unicode data types such as nvarchar, nchar unnecessary.
Indexes
Indexes are implemented to improve the database performance of queries. However, indexes will consume disk space. Therefore, you need to manage indexes optimally. If there are no used or less used indexes, it is better to drop those indexes in order to retain the disk space.
This article, How to identify and monitor unused indexes in SQL Server, explains how to work with unused indexes in SQL Server. However, though indexes are rarely used, that does not say that they should be dropped. Thus, you need to look at what indexes to be dropped with the consultation of the system developers.
Index fragmentation is another phenomenon that will result in unnecessary disk space. There are two types of fragmentations, those are Internal and External Fragmentations. External fragmentation is the different order of physical and logical ordering of data pages, that will not be accounted for the additional disk space.
On the other hand, Internal fragmentation means empty spaces in data or index pages. This will account for additional disk spaces. Therefore, it is important to rebuild the indexes regularly in SQL Express databases. Since Index Reorganize will fix the external fragmentation only, it will not have any impact on the disk space. Therefore, it is essential to perform an index rebuild. Since index rebuild is similar to drop and recreate of indexes, rebuild of indexes will consume resources from the database. This means that you need to schedule the index rebuild operation at the least used time of the SQL Server Express database.
Further, you can reduce the index sizes by removing columns in order to reduce disk usage. However, this will not be a major impact on disk space anyway.
Shrink Databases
When you archive your data or when you perform index rebuild, database space will not be released to the operating system, but it will be shown as an unallocated space in the Disk Usage report. However, this will not be an issue as new data will consume the unallocated space. Even if you want to remove the unallocated space, you can use database shrink in order to remove the unallocated space as shown below
Memory
Another limitation in SQL Server Express is the database memory which is around 1 GB. Though there is nothing much you need to manage on the memory side of the database, you need to limit your query output and better indexes. As you can imagine, memory plays a vital role when it comes to database performance. Therefore, in the SQL Server Express edition, you need to manage your queries efficiently.
Backups
Even if it is SQL Server Express, it is your data. Hence you need to maintain proper database backups. Like other databases, you need to perform full database backups. In order to maintain the point time recovery option, you should take log backups. Further, to improve the restoring process, you can implement differential backups as well.
Since the SQL Server express database will have a maximum of 10 GB size, performing a full backup won’t impact server resources. Since the database is not too large, a full backup will not consume much time as well. Considering all these situations, in most of the real-world implementations, you don’t need differential backups and you can maintain only full and transactional log backups in SQL Express databases.
Since SQL Server Express does not have SQL Server Agent capabilities, you will not be able to create scheduled tasks from SQL Server Agent Jobs. Since database backups should be created in a proper schedule, it will be better to include some other means such as customized applications to create database backups on a defined schedule. The same scenario is applicable for index rebuilding as well.
Conclusion
In this article, we examined how to manage the SQL Express database. Since SQL Server Express does not require any licensing cost, many small scall users tend to use the Express databases. However, due to the main limitation which 10GB for data size, few other tasks need to be done starting from the design stage.
At the design stage, you need to look at the distributed database design pattern and better data archiving strategies. Since Index maintaining is another important factor, it is important to index rebuild regularly and monitors unused or underused non-clustered indexes.
Memory is another limitation in SQL Server Express edition where it supports only 1.4 GB of database memory. To efficiently handle database memory, you need to write optimal queries and include optimal indexes.
We also looked at the database backup strategies for the SQL Server Express database and conclude that Express databases do not need differential backups, but it still needs full and transaction log backups.
View all posts by Dinesh Asanka
- Testing Type 2 Slowly Changing Dimensions in a Data Warehouse - May 30, 2022
- Incremental Data Extraction for ETL using Database Snapshots - January 10, 2022
- Use Replication to improve the ETL process in SQL Server - November 4, 2021