Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
L’exécution d’opérations ALTER sur des tables mémoire optimisées n’est pas prise en charge. Cela inclut des opérations telles que la modification du bucket_count, l’ajout ou la suppression d’un index et l’ajout ou la suppression d’une colonne. Cette rubrique fournit des instructions sur la mise à jour des tables optimisées en mémoire.
Mise à jour de la définition d’une table Memory-Optimized
La mise à jour de la définition d’une table optimisée en mémoire vous oblige à créer une table avec la définition de table mise à jour, à copier les données dans la nouvelle table et à commencer à utiliser la nouvelle table. Sauf si la table est en lecture seule, cela nécessite l’arrêt de la charge de travail sur la table pour garantir qu’aucune modification n’est apportée à la table pendant l’exécution de la copie des données.
La procédure suivante décrit les étapes requises pour mettre à jour une table. Dans cet exemple, la mise à jour ajoute un index. Cette procédure conserve le nom de la table et nécessite deux opérations de copie de données : une fois dans une table temporaire et une fois dans la nouvelle table. La modification de la bucket_count d’un index ou l’ajout ou la suppression d’une colonne est effectuée de la même façon.
Suspendez la charge de travail sur la table.
Générez un script pour la table et ajoutez le nouvel index au script.
Générez un script pour les objets liés au schéma (principalement des procédures stockées compilées en mode natif) référençant T et leurs autorisations.
Les objets liés au schéma faisant référence à la table sont disponibles à l’aide de la requête suivante :
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;Les autorisations d’une procédure stockée peuvent être scriptées à l’aide de Transact-SQL suivante :
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 @permissionsCréez une copie de la table et copiez les données de la table d’origine vers la copie de la table. La copie peut être créée à l’aide de Transact-SQL1 suivante.
select * into dbo.T_copy from dbo.TS’il existe suffisamment de mémoire disponible,
T_copyil peut s’agir d’une table optimisée en mémoire, ce qui accélère la copie des données.2Supprimez les objets liés au schéma référençant la table d’origine.
Supprimez la table d'origine.
Créez une nouvelle table (
T) avec le script contenant le nouvel index.Copiez les données de
T_copyversT.Recréez les objets liés au schéma de référence et appliquez les autorisations.
Démarrez la charge de travail sur
T.
1 Notez que dans cet exemple, T_copy est sauvegardé sur le disque. Si une sauvegarde de T est disponible, T_copy peut être une table temporaire ou non persistante.
2 Il doit y avoir suffisamment de mémoire pour T_copy. La mémoire n’est pas libérée immédiatement sur DROP TABLE. Si T_copy la mémoire est optimisée, il doit y avoir suffisamment de mémoire pour deux copies supplémentaires de T. S’il T_copy s’agit d’une table sur disque, il ne doit y avoir que suffisamment de mémoire pour une copie supplémentaire de T, en raison du garbage collector qui doit rattraper le rattrapage après avoir supprimé l’ancienne version de T.
Modification du schéma (PowerShell)
Les scripts PowerShell suivants préparent et génèrent des modifications de schéma en scriptant la table et les autorisations associées.
prepare_schema_change.ps1 <serverName> <databaseName> <schemaName> <tableName>
Ce script prend en tant qu’arguments une table, et scripte l’objet et ses autorisations et référence les objets liés au schéma et leurs autorisations dans le dossier actif. Un total de 7 scripts sont générés pour mettre à jour le schéma de la table d’entrée :
Copiez des données dans une table temporaire (segment de mémoire).
Supprimez les objets liés au schéma référençant la table.
Supprimez le tableau.
Recréez la table avec le nouveau schéma et réappliquez les autorisations.
Copiez les données de la table temporaire vers la table recréée.
Recréez des objets liés au schéma référençant la table et leurs autorisations.
Supprimez la table temporaire.
Le script de l’étape 4 doit être mis à jour pour refléter les modifications de schéma souhaitées. S’il existe des modifications dans les colonnes de la table, les scripts des étapes 5 (copier des données à partir d’une table temporaire) et 6 (recréer des procédures stockées) doivent être mis à jour si nécessaire.
# 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 ""
Le script PowerShell suivant exécute les modifications de schéma qui ont été scriptées dans l’exemple précédent. Ce script prend comme argument une table et exécute les scripts de modification de schéma générés pour cette table et les procédures stockées associées.
Utilisation : 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 ""