I am looking for a solution for a secondary replica change. I have a vendor database that uses memory optimized tables. I am trying to find a solution that would allow the In Memory tables be changed to On Disk tables in a secondary replica for external integrations and reporting.

Can anyone think of a way to accomplish this?

SQL 2016 EE

  • Why do "external integrations and reporting" care whether the tables are in memory are on disk? – Aaron Bertrand May 30 '19 at 19:22
  • Becuase the external integrations are run from another database and you can't query in memory tables from another database. – TallKewlOnez May 31 '19 at 16:33

Due to the "availability-groups" tag and the other terms you have used, I am assuming you are referring to a database in an availability group.

In short, the answer is no, you cannot do this. The secondary database is read-only since it is a replica of the database on the primary. If you removed the target replica from the AG, made the modification, and then tried to add it back to the AG, replication from the primary would no longer work. The solution you are suggesting is 110% not possible with availability groups.

Since you can't modify the schema of the secondary replica, you'll have to start with a completely separate database for the reporting and keep it in sync with replication or an ETL process. A lot more information would be required for anyone to be able to determine what would be the best solution.

| improve this answer | |
  • Yeah, I put in AG because I was originally thinking AG, but I believe I will need to create a different process. It is not currently in an AG, possibly Log Shipping? – TallKewlOnez May 30 '19 at 18:46
  • 1
    No, you will not be able to use log shipping for this either--the secondary database has to be exactly like the source database. If you modify the secondary, you will not be able to restore any more log files from the primary. – Tony Hinkle May 30 '19 at 18:59
  • Thanks for your input. Went with Log Shipping to get the data to secondary sql server. then an etl process to copy data to an external db. – TallKewlOnez May 31 '19 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.