This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Can the DPA monitoring DB be installed on an AWS RDS database?

I would like to migrate my current DPA monitoring database to an RDS database on AWS, instead of having it supported on an EC2 instance. I can see that It can be installed on an Aurora RDS database, but am not seeing the option for an SQL Server RDS instance.

I am just looking for confirmation if it is possible or not.

Thanks,

Clayton

  • It can monitor the following currently:

    Amazon RDS

    However, if you are talking about the repository, you can see that we're more limited for RDS deployments (MySQL/Aurora):

    Supported database versions:

    DATABASEEDITIONVERSION
    Microsoft SQL Server
    • Standard
    • Enterprise
    • 2008 R2 SP3
    • 2012 SP3
    • 2014 SP2
    • 2016 SP1
    Azure SQLStandard Service Tier or higherV12
    MySQL
    • Community
    • Enterprise
    • 5.6.10 and later
    • 5.7.9 and later
    • MySQL RDS
    • Aurora 5.6.10a and later
    Oracle
    • Standard
    • Enterprise
    • 11.2
    • 12.1 (single tenant and multitenant)
    • 12.2 (single tenant and multitenant)

    Now, having said all that, if you restore your repo database in an RDS SQL Server instance, I'm not sure DPA would even know (just repoint the repo.properties file to the new instance/database).

    This configuration would not be officially supported though and it has not been tested to my knowledge.

  • I am tempted to try it out. I can't think of any reasons why it would not work. The database is just being used as a collection of tables. No other features are used, so it is pretty simple in terms of what it is expecting of the database.

  • My thought exactly. You can't set it up initially using RDS as we do a check if creating the repository that it will trip over.

    But if the repository already exists and we edit the repo.properties file, don't see why the app would care.

    If you do try it, let me know how it goes. Again, bear in mind that this would not be a supported configuration...

  • Rob,

    Here is how it went... Any suggestions on if/how I can get around the roadblock encountered would be greatly appreciated.

    We finally got around to trying migrating the repository to RDS, with limited success. The service connected fine, and was able to log messages to the [ignite].[CONLOG] table, but threw an error when trying to write to the table [ignite].[CONTT_5].

    If anything jumps out at you that I should try, please let me know. Below are the steps of the test, the error and stack trace.

    1. Set up SQL 2016 database on AWS RDS names [dpa_test].
    2. Used AWS Database Migration Service to populate the database from the current host database [dpa_repository] (point in time, with no sync to keep up).
    3. Ensured the same user accounts, password and rights were established. Tested the DPA login to new server & database with SSMS.
    4. Confirmed the repository content had been copied successfully. We did not immediately test a cut-over, as this test was primarily to try out the AWS database migration service.

    5. Two days later, (in case that matters), we edited the repo.properties file on the DPA web server as follows to repoint the repository connection:

    repo.databaseName=dpa_test
    repo.host=solarwinds.c7xxxxxxxxxx.us-east-1.rds.amazonaws.com
    #repo.databaseName=dpa_repository
    #repo.host=DW10
    repo.password=redacted
    repo.name=DPA@DW10
    6. Restarted the "Ignite PI Server" on the web host to point it to the new database location.

    7. logged into the DPA web site

    8. Determined that there were not metrics being logged. The history showed up fine, but it was acting like it could not start up the monitors one each server.

    Clicking into the log for one of the servers, the following error was displayed:

    Message: "Monitor for database [MyServerName] failed to start due to [Cannot insert the value NULL into column 'PUB', table 'dpa_test.ignite.CONTT_5'; column does not allow nulls. INSERT fails.]. Will attempt to retry every [60] seconds..."

    Module: DatabaseMonitor

    Stack trace detail:

    "org.quartz.JobExecutionException: DatabaseMonitorStartJob failed

    [See nested exception: org.quartz.JobExecutionException: PublishJob failed

    [See nested exception: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback;

    SQL [INSERT INTO CONTT_5 (CNT, IEDX, QP, SP) SELECT ?, ?, QP, SP FROM COND WHERE ID = ?];

    Cannot insert the value NULL into column 'PUB', table 'dpa_test.ignite.CONTT_5'; column does not allow nulls. INSERT fails.;

    nested exception is java.sql.SQLException:

    Cannot insert the value NULL into column 'PUB', table 'dpa_test.ignite.CONTT_5'; column does not allow nulls. INSERT fails.]]  

    at com.confio.idc.database.job.DatabaseMonitorStartJob.execute(SourceFile:151)  

    at com.confio.idc.database.job.DatabaseMonitorStartJob.execute(SourceFile:160)  

    at com.confio.idc.database.DatabaseMonitor.startScheduler(SourceFile:808)  

    at com.confio.idc.database.DatabaseMonitor.startSchedulers(SourceFile:790)  

    at com.confio.idc.database.DatabaseMonitor.start(SourceFile:571)  

    at com.confio.idc.database.job.DatabaseMonitorLifecycleJob.execute(SourceFile:105)  

    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)  

    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)  

    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)  

    at java.lang.Thread.run(Thread.java:745) 

    Caused by: org.quartz.JobExecutionException: PublishJob failed

    [See nested exception: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback;

    SQL [INSERT INTO CONTT_5 (CNT, IEDX, QP, SP) SELECT ?, ?, QP, SP FROM COND WHERE ID = ?];

    Cannot insert the value NULL into column 'PUB', table 'dpa_test.ignite.CONTT_5'; column does not allow nulls. INSERT fa "

    9. After seeing this, we updated the configuration file and restarted the website for DPA.
    The site returned, with a few minutes of amnesia, and everything has been running fine since.  There is something about the query populating the table ignite.CONTT_5 that is not getting a value from the RDS server, (my assumption is that it is a server-level DMV function or view).

    We are planning to try again, but this time stopping all the monitors before replicating the database and cutting the web server over, and then restarting the monitors. Again, if it does not work, we'll fall back to the original configuration.

    Any insights or suggestions welcome. If you need additional information, I still have the  dpa_test database available and can get any logs needed off of the web server.

    Warm regards,

    Clayton

  • Wow - looks like it's not even trying to insert a value for PUB.

    INSERT INTO CONTT_5 (CNT, IEDX, QP, SP) SELECT ?, ?, QP, SP FROM COND WHERE ID = ?

    Not even accounted for in the insert statement...

  • What version of DPA are you running? Wonder if the schema doesn't match the version (as in a new column)?

    Mine are all populated with a 'Y'. Let me dig a bit more.

  • Can you send me a table description for the one out there on RDS + a description on the one that's working?

    There should be a default value in the CONTT table for PUB that I suspect is not there in RDS...

  • That is it. The AWS DB Migration tool did not migrate the column defaults... That leaves the option of doing a full backup/restore while DPA is offline, or scripting out the differences and applying that script once the data migration is complete.

    Table from Origin Database:

    CREATE TABLE [ignite].[CONTT_6](

    [IEDX] [datetime] NOT NULL,

    [CNT] [bigint] NULL,

    [SP] [bigint] NULL,

    [QP] [bigint] NULL,

    [PUB] [char](1) NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [IEDX] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    ALTER TABLE [ignite].[CONTT_6] ADD  DEFAULT ('N') FOR [PUB]

    GO

    --Migrated table:

    CREATE TABLE [ignite].[CONTT_6](

    [IEDX] [datetime] NOT NULL,

    [CNT] [bigint] NULL,

    [SP] [bigint] NULL,

    [QP] [bigint] NULL,

    [PUB] [char](1) NOT NULL,

    CONSTRAINT [PK__CONTT_6__B8F277833035D103] PRIMARY KEY CLUSTERED

    (

    [IEDX] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • So if the migration missed that, it begs the question of other potential pitfalls yet to be run into.

    I agree with the full backup/restore option - safest bet.