Announcement

Collapse
No announcement yet.

MS SQL Express db hitting max size

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MS SQL Express db hitting max size

    Initially when we installed Mirth we set it up with MS SQL Express for the DB as we are primarily a MS SQL based practice. This setup was great for the first three years however recently we added an immunization interface and even with pruning daily the db hits 10gb and stops accepting messages.

    At this point it is clear we need to migrate to a different db server whether that is MariaDB or MYSql I don't know or care as long as the tools are there. I would have loved to just move the db off the Mirth server to one of the bigger DB Servers but the express is 2016 and all the other DB servers are 2012 hence we can't even just move the db to a different server.

    I have hunted around for a recent guide but have been unable to find out and nothing for migrating from SQL to something else.

    If someone can point me in the correct direction that would be great.

  • #2
    Hi there,

    I was wondering if you couldnt just configure a new DB (putting all the table and data you already have), for example MySQL, that has way more space available than 10GB.

    And Then just point Mirth to that new DB, and do the changes you need on the channels.

    Comment


    • #3
      Since I am clearing the db daily I think that would be fine. Is there a set of instructions for this as I do not want to recreate the channels again and load the certificate required for our main channel?

      Comment


      • #4
        HI johnlhall,

        I was wondering 10 Gb each day just adding inmunizations? I don't know what requirements you have but, do you really need all this amount of information?. It sounds like you are storing base64 content in your database that you really don't need, am I wrong?.

        Maybe adjusting loggin level in your channels let you work with same database. If I'm really wrong, take a look at Postgres.

        Hope this helps,

        RB

        Comment


        • #5
          Did you change the channel message storage to something like Raw?

          Otherwise what I have experienced in the past is taking the Mirth service offline, backing the Mirth DB up, make backups of all your channels (just because), restoring the DB to different server then pointing Mirth to that new DB server/instance, bring mirth back online, celebrate with drinks.

          Worse case scenario is you point it back to the original and regroup.

          Comment


          • #6
            I have already set the logging to error level. The issue is the number of responses we receive from the registry. Any single patient will result in ~50 messages from the registry. We have 80 providers, 9 clinics so we schedule a lot of patients per day and the systems asks for the imms data every time (unfortunately we can not configure this)

            Comment


            • #7
              Message storage is set to Production

              Comment


              • #8
                As for restoring to a different server the issue is that the current express server is 2016 and we do not have a database server with anything higher than 2012R2 and you can't restore a database to a lower level server than the one it was backed up on.

                Comment


                • #9
                  Then the MSSQLDB backup/restore may be your best option at this point.

                  Or a job that runs and deletes the messages out of Mirth every X mins, but that could cause performance issues.

                  Comment


                  • #10
                    Originally posted by dforesman View Post
                    Then the MSSQLDB backup/restore may be your best option at this point.

                    Or a job that runs and deletes the messages out of Mirth every X mins, but that could cause performance issues.

                    Backup and restore was unsuccessful as the source is on a higher version of SQL Server than the destination.

                    Comment


                    • #11
                      Sorry, yeah the SQL versions would need to be the same or the destination would need to be a newer version.

                      Ive done the SQL backup/restore to the same version and I know it works from personal experience.

                      Comment


                      • #12
                        Originally posted by hugito24 View Post
                        Hi there,

                        I was wondering if you couldnt just configure a new DB (putting all the table and data you already have), for example MySQL, that has way more space available than 10GB.

                        And Then just point Mirth to that new DB, and do the changes you need on the channels.
                        I'mo more than happy to do this I just need to know how to transfer all the existing channels,config and certs to the new database. Is there a guide for this somewhere.

                        Comment


                        • #13
                          You can export/import the entire mirth config, which includes all of your channels. Export your config from the current server, create a new mirth db on your production SQL instance. Point mirth to the new db, and it will recreate the tables for a fresh install, then import your config.

                          Messages are not included in the export, but it sounds like you don't care about those anyway. I think your mirth users will need to be manually reconfigured (or possibly migrated from the old db.)

                          Comment


                          • #14
                            agermano is correct, once you create a new DB and point Mirth to it, Mirth will take a couple secs/mins to start as it builds out the structure on the new DB (if you dont backup/restore).

                            You will need to login as admin and recreate users, that has been my experience.

                            Comment


                            • #15
                              How long do you store the messages in the Engine?

                              Comment

                              Working...
                              X