I have a very big table(T1) with a Tera byte of data in the Prod environment. There are some columns with nvarchar(max) which are such a miss. The table is created by code-first Ef core. Since it is very time-consuming to do it with migration we want to do it by:
- creating a new table (T2) with new types for those columns like varchar(100) with code-first.
- Copying all data from T1 to T2 (DBA process) (needs downtime)
- Renaming those with code-first and creating migration file
- Exporting script of step 3 and run it directly in Database with DBAs
- After achieving success in step 4 update the database with the renaming migration. Since changes were done in step 4 and we have the migration record in the database table it will execute quickly.
- Now we can drop the T1 with migration
- Exporting its script and run it directly in the database
- Updating database with step 7 migration
So do you have such experience or idea to add?