Making life a little easier on this was an application change that was going to require me dropping and recreating replication so I figured it would be a simple case to change the replication options when recreating the publications.
Using the GUI I scripted out all of the existing publications and then dropped them.
Now I wanted to adjust the script so that the schema options I wanted replicated were applied. Here’s the sp_addarticle for one of these tables (SQL 2008)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Adding the transactional articles use [TestDB] exec sp_addarticle @publication = N’TestDB_TestTable’ , @article = N’TestTable’ , @source_owner = N’dbo’ , @source_object = N’TestTable’ , @type = N’logbased’ , @description = N” , @creation_script = N” , @pre_creation_cmd = N’drop’ , @schema_option = 0x000000000803509F , @identityrangemanagementoption = N’none’ , @destination_table = N’TestTable’ , @destination_owner = N’dbo’ , @status = 24 , @vertical_partition = N’false’ , @ins_cmd = N’CALL [dbo].[sp_MSins_dboTestTable]’ , @del_cmd = N’CALL [dbo].[sp_MSdel_dboTestTable]’ , @upd_cmd = N’SCALL [dbo].[sp_MSupd_dboTestTable]’ GO |
Now this doesn’t tell you a lot about what schema options are in place. The binary value @schema_option has all the information you need however it’s not exactly user friendly. Books Online has the information available to be able to figure this out, it just takes a little calculation. Somewhat of a pain I’ve created a script to tell me what options are enabled.
All you need to do is pass in the @schema_option value and it will tell you what options are enabled (in this case 0x000000000803509F)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
/* PROVIDES THE REPLICATION OPTIONS ENABLED FOR A GIVEN @SCHEMA_OPTION IN SYSARTICLES */ declare @schema_option varbinary(8) = 0x000000000803509F –< PUT YOUR SCHEMA_OPTION HERE set nocount on declare @OptionTable table ( HexValue varbinary(8), IntValue as cast(HexValue as bigint), OptionDescription varchar(255)) insert into @OptionTable (HexValue, OptionDescription) select 0x01 ,‘Generates object creation script’ union all select 0x02 ,‘Generates procs that propogate changes for the article’ union all select 0x04 ,‘Identity columns are scripted using the IDENTITY property’ union all select 0x08 ,‘Replicate timestamp columns (if not set timestamps are replicated as binary)’ union all select 0x10 ,‘Generates corresponding clustered index’ union all select 0x20 ,‘Converts UDT to base data types’ union all select 0x40 ,‘Create corresponding nonclustered indexes’ union all select 0x80 ,‘Replicate pk constraints’ union all select 0x100 ,‘Replicates user triggers’ union all select 0x200 ,‘Replicates foreign key constraints’ union all select 0x400 ,‘Replicates check constraints’ union all select 0x800 ,‘Replicates defaults’ union all select 0x1000 ,‘Replicates column-level collation’ union all select 0x2000 ,‘Replicates extended properties’ union all select 0x4000 ,‘Replicates UNIQUE constraints’ union all select 0x8000 ,‘Not valid’ union all select 0x10000 ,‘Replicates CHECK constraints as NOT FOR REPLICATION so are not enforced during sync’ union all select 0x20000 ,‘Replicates FOREIGN KEY constraints as NOT FOR REPLICATION so are not enforced during sync’ union all select 0x40000 ,‘Replicates filegroups’ union all select 0x80000 ,‘Replicates partition scheme for partitioned table’ union all select 0x100000 ,‘Replicates partition scheme for partitioned index’ union all select 0x200000 ,‘Replicates table statistics’ union all select 0x400000 ,‘Default bindings’ union all select 0x800000 ,‘Rule bindings’ union all select 0x1000000 ,‘Full text index’ union all select 0x2000000 ,‘XML schema collections bound to xml columns not replicated’ union all select 0x4000000 ,‘Replicates indexes on xml columns’ union all select 0x8000000 ,‘Creates schemas not present on subscriber’ union all select 0x10000000 ,‘Converts xml columns to ntext’ union all select 0x20000000 ,‘Converts (max) data types to text/image’ union all select 0x40000000 ,‘Replicates permissions’ union all select 0x80000000 ,‘Drop dependencies to objects not part of publication’ union all select 0x100000000 ,‘Replicate FILESTREAM attribute (2008 only)’ union all select 0x200000000 ,‘Converts date & time data types to earlier versions’ union all select 0x400000000 ,‘Replicates compression option for data & indexes’ union all select 0x800000000 ,‘Store FILESTREAM data on its own filegroup at subscriber’ union all select 0x1000000000 ,‘Converts CLR UDTs larger than 8000 bytes to varbinary(max)’ union all select 0x2000000000 ,‘Converts hierarchyid to varbinary(max)’ union all select 0x4000000000 ,‘Replicates filtered indexes’ union all select 0x8000000000 ,‘Converts geography, geometry to varbinary(max)’ union all select 0x10000000000 ,‘Replicates geography, geometry indexes’ union all select 0x20000000000 ,‘Replicates SPARSE attribute ‘ select HexValue,OptionDescription as ‘Schema Options Enabled’ From @OptionTable where (cast(@schema_option as bigint) & cast(HexValue as bigint)) <> 0 |
This is really great and useful. In fact you can use this script to check out the schema options for any article, just plug in the schema_option value from sysarticles to get the data.
Now to take it a step further I wanted to know what the new value would need to be for @schema_option in order to replicate the data compression settings. For this I wrote another query. This time it’s just a case of uncommenting the lines for the schema options that I want and running the select.
Continue reading on SirSQL.net.