11 Replies Latest reply on Dec 20, 2017 12:14 PM by cgroom

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

    cgroom

      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

        • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
          mandevil

          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.

          1 of 1 people found this helpful
            • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
              cgroom

              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.

                • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                  mandevil

                  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...

                    • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                      cgroom

                      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

                        • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                          mandevil

                          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...

                            • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                              mandevil

                              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.

                              • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                                mandevil

                                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...

                                  • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                                    cgroom

                                    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

                                    • Re: Can the DPA monitoring DB be installed on an AWS RDS database?
                                      cgroom

                                      Rob,

                                       

                                      The AWS Database Migration tool does not migrate triggers, indexes or column defaults, but there are still a couple ways to successfully migrate:

                                       

                                      1. Stop DPA from monitoring SQL servers using the Home page in DPA.
                                      2. Stop the "Ignite PI Server" in the Services console of the DPA web server.
                                      3. Use the AWS data Migration tool to copy the database.
                                      4. Use SQL Server Data Tools (SSDT) to generate a change script from the current local repository database to the one now on RDS.
                                      5. Add the SQL Login for the DPA service to the RDS database and map it to the ignite login in the repository as a database owner. Be sure to set the password to the same as the account for the local repository. Do not set the "Enforce password expiration" option for the new account.
                                      6. Run the change script to add all missing defaults and indexes.
                                      7. Update the repo.properties connection information with the new server name & save.
                                      8. Start the "Ignite PI Server" service
                                      9. Login to DPA home page and start monitoring for each SQL Server.
                                      10. Confirm DPA is logging data to the new repository.

                                       

                                       

                                      Alternately, you could use the AWS Database Migration tool, and leverage the two step migration where you migrate the data, then enable a replication sync process. Hindsight being 20/20, where we messed up with this approach was adding the missing indexes and defaults between the full data copy and enabling the replication sync process. It failed when trying to sync the data because of the missing defaults.

                                       

                                      It only took 30 minutes to copy the quiescent database, and 30 minutes without monitoring was a risk worth taking over spending a lot more time testing the more complex migration method.

                                       

                                      Warm regards,

                                       

                                      Clayton

                                      1 of 1 people found this helpful