LocalDB supersedes Access in v.12

Starting with Promodag Reports 12, SQL LocalDB is one of the available database formats for hosting your Promodag Reports database. The LocalDB option is a custom variant of Microsoft SQL Server Express. It was introduced into Promodag Reports as a replacement for the obsolete Microsoft Access database format. This post provides more detailed information about this evolution.

What is LocalDB?

Microsoft SQL Server Express LocalDB is a feature of SQL Server Express targeted to developers. It has many of the same properties as SQL Server but also some notable differences. SQL Server was used as the basis for Promodag Reports’ LocalDB implementation as it is compatible with a wide range of operating systems and architectures. Below is a comparison between Promodag Reports’ different database format options.

Why was this new database option introduced?

LocalDB was introduced primarily as a replacement for the outdated Microsoft Access database format. It comes packaged with the Promodag Reports installer and is therefore easy to set up. We decided to phase out Microsoft Access for multiple reasons:

• Access has a number of limitations in functionality and reporting that make it incompatible with future Promodag Reports features, particularly the support of Modern Authentication with Office 365.
• Access has a 2GB database size limit, which proves to be insufficient in many cases.
• Access has limited performance when it comes to importing and reporting on a significant amount of data.
• Access is fundamentally different from SQL Server, requiring separate development. As SQL LocalDB and SQL Server are basically the same database engine, dropping Access allows for Promodag Reports development to be streamlined.
• Microsoft Access is a legacy database repository that faces an uncertain future. It is already 25 years old.

Manage your LocalDB database

You can create a new LocalDB database using the Configuration Wizard the first time you run Promodag Reports or using the File > New Database option.

Your SQL instance name under LocalDB is (localDB)\Promodag.

The default path to the data file (.mdf) and transaction log (.ldf) of the database is C:\Users\Public\Documents\Promodag\Databases\. This path can be modified manually using the … button.

Your LocalDB database can be compacted (‘shrunk’) using the usual Compact Database option in File > Database Properties and Tools > Compact Database.

You can connect to your LocalDB instance using SQL Server Management Studio (SSMS), though only locally from your Promodag computer (or console). The Windows user that performed the installation of Promodag Reports has access to your LocalDB instance by default. Since SSMS allows you to launch custom queries and perform maintenance or troubleshooting tasks as needed, it is recommended to install it.

Conclusion

The introduction of LocalDB is a positive change that should enable users of the Standard and Professional editions of Promodag Reports to import data covering a longer period of time, make database maintenance easier and more reliable, and allow a seamless transition to SQL Server should the need arise. We recommend that all Access database users opt for this new database format in version 12.