시스템 버전 관리된 임시 테이블에서 기록 데이터의 보존 관리Manage Retention of Historical Data in System-Versioned Temporal Tables

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

시스템 버전 관리된 임시 테이블에서 기록 테이블은 특히 다음과 같은 상황에서 일반 테이블보다 데이터베이스 크기를 좀 더 크게 늘릴 수 있습니다.With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions:

  • 기록 데이터를 오랫동안 보관You retain historical data for a long period of time

  • 업데이트가 있거나 과도한 데이터 수정 패턴을 삭제함You have an update or delete heavy data modification pattern

    크기가 크고 점점 증가하는 기록 테이블은 기본 저장소 비용과 임시 쿼리에 대한 성능 세금 부과로 인해 문제가 발생할 수 있습니다.A large and ever-growing history table can become an issue both due to pure storage costs as well as imposing a performance tax on temporal querying. 따라서 기록 테이블에서 데이터를 관리하기 위한 데이터 보존 정책을 개발하는 것이 모든 임시 테이블의 수명 주기 계획 및 관리의 중요한 요소입니다.Hence, developing a data retention policy for managing data in the history table is an important aspect of planning and managing the lifecycle of every temporal table.

기록 테이블에 대한 데이터 보존 관리Data retention management for history table

임시 테이블 데이터 보존 관리는 각 임시 테이블에 대한 필수 보존 기간을 결정하는 것부터 시작됩니다.Managing temporal table data retention begins with determining the required retention period for each temporal table. 대부분의 경우 보존 정책은 임시 테이블을 사용하는 응용 프로그램의 비즈니스 논리의 일부로 간주해야 합니다.Your retention policy, in most cases, should be considered to be part of the business logic of the application using the temporal tables. 예를 들어 데이터 감사와 시간 이동 시나리오에서 응용 프로그램은 온라인 쿼리에 사용할 수 있는 기록 데이터의 기간 측면에서 확실하게 요구되는 사항이 있습니다.For example, applications in data audit and time travel scenarios have firm requirements in terms of for how long historical data must be available for online querying.

데이터 보존 기간을 결정하고 나면 그 다음으로 기록 데이터 저장 방법과 저장 위치, 그리고 요구되는 보존 기간보다 오래된 기록 데이터를 삭제하는 방법 등 기록 데이터를 관리하기 위한 계획을 개발합니다.Once you determine your data retention period, your next step is to develop a plan for managing historical data how and where you store your historical data and how to delete historical data that is older than your retention requirements. 다음 네 가지 방법으로 임시 기록 테이블에서 기록 데이터를 관리할 수 있습니다.The following four approaches for managing historical data in the temporal history table are available:

  • 스트레치 데이터베이스Stretch Database

  • 테이블 분할Table Partitioning

  • 사용자 지정 정리 스크립트Custom Cleanup Script

  • 보존 정책Retention Policy

    기록 데이터 문제 완화 또는 정리를 위한 논리는 이 방법 중 한 가지를 사용하며 현재 테이블에서 기간 종료에 해당하는 열을 기반으로 합니다.With each of these approaches, the logic for migrating or cleaning history data is based on the column that corresponds to end of period in the current table. 각 행에 대해 기간 값의 끝에서는 행 버전이 "닫힌" 상태가 되는, 즉 기록 테이블에서 해당 값이 처음 삽입되는 순간을 결정합니다.The end of period value for each row determines the moment when the row version becomes “closed”, i.e. when it lands in the history table. 예를 들어 조건 SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) 은1개월보다 오래된 기록 데이터를 기록 테이블에서 제거하거나 제외해야 한다고 지정합니다.For example, the condition SysEndTime < DATEADD (DAYS, -30, SYSUTCDATETIME ()) specifies that historical data older than one month needs to be removed or moved out from the history table.

참고: 이 항목의 예제에서는 이 임시 테이블 예제를 사용합니다.NOTE: The examples in this topic use this Temporal Table example.

스트레치 데이터베이스 접근 방식 사용Using Stretch Database approach

참고: Stretch Database 접근 방식 사용은 SQL Server 2017SQL Server 2017 에만 적용되며 SQL 데이터베이스SQL Database에는 적용되지 않습니다.NOTE: Using the Stretch Database approach only applies to SQL Server 2017SQL Server 2017 and does not apply to SQL 데이터베이스SQL Database.

스트레치 데이터베이스 에서 SQL Server 2017SQL Server 2017 는 Azure로 기록 데이터를 투명하게 마이그레이션합니다.Stretch Database in SQL Server 2017SQL Server 2017 migrates your historical data transparently to Azure. 추가 보안을 위해 SQL Server의 항상 암호화 기능을 사용하여 동작에 대한 데이터를 암호화할 수 있습니다.For additional security, you can encrypt data in motion using SQL Server's Always Encrypted feature. 또한 데이터 보호를 위해 행 수준 보안 및 기타 고급 SQL Server 보안 기능을 임시 및 스트레치 데이터베이스와 함께 사용할 수 있습니다.Additionally, you can use Row-Level Security and other advanced SQL Server security features with Temporal and Stretch Database to protect your data.

스트레치 데이터베이스 접근 방식을 사용하면 기록 데이터 일부 또는 전체를 Azure에 스트레치할 수 있으며 SQL Server에서 Azure로 기록 데이터를 자동으로 옮깁니다.Using the Stretch Database approach, you can stretch some or all of your temporal history tables to Azure and SQL Server will silently move historical data to Azure. 기록 테이블에서 스트레치를 사용하도록 설정한다고 해서 데이터 수정 및 임시 쿼리 측면에서 임시 테이블을 조작하는 방식이 변경되는 것은 아닙니다.Stretch-enabling a history table does not change how you interact with the temporal table in terms of data modification and temporal querying.

  • 전체 기록 테이블 스트레치: 주 시나리오에서 기록 데이터에서 데이터 변경 빈도가 높은 반면 쿼리는 상대적으로 드문 환경에서 데이터 감사를 수행할 경우 전체 기록 테이블에 대해 스트레치 데이터베이스를 구성합니다.Stretch the entire history table: Configure Stretch Database for your entire history table if your main scenario is data audit in the environment with frequent data changes and relatively rare querying on historical data. 즉, 임시 쿼리 작업의 성능이 중요하지 않은 경우 이 방법을 사용합니다.In other words, use this approach if performance of temporal querying is not critical. 이 경우 Azure에서 제공하는 비용 효율성이 매력적일 수 있습니다.In this case, the cost-effectiveness provided by Azure may be compelling.
    전체 기록 테이블을 스트레치할 때 스트레치 마법사 또는 TRANSACT-SQL을 사용할 수 있습니다.When stretching the entire history table, you can either use the Stretch Wizard or Transact-SQL. 두 방법에 대한 예는 아래에 표시됩니다.Examples of both appear below.

  • 일부 기록 테이블 스트레치: 주 시나리오에서 최근 기록 데이터를 주로 쿼리하지만 필요할 경우 이전 기록 데이터를 저렴한 비용으로 원격 저장하면서 쿼리하는 옵션을 유지하려고 할 경우 성능 개선을 위해 기록 테이블의 일부에 대해서만 스트레치 데이터베이스를 구성합니다.Stretch a portion of the history table: Configure Stretch Database for only a portion of your history table to improve performance if your main scenario involves primarily querying recent historical data, but you wish to preserve the option to query older historical data when needed while storing this data remotely at a lower cost. TRANSACT-SQL을 사용하면 전체 행을 마이그레이션하지 않고 기록테이블에서 마이그레이션할 행을 선택할 수 있도록 조건자 함수를 지정하여 이 작업을 수행할 수 있습니다.With Transact-SQL, you can accomplish this by specifying a predicate function to select the rows that will be migrated from the history table rather than migrating all of the rows. 임시 테이블에서 작업 시 일반적으로 시간 조건을 기준으로 데이터를 이동하는 것이 좋습니다(예: 기록 테이블의 행 버전 보존 기간 기준)When you work with temporal tables, it typically makes sense to move data based on time condition (i.e. based on age of the row version in the history table).
    결정적 조건자 함수를 사용하면 현재 데이터가 있는 동일한 데이터베이스에서 기록의 일부를 보관하고 나머지는 Azure로 마이그레이션할 수 있습니다.Using a deterministic predicate function, you can keep portion of history in the same database with the current data, while the rest is migrated to Azure.
    예제 및 제한 사항에 대한 자세한 내용은 필터 함수를 사용하여 마이그레이션할 행 선택(Stretch Database)을 참조하세요.For examples and limitations, see Select rows to migrate by using a filter function (Stretch Database). 비결정적인 함수는 유효하지 않으므로 슬라이딩 윈도우 방식으로 기록 데이터를 전송하려는 경우에는 로컬로 보관할 행의 창이 보관 기간 측면에서 일정하도록 인라인 조건자 함수 정의를 지속적으로 변경해야 합니다.Because non-deterministic functions are not valid, if you want to transfer history data in sliding window manner, you would need to regularly alter definition of the inline predicate function so that window of rows you keep locally is constant in terms of age. 슬라이딩 윈도우를 사용하면 1개월이 지난 기록 데이터를 지속적으로 Azure로 이동할 수 있습니다.Sliding window allows you to constantly move historical data older than one month to Azure. 이 접근 방법의 예는 아래에 표시됩니다.An example of this approach appears below.

참고: Stretch Database는 데이터를 Azure로 마이그레이션합니다.NOTE: Stretch Database migrates data to Azure. 따라서 대금 청구를 위해 Azure 계정 및 구독이 있어야 합니다.Therefore, you have to have an Azure account and a subscription for billing. Azure 무료 평가판 계정을 얻으려면 1개월 무료 평가판을 클릭하세요.To get a free trial Azure account, click Free One-Month Trial.

스트레치 마법사 또는 TRANSACT-SQL을 사용하여 스트레치를 위해 임시 기록 테이블을 구성할 수 있으며 시스템 버전 관리가 ON으로 설정되어 있어도 임시 기록 테이블에 스트레치를 사용하도록 설정할 수 있습니다.You can configure a temporal history table for Stretch using either the Stretch Wizard or Transact-SQL, and you can stretch-enable a temporal history table while system-versioning is set to ON. 현재 테이블을 스트레치하는 것은 의미가 없으므로 허용되지 않습니다.Stretching the current table is not allowed because it does not make sense to stretch the current table.

스트레치 마법사를 사용하여 전체 기록 테이블 스트레치Using the Stretch Wizard to stretch the entire history table

초보자를 위한 가장 쉬운 방법은 스트레치 마법사를 사용하여 전체 데이터베이스에 대해 스트레치를 사용하도록 설정한 다음 스트레치 마법사 내에서 임시 기록 테이블을 선택 하는 것입니다(이 예에서는 다른 빈 데이터베이스에서 Department 테이블을 시스템 버전 관리된 임시 테이블로 구성했다고 가정).The easiest method for beginners is to use the Stretch Wizard to enable stretch for the entire database and then select the temporal history table within the Stretch wizard (this example assumes that you have configured the Department table as a system-versioned temporal table in an otherwise empty database). SQL Server 2016SQL Server 2016에서는 임시 기록 테이블 자체를 마우스 오른쪽 단추로 클릭하고 스트레치를 클릭할 수 없습니다.In SQL Server 2016SQL Server 2016, you cannot right-click the temporal history table itself and click Stretch.

  1. 데이터베이스를 마우스 오른쪽 단추로 클릭하고 태스크, 스트레치를 차례로 클릭하고 사용 을 클릭하여 마법사를 시작합니다.Right-click your database and point to Tasks, point to Stretch, and then click Enable to launch the wizard.

  2. 테이블 선택 창에서 임시 기록 테이블에 대한 확인란을 선택하고 다음을 클릭합니다.In the Select tables window, select the checkbox for the temporal history table and click Next.

    테이블 선택 페이지에서 기록 테이블 선택Selecting the history table on the Select tables page

  3. Azure 구성 창에서 로그인 자격 증명을 제공합니다.In the Configure Azure window, provide your login credentials. Microsoft Azure에 로그인하거나 계정을 등록합니다.Sign in to Microsoft Azure or sign-up for an account. 사용할 구독을 선택하고 Azure 지역을 선택합니다.Select the subscription to use, select the Azure region. 그런 다음 새 서버를 만들거나 기존 서버를 선택합니다.Then either create a new server or select an existing server. 다음을 클릭합니다.Click Next.

    새 Azure 서버 만들기 - Stretch Database 마법사Create new Azure server - Stretch Database wizard

  4. 보안 자격 증명 창에서 원본 SQL Server 데이터베이스 자격 증명을 보호하기 위해 데이터베이스 마스터 키에 대한 암호를 제공하고 다음을 클릭합니다.In the Secure credentials window, provide a password for the database master key to secure your source SQL Server database credential and click Next.

    Stretch Database 마법사의 보안 자격 증명 페이지Secure credentials page of the Stretch Database wizard

  5. 선택 IP 주소 창에서 Azure 서버가 SQL Server와 통신할 수 있도록 SQL Server에 대한 IP 주소 범위를 제공합니다. 방화벽 규칙이 이미 있는 기존 서버를 선택한 경우 여기서 다음을 클릭하기만 하면 기존 방화벽 규칙을 사용할 수 있습니다.In the Select IP address window, provide the IP address range for your SQL Server to let your Azure server communicate with your SQL Server (if you select an existing server for which a firewall rule already exists, simply click Next here to use the existing firewall rule). 다음 을 클릭하고 마침 을 클릭하여 스트레치 데이터베이스를 사용하도록 설정한 다음 임시 기록 테이블을 스트레치합니다.Click Next and then click Finish to enable Stretch Database and stretch the temporal history table.

    Stretch Database 마법사의 IP 주소 선택 페이지Select IP address page of the Stretch Database wizard

  6. 마법사가 완료되면 데이터베이스에 스트레치가 사용하도록 설정되었는지 확인합니다.When the wizard completes, verify that your database was successfully stretch-enabled. 개체 탐색기에서 데이터베이스가 스트레치되었는지 나타내는 아이콘을 살펴봅니다.Notice the icons in Object Explorer indicating the database was stretched

참고: 스트레치에 데이터베이스 사용이 실패하는 경우 오류 로그를 검토하세요.NOTE: If the Enable Database for Stretch fails, review the error log. 일반적으로 방화벽 규칙을 잘못 구성했을 때 오류가 발생합니다.A common error is improperly configuring the firewall rule.

참고 항목:See also:

Transact-SQL을 사용하여 전체 기록 테이블 스트레치Using Transact-SQL to stretch the entire history table

Transact-SQL을 사용하여 로컬 서버에서 스트레치를 사용하도록 설정하고 데이터베이스에 대해 스트레치 데이터베이스를 사용하도록 설정할 수 있습니다.You can also use Transact-SQL to enable Stretch on the local server and Enable Stretch Database for a database. 그런 다음 TRANSACT-SQL을 사용하여 테이블에서 스트레치 데이터베이스를 사용하도록 설정할 수 있습니다.You can then use Transact-SQL to enable Stretch Database on a table. 스트레치 데이터베이스에 대해 이전에 사용하도록 설정된 데이터베이스를 사용하여 기존 시스템 버전 관리된 임시 기록 테이블을 스트레치할 수 있도록 다음 Transact-SQL 스크립트를 실행합니다.With a database previously enabled for Stretch Database, execute the following Transact-SQL script to stretch an existing system-versioned temporal history table:

ALTER TABLE <history table name>   
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND));  

Transact-SQL을 사용하여 일부 기록 테이블 스트레치Using Transact-SQL to stretch a portion of the history table

기록 테이블 중 일부만 스트레치하려면 인라인 조건자 함수를 만들어 시작합니다.To stretch only a portion of the history table, you start by creating an inline predicate function. 이 예에서는 2015년 12월 1일에 처음으로 인라인 조건자 함수를 구성하고 2015년 11월 1일보다 오래된 모든 기록 날짜를 Azure로 스트레치한다고 가정합니다.For this example, let’s assume that you configured inline predicate function for the first time on December 1, 2015 and want to stretch to Azure all history date older than November 1, 2015. 이 작업 수행을 위해 다음 함수를 만들기 시작합니다.To accomplish this, start by creating the following function:

CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151101(@systemEndTime datetime2)   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
RETURN SELECT 1 AS is_eligible   
  WHERE @systemEndTime < CONVERT(datetime2, '2015-11-01T00:00:00', 101) ;  

다음 작업으로, 다음 스크립트를 사용하여 필터 조건자를 기록 테이블에 추가하고 마이그레이션 상태를 OUTBOUND로 설정하여 기록 테이블에 대해 조건자 기반 데이터 마이그레이션을 사용하도록 설정합니다.Next, use the following script to add the filter predicate to the history table and set the migration state to OUTBOUND to enable predicate based data migration for the history table.

ALTER TABLE <history table name>   
SET (   
        REMOTE_DATA_ARCHIVE = ON   
                (   
                        FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151101 (SysEndTime)  
                                , MIGRATION_STATE = OUTBOUND   
                )  
        )   
;  

슬라이딩 윈도우 방식을 유지하려면 조건자 함수를 매일 정확하게 조정해야 합니다(즉, 1일을 기준으로 매일 행 필터링 조건 변경).To maintain a sliding window, you need to make predicate function to be accurate every day (i.e. change filtering row condition every day by one day). 다음 스크립트는 2015년 12월 2일에 실행해야 하는 스크립트입니다.The following script is the script that you would you need to execute on December 2, 2015:

BEGIN TRAN  
           /*(1) Create new predicate function definition */  
        CREATE FUNCTION dbo.fn_StretchBySystemEndTime20151102(@systemEndTime datetime2)  
        RETURNS TABLE  
        WITH SCHEMABINDING   
        AS   
        RETURN SELECT 1 AS is_eligible  
               WHERE @systemEndTime < CONVERT(datetime2,'2015-11-02T00:00:00', 101)  
        GO  

        /*(2) Set the new function as filter predicate */  
        ALTER TABLE <history table name>  
        SET   
        (  
               REMOTE_DATA_ARCHIVE = ON  
               (  
                       FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20151102(SysEndTime),  
                       MIGRATION_STATE = OUTBOUND  
               )  
        )   
COMMIT ;  

SQL Server 에이전트 또는 일부 다른 예약 메커니즘을 사용하여 조건자 함수 정의가 항상 유효한지 확인합니다.Use SQL Server Agent or some other scheduling mechanism to ensure valid predicate function definition all the time.

테이블 분할 접근 방법 사용Using Table Partitioning Approach

테이블 분할 을 사용하면 큰 테이블을 좀 더 관리 및 확장할 수 있습니다.Table partitioning can make large tables more manageable and scalable. 테이블 분할 방법으로 기록 테이블 파티션을 사용하여 시간 조건을 기반으로 오프라인으로 보관하거나 사용자 지정 데이터를 정리할 수 있습니다.Using the table partitioning approach, you can use history table partitions to implement custom data cleanup or offline archival based on a time condition. 또한 테이블 분할을 통해 데이터 기록 하위 집합에서 임시 테이블을 쿼리할 때 파티션 제거를 사용하여 성능이 향상될 수도 있습니다.Table partitioning will also give you performance benefits when querying temporal tables on a subset of data history by using partition elimination.

테이블 분할 기능을 사용하면 슬라이딩 윈도우 접근 방식을 구현하여 필수 보존 기간에 맞게 데이터를 기록 테이블에서 유지 관리하면서도 기록 테이블에서 가장 오래된 기록 데이터 일부를 이동하여 제외시키고, 기간 측면에서 보관 부분의 크기를 지속적으로 유지할 수 있습니다.With table partitioning, you can implement a sliding window approach to move out oldest portion of the historical data from the history table and keep the size of the retained part constant in terms of age - maintaining data in the history table equal to required retention period. SYSTEM_VERSIONING이 ON이더라도 기록 테이블의 데이터를 외부로 전환하는 작업은 지원됩니다. 즉 유지 관리 창을 도입하거나 정기 작업을 차단하지 않고 기록 데이터의 일부를 정리할 수 있습니다.The operation of switching data out from the history table is supported while SYSTEM_VERSIONING is ON, which means that you can clean a portion of the history data without introducing a maintenance windows or blocking your regular workloads.

참고: 파티션 전환 작업을 수행하려면 기록 테이블에서 클러스터형 인덱스를 분할 스키마에 정렬해야 합니다(SysEndTime 포함 필수).NOTE: In order to perform partition switching, your clustered index on history table must be aligned with the partitioning schema (it has to contain SysEndTime). 시스템에서 만든 기본 기록 테이블에는 SysEndTime 및 SysStartTime 열이 포함된 클러스터형 인덱스가 있어 분할, 새 기록 데이터 삽입, 일반적인 임시 쿼리 작업에 적합합니다.The default history table created by the system contains a clustered index that includes the SysEndTime and SysStartTime columns, which is optimal for partitioning, inserting new history data, and typical temporal querying. 자세한 내용은 Temporal Tables을 참조하세요.For more information, see Temporal Tables.

슬라이딩 윈도우 방식에서는 다음 두 가지 작업을 수행해야 합니다.A sliding window approach has two sets of tasks that you need to perform:

  • 분할 구성 작업A partitioning configuration task

  • 반복적인 파티션 유지 관리 작업Recurring partition maintenance tasks

    아래 그림의 경우 6개월 동안에 대한 기록 데이터를 보관하고 월별 데이터를 별도의 파티션으로 유지한다고 가정합니다.For the illustration, let’s assume that we want to keep historical data for 6 months and that we want to keep every month of data in a separate partition. 또한 2015년 9월에 시스템 버전 관리를 활성화했다고 가정해 보겠습니다.Also, let’s assume that we activated system-versioning in September of 2015.

    분할 구성 작업으로 기록 테이블에 대한 초기 분할 구성이 만들어집니다.A partitioning configuration task creates the initial partitioning configuration for the history table. 이 예제의 경우 월별로 슬라이딩 윈도우 크기와 동일한 개수의 파티션과, 미리 준비된 한 개의 추가 빈 파티션(아래에 설명)을 만듭니다.For this example, we would create the same number partitions as the size of sliding window, in months, plus one additional empty partition pre-prepared (explained below). 이 구성을 통해 처음으로 반복적인 파티션 유지 관리 작업을 시작할 때 시스템에서 새 데이터를 제대로 저장할 수 있게 되며, 파티션을 데이터로 분할하지 않아 데이터 이동으로 인한 높은 비용이 발생하지 않습니다.This configuration ensures that the system will be able to store new data correctly when we start the recurring partition maintenance task for the first time and guarantees that we never split partitions with data to avoid expensive data movements. 이 작업은 아래의 예제 스크립트로 TRANSACT-SQL을 사용하여 이 작업을 수행해야 합니다.You should perform this task using Transact-SQL using the example script below.

    다음 그림은 6개월 분량의 데이터 보관을 위한 초기 분할 구성을 보여 줍니다.The following picture shows initial partitioning configuration to keep 6 months of data.

    분할Partitioning

참고: 분할 구성 시 RANGE LEFT와 RANGE RIGHT를 사용할 때 성능에 미치는 영향에 대한 자세한 내용은 아래의 테이블 분할 시 성능 고려 사항을 참조하세요.NOTE: See Performance considerations with table partitioning below for the performance implications of using RANGE LEFT versus RANGE RIGHT when configuring partitioning.

첫 번째 및 마지막 파티션은 각각 위쪽 경계와 아래쪽 경계에 “열린” 상태로 유지됩니다. 이렇게 하면 분할 열 값에 상관없이 모든 새 행에 대상 파티션이 포함됩니다.Note that first and last partition are “open” on lower and upper boundaries respectively to ensure that every new row has destination partition regardless of the value in partitioning column.
시간이 지남에 따라 기록 테이블의 새 행은 더 높은 파티션에 순서대로 삽입됩니다.As time goes by, new rows in history table will land in higher partitions. 6번째 파티션이 채워지면 대상으로 지정된 보존 기간에 도달합니다.When 6th partition gets filled up, we will have reached the targeted retention period. 이 순간에 반복적인 파티션 유지 관리 작업이 처음으로 시작됩니다(이 예에서는 한 달에 한 번 정기적으로 실행하도록 예약해야 합니다).This is the moment to start the recurring partition maintenance task for the first time (it needs to be scheduled to run periodically, once per month in this example).

다음 그림은 반복적인 파티션 유지 관리 작업을 보여 줍니다(자세한 단계는 아래 참조).The following picture illustrates the recurring partition maintenance tasks (see detailed steps below).

Partitioning2Partitioning2

반복적인 파티션 유지 관리 작업 단계는 아래와 같습니다.The detailed steps for the recurring partition maintenance tasks are:

  1. 전환: 준비 테이블을 만든 다음 SWITCH PARTITION 인수를 사용하여 ALTER TABLE(Transact-SQL) 문에서 기록 테이블과 준비 테이블 사이에서 파티션을 전환합니다(예 C. 테이블 간 파티션 전환 참조).SWITCH OUT: Create a staging table and then switch a partition between the history table and the staging table using the ALTER TABLE (Transact-SQL) statement with the SWITCH PARTITION argument (see Example C. Switching partitions between tables).

    ALTER TABLE <history table> SWITCH PARTITION 1 TO <staging table>  
    

    파티션 전환 후에는 준비 테이블에서 데이터를 선택적으로 보관한 다음 준비 테이블을 삭제하거나 잘라 그 다음에 수행할 반복적인 파티션 유지 관리 작업 준비를 할 수 있습니다.After the partition switch, you can optionally archive the data from staging table and then either drop or truncate the staging table to be ready for the next time you need to perform this recurring partition maintenance task.

  2. MERGE RANGE: ALTER PARTITION FUNCTION(Transact-SQL)에서 MERGE RANGE를 사용하여 빈 파티션 1을 파티션 2와 병합합니다(예 B 참조).MERGE RANGE: Merge the empty partition 1 with partition 2 using the ALTER PARTITION FUNCTION (Transact-SQL) with MERGE RANGE (See example B). 이 함수를 사용하여 가장 낮은 경계를 제거함으로써 빈 파티션 1을 기존의 파티션 2와 효율적으로 병합하여 새 파티션 1을 생성합니다.By removing the lowest boundary using this function, you effectively merge the empty partition 1 with the former partition 2 to form new partition 1. 다른 파티션도 효과적으로 서수가 변경됩니다.The other partitions also effectively change their ordinals.

  3. SPLIT RANGE: ALTER PARTITION FUNCTION(Transact-SQL)에서 SPLIT RANGE를 사용하여 새 빈 파티션 7을 만듭니다(예 A 참조).SPLIT RANGE: Create a new empty partition 7 using the ALTER PARTITION FUNCTION (Transact-SQL) with SPLIT RANGE (See example A). 이 함수를 사용하여 새 상한 경계를 추가함으로써 다음 달에 대한 별도의 파티션을 효율적으로 만듭니다.By adding a new upper boundary using this function, you effectively create a separate partition for the upcoming month.

TRANSACT-SQL을 사용하여 기록 테이블에서 파티션 만들기Use Transact-SQL to create partitions on history table

아래 코드 창에서 TRANSACT-SQL 스크립트를 사용하여 파티션 함수, 파티션 스키마를 만들고, 파티션 스키마에 파티션이 정렬되도록 클러스터형 인덱스를 다시 만듭니다.Use the Transact-SQL script in the code window below to create the partition function, the partition schema, and recreate the clustered index to be partition-aligned with the partition schema, partitions. 이 예에서는 2015년 9월부터 시작하는 월별 파티션을 사용하여 6개월의 슬라이딩 윈도우 방식을 만들겠습니다.For this example, we will creating a six-month sliding window approach with monthly partitions beginning September, 2015.

BEGIN TRANSACTION  

        /*Create partition function*/  
        CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime] (datetime2(7))   
                    AS RANGE LEFT FOR VALUES   
                                (N'2015-09-30T23:59:59.999'  
                                , N'2015-10-31T23:59:59.999'  
                                , N'2015-11-30T23:59:59.999'  
                                , N'2015-12-31T23:59:59.999'  
                                , N'2016-01-31T23:59:59.999'  
                                , N'2016-02-29T23:59:59.999')  

        /*Create partition scheme*/  
        CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime]   
                        AS PARTITION [fn_Partition_DepartmentHistory_By_SysEndTime]   
                        TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])  

        /*Re-create index to be partition-aligned with the partitioning schema*/  
        CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory]  
        (  
                    [SysEndTime] ASC,  
                    [SysStartTime] ASC  
        )  
            WITH   
                        (PAD_INDEX = OFF  
                        , STATISTICS_NORECOMPUTE = OFF  
                        , SORT_IN_TEMPDB = OFF  
                        , DROP_EXISTING = ON  
                        , ONLINE = OFF  
                        , ALLOW_ROW_LOCKS = ON  
                        , ALLOW_PAGE_LOCKS = ON  
                        , DATA_COMPRESSION = PAGE)  
            ON [sch_Partition_DepartmentHistory_By_SysEndTime] ([SysEndTime])  

COMMIT TRANSACTION;  

Transact-SQL을 사용하여 슬라이딩 윈도우 시나리오에서 파티션 유지 관리Using Transact-SQL to maintain partitions in sliding window scenario

아래 코드 창에서 TRANSACT-SQL 스크립트를 사용하여 슬라이딩 윈도우 시나리오에서 파티션을 유지 관리합니다.Use the Transact-SQL script in the code window below to maintain partitions in the sliding window scenario. 이 예에서는 MERGE RANGE를 사용하여 2015년 9월에 해당하는 파티션을 외부로 전환한 다음 SPLIT RANGE를 사용하여 2016년 3월에 해당하는 새 파티션을 추가합니다.For this example, we will switch out the partition for September of 2015 using MERGE RANGE and then add a new partition for March of 2016 using SPLIT RANGE.

BEGIN TRANSACTION  

         /*(1)  Create staging table */  
         CREATE TABLE [dbo].[staging_DepartmentHistory_September_2015]  
        (  
                 [DeptID] [int] NOT NULL  
                 , [DeptName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
                 , [ManagerID] [int] NULL  
                 ,  [ParentDeptID] [int] NULL  
                 ,  [SysStartTime] [datetime2](7) NOT NULL  
                 ,  [SysEndTime] [datetime2](7) NOT NULL  
         ) ON [PRIMARY]  
         WITH  
         (  
              DATA_COMPRESSION = PAGE  
         )  

         /*(2) Create index on the same filegroups as the partition that will be switched out*/  
         CREATE CLUSTERED INDEX [ox_staging_DepartmentHistory_September_2015]    
         ON [dbo].[staging_DepartmentHistory_September_2015]  
         (  
                  [SysEndTime] ASC,  
                  [SysStartTime] ASC  
         )  
      WITH   
          (  
               PAD_INDEX = OFF  
               , SORT_IN_TEMPDB = OFF  
               , DROP_EXISTING = OFF  
               , ONLINE = OFF  
               , ALLOW_ROW_LOCKS = ON  
               , ALLOW_PAGE_LOCKS = ON  
          )   
         ON [PRIMARY]  

         /*(3) Create constraints matching the partition that will be switched out*/  
         ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]  WITH CHECK   
               ADD  CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]   
                    CHECK  ([SysEndTime]<=N'2015-09-30T23:59:59.999')  
         ALTER TABLE [dbo].[staging_DepartmentHistory_September_2015]   
               CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2015_partition_1]  

         /*(4) Switch partition to staging table*/  
         ALTER TABLE [dbo].[DepartmentHistory]   
         SWITCH PARTITION 1 TO [dbo].[staging_DepartmentHistory_September_2015]   
         WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))  

         /*(5) [Commented out] Optionally archive the data and drop staging table  
         INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]   
         SELECT * FROM [dbo].[staging_DepartmentHistory_September_2015];  
         DROP TABLE [dbo].[staging_DepartmentHIstory_September_2015];  
         */  

         /*(6) merge range to move lower boundary one month ahead*/  
         ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]()   
               MERGE RANGE(N'2015-09-30T23:59:59.999')  

         /*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/  
         ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_SysEndTime] NEXT USED [PRIMARY]  
         ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_SysEndTime]() SPLIT RANGE(N'2016-03-31T23:59:59.999')  

COMMIT TRANSACTION  

위의 스크립트를 약간만 수정하여 다음과 같이 정기적인 월별 유지 관리 프로세스에서 사용할 수 있습니다.You can slightly modify script above and use it in regular monthly maintenance process:

  1. 단계 (1)에서는 제거할 달에 대한 새 준비 테이블을 만듭니다(위 예에서 10월이 그 다음 달에 해당)In step (1) create new staging table for the month you want to remove (October would be next one in our example)

  2. 단계 (3)에서는 제거할 데이터의 월에 일치하는 제약 조건을 만들고 검사합니다(10월 파티션에 대해 [SysEndTime]<=N'2015-10-31T23:59:59.999' ).In step (3) create and check constraint that matches the month of data you want to remove: [SysEndTime]<=N'2015-10-31T23:59:59.999' for October partition

  3. 단계 (4)에서는 파티션 1을 새로 만든 준비 테이블로 전환합니다.In step (4) SWITCH partition 1 to newly created staging table

  4. 단계 (6)에서는 하한 경계를 병합하여 파티션 함수를 변경합니다(10월에 대한 데이터를 이동하여 제외한 후 MERGE RANGE(N'2015-10-31T23:59:59.999' ).In step (6) alter partition function by merging lower boundary: MERGE RANGE(N'2015-10-31T23:59:59.999' after you moved out data for October

  5. 단계 (7)에서는 새 상한 경계를 만드는 파티션 함수를 분리합니다(10월에 대한 데이터를 이동하여 제외한 후 SPLIT RANGE (N'2016-04-30T23:59:59.999' ).In step (7) split partition function creating new upper boundary: SPLIT RANGE (N'2016-04-30T23:59:59.999' after you moved out data for October.

    그러나 최적의 솔루션은 스크립트를 수정하지 않고 매월 적절한 작업을 수행할 수 있도록 일반 TRANSACT-SQL 스크립트를 정기적으로 실행하는 것입니다.However, the optimal solution would be to regularly run a generic Transact-SQL script that is a capable of performing the appropriate action every month without script modification. 제공된 매개 변수(병합해야 할 하한 경계와 파티션 분할로 만들어지는 새 경계)로 동작을 수행하도록 위 스크립트를 일반화할 수도 있습니다.It is possible to generalize the script above to act upon provided parameters (lower boundary that needs to be merged and new boundary that will be created by with partition split). 매달 준비 테이블 생성을 방지하기 위해 사전에 미리 테이블을 만들고 전환할 파티션과 일치하도록 확인 제약 조건을 변경하여 다시 사용할 수 있습니다. 다음 페이지를 살펴보고 TRANSACT-SQL 스크립트를 사용하여 슬라이딩 윈도우를 완전히 자동화할 수 있는 방법 에 대한 아이디어를 얻어보세요.In order to avoid staging table creation every month, you can create one beforehand and reuse by changing check constraint to match partition that will be switched out. Take a look at the following pages to get ideas on how sliding window can be fully automated using a Transact-SQL script.

테이블 분할 시 성능 고려 사항Performance considerations with table partitioning

데이터 이동 시 발생되는 성능 오버헤드가 상당할 수 있으므로 데이터 이동을 방지하도록 범위 병합 및 범위 분할 작업을 수행하는 것이 중요합니다.It is important to perform the MERGE and SPLIT RANGE operations to avoid any data movement as data movement can incur significant performance overhead. 자세한 내용은 파티션 함수 수정을 참조하세요. 이 작업은 CREATE PARTITION FUNCTION(Transact-SQL) 작업 시 RANGE RIGHT보다는 RANGE LEFT를 사용하여 수행합니다.For more information, see Modify a Partition Function.You accomplish this by using RANGE LEFT rather than RANGE RIGHT when you CREATE PARTITION FUNCTION (Transact-SQL).

먼저 시각적으로 RANGE LEFT 및 RANGE RIGHT 옵션의 의미에 대해 시각적으로 설명해 보겠습니다.Let’s first visually explain meaning of the RANGE LEFT and RANGE RIGHT options:

Partitioning3Partitioning3

파티션 함수를 RANGE LEFT로 정의하면 지정된 값은 파티션 상한이 됩니다.When you define a partition function as RANGE LEFT, the specified values are the upper boundaries of the partitions. RANGE RIGHT를 사용하면 지정된 값은 파티션 하한이 됩니다.When you use RANGE RIGHT, the specified values are the lower boundaries of the partitions. MERGE RANGE를 사용하여 파티션 정의 범위에서 경계를 제거할 경우 기본적으로 경계가 포함된 파티션도 제거되도록 구현됩니다.When you use the MERGE RANGE operation to remove a boundary from the partition function definition, the underlying implementation also removes the partition which contains the boundary. 해당 파티션이 비어 있지 않으면 데이터는 MERGE RANGE 작업의 결과로 생성되는 파티션으로 이동합니다.If that partition is not empty, data will be moved to the partition that is result of MERGE RANGE operation.

슬라이딩 윈도우 시나리오에서는 항상 가장 낮은 파티션 경계를 제거합니다.In sliding window scenario, we always remove lowest partition boundary.

  • RANGE LEFT 사례: RANGE LEFT의 경우, 가장 낮은 파티션 경계가 파티션 전환 후 비어 있는 파티션 1에 속하여 MERGE RANGE로 데이터 이동이 발생되지 않습니다.RANGE LEFT case: In RANGE LEFT case, the lowest partition boundary belongs to partition 1, which is empty (after partition switch out), so MERGE RANGE won’t incur any data movement.

  • RANGE RIGHT 사례: RANGE RIGHT의 경우, 전환을 통해 파티션 1이 비었다고 가정하므로 가장 낮은 파티션 경계가 비어 있지 않은 파티션 2에 속합니다. 이 경우 MERGE RANGE에서 데이터 이동이 발생합니다. 즉 파티션 2의 데이터가 파티션 1로 이동합니다.RANGE RIGHT case: In RANGE RIGHT case, the lowest partition boundary belongs to partition 2, which is not empty as we assumed that partition 1 was emptied by switch out. In this case MERGE RANGE will incur data movement (data from partition 2 will be moved to partition 1). 이 문제를 방지하려면 슬라이딩 윈도우 시나리오에서 RANGE RIGHT에는 항상 빈 상태인 파티션 1이 있어야 합니다.To avoid this, RANGE RIGHT in the sliding window scenario needs to have partition 1, which is always empty. 다시 말하면 RANGE RIGHT를 사용할 경우 RANGE LEFT 경우와 비교되는 추가 파티션을 하나 만들어 유지 관리해야 합니다.This means that if we use RANGE RIGHT, we should create and maintain one additional partition compared to RANGE LEFT case.

    결론: 슬라이딩 파티션에서 RANGE LEFT를 사용하면 파티션 관리가 훨씬 간단해지고 데이터 이동이 발생하지 않습니다.Conclusion: Using RANGE LEFT in sliding partition is much simpler for the partition management and avoids data movement. 하지만 RANGE RIGHT를 사용하여 파티션 경계를 정의하는 것이 datetime 시간 틱 문제를 처리하지 않아도 되므로 좀 더 간단합니다.However, defining partition boundaries with RANGE RIGHT is slightly simpler as you don't have to deal with datetime time tick issues.

사용자 지정 정리 스크립트 방식 사용Using Custom Cleanup Script Approach

스트레치 데이터베이스 및 테이블 분할 방식이 실행 가능한 옵션이 아닐 경우 세 번째로 사용자 지정 정리 스크립트를 사용하여 기록 데이터에서 데이터를 삭제하는 방법이 있습니다.In cases when the Stretch Database and table partitioning approached are not viable options, the third approach is to delete the data from history table using the custom cleanup script. SYSTEM_VERSIONING = OFF일 경우에만 기록 테이블에서 데이터를 삭제할 수 있습니다.Deleting data from history table is possible only when SYSTEM_VERSIONING = OFF. 데이터 불일치를 방지하기 위해서는 유지 관리 창(데이터 수정 작업이 활성 상태가 아닌 경우) 또는 트랜잭션(효율적으로 다른 작업 차단) 내에서 정리를 수행해야 합니다.In order to avoid data inconsistency, perform cleanup either during the maintenance window (when workloads that modify data are not active) or within a transaction (effectively blocking other workloads). 이 작업을 수행하려면 현재 및 기록 테이블에 CONTROL 권한이 있어야 합니다.This operation requires CONTROL permission on current and history tables.

일반 응용 프로그램 및 사용자 쿼리를 최소한으로 차단하려면 트랜잭션 내에서 정리 스크립트를 수행할 때 지연을 두고 더 작은 청크로 데이터를 삭제합니다.To minimally block regular applications and user queries, delete data in smaller chunks with a delay when performing the cleanup script inside a transaction. 모든 시나리오에서 삭제할 각 데이터 청크에 적합한 크기는 없지만 단일 트랜잭션에서 10,000개 이상의 행을 삭제하면 상당한 영향을 미칠 수 있습니다.While there is no optimal size of for each data chunk to be deleted for all scenarios, deleting more than 10,000 rows in a single transaction may impose a significant impact.

정리 논리는 모든 임시 테이블에서 동일합니다. 따라서 데이터 기록을 제한하려는 모든 임시 테이블에 대해 정기적인 실행이 예약된 일반 저장 프로시저를 통해 비교적 쉽게 자동화할 수 있습니다.The cleanup logic is the same for every temporal table, so it can be automated relatively easily through a generic stored procedure that you schedule to run periodically for every temporal table for which you want to limit data history.

다음 다이어그램에서는 실행 중인 작업에 미치는 영향을 줄이기 위해 단일 테이블에 대해 정리 논리를 구성하는 방식을 보여 줍니다.The following diagram illustrates how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

CustomCleanUpScriptDiagramCustomCleanUpScriptDiagram

다음은 프로세스의 구현을 위한 높은 수준의 몇 가지 지침입니다.Here are some high-level guidelines for implementing the process. 정리 논리를 매일 실행하고 데이터 정리에 필요한 모든 임시 테이블에서 반복될 수 있게 예약합니다.Schedule cleanup logic to run every day and iterate over all temporal tables that need data cleanup. SQL Server 에이전트 또는 다른 도구를 사용하여 다음 프로세스를 예약합니다.Use SQL Server Agent or different tool to schedule this process:

  • 가장 오래된 행부터 시작하여 가장 최신 행까지 작은 청크로 여러 번 반복을 거쳐 모든 임시 테이블의 기록 데이터를 삭제하고, 위 그림에서와 같이 단일 트랜잭션의 모든 행을 삭제하지 마세요.Delete historical data in every temporal table starting from the oldest to the most recent rows in several iterations in small chunks and avoid deleting all rows in a single transaction as shown on picture above.

  • 모든 반복은 기록 테이블에서 일부 데이터를 제거하는 일반 저장 프로시저를 호출하여 구현합니다(이 프로시저에 대한 내용은 아래 코드 예제 참조).Implement every iteration as an invocation of generic stored procedure that removes a portion of data from the history table (see code example below for this procedure).

  • 프로세스를 호출할 때마다 개별 임시 테이블에 대해 삭제해야 하는 행 수를 계산합니다.Calculate how many rows you need to delete for an individual temporal table every time you invoke the process. 행 수와 호출 수에 따라 모든 프로시저 호출에 대한 분할 지점을 동적으로 결정합니다.Based on that and number of number of iterations you want to have determine dynamically split points for every procedure invocation.

  • 임시 테이블에 액세스하는 응용 프로그램에 미치는 영향을 줄이기 위해 단일 테이블에 대한 반복 간 지연 기간을 설정하도록 계획합니다.Plan to have a period of delay between iterations for a single table to reduce impact on applications that access the temporal table.

    단일 임시 테이블에 대한 데이터를 삭제하는 저장 프로시저는 다음 코드 조각과 유사하게 표현될 수 있습니다(환경에 적용하기 전에 이 코드를 주의 깊게 검토한 후 조정 필요).A stored procedure that deletes the data for a single temporal table might look like in the following code snippet (review this code carefully and adjust it before apply in your environment):

DROP PROCEDURE IF EXISTS sp_CleanupHistoryData;  
GO  

CREATE PROCEDURE sp_CleanupHistoryData  
         @temporalTableSchema sysname  
       , @temporalTableName sysname  
       , @cleanupOlderThanDate datetime2  
AS  
    DECLARE @disableVersioningScript nvarchar(max) = '';  
    DECLARE @deleteHistoryDataScript nvarchar(max) = '';  
    DECLARE @enableVersioningScript nvarchar(max) = '';  

DECLARE @historyTableName sysname    
DECLARE @historyTableSchema sysname    
DECLARE @periodColumnName sysname    

/*Generate script to discover history table name and end of period column for given temporal table name*/  
EXECUTE sp_executesql   
    N'SELECT @hst_tbl_nm = t2.name, @hst_sch_nm = s.name, @period_col_nm = c.name  
        FROM sys.tables t1   
           JOIN sys.tables t2 on t1.history_table_id = t2.object_id  
        JOIN sys.schemas s on t2.schema_id = s.schema_id  
            JOIN sys.periods p on p.object_id = t1.object_id  
           JOIN sys.columns c on p.end_column_id = c.column_id and c.object_id = t1.object_id  
                  WHERE   
                 t1.name = @tblName and s.name = @schName'  
                , N'@tblName sysname  
                , @schName sysname  
                , @hst_tbl_nm sysname OUTPUT  
                , @hst_sch_nm sysname OUTPUT  
                , @period_col_nm sysname OUTPUT'  
                , @tblName = @temporalTableName  
                , @schName = @temporalTableSchema  
                , @hst_tbl_nm = @historyTableName OUTPUT  
                , @hst_sch_nm = @historyTableSchema OUTPUT  
                , @period_col_nm = @periodColumnName OUTPUT   

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL  
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1  

/*Generate 3 statements that will run inside a transaction: SET SYSTEM_VERSIONING = OFF, DELETE FROM history_table, SET SYSTEM_VERSIONING = ON */  
SET @disableVersioningScript =  @disableVersioningScript + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + '] SET (SYSTEM_VERSIONING = OFF)'  
SET @deleteHistoryDataScript =  @deleteHistoryDataScript + ' DELETE FROM  [' + @historyTableSchema + '].[' + @historyTableName + ']   
     WHERE ['+ @periodColumnName + '] < ' + '''' + convert(varchar(128), @cleanupOlderThanDate, 126) +  ''''   
SET @enableVersioningScript =  @enableVersioningScript + ' ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName + ']   
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '   

BEGIN TRAN  
    EXEC (@disableVersioningScript);  
    EXEC (@deleteHistoryDataScript);  
    EXEC (@enableVersioningScript);  
COMMIT;  

임시 기록 보존 정책 접근 방식 사용Using Temporal History Retention Policy Approach

참고: Temporal 기록 보존 정책 접근 방식 사용은 SQL 데이터베이스SQL Database 및 SQL Server 2017 CTP 1.3 이상에 적용됩니다.NOTE: Using the Temporal History Retention Policy approach applies to SQL 데이터베이스SQL Database and SQL Server 2017 starting from CTP 1.3.

Temporal 기록 보존은 개별 테이블 수준에서 구성할 수 있으므로 사용자가 유연한 에이징 정책을 만들 수 있습니다.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. Temporal 보존 적용은 간단해서, 테이블을 만들거나 스키마를 변경할 때 매개 변수 하나만 설정하면 됩니다.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

보존 정책을 정의한 후부터 Azure SQL Database는 자동 데이터 정리를 사용할 수 있는 기록 행이 있는지 정기적으로 확인합니다.After you define retention policy, Azure SQL Database starts checking regularly if there are historical rows that are eligible for automatic data cleanup. 일치하는 행을 식별하고 기록 테이블에서 제거하는 작업은 시스템에서 예약하고 실행하는 백그라운드 태스크로 투명하게 수행됩니다.Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. 기록 테이블 행의 기간 조건은 SYSTEM_TIME 기간의 종료를 나타내는 열을 기준으로 확인됩니다.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. 예를 들어 보존 기간이 6개월로 설정된 경우 정리할 수 있는 테이블 행은 다음 조건을 만족합니다.If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

앞의 예제에서는 ValidTo 열이 SYSTEM_TIME 기간의 종료에 해당한다고 가정했습니다.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

보존 정책을 구성하는 방법How to configure retention policy?

Temporal 테이블의 보존 정책을 구성하려면 먼저 데이터베이스 수준에서 temporal 기록 보존이 사용하도록 설정되어 있는지 확인해야 합니다.Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

데이터베이스 플래그 is_temporal_history_retention_enabled는 기본적으로 ON으로 설정되어 있지만 사용자가 ALTER DATABASE 문을 사용하여 변경할 수 있습니다.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. 특정 시점 복원 작업 후에도 자동으로 OFF로 설정됩니다.It is also automatically set to OFF after point in time restore operation. 데이터베이스에 대한 temporal 기록 보존 정리를 사용하도록 설정하려면 다음 문을 실행합니다.To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

보존 정책은 테이블을 만들 때 HISTORY_RETENTION_PERIOD 매개 변수 값을 지정하여 구성합니다.Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

CREATE TABLE dbo.WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH
 (
     SYSTEM_VERSIONING = ON
     (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
     )
 );

DAYS, WEEKS, MONTHS 및 YEARS와 같은 여러 시간 단위를 사용하여 보존 기간을 지정할 수 있습니다.You can specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. HISTORY_RETENTION_PERIOD를 생략하면 INFINITE 보존으로 간주됩니다.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. INFINITE 키워드를 명시적으로 사용할 수도 있습니다.You can also use INFINITE keyword explicitly. 일부 시나리오에서는 테이블을 만든 후 보존을 구성할 수도 있고 이전에 구성한 값을 변경할 수도 있습니다.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. 이런 경우에 ALTER TABLE 문을 사용합니다.In that case use ALTER TABLE statement:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

보존 정책의 현재 상태를 살펴보려면 데이터베이스 수준에서 temporal 보존 사용 플래그를 개별 테이블의 보존 기간과 조인하는 다음 쿼리를 사용합니다.To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

SELECT DB.is_temporal_history_retention_enabled,
SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
T1.name as TemporalTableName,  SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
T2.name as HistoryTableName,T1.history_retention_period,
T1.history_retention_period_unit_desc
FROM sys.tables T1  
OUTER APPLY (select is_temporal_history_retention_enabled from sys.databases
where name = DB_NAME()) AS DB
LEFT JOIN sys.tables T2   
ON T1.history_table_id = T2.object_id WHERE T1.temporal_type = 2

SQL Database에서 오래된 행을 삭제하는 방법How SQL Database deletes aged rows?

정리 프로세스는 기록 테이블의 인덱스 레이아웃에 따라 달라집니다.The cleanup process depends on the index layout of the history table. 클러스터형 인덱스(B-트리 또는 columnstore)를 사용하는 기록 테이블에만 유한 보존 정책을 구성할 수 있다는 점을 기억해야 합니다.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. 보존 기간이 한정된 모든 temporal 테이블에 대한 오래된 데이터 정리를 수행하는 백그라운드 태스크가 만들어집니다.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. rowstore(B-트리) 클러스터형 인덱스에 대한 정리 논리에서는 더 작은 청크(최대 10K)의 오래된 행을 삭제하여 데이터베이스 로그 및 I/O 하위 시스템에 주는 부담을 최소화합니다.Cleanup logic for the rowstore (B-tree) clustered index deletes aged rows in smaller chunks (up to 10K) minimizing pressure on database log and I/O subsystem. 정리 논리에서 필요한 B-트리 인덱스를 활용하긴 하지만 보존 기간보다 오래된 행의 삭제 순서를 확실히 보장할 수는 없습니다.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. 따라서 응용 프로그램의 정리 순서를 신뢰하지 마세요.Hence, do not take any dependency on the cleanup order in your applications.

클러스터형 columnstore에 대한 정리 태스크는 전체 행 그룹을 한 번에 제거하므로(일반적으로 각 그룹에 1백만 개 행 포함) 매우 효율적이며, 기록 데이터가 빠른 속도로 생성되는 경우 특히 효율적입니다.The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

클러스터형 columnstore 보존Clustered columnstore retention

클러스터형 columnstore 인덱스는 데이터 압축이 뛰어나고 보존 정리가 효율적이므로 작업에서 대량의 기록 데이터를 빠르게 생성하는 시나리오에 적합합니다.Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. 이런 패턴은 변경 내용 추적 및 감사, 추세 분석 또는 IoT 데이터 수집에 temporal 테이블을 사용하는 집약적 트랜잭션 처리 작업에서 일반적으로 나타납니다.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

자세한 내용은 보존 정책을 사용하여 임시 테이블에서 기록 데이터 관리를 참조하세요.Please check Manage historical data in Temporal Tables with retention policy for more details.

관련 항목:See Also

임시 테이블 Temporal Tables
시스템 버전 관리 임시 테이블 시작 Getting Started with System-Versioned Temporal Tables
임시 테이블 시스템 일관성 검사 Temporal Table System Consistency Checks
임시 테이블을 사용하여 분할 Partitioning with Temporal Tables
임시 테이블 고려 사항 및 제한 사항 Temporal Table Considerations and Limitations
임시 테이블 보안 Temporal Table Security
메모리 액세스에 최적화된 테이블을 포함한 시스템 버전 임시 테이블 System-Versioned Temporal Tables with Memory-Optimized Tables
임시 테이블 메타데이터 뷰 및 함수Temporal Table Metadata Views and Functions