![]() ![]() The specified role can be an existing fixed server role or a database role. ![]() The purpose of the named role is to control access to the change data. Enable a Table Specifying Filegroup Option TemplateĪ role for controlling access to a change table. See the Enable a Table Specifying Filegroup Option template for an example showing use of the parameter. Generally, it is recommended that change tables be placed in a filegroup separate from source tables. Database owners who want to control the placement of individual change tables can use the parameter to specify a particular filegroup for the change table associated with the capture instance. If only a subset of columns needs to be tracked, such as for privacy or performance reasons, use the parameter to specify the subset of columns.īy default, the change table is located in the default filegroup of the database. The following options can be specified when creating a capture instance:Ĭolumns in the source table to be captured.īy default, all of the columns in the source table are identified as captured columns. To determine whether a source table has already been enabled for change data capture, examine the is_tracked_by_cdc column in the sys.tables catalog view. Disable Database for change data capture templateĪfter a database has been enabled for change data capture, members of the db_owner fixed database role can create a capture instance for individual source tables by using the stored procedure sys.sp_cdc_enable_table. There is also a Template Explorer icon on the SQL Server Management Studio toolbar. ![]() change data capture is a sub-folder where you will find all the templates that are referenced in this topic. To locate the templates in SQL Server Management Studio, go to View, click Template Explorer, and then click SQL Server Templates. See the Disable Database for change data capture template for an example of disabling a database. If a change data capture enabled database is dropped, change data capture jobs are automatically removed. To determine if a database is enabled, query the is_cdc_enabled column in the sys.databases catalog view. However, any gating roles created by change data capture will not be removed automatically and must be explicitly deleted. Disabling the database removes all associated change data capture metadata, including the cdc user and schema and the change data capture jobs. It is not necessary to disable individual tables before you disable the database. Use sys.sp_cdc_disable_db (Transact-SQL) in the database context to disable change data capture for a database. Under this folder, you will find all the templates referenced in this topic. To locate the templates in SQL Server Management Studio, go to View, click Template Explorer, and then select SQL Server Templates. See the Enable Database for change data capture template for an example of enabling a database. If either a schema or a database user named cdc currently exists in a database, the database cannot be enabled for change data capture until the schema and or user are dropped or renamed. The cdc schema also contains associated system functions used to query for change data.Ĭhange data capture requires exclusive use of the cdc schema and cdc user. The cdc schema contains the change data capture metadata tables and, after source tables are enabled for change data capture, the individual change tables serve as a repository for change data. When a database is enabled for change data capture, the cdc schema, cdc user, metadata tables, and other system objects are created for the database. To determine if a database is already enabled, query the is_cdc_enabled column in the sys.databases catalog view. To enable change data capture, run the stored procedure sys.sp_cdc_enable_db (Transact-SQL) in the database context. Enable for a databaseīefore you can create a capture instance for individual tables, you must enable the database for change data capture. Members of these roles have full access to the data in the changed tables for the specified products. ![]() The permissions required to enable or disable change data capture depend on the product. This topic describes how to enable and disable change data capture for a database and a table. Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |