Today I want to share how to solve one of the biggest pain points when you are working with Extensions in 2018. On C/Side, it was easy to change a field name or remove it without losing all the data. Just do the change and forcesync. This is impossible with the earlier versions of extensions development (2017, 2018, <BC14). You always need to use the sync-mode-clean, but this will recreate the tables, and you lose all your data.

  1. You can use rapidstart and download everything, sync-mode-clean and update the app, and import everything again.
  2. You can create a helperapp with all the same tables and fields. First, move all data there and sync-mode-clean and update the app. Then, move the data back and remove the helperapp.
  3. You can do everything on SQL.

Let us check the pro and cons of the solutions:

  1. Rapidstart
    + no programming effort needed
    + fast and easy on small apps (1 table)
    – it gets complex if you have alot of tables in
    – table extensions are hard to manage
    – it gets very slow if you have ALOT of data
  2. By Coding
    + good for complete restructuring because every record is touched anyway
    + good if you need to validate your values again
    – coding is required
    – additional licenses are required
    – getting slower as more data needs to be moved
  3. SQL
    + very fast
    + almost no effort

No negative points on SQL? Why?

Let`s deepdive:

We would need a copy of the tables with all the data, and then we can sync-mode-clean and update our app and move the data back into the app.

1. [SQL] Rename all App tables to _BK (table name contains prefix/suffix or id)
2. [SQL] Recreate all _BK tables as original Tables (without Data)

[SCRIPT-1]

now we have backuped all our data in a seperate table.
We should also save our indexes that we DONT have to recreate them

3. [SQL] Rename all Clustered Indexes to _BK (index name contains prefix/suffix or id)

[SCRIPT-2]

now we are good to go with sync-mode-clean and update our app:

4. [NAV] Uninstall App
5. [NAV] Clean Sync App
6. [NAV] Deploy correct(ed) version

now we need to get our data back into the app. You can write a script which move the data from one to another table and ignore the changed fields.
but the easier way would be if we do the structure changes also on the _BK tables. This would allow us to use this tables as the new original.

7. [SQL] Change/Delete _BK Table/Field to the correct value/datatype

Its time to move the backup tables back as original:

8. [SQL] Delete original Tables
9. [SQL] Rename _BK Tables to original name
10. [SQL] Rename _BK Clustered indexes to original name

[SCRIPT-3, SCRIPT-4]

Done!

We have not copied any data, which is why it is incredibly fast. When I have the choice, I would always avoid the NAV service tier to solve such problems!

Let me provide you with some scripts which help me to perform these steps
* scripts only work if you use a pre or suffix
* using these scripts in production is your own risk!

/* 
	FIRST SCRIPT 
	Rename Original Table to _BK
*/
Use #DATABASENAME#;
DECLARE @AppShortcut AS VARCHAR(10) = '#APPSHORTCUT#';
DECLARE @AppId AS VARCHAR(36) = '#APPID#';
DECLARE @BackupShortcut AS VARCHAR(10) = '_BK';
declare @BufferTable table(
    TableName varchar(1000)
    , Id int
)


insert into @BufferTable
select 
t.name as table_name,
row_number() over (order by (select 1))
from 
	sys.tables t
WHERE 
	(t.name like '%' + @AppShortcut +'%' or t.name like '%' + @AppId + '%')
	and t.name not like '%' +@BackupShortcut ;


Select * From @BufferTable
declare @LastId int = (Select Max(Id) from @BufferTable)
Select @LastId


declare @i int = 1
declare @TableName varchar(1000)
declare @TempTableName varchar(1000)

while @i <= @LastId
begin
    select @TableName = TableName from @BufferTable where Id = @i
	set @TempTableName =  @TableName + @BackupShortcut
    exec sp_rename @TableName, @TempTableName
	exec ('SELECT * INTO "' + @TableName + '" FROM "' + @TempTableName + '" WHERE 1=0;')
    set @i = @i + 1
end
/* 
    SCRIPT 2 
    Rename Original Clustered Indexes
*/
use #DATABASENAME#;
DECLARE @AppShortcut AS VARCHAR(10) = '#APPSHORTCUT#';
DECLARE @AppId AS VARCHAR(36) = '#APPID#';
DECLARE @BackupShortcut AS VARCHAR(10) = '_BK';
declare @BufferTable table(
    TableName varchar(1000)
    , Id int
)
declare @BufferIndexNames table(
    BufferIndexNames varchar(1000)
)


Insert into @BufferIndexNames
SELECT 
	DISTINCT i.name AS IndexName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.name like '%' + @AppShortcut +'%' or i.name like '%' + @AppId + '%'


Insert into @BufferTable
Select 
	BufferIndexNames,
	row_number() over (order by (select 1))
from @BufferIndexNames

Select * From @BufferTable
declare @LastId int = (Select Max(Id) from @BufferTable)
Select @LastId


declare @i int = 1
declare @TableName varchar(1000)
declare @TempTableName varchar(1000)

while @i <= @LastId
begin
    select @TableName = TableName from @BufferTable where Id = @i
	set @TempTableName =  @TableName + @BackupShortcut
    exec sp_rename @TableName, @TempTableName
    set @i = @i + 1
end
/* 
    SCRIPT 3 
    Rename _BK Tables back
*/
Use #DATABASENAME#;
DECLARE @AppShortcut AS VARCHAR(10) = '#APPSHORTCUT#';
DECLARE @AppId AS VARCHAR(36) = '#APPID#';
DECLARE @BackupShortcut AS VARCHAR(10) = '_BK';
declare @BufferTable table(
    TableName varchar(1000)
    , Id int
)


insert into @BufferTable
select 
t.name as table_name,
row_number() over (order by (select 1))
from 
	sys.tables t
WHERE 
	(t.name like '%' + @AppShortcut +'%' or t.name like '%' + @AppId + '%')
	and t.name  like '%' +@BackupShortcut ;

	
Select * From @BufferTable
declare @LastId int = (Select Max(Id) from @BufferTable)
Select @LastId


declare @i int = 1
declare @TableName varchar(1000)
declare @TempTableName varchar(1000)

while @i <= @LastId
begin
    select @TempTableName = TableName from @BufferTable where Id = @i
	set @TableName = left(@TempTableName, charindex(@BackupShortcut, @TempTableName) - 1)
    exec ('DROP Table "' + @TableName + '"')
    exec sp_rename @TempTableName, @TableName
    set @i = @i + 1
end
/* 
  SCRIPT 4 
  rename _BK Clustered Index back to Original  
*/
Use #DATABASENAME#;
DECLARE @AppShortcut AS VARCHAR(10) = '#APPSHORTCUT#';
DECLARE @AppId AS VARCHAR(36) = '#APPID#';
DECLARE @BackupShortcut AS VARCHAR(10) = '_BK';
declare @BufferTable table(
    TableName varchar(1000)
    , Id int
)
declare @BufferIndexNames table(
    BufferIndexNames varchar(1000)
)


Insert into @BufferIndexNames
SELECT 
	DISTINCT i.name AS IndexName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.name like '%' + @AppShortcut +'%' or i.name like '%' + @AppId + '%'

Insert into @BufferTable
Select 
	BufferIndexNames,
	row_number() over (order by (select 1))
from @BufferIndexNames
	
Select * From @BufferTable
declare @LastId int = (Select Max(Id) from @BufferTable)
Select @LastId


declare @i int = 1
declare @TableName varchar(1000)
declare @TempTableName varchar(1000)

while @i <= @LastId
begin
    select @TempTableName = TableName from @BufferTable where Id = @i
	set @TableName = left(@TempTableName, charindex(@BackupShortcut, @TempTableName) - 1)
    exec sp_rename @TempTableName, @TableName
    set @i = @i + 1
end