webMethods switch from Oracle to SQL server

Hi Folks,

We are considering switching our IS, MWS, Optimize RDMS database from Oracle to Microsoft SQL Server. I see the requirements document supports SQL Server.
Is anyone using Microsoft SQL server as the RDMS for webMethods running on Linux?
Any issues or cautions?
Can the the Oracle database data be moved to the SQL server database to avoid having to configure everything from scratch?

Thanks, Dave

Hi David,

I think someone has done this before, but as far as i know Software AG doesn’t provide out of the box tool to do so. So it must be handled on the database side and perhaps required involvement of DBA to help you out.

Hi David,

if possible I would prefer migrating the database to a MySQL hosted on the same Linux as the webMethods installation.

What is the current OS for Oracel DB and webMethods?
Which versions of wM and Oracle are you currently running on?

Why do you want to mix a Linux wM env with a Database which requires Windows?
Esp. from productive point of maintenance this does not sound as a good scenario for me.

Regards,
Holger

Hi Holger, thanks for the comments. Let me clarify our current and future state.

Currently our webMethods products ( IS, Optimize, MWS, Broker, … ) all run on separate AIX host, and our Oracle database runs on other AIX host, and is managed by a different group.

Our future state is to move the webMethods products from AIX to Linux. Along with that, we need to move our database off of Oracle. Our Database group has recommended Microsoft SQL server. Because the database is managed by a different group, I don’t think it’s a concern to me which operating system they are using. I just need the database to be highly available.

We are currently running webMethods 10.3 on AIX with Oracle 19c on AIX.
We intend to move webMethods 10.3 to Linux with Microsoft SQL server on Windows.

Question 1: If the database is managed by a different group and I don’t think it matters to me what operating system they are on, do you see any issues with webMethods on Linux, and database on Microsoft SQL server in this situation?

Question 2: Does anyone know if we can migrate/copy the database tables from Oracle to SQL Server? For instance we need to migrate the database MWS uses with all the optimize rules and monitored components. SoftwareAG doesn’t have method for exporting/importing those rules and monitored compontents, so without being able to move that data, we’d need to manually recreate it.

Thanks,
Dave

Theoretically and ostensibly, no. Practically, there may be times when the available drivers for Linux will not meet the need for SQL Server in some way. Connectivity to SQL Server from non-Windows is not entirely an afterthought but is not a primary focus either.

In the “here is what another company is doing” category – In our AIX environment, we regularly connect to SQL Server instances via JDBC adapter connections without much fanfare. But we do not use SQL Server for the “core” DB for the wM components. We use Oracle hosted on AIX (our DB team preference – interesting how teams vary :slight_smile: ).

For the migration, it is likely to be entirely up to you. One approach is to stand up an environment on the new infrastructure then analyze the tables in old and new to determine a data copy strategy. Should be mostly straight-forward. The wM components might use DB-specific queries/DDL but I would think (hope!) that it would be minimal. You may also need to pay attention to non-DB config files for some items. Another option: manually recreate things. Not appealing I’m sure but would provide an opportunity for some potential house-cleaning to eliminate items that are no longer needed. And perhaps might be faster to do than the DB analysis and chasing errors and “what did we miss?”

HTH!

1 Like

Hi

There should not be any issues using WebMethods on Linux with SQL Server. As far as Oracle to Microsoft SQL Server migration is concerned, you might want to

check Microsoft Documentation https://docs.microsoft.com/en-us/sql/ssma/oracle/migrating-oracle-databases-to-sql-server-oracletosql?view=sql-server-ver15

Thanks

Manish

Hi Dave,

from the High Availability point of view I would avoid Windows by any means.

Which Linux type are you planning to use?
SuSE Enterprise or RedHat (should be working on Oracle Linux as well)?

Migrating schemas from database vendor to another is very tricky.
I did so the other way round (from MS SQL to Oracle) last year and this is not finished yet (still requiring some fine tuning).

We used Oracle SQL Developer equipped with an additionall MS SQL JDBC driver Type 4 to copy the structure and data.
But most of this was custom data not related to the wM schemas which have been created from scratch.

Regarding the Drivers:
wMs bundled DataDirect has JDBC Type 4 (Thin Mode) support for Oracle as well as MS SQL built in.
For the JDBC Adapter there is also a Type 4 driver available for download from Microsoft, which is independent of OS.

Regards,
Holger

Thanks for the comments Reamon, Manish, and Holger.

We will be using RedHat Linux.
Reason to move away from Oracle is cost.
We also have adapter connections to Microsoft SQL databases in production, but this would be the first for the webMethods “core/audit” databases.

Based on Holger’s unfinished migration from SQL to Oracle, and Reamon’s comments about starting from scratch as an opportunity for cleanup ( seems as there are many situations where old data is not easily removed from the database ) I may elect to go with the start from scratch method, as much as I hate to because of the tons of manual configuration needed.

I have moved webMethods infrastructure from data center to data center along with the databases, and it’s not been to difficult. This time I’m moving to a new datacenter, new webMethods operating system, and new core database technology. This one will be difficult.

Hi Folks,

I don’t think I received confirmation if anyone is using Microsoft SQL database for core database from webMethods IS server and MWS server running on Linux. If you are doing this, can you let me know.
Thanks,
Dave

Hi David,

I am not sure if this will be an option for you but you can consider switching from Oracle DB to MySQL DB, which is very similar to the Oracle DB regarding the SQL dialect.
Check if MySQL can be hosted on RedHat by your DBA team.

Check with the System Requirements Guide and the Adapter System Requirements Guide which versions/combinations are supported exactly and which driver versions are needed to match these requirements.

Regards,
Holger