Modificación de tablas Memory-Optimized

2017-06-13

No se admite la realización de operaciones ALTER en tablas optimizadas para memoria. Esto incluye operaciones como cambiar el bucket_count, agregar o quitar un índice y agregar o quitar una columna. En este tema se proporcionan instrucciones sobre cómo actualizar tablas optimizadas para memoria.

Actualizando la definición de una tabla Memory-Optimized

La actualización de la definición de una tabla optimizada para memoria requiere que cree una nueva tabla con la definición de tabla actualizada, copie los datos en la nueva tabla y empiece a usar la nueva tabla. A menos que la tabla sea de solo lectura, esto requiere detener la carga de trabajo en la tabla para asegurarse de que no se realizan cambios en la tabla mientras se realiza la copia de datos.

En el procedimiento siguiente se describen los pasos necesarios para actualizar una tabla. En este ejemplo, la actualización agrega un índice. Este procedimiento conserva el nombre de la tabla y requiere dos operaciones de copia de datos: una vez en una tabla temporal y una vez en la nueva tabla. Cambiar el bucket_count de un índice o agregar o quitar una columna se realiza de la misma manera.

Detenga la carga de trabajo en la mesa.
Genere el script para la tabla y agregue el nuevo índice al script.

Generar script para los objetos enlazados al esquema (principalmente procedimientos almacenados compilados de forma nativa) que hacen referencia a T y sus permisos.

Los objetos enlazados a esquema que hacen referencia a la tabla se pueden encontrar mediante la consulta siguiente:

declare @t nvarchar(255) = N'<table name>'  

select r.referencing_schema_name, r.referencing_entity_name  
from sys.dm_sql_referencing_entities (@t, 'OBJECT') as r join sys.sql_modules m on r.referencing_id=m.object_id  
where r.is_caller_dependent = 0 and m.is_schema_bound=1;

Los permisos de un procedimiento almacenado se pueden secuenciar mediante el siguiente Transact-SQL.

declare @sp nvarchar(255) = N'<procedure name>'  
declare @permissions nvarchar(max) = N''  

select @permissions += dp.state_desc + N' ' + dp.permission_name + N' ON ' +   
   quotename(schema_name(o.schema_id)) + N'.' + quotename(o.name) + N' TO ' +  
   quotename(u.name) + N'; ' + char(13)  
from sys.database_permissions as dp  

join sys.database_principals as u  
   on u.principal_id = dp.grantee_principal_id  

join sys.objects as o  
   on o.object_id = dp.major_id  
where dp.class = 1 /* object */  
   and dp.minor_id = 0 and o.object_id=object_id(@sp);  

select @permissions

Cree una copia de la tabla y copie los datos de la tabla original en la copia de la tabla. La copia se puede crear mediante el siguiente Transact-SQL¹.
```
select * into dbo.T_copy from dbo.T  
```
Si hay suficiente memoria disponible, T_copy podría ser una tabla optimizada para memoria, lo que agiliza la copia de datos.²
Quite los objetos enlazados al esquema que hacen referencia a la tabla original.
Quite la tabla original.
Cree la nueva tabla (T) con el script que contiene el nuevo índice.
Copie los datos de T_copy a T.
Vuelva a crear los objetos enlazados al esquema de referencia y aplique los permisos.
Inicie la carga de trabajo en T.

¹ Tenga en cuenta que T_copy se conserva en el disco en este ejemplo. Si hay disponible una copia de seguridad de T , T_copy podría ser una tabla temporal o no duradera.

² Debe haber suficiente memoria para T_copy. La memoria no se libera inmediatamente en DROP TABLE. Si T_copy está optimizada para memoria, debe haber suficiente memoria para dos copias adicionales de T. Si T_copy es una tabla basada en disco, solo debe haber suficiente memoria para una copia adicional de T, debido a que el recolector de elementos no utilizados necesita ponerse al día después de eliminar la versión anterior de T.

Cambio de esquema (PowerShell)

Los siguientes scripts de PowerShell preparan y generan cambios de esquema mediante el uso de scripts para la tabla y los permisos asociados.

prepare_schema_change.ps1 <serverName> <databaseName> <schemaName> <tableName>

Este script toma como argumentos una tabla y escribe el objeto y sus permisos y hace referencia a objetos enlazados a esquema y sus permisos en la carpeta actual. Se generan un total de 7 scripts para actualizar el esquema de la tabla de entrada:

Copiar datos en una tabla temporal (un montón).
Quite los objetos enlazados al esquema que hacen referencia a la tabla.
Elimine la tabla.
Vuelva a crear la tabla con el nuevo esquema y vuelva a aplicar los permisos.
Copie los datos de la tabla temporal en la tabla recreada.
Vuelva a crear objetos vinculados al esquema que referencian la tabla y sus permisos.
Eliminar la tabla temporal.

El script del paso 4 debe actualizarse para reflejar los cambios de esquema deseados. Si hay algún cambio en las columnas de la tabla, los scripts de los pasos 5 (copiar datos de la tabla temporal) y 6 (volver a crear procedimientos almacenados) deben actualizarse según sea necesario.

# Prepare for schema changes by scripting out the table, as well as associated permissions
# Usage: prepare_schema_change.ps1 server_name db_name schema_name table_name  
# stop execution once an error occurs  
$ErrorActionPreference="Stop"  
  
if($args.Count -le 3)  
{  
   throw "Usage prepare_schema_change.ps1 server_name db_name schema_name table_name"  
}  
  
$servername = $args[0]  
$database = $args[1]  
$schema = $args[2]  
$object = $args[3]  
  
$object_heap = "$object$(Get-Random)"  
  
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null  
  
$server =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)  
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server)  
  
## initialize table variable  
$tableUrn = $server.Databases[$database].Tables[$object, $schema]  
if($tableUrn.Count -eq 0)  
{  
   throw "Table or database not found"  
}  
  
## initialize scripting object  
$scriptingOptions = New-Object ("Microsoft.SqlServer.Management.SMO.ScriptingOptions")
$scriptingOptions.Permissions = $True  
$scriptingOptions.ScriptDrops = $True  
  
$scripter.Options = $scriptingOptions;  
  
Write-Host "(1) Scripting SELECT INTO $object_heap for table [$object] to 1_copy_to_heap_for_$schema`_$object.sql"  
Echo "SELECT * INTO $schema.$object_heap FROM $schema.$object WITH (SNAPSHOT)" | Out-File "1_copy_to_heap_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(2) Scripting DROP for procs schema-bound to [$object] 2_drop_procs_$schema`_$object.sql"  
## query referencing schema-bound objects  
$dt = $server.Databases[$database].ExecuteWithResults("select r.referencing_schema_name, r.referencing_entity_name  
from sys.dm_sql_referencing_entities ('$schema.$object', 'OBJECT') as r join sys.sql_modules m on r.referencing_id=m.object_id  
where r.is_caller_dependent = 0 and m.is_schema_bound=1;")  
  
## initialize out file  
Echo "" | Out-File "2_drop_procs_$schema`_$object.sql"  
## loop through schema-bound objects  
ForEach ($t In $dt.Tables)  
{    
   ForEach ($r In $t.Rows)  
   {    
      ## script object   
      $so =  $server.Databases[$database].StoredProcedures[$r[1], $r[0]]  
      $scripter.Script($so) | Out-File -Append "2_drop_procs_$schema`_$object.sql"  
   }  
}  
Write-Host "--done--"  
Write-Host ""  
Write-Host "(3) Scripting DROP table for [$object] to 3_drop_table_$schema`_$object.sql"
$scripter.Script($tableUrn) | Out-File "3_drop_table_$schema`_$object.sql";
Write-Host "--done--"  
Write-Host ""  
  
## now script creates  
$scriptingOptions.ScriptDrops = $False  
  
Write-Host "(4) Scripting CREATE table and permissions for [$object] to !please_edit_4_create_table_$schema`_$object.sql"  
Write-Host "***** rename this script to 4_create_table.sql after completing the updates to the schema"
$scripter.Script($tableUrn) | Out-File "!please_edit_4_create_table_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(5) Scripting INSERT INTO table from heap and UPDATE STATISTICS for [$object] to 5_copy_from_heap_$schema`_$object.sql"  
Write-Host "[update this script if columns are added to or removed from the table]"  
Echo "INSERT INTO [$schema].[$object] SELECT * FROM [$schema].[$object_heap]; UPDATE STATISTICS [$schema].[$object] WITH FULLSCAN, NORECOMPUTE" | Out-File "5_copy_from_heap_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(6) Scripting CREATE PROC and permissions for procedures schema-bound to [$object] to 6_create_procs_$schema`_$object.sql"  
Write-Host "[update the procedure definitions if columns are renamed or removed]"  
## initialize out file  
Echo "" | Out-File "6_create_procs_$schema`_$object.sql"  
## loop through schema-bound objects  
ForEach ($t In $dt.Tables)  
{    
   ForEach ($r In $t.Rows)  
   {    
      ## script the schema-bound object  
      $so =  $server.Databases[$database].StoredProcedures[$r[1], $r[0]]  
      ForEach($s In $scripter.Script($so))  
        {  
            Echo $s | Out-File -Append "6_create_procs_$schema`_$object.sql"  
            Echo "GO" | Out-File -Append "6_create_procs_$schema`_$object.sql"  
        }  
   }  
}  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(7) Scripting DROP $object_heap to 7_drop_heap_$schema`_$object.sql"  
Echo "DROP TABLE $schema.$object_heap" | Out-File "7_drop_heap_$schema`_$object.sql";  
Write-Host "--done--"  
Write-Host ""

El siguiente script de PowerShell ejecuta los cambios de esquema que se crearon en el ejemplo anterior. Este script toma como argumento una tabla y ejecuta los scripts de cambio de esquema que se generaron para esa tabla y los procedimientos almacenados asociados.

Uso: execute_schema_change.ps1 server_name**db_name table_nameschema_name

# stop execution once an error occurs  
$ErrorActionPreference="Stop"  
  
if($args.Count -le 3)  
{  
   throw "Usage execute_schema_change.ps1 server_name db_name schema_name table_name"  
}  
  
$servername = $args[0]  
$database = $args[1]  
$schema = $args[2]  
$object = $args[3]  
  
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null  
  
$server =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)  
$database = $server.Databases[$database]  
$table = $database.Tables[$object, $schema]  
if($table.Count -eq 0)  
{  
   throw "Table or database not found"  
}  
  
$1 = Get-Item "1_copy_to_heap_$schema`_$object.sql"  
$2 = Get-Item "2_drop_procs_$schema`_$object.sql"  
$3 = Get-Item "3_drop_table_$schema`_$object.sql"  
$4 = Get-Item "4_create_table_$schema`_$object.sql"  
$5 = Get-Item "5_copy_from_heap_$schema`_$object.sql"  
$6 = Get-Item "6_create_procs_$schema`_$object.sql"  
$7 = Get-Item "7_drop_heap_$schema`_$object.sql"  
  
Write-Host "(1) Running SELECT INTO heap for table [$object] from 1_copy_to_heap_for_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $1.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(2) Running DROP for procs schema-bound from [$object] 2_drop_procs_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $2.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(3) Running DROP table for [$object] to 4_drop_table_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $3.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(4) Running CREATE table and permissions for [$object] from 4_create_table_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $4.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(5) Running INSERT INTO table from heap for [$object] and UPDATE STATISTICS from 5_copy_from_heap_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $5.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(6) Running CREATE PROC and permissions for procedures schema-bound to [$object] from 6_create_procs_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $6.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""  
  
Write-Host "(7) Running DROP heap from 7_drop_heap_$schema`_$object.sql"  
$database.ExecuteNonQuery("$(Echo $7.OpenText().ReadToEnd())")  
Write-Host "--done--"  
Write-Host ""

Véase también

Memory-Optimized Tablas

Partekatu honen bidez:

Modificación de tablas Memory-Optimized

Actualizando la definición de una tabla Memory-Optimized

Cambio de esquema (PowerShell)

Véase también

Baliabide gehigarriak