Windows server version with OpenXDA

GPA Products openXDA
1

I’m looking forward to using OpenXDA in a new application. I understand that the required database is SQL Server, and I already have one running on Microsoft Server 2016 with SQL Server 2019 Developer Edition, which is currently capable of handling all the data. I’d like to know if anyone is using SQL Server 2022 and if there are any significant advantages to using it with OpenXDA.

2

Hi LLorensi,

SQL Server 2022 is supported, but I don’t think there are any real advantages with regard to how openXDA itself functions. Of course, you may find that some of the features of the newer version may help you query or visualize the data that openXDA stores in the database. You’ll have to read up on what the new features are to determine if they could be relevant to your use case.

Thanks,
Stephen

1 Like
3

Hi Stephen,

Apologies if this isn’t the right section.

We are considering moving away from Microsoft SQL Server for our openXDA deployment and exploring whether we can use another RDBMS. The goal is to maintain a 1:1 database and table structure in the new database system while making only minimal adjustments to the existing C# code or SQL scripts.

Has anyone successfully migrated openXDA to another database management system? If so, which RDBMS did you use, and what were the main challenges or modifications needed?

Any insights or recommendations would be greatly appreciated. (A big “No, it’s impossible” is also valuable, as it helps set expectations)

Thanks!

4

Hi Renan_Pires,

As far as I am aware, no one has really attempted moving away from SQL Server. Early on, when I received the requirements for the development of openXDA, SQL Server was given as the only supported database engine. So you can imagine that a number of SQL Server-specific features have creeped into the codebase over the years.

One other thing that happened, however, was that Ritchie had developed the AdoDataConnection and TableOperations<T> classes in GSF to help make writing queries in C# easier. As more developers were added to openXDA’s development, we started phasing out a lot of the SQL Server-specific APIs in favor of the GSF APIs, simply because they were easier for everyone to use. The good news about this is that much of the system should already support querying other database engines with very few code changes.

There are still some gotchas, though, particularly when it comes to the SQL code itself. Depending on the database you pick, you’ll have to look out for SQL Server-specific syntax and replace it with equivalent syntax supported by your database engine. Off the top of my head, here are some things to look out for.

  • The SqlConnection class, typically only used when querying stored procedures. It should be easy to replace this with the ADO provider for the database engine you’re choosing.
  • SELECT TOP N for which every database seems to have their own syntax.
  • MERGE <TableName> WITH (HOLDLOCK) which is used when augmenting a table with new data (upload new records into temp table, merge with existing table). SQL Server needed the HOLDLOCK to avoid deadlocks in this query so this may be the trickiest one to find a replacement for.

There’s probably more, but I naturally can’t remember them all. I wouldn’t go so far as to say it’s definitely impossible, but I also can’t say for sure how difficult it will be. Good luck!

Thanks,
Stephen

1 Like