Using Microsoft SQL Express Edition to Log Data

Would you like to get started on a data collection and reporting project? Are you hesitating because the database server costs seem too high and are one more hurdle? Well, I have some good news for you! The free Microsoft SQL Server Express Edition might work for your project and we can help you get started.

For data logging and reporting applications it is optimal to have plenty of processing power, disk space, and memory. Our typical database solution is to use Microsoft SQL Server Standard Edition on a Windows Server operating system, on a well provisioned Virtual Machine. This provides a solid foundation that will provide secure and high-performance reporting for years to come and is the recommended approach for on-premises database and reporting solutions.

Projects vary in size and requirements, so there is not one standard solution. For small-scale projects or for a data collection pilot project, another option is to use Microsoft SQL Server Express Edition. Here are the advantages and limitations of using SQL Server Express Edition:

Advantages

SQL Server Express Edition is a free download
SQL Server Reporting Services (SSRS) can be installed as an additional free download
Can be managed with Microsoft SQL Server Management Studio
Runs on Windows 10, Windows Server 2016 and greater, Linux, and Docker Engine 1.8+

Limitations

Limited to 10 GB database size
Limited to use of a single physical CPU and 1 GB of RAM
Does not include SQL Server Agent
Does not include Analysis Services, Integration Services, and other advanced features

For many data collection and reporting projects the 10 GB database size limitation is a knockout. The limitations on CPU and RAM are also serious constraints for any large-scale project or projects supporting many active users. Although 1 GB RAM is specified as the limit, in actual use I measured 1.6 to 2.3 GB of RAM was allocated to SQL Server Express, so RAM should be sized to allow SQL Server to use at least 2 GB of RAM.

The SQL Server Agent is very useful for automation of maintenance tasks and scheduled tasks, such as backups and record purging. Since SQL Server Agent is not included with Express Edition, any needed scheduled tasks require a workaround. For database backups, the Windows Task Scheduler can be used. The limitations of Express Edition limit use of this edition to small-scale projects outside of a regulated environment.

Example Demo Database

Despite the limitations, SQL Server Express can be used in many applications where huge amounts of data will not be permanently logged and where data records can be purged after several years. The database can be installed on an inexpensive server or edge device, with Windows 10 operating system, and a small amount of memory. Note also, that the limit is 10 GB per database. It is possible to use multiple databases per server to greatly extend the storage capability.

To demonstrate, a small demo database with a Sample Data table and Tag table was built. The Sample Data table includes columns: row number, timestamp, tag name, 4 integer columns, and 4 float columns. The Sample Data table was loaded with 50 million records. This database uses 6.3 GB of space, which is about 3 GB less than the database size limit. Future growth of the database can be managed with a record purging script. The 6.3 GB of space, includes a non-clustered index, which is needed so that the queries will quickly find the records for report queries.

Space used in Demo database

Spaced used in Demo Database

A set of queries were used to test performance running on a laptop computer with Windows 10 Pro, Intel® Core™ i7-860U CPU and 32 GB of RAM. The results indicate that for a large table with 50 million rows, fast response is obtained for small datasets returned, and marginal response is obtained for grouping of large sets of data. For reporting, these execution times could be acceptable.

A connection to an Excel Workbook was set up and filtered to obtain 1 million rows. The connection can refresh and reload a pivot table in 15 seconds. The Excel query was modified to load 25 million records, which took nearly 3 minutes. SQL Server reported that the query to obtain the data took 1 minute. These results indicate that loading large datasets (25 million rows) for SQL Server Express to Excel is possible and can work in a reasonable amount of time on a computer with adequate processing power and RAM. excel workbook

It is possible to use SQL Server Express under the right conditions and requirements as a database server for small-scale and non-critical data collection and reporting.

Give us a call and we can work with you to start a data collection and report project that can give you increased visibility and control of your process and operations.

About the Author

Steve has retired from Hallam-ICS, but his contributions to the company continue to be valued.

Read My Hallam Story

About Hallam-ICS

Hallam-ICS is an engineering and automation company that designs MEP systems for facilities and plants, engineers control and automation solutions, and ensures safety and regulatory compliance through arc flash studies, commissioning, and validation. Our offices are located in Massachusetts, Connecticut, New York, Vermont and North Carolina and our projects take us world-wide.