sql-serverdatabasemigrationbackendpatternsproduction

Migrar INT a BIGINT en SQL Server sin downtime: la estrategia de columna shadow

INT en SQL Server soporta hasta 2,147,483,647 valores positivos. En una tabla que inserta registros frecuentemente, ese límite es más cercano de lo que parece. Cuando lo alcanzas en producción, los nuevos inserts fallan con overflow error. Cuando estás cerca, necesitas migrar antes de llegar.

El problema: no puedes hacer ALTER COLUMN id INT → BIGINT directamente en una tabla grande con actividad de producción. El ALTER requiere un lock en la tabla y puede tardar horas en tablas de decenas de millones de filas. Durante esas horas, ningún insert funciona.

Este post es la estrategia de migración sin downtime que usé.

Por qué ALTER COLUMN es problemático

-- Esto FUNCIONA pero bloquea la tabla
ALTER TABLE pedidos ALTER COLUMN id BIGINT NOT NULL;

Para una tabla de 50M filas, este comando puede tardar 30-60 minutos. Durante ese tiempo:

En producción con SLA, esto es inaceptable.

La estrategia: columna shadow

La estrategia de columna shadow evita el lock:

  1. Agregar columna id_bigint BIGINT (nullable, sin lock)
  2. Backfill de valores existentes en batches (sin lock)
  3. Hacer id_bigint NOT NULL con el valor por defecto del max+1
  4. Actualizar la aplicación para usar id_bigint en nuevos inserts
  5. En una ventana de mantenimiento corta: swap final
-- Step 1: Agregar columna nullable (rápido, no bloquea)
ALTER TABLE pedidos ADD id_bigint BIGINT NULL;

Agregar una columna nullable es una operación de metadata — casi instantánea, sin lock en los datos.

Backfill en batches

-- Step 2: Backfill en batches de 10,000
-- Ejecutar en loop hasta que no haya más NULL
DECLARE @batch_size INT = 10000;
DECLARE @updated INT = 1;

WHILE @updated > 0
BEGIN
    UPDATE TOP (@batch_size) pedidos
    SET id_bigint = CAST(id AS BIGINT)
    WHERE id_bigint IS NULL;

    SET @updated = @@ROWCOUNT;
    WAITFOR DELAY '00:00:01';  -- Pausa entre batches para no saturar IO
    PRINT CONCAT('Updated ', @updated, ' rows');
END;

El TOP (@batch_size) limita el lock a 10,000 filas por vez. La pausa de 1 segundo entre batches permite que otras operaciones se ejecuten. El backfill completo de 50M registros toma ~1.5 horas, pero sin downtime.

Sincronización durante el backfill

Mientras el backfill corre, nuevos registros se insertan con id_bigint = NULL. Necesitas un trigger o un job que mantenga la columna sincronizada:

-- Trigger para mantener id_bigint sincronizado con nuevos inserts
CREATE TRIGGER tr_sync_id_bigint
ON pedidos
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
    SET id_bigint = CAST(p.id AS BIGINT)
    FROM pedidos p
    INNER JOIN inserted i ON p.id = i.id
    WHERE p.id_bigint IS NULL;
END;

El trigger mantiene la columna shadow al día. Cuando el backfill termine, todos los registros tendrán id_bigint populated.

Verificar el backfill

Antes de continuar, verificar que no hay NULLs:

-- Debe retornar 0
SELECT COUNT(*) FROM pedidos WHERE id_bigint IS NULL;

-- Verificar que los valores son correctos (sample)
SELECT TOP 100
    id,
    id_bigint,
    CASE WHEN CAST(id AS BIGINT) = id_bigint THEN 'OK' ELSE 'MISMATCH' END as check
FROM pedidos
ORDER BY NEWID();

-- Verificar max value
SELECT MAX(id), MAX(id_bigint) FROM pedidos;

Hacer NOT NULL la columna shadow

Una vez que el backfill es completo y verificado:

-- Esto sigue siendo rápido porque la columna ya existe y tiene valores
ALTER TABLE pedidos ALTER COLUMN id_bigint BIGINT NOT NULL;

Cambiar de NULL a NOT NULL cuando la columna ya no tiene NULLs es significativamente más rápido que el ALTER original — SQL Server solo actualiza los metadatos, no los datos.

Actualizar la aplicación

Con id_bigint poblado y NOT NULL, actualizar la aplicación para leer de id_bigint en lugar de id:

-- Nuevo: agregar identity seed en id_bigint para futuros inserts
-- (Primero hacer que la app use id_bigint para inserts)

-- Agregar default basado en secuencia o identity
ALTER TABLE pedidos
ADD CONSTRAINT df_id_bigint DEFAULT (NEXT VALUE FOR seq_pedidos_bigint) FOR id_bigint;

La migración de la aplicación es el paso más costoso — todos los queries que referencian id deben actualizarse. En C# con Entity Framework, cambiar el tipo del campo Id de int a long en la clase y actualizar las migraciones es suficiente.

La ventana de mantenimiento final

El swap final (renombrar columnas) requiere una ventana de mantenimiento corta — típicamente 30 segundos si está bien preparado:

-- Ventana de mantenimiento (aplicación en modo read-only o detenida)
BEGIN TRANSACTION;

-- Eliminar trigger de sincronización
DROP TRIGGER tr_sync_id_bigint;

-- Eliminar constraint identity original si existe
ALTER TABLE pedidos DROP CONSTRAINT IF EXISTS pk_pedidos;

-- Renombrar columnas
EXEC sp_rename 'pedidos.id', 'id_int_deprecated', 'COLUMN';
EXEC sp_rename 'pedidos.id_bigint', 'id', 'COLUMN';

-- Recrear PK en la nueva columna id
ALTER TABLE pedidos ADD CONSTRAINT pk_pedidos PRIMARY KEY CLUSTERED (id);

COMMIT;

sp_rename es rápido — cambia solo los metadatos. El riesgo principal: foreign keys en otras tablas que referencian pedidos.id también necesitan actualización.

Foreign keys en otras tablas

-- Identificar todas las FKs que referencian la tabla
SELECT
    fk.name as constraint_name,
    tp.name as parent_table,
    cp.name as parent_column
FROM sys.foreign_keys fk
INNER JOIN sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id
    AND fkc.parent_column_id = cp.column_id
WHERE fk.referenced_object_id = OBJECT_ID('pedidos');

Cada tabla con FK a pedidos.id necesita el mismo proceso de migración. En un sistema con alta normalización, esto puede ser 5-10 tablas adicionales.

Lo que aprendí

Nunca hacer ALTER COLUMN directamente en tablas productivas grandes. El lock puede durar horas. La estrategia de columna shadow es más compleja pero la diferencia entre minutos de downtime y horas.

El trigger de sincronización es temporal, no permanente. Una vez que la aplicación escribe directamente en id_bigint, el trigger es overhead innecesario. Eliminar en la ventana de mantenimiento final.

Identificar las foreign keys antes de empezar. Descubrir que cinco tablas tienen FK a la columna que estás migrando en medio del proceso es el peor momento. La query de FK es el primer paso antes de empezar la migración.

El backfill en batches con pausa es más seguro que sin pausa. En una tabla activa, un UPDATE masivo sin pausa puede saturar el IO del disco y degradar la performance del sistema completo. La pausa de 1 segundo entre batches distribuye el impacto.

Volver al blog