0

I have a database with an in-memory filegroup that I want to remove (I won't go into many details why, but I want to move the database to another place without this filegroup).

I'm trying to move the database to Azure SQL Managed Instance (General Purpose), and this PaaS solution doesn't support in-memory filegroups.

Notes:

  • This is running SQL Server 2019
  • This database is quite big: more than 500GB
  • There is no data in that filegroup at this time
  • It have tables (of course), views, SP, Synonyms...

From the documentation "Once you use a memory-optimized filegroup, you can only remove it by dropping the database.". So, I need to drop the database, and create it again without the file group. But doesn't detail the best way to do this.

What is the best option to do this?

I've tried the Copy Database Wizard but it copies the filegroup that I want to delete.

I've tried the SQL Server Import and Export Wizard but it takes too long. I've tried to export to another instance, but due to the size of the database it fails after several hours (network error or session lost).

I've tried to use the option "Generate Scripts" and do this:

  1. Export schema only
  2. Export data only
  3. Edit the file to remove the filegroup
  4. Drop the database
  5. Recreate the database with the edited file
  6. Import the data

But this is taking too long. There is any other way (best and quick way) to do this?

  1. Extract the schema from DatabaseA (from Generate Scripts: Types of data to script: Schema only)
  2. Put the DatabaseA in single user mode
  3. Create the DatabaseB in the same instance Create all the objects in DatabaseB from the extract without that filegroup
  4. Use the SQL Server Import and Export Wizard and copy all the data from DatabaseA to DatabaseB
  5. Rename the database DatabaseA to DatabaseA_old
  6. Rename the database DatabaseB to DatabaseA
0

2 Answers 2

4

Your 3rd option is the best of 3 bad choices, for whatever reason an in-memory filegroup cannot be dropped or removed, it’s been like this since release. The only thing you can do is speed up your export and import, use bulk methods, set the database to simple recovery, avoid very large transactions, if you can add a SSD to the server to get the best I/O

2
  • All bad choices... Specially with a 500GB database. Thanks.
    – dcop7
    Jun 5, 2023 at 7:10
  • 2
    @dcop7 you don't have any good choices, which is the basic point here. You're kinda screwed unless you a) don't use MI, or b) use a tier of MI that supports IMOLTP. Jun 5, 2023 at 14:02
1

How about using the Managed Instance as a transactional replication subscriber?

You could script all non-table objects, and deploy them to MI after the MI subscriber has been synched. Not sure how long it would take, but once things are synched, you would likely need an outage to cutover.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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