

-- Manual upgrade script as of JULY 2025 from 3.9 to 3.16 - for MS SQL Server databases.
-- STOP the SymmetricDS service before applying these changes!
-- Overview:
-- Stages 100x: Truncate tables with trasitory data, unnecessary for the upgrade itself.
-- Stages 200-400: Migrate 3 biggest tables to new structure and purge in one swoop. 


-- Stage 100. Clear temporary data in tables:
TRUNCATE TABLE SYM_INCOMING_BATCH;
TRUNCATE TABLE SYM_DATA_GAP;
TRUNCATE TABLE SYM_NODE_COMMUNICATION;

-- Stage 110. Backup and rename large tables: 

  -- Backup old tables:
  ALTER TABLE sym_outgoing_batch RENAME TO BKPSYM_OUTGOING_BATCH;
  ALTER TABLE SYM_DATA_EVENT RENAME TO BKPSYM_DATA_EVENT;
  ALTER TABLE SYM_DATA RENAME TO BKPSYM_DATA;

 -- Create blank tables with new structure:
  CREATE TABLE sym_data_event (
    data_id bigint NOT NULL,
    batch_id bigint NOT NULL,
    create_time datetime2 NULL,
    CONSTRAINT sym_data_event_pkey PRIMARY KEY (data_id, batch_id)
  );
 
  CREATE TABLE sym_data (
    data_id bigint IDENTITY(1,1) NOT NULL,
    table_name varchar(255) NOT NULL,
    event_type char(1) NOT NULL,
    row_data varchar(MAX) NULL,
    pk_data varchar(MAX) NULL,
    old_data varchar(MAX) NULL,
    trigger_hist_id int NOT NULL,
    channel_id varchar(128) NULL,
    transaction_id varchar(255) NULL,
    source_node_id varchar(50) NULL,
    external_data varchar(50) NULL,
    node_list varchar(255) NULL,
    is_prerouted smallint DEFAULT 0 NOT NULL,
    create_time datetime2 NULL,
    CONSTRAINT sym_data_pkey PRIMARY KEY (data_id)
  );

  CREATE TABLE sym_outgoing_batch(
    batch_id bigint NOT NULL,
    node_id varchar(50) NOT NULL,
    channel_id varchar(128) NULL,
    status char(2) NULL,
    error_flag smallint DEFAULT 0 NULL,
    sql_state varchar(10) NULL,
    sql_code int DEFAULT 0 NOT NULL,
    sql_message varchar(MAX) NULL,
    last_update_hostname varchar(255) NULL,
    last_update_time datetime2 NULL,
    create_time datetime2 NULL,
    summary varchar(255) NULL,
    ignore_count int DEFAULT 0 NOT NULL,
    byte_count bigint DEFAULT 0 NOT NULL,
    load_flag smallint DEFAULT 0 NULL,
    extract_count int DEFAULT 0 NOT NULL,
    sent_count int DEFAULT 0 NOT NULL,
    load_count int DEFAULT 0 NOT NULL,
    reload_row_count int DEFAULT 0 NOT NULL,
    other_row_count int DEFAULT 0 NOT NULL,
    data_row_count int DEFAULT 0 NOT NULL,
    extract_row_count int DEFAULT 0 NOT NULL,
    load_row_count int DEFAULT 0 NOT NULL,
    data_insert_row_count int DEFAULT 0 NOT NULL,
    data_update_row_count int DEFAULT 0 NOT NULL,
    data_delete_row_count int DEFAULT 0 NOT NULL,
    extract_insert_row_count int DEFAULT 0 NOT NULL,
    extract_update_row_count int DEFAULT 0 NOT NULL,
    extract_delete_row_count int DEFAULT 0 NOT NULL,
    load_insert_row_count int DEFAULT 0 NOT NULL,
    load_update_row_count int DEFAULT 0 NOT NULL,
    load_delete_row_count int DEFAULT 0 NOT NULL,
    network_millis int DEFAULT 0 NOT NULL,
    filter_millis int DEFAULT 0 NOT NULL,
    load_millis int DEFAULT 0 NOT NULL,
    router_millis int DEFAULT 0 NOT NULL,
    extract_millis int DEFAULT 0 NOT NULL,
    transform_extract_millis int DEFAULT 0 NOT NULL,
    transform_load_millis int DEFAULT 0 NOT NULL,
    load_id bigint NULL,
    thread_id int NULL,
    common_flag smallint DEFAULT 0 NULL,
    fallback_insert_count int DEFAULT 0 NOT NULL,
    fallback_update_count int DEFAULT 0 NOT NULL,
    conflict_win_count int DEFAULT 0 NOT NULL,
    conflict_lose_count int DEFAULT 0 NOT NULL,
    ignore_row_count int DEFAULT 0 NOT NULL,
    missing_delete_count int DEFAULT 0 NOT NULL,
    skip_count int DEFAULT 0 NOT NULL,
    total_extract_millis int DEFAULT 0 NOT NULL,
    total_load_millis int DEFAULT 0 NOT NULL,
    extract_job_flag smallint DEFAULT 0 NULL,
    extract_start_time datetime2 NULL,
    transfer_start_time datetime2 NULL,
    load_start_time datetime2 NULL,
    failed_data_id bigint DEFAULT 0 NOT NULL,
    failed_line_number bigint DEFAULT 0 NOT NULL,
    create_by varchar(255) NULL,
    bulk_loader_flag smallint DEFAULT 0 NULL,
    CONSTRAINT sym_outgoing_batch_pkey PRIMARY KEY (batch_id, node_id)
  ); 


-- Stage 200. Convert SYM_OUTGOING_BATCH and purge at the same time
INSERT INTO sym_outgoing_batch 
	(batch_id,node_id,channel_id,status,error_flag,sql_state,sql_code,sql_message,last_update_hostname,last_update_time
	,create_time,summary,ignore_count,byte_count,load_flag,extract_count,sent_count,load_count,reload_row_count
	,other_row_count,data_row_count,extract_row_count,load_row_count,data_insert_row_count,data_update_row_count
	,data_delete_row_count,extract_insert_row_count,extract_update_row_count,extract_delete_row_count
	,load_insert_row_count,load_update_row_count,load_delete_row_count,network_millis,filter_millis,load_millis
	,router_millis,extract_millis,transform_extract_millis,transform_load_millis,load_id,thread_id,common_flag
	,fallback_insert_count,fallback_update_count,conflict_win_count,conflict_lose_count,ignore_row_count
	,missing_delete_count,skip_count,total_extract_millis,total_load_millis,extract_job_flag,extract_start_time
	,transfer_start_time,load_start_time,failed_data_id,failed_line_number,create_by,bulk_loader_flag)
  SELECT
    batch_id,node_id,channel_id,status,error_flag,
    NULL AS SQL_STATE,
    0 AS SQL_CODE,
    NULL AS SQL_MESSAGE,
    LAST_UPDATE_HOSTNAME,
    LAST_UPDATE_TIME,
    CREATE_TIME ,
    'OLDER_SYMMETRIC_VERSION' AS SUMMARY,
    0 AS IGNORE_COUNT ,
    BYTE_COUNT ,
    0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
    NETWORK_MILLIS,
    FILTER_MILLIS,
    0,0,0,0,0,0,0,0,0,0,0,0,0,
    MISSING_DELETE_COUNT,
    SKIP_COUNT,
    0,0,0,
    CREATE_TIME AS EXTRACT_START_TIME,
    CREATE_TIME AS TRANSFER_START_TIME,
    CREATE_TIME AS LOAD_START_TIME,
    0 AS FAILED_DATA_ID,
    FAILED_LINE_NUMBER,
    'OLDER_SYMMETRIC_VERSION' AS CREATE_BY,
    0 AS BULK_LOADER_FLAG
  FROM BKPSYM_OUTGOING_BATCH
  WHERE STATUS != 'OK';


-- Stage 210. Reinstall indexes for table SYM_OUTGOING_BATCH
CREATE INDEX sym_idx_ob_in_error ON sym_outgoing_batch (error_flag);

CREATE INDEX sym_idx_ob_node_status ON sym_outgoing_batch (node_id, status, channel_id);

CREATE INDEX sym_idx_ob_status ON sym_outgoing_batch (status);


-- Stage 300. Convert table SYM_DATA_EVENT and purge at the same time
INSERT INTO sym_data_event
  WITH OUTGOING as ( select DISTINCT BATCH_ID from SYM_OUTGOING_BATCH )
  SELECT  
    be.DATA_ID,
    be.BATCH_ID,
    be.CREATE_TIME
  FROM BKPSYM_DATA_EVENT be
  join OUTGOING ON be.BATCH_ID = OUTGOING.BATCH_ID;

-- Stage 310. Reinstall indexes for table SYM_DATA_EVENT 
CREATE INDEX sym_idx_de_batchid ON sym_data_event (batch_id);


-- Stage 400. Convert table SYM_DATA and purge at the same time:
INSERT INTO sym_data
  WITH EVENT as ( select DISTINCT DATA_ID from SYM_DATA_EVENT )
  SELECT  
    d.DATA_ID,
    d.TABLE_NAME,
    d.EVENT_TYPE,
    d.ROW_DATA,
    d.PK_DATA,
    d.OLD_DATA,
    d.TRIGGER_HIST_ID,
    d.CHANNEL_ID,
    d.TRANSACTION_ID,
    d.SOURCE_NODE_ID,
    d.EXTERNAL_DATA,
    d.NODE_LIST,
    0 AS IS_PREROUTED,
    d.CREATE_TIME
  FROM BKPSYM_DATA d
  JOIN EVENT ON d.DATA_ID = EVENT.DATA_ID;


-- Stage 410. Reinstall indexes for table SYM_DATA
CREATE UNIQUE NONCLUSTERED INDEX sym_idx_d_channel_id ON sym_data (data_id, channel_id);



-- Stage 500. Validation:
SELECT * FROM (
	SELECT 'SYM_OUTGOING_BATCH' AS table_name, Count(*) AS total_rows FROM SYM_OUTGOING_BATCH
	UNION ALL
	SELECT 'BKPSYM_OUTGOING_BATCH not OK' AS table_name, Count(*) AS total_rows FROM BKPSYM_OUTGOING_BATCH WHERE STATUS != 'OK'
	UNION ALL
	SELECT 'SYM_DATA' AS table_name, Count(*) AS total_rows FROM SYM_DATA
	UNION ALL
	SELECT 'BKPSYM_DATA' AS table_name, Count(*) AS total_rows FROM BKPSYM_DATA
	UNION ALL
	SELECT 'SYM_DATA_EVENT' AS table_name, Count(*) AS total_rows FROM SYM_DATA_EVENT
	UNION ALL
	SELECT 'BKPSYM_DATA_EVENT' AS table_name, Count(*) AS total_rows FROM BKPSYM_DATA_EVENT
);


-- Stage 600. Database performance tuning
UPDATE STATISTICS sym_data WITH SAMPLE 1 PERCENT, PERSIST_SAMPLE_PERCENT=ON;
UPDATE STATISTICS sym_data_event WITH SAMPLE 1 PERCENT, PERSIST_SAMPLE_PERCENT=ON;
UPDATE STATISTICS sym_outgoing_batch WITH SAMPLE 1 PERCENT, PERSIST_SAMPLE_PERCENT=ON;
