Репликация всех представлений, процессов и функций в SQL Server 2008

Если бы я должен был сделать это, я бы сделал это так:

1. Создайте триггер DDL или уведомление о событии для типов объектов, схему которых вы хотите реплицировать. В любом случае процесс поместит сообщение в очередь брокера служб.
2. Создайте процедуру, которая будет извлекать сообщение из очереди, и вызовите процедуры sp_addarticle и sp_addsubscription с соответствующими параметрами (не так уж плохо, так как это просто статьи на основе схемы).
3. Сделайте процедуру процедурой активации для очереди брокера
4. По расписанию вызов агента моментальных снимков. Убедитесь, что публикация не настроена на немедленную синхронизацию, иначе будет создан моментальный снимок всех статей. Вам нужны только новые.

Насколько мне известно, не существует репликации, основанной на правилах.

Вы должны «закодировать» каждую таблицу.

Когда вы пройдете через графический интерфейс, он даст вам возможность «Создать сценарий».

Вы можете сгенерировать скрипт. И использовать его как основу. Но вам все равно придется настроить его (например, добавить новые таблицы, объекты и т. д.) в сценарий.

Репликация должна выполняться через tsql-скрипты, ИМХО, потому что слишком много параметров, которые можно забыть установить.

Вот пример:

use [AdventureWorks]
 exec sp_addarticle @publication = N'AdventureWorksPublication2', @article =
 N'Address', @source_owner = N'Person', @source_object = N'Address', @type =
 N'logbased', @description = null, @creation_script = null, @pre_creation_cmd
 = N'drop', @schema_option = 0x000000000803589F,
 @identityrangemanagementoption = N'manual', @destination_table = N'Address',
 @destination_owner = N'Person', @vertical_partition = N'true', @ins_cmd =
 N'CALL sp_MSins_PersonAddress', @del_cmd = N'CALL sp_MSdel_PersonAddress',
 @upd_cmd = N'SCALL sp_MSupd_PersonAddress'

 -- Adding the article's partition column(s)
 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'AddressID', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1


 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'AddressLine1', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'AddressLine2', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'City', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'StateProvinceID', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'PostalCode', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
 exec sp_articlecolumn @publication = N'AdventureWorksPublication2', @article
 = N'Address', @column = N'rowguid', @operation = N'add',
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

 -- Adding the article synchronization object
 exec sp_articleview @publication = N'AdventureWorksPublication2', @article =
 N'Address', @view_name = N'SYNC_Address_1__64', @filter_clause = null,
 @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
 GO

http://msdn.microsoft.com/en-us/library/ms173857.aspx

Теперь вы можете написать код, который пишет код.

declare @publicationName varchar(64)
select @publicationName = 'AdventureWorksPublication2'

select 'exec sp_articlecolumn @publication = N' + char(39) + 'AdventureWorksPublication2' + char(39) + ', @article = N' + char(39) + TABLE_NAME + char(39) + ', @column = N' + char(39) + COLUMN_NAME + char(39) + ', @operation = N' + char(39) + 'add' + char(39) + ', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1'
from INFORMATION_SCHEMA.COLUMNS IC 
where TABLE_NAME = 'Address' 

Обратите внимание, это образец. Он получает столбцы одной таблицы.

Если/когда вы выполняете «скрипт», вам нужно обратить внимание на SchemaOptionValue Вот «проверка», которую я написал в свое время. (Вы даете ему значение, это даст вам небольшой отчет)

Это причина НОМЕР ОДИН (ИМХО) для написания сценария репликации. Есть ТАК много вариантов, я не думаю, что вы никогда не сделаете одно и то же дважды по памяти.

--------------START TSQL
 set nocount on

 declare @CurrentOptionCompareValue int

 declare @SchemaOptionValue int

 select @SchemaOptionValue = 0x000000000803589F --<<Substitute your value
 here







 select @CurrentOptionCompareValue = 0x00

 print 'Disables scripting by the Snapshot Agent and uses creation_script. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x01

 print 'Generates the object creation script (CREATE TABLE, CREATE PROCEDURE,
 and so on). This value is the default for stored procedure articles. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x02

 print 'Generates the stored procedures that propagate changes for the
 article, if defined. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x04

 print 'Identity columns are scripted using the IDENTITY property. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x08

 print 'Replicate timestamp columns. If not set, timestamp columns are
 replicated as binary. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x10

 print 'Generates a corresponding clustered index. Even if this option is not
 set, indexes related to primary keys and unique constraints are generated if
 they are already defined on a published table. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x20

 print 'Converts user-defined data types (UDT) to base data types at the
 Subscriber. This option cannot be used when there is a CHECK or DEFAULT
 constraint on a UDT column, if a UDT column is part of the primary key, or
 if a computed column references a UDT column. Not supported for Oracle
 Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x40

 print 'Generates corresponding nonclustered indexes. Even if this option is
 not set, indexes related to primary keys and unique constraints are
 generated if they are already defined on a published table. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x80

 print 'Replicates primary key constraints. Any indexes related to the
 constraint are also replicated, even if options select
 @CurrentOptionCompareValue = 0x10 and select @CurrentOptionCompareValue =
 0x40 are not enabled. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x100

 print 'Replicates user triggers on a table article, if defined. Not
 supported for Oracle Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x200

 print 'Replicates foreign key constraints. If the referenced table is not
 part of a publication, all foreign key constraints on a published table are
 not replicated. Not supported for Oracle Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x400

 print 'Replicates check constraints. Not supported for Oracle Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x800

 print 'Replicates defaults. Not supported for Oracle Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x1000

 -- Replicates column-level collation.

 print 'Note: This option should be set for Oracle Publishers to enable
 case-sensitive comparisons. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''






 select @CurrentOptionCompareValue = 0x2000

 print 'Replicates extended properties associated with the published article
 source object. Not supported for Oracle Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x4000

 print 'Replicates UNIQUE constraints. Any indexes related to the constraint
 are also replicated, even if options select @CurrentOptionCompareValue =
 0x10 and select @CurrentOptionCompareValue = 0x40 are not enabled. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x8000

 print 'This option is not valid for SQL Server 2005 Publishers. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x10000

 print 'Replicates CHECK constraints as NOT FOR REPLICATION so that the
 constraints are not enforced during synchronization. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x20000

 print 'Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so that the
 constraints are not enforced during synchronization. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x40000

 print 'Replicates filegroups associated with a partitioned table or index. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x80000

 print 'Replicates the partition scheme for a partitioned table. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x100000

 print 'Replicates the partition scheme for a partitioned index. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x200000

 print 'Replicates table statistics. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x400000

 print 'Default Bindings '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x800000

 print 'Rule Bindings '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x1000000

 print 'Full-text index '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x2000000

 print 'XML schema collections bound to xml columns are not replicated. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x4000000

 print 'Replicates indexes on xml columns. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x8000000

 print 'Create any schemas not already present on the subscriber. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x10000000

 print 'Converts xml columns to ntext on the Subscriber. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x20000000

 print 'Converts large object data types (nvarchar(max), varchar(max), and
 varbinary(max)) introduced in SQL Server 2005 to data types that are
 supported on SQL Server 2000. For information about how these types are
 mapped, see the "Mapping New Data Types for Earlier Versions" section in
 Using Multiple Versions of SQL Server in a Replication Topology. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x40000000

 print 'Replicate permissions. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x80000000

 print 'Attempt to drop dependencies to any objects that are not part of the
 publication. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x100000000

 print 'Use this option to replicate the FILESTREAM attribute if it is
 specified on varbinary(max) columns. Do not specify this option if you are
 replicating tables to SQL Server 2005 Subscribers. Replicating tables that
 have FILESTREAM columns to SQL Server 2000 Subscribers is not supported,
 regardless of how this schema option is set. '

 -- See related option select @CurrentOptionCompareValue = 0x800000000.

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x200000000

 print 'Converts date and time data types (date, time, datetimeoffset, and
 datetime2) introduced in SQL Server 2008 to data types that are supported on
 earlier versions of SQL Server. For information about how these types are
 mapped, see the "Mapping New Data Types for Earlier Versions" section in
 Using Multiple Versions of SQL Server in a Replication Topology. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x400000000

 print 'Replicates the compression option for data and indexes. For more
 information, see Creating Compressed Tables and Indexes. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x800000000

 print 'Set this option to store FILESTREAM data on its own filegroup at the
 Subscriber. If this option is not set, FILESTREAM data is stored on the
 default filegroup. Replication does not create filegroups; therefore, if you
 set this option, you must create the filegroup before you apply the snapshot
 at the Subscriber. For more information about how to create objects before
 you apply the snapshot, see Executing Scripts Before and After the Snapshot
 Is Applied. '

 -- See related option select @CurrentOptionCompareValue = 0x100000000.

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''


 select @CurrentOptionCompareValue = 0x1000000000

 print 'Converts common language runtime (CLR) user-defined types (UDTs) that
 are larger than 8000 bytes to varbinary(max) so that columns of type UDT can
 be replicated to Subscribers that are running SQL Server 2005. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x2000000000

 print 'Converts the hierarchyid data type to varbinary(max) so that columns
 of type hierarchyid can be replicated to Subscribers that are running SQL
 Server 2005. For more information about how to use hierarchyid columns in
 replicated tables, see hierarchyid (Transact-SQL). '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x4000000000

 print 'Replicates any filtered indexes on the table. For more information
 about filtered indexes, see Filtered Index Design Guidelines. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x8000000000

 print 'Converts the geography and geometry data types to varbinary(max) so
 that columns of these types can be replicated to Subscribers that are
 running SQL Server 2005. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x10000000000

 print 'Replicates indexes on columns of type geography and geometry. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''




 select @CurrentOptionCompareValue = 0x20000000000

 print 'Replicates the SPARSE attribute for columns. For more information
 about this attribute, see Using Sparse Columns. '

 if ( @CurrentOptionCompareValue & @SchemaOptionValue ) > 0 begin print
 'Above Value is ON' end else begin print 'Above value is OFF' end

 print ''