SQL 2005 Subscriber dropping IDENTITY attribute

Question

Hi All,

I have a strange problem here.  On our production servers, which run transactional read-only publication from a SQL 2005 Enterprise publisher to a SQL 2005 Standard distributor/subscriber, I have found that the IDENTITY attribute is replicated to the subscriber.

In our test environment, running SQL 2005 Developer edition on both publisher and distributor/subscriber, we have scripted off the publication from the production server and run it there, and found that the IDENTITY attribute is missing from the subscriber.

All production versions of SQL are 9.0.3186 SP4, while in test we are using 9.0.5292.

Why is that, and how can I fix it so that the IDENTITY attribute is replicated across?

Regards,

Diane.

---

Diane

Answer 1

chekout this: http://technet.microsoft.com/en-us/library/ms152543.aspx

probably Identity range is not setup correctly on subscriber.

---

Kindly mark the reply as answer if they help

Answer 2

Not such all features  of replication either Merge replication or transactional replication or Snapshot replication should be included within that script  generated from Replication since each feature might be included in an individual script particularly more these features specified for individual tables

Thereby it is preferred to establish publishers through replication wizard and then manage replication feature through replication GUI or Scripts as per link sent above

---

Shehap (DBA /DB Analyst/DB Architect) Think More deeply of SQL Stress Stabilities

Answer 3

Thanks for the replies so far.

Sunil, I had checked out that MSDN article before, but that relates more to identity range management, not to the actual scripting of the IDENTITY property.

Shehap, although you might have a point, we rebuild a lot of servers over time and it is not feasible to use the GUI to track how each article is replicated across.  I am finding that there seems to be a problem with scripting though.

I tracked down the original sp_addarticle command that was used to add the tables in question to the publication list.  It was missing the @schema_option parameter:

EXEC sp_addarticle @publication = N'My Publication', @article = N'my_table', @source_owner = N'dbo', @source_object = N'my_table', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @identityrangemanagementoption = N'manual', @destination_table = N'my_table', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table]', @del_cmd = N'CALL [sp_MSdel_my_table]', @upd_cmd = N'MCALL [sp_MSupd_my_table]'
GO

According to Books Online, if @schema_option is omitted, then the default schema_option should be 0x30F3 for adding a logbased article to a transactional read-only replication type.

However, when Replication is scripted from the publisher, here's what the command looks like:

exec sp_addarticle @publication = N'My Publication', @article = N'my_table', @source_owner = N'dbo', @source_object = N'my_table', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030077, @identityrangemanagementoption = N'manual', @destination_table = N'my_table', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table]', @del_cmd = N'CALL [sp_MSdel_my_table]', @upd_cmd = N'MCALL [sp_MSupd_my_table]'
GO

As you can see, the schema_option is 0x0000000000030077 instead.

I tried bitwise AND with this value and noticed that IDENTITY property (0x04) and clustered (0x10) and non-clustered (0x40) indices should be generated with this, while primary key constraints (0x80) and defaults (0x800) are not.  With the exception of identity property and defaults, the schema option 0x30F3 matches.

So I'm still confused as to why then the original sp_addarticle, without any schema_option specified seemed to create the IDENTITY property and defaults, but why the command scripted from replication does not create the identity?  Is there something else I'm missing?  When I look at the article properties of both servers where the production one was added via the original command and the test sertver was added via the replication script, the article options are the same.

How can I guarantee that the test subscriber will have the same schema as the production subscriber, since even after restoring, the schema is blown away by the replication script?  I would rather not have to mess around with the replication script to comment out the schema options and creation script commands.

Any help would be appreciated!

---

Diane

Answer 4

Anybody out there know why the schema options do not get applied?  Even if the option scripted from replication were applied, it should in theory add the IDENTITY property to my table, but it doesn't.

---

Diane

Answer 5

Diane - Using schema_option 0x0000000000030077, the IDENTITY property is set properly for identity columns on my test Subscribers.  The schema_option 0x0000000000030077 includes schema option: Identity columns are scripted using the IDENTITY property .  This works for me in both SQL Server 2005 Developer Edition (9.00.5292.00) and SQL Server 2008 R2 Developer Edition (10.50.2500.0).

I'm unsure why this isn't working for you atm.  Can we see your table schema?

---

Brandon Williams (blog | linkedin)

Answer 6

Here's the table schema (I've renamed the table and columns to protect the innocent):

USE [my_db]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[my_table](
	[order_date] [datetime] NOT NULL,
	[table1_record_id] [int] NOT NULL,
	[table2_record_id] [int] NOT NULL,
	[table2_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[table2_description] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[table2_button_description] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[table3_record_id] [int] NOT NULL,
	[shift_start_time] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[start_datetime] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[is_shift_start_time] [bit] NOT NULL,
	[table4_record_id] [int] NULL,
	[table5_record_id] [int] NOT NULL,
	[table5_detail_group_record_id] [int] NOT NULL,
	[table5_detail_record_id] [int] NOT NULL,
	[position_number] [int] NOT NULL,
	[is_value1] [bit] NOT NULL,
	[is_value2] [bit] NOT NULL,
	[is_value3] [bit] NOT NULL,
	[table7_record_id] [int] NULL,
	[table8_record_id] [int] NULL,
	[table9_record_id] [int] NULL,
	[sequence1] [int] NULL,
	[sequence2] [numeric](30, 0) NULL,
	[table10_record_id] [int] NULL,
	[table10_code] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[table10_description] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[table10_sequence] [int] NULL,
	[obsolete_flag] [bit] NOT NULL,
	[create_user] [char](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[create_datetime] [datetime] NOT NULL,
	[record_id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[detail_create_datetime] [datetime] NOT NULL,
	[is_value_4] [int] NULL CONSTRAINT [DF__my_table__is_val__566E88C8]  DEFAULT ((0)),
	[table11_record_id] [int] NULL,
	[table12_record_id] [int] NULL,
	[sequence3] [numeric](30, 0) NULL,
	[sequence4] [numeric](30, 0) NULL,
	[is_value_5] [int] NULL CONSTRAINT [DF__my_table__is_val__5762AD01]  DEFAULT ((0)),
	[is_value_6] [int] NULL CONSTRAINT [DF__my_table__is_val__5856D13A]  DEFAULT ((0)),
	[table7_description] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[is_value_7] [int] NULL,
	[is_value_8] [int] NULL,
	[is_value_9] [int] NULL,
	[is_value_10] [bit] NOT NULL,
	[is_value_11] [bit] NOT NULL CONSTRAINT [DF__my_table__is_val__4A3DBC0D]  DEFAULT ((0)),
 CONSTRAINT [pk_my_table_rid] PRIMARY KEY CLUSTERED 
(
	[record_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [my_table_IDX03] ON [dbo].[my_table] 
(
	[order_date] ASC,
	[table2_record_id] ASC,
	[table3_record_id] ASC,
	[obsolete_flag] ASC
)
INCLUDE ( [is_value1]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [my_table_IDX04] ON [dbo].[my_table] 
(
	[order_date] ASC,
	[table2_record_id] ASC,
	[obsolete_flag] ASC
)
INCLUDE ( [table3_record_id],
[table5_record_id],
[table5_detail_group_record_id],
[table5_detail_record_id],
[is_value1],
[table13_record_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [my_table_IDX05] ON [dbo].[my_table] 
(
	[table2_record_id] ASC,
	[order_date] ASC,
	[obsolete_flag] ASC
)
INCLUDE ( [table5_record_id],
[table5_detail_group_record_id],
[table5_detail_record_id]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [my_table_UI02] ON [dbo].[my_table] 
(
	[table1_record_id] ASC,
	[table5_detail_record_id] ASC
)
INCLUDE ( [table5_record_id],
[table5_detail_group_record_id],
[sequence1],
[sequence2]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[bfalse]', @objname=N'[dbo].[my_table].[obsolete_flag]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[create_user]', @objname=N'[dbo].[my_table].[create_user]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[create_datetime]', @objname=N'[dbo].[my_table].[create_datetime]' , @futureonly='futureonly'

And here's the script we use to restore replication after the restore of the published server in our test environment:

/****** Scripting replication configuration for server SERVER_0. Script Date: 02.04.2012 11:35:42 AM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Begin: Script to be run at Publisher: SERVER_0 ******/
/****** Installing the server SERVER_1 as a Distributor. Script Date: 02.04.2012 11:35:43 AM ******/
use master
exec sp_adddistributor @distributor = N'SERVER_1', @password = N'<distrib_password>'
GO
exec sp_addsubscriber @subscriber = N'SERVER_1', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'SERVER_2', @type = 0, @description = N''
GO
exec sp_addsubscriber @subscriber = N'SERVER_3', @type = 0, @description = N''
GO
/****** End: Script to be run at Publisher: SERVER_0 ******/
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'my_db', @optname = N'publish', @value = N'true'
GO
exec [my_db].sys.sp_addlogreader_agent @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
GO
exec [my_db].sys.sp_addqreader_agent @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @frompublisher = 1
GO
-- Adding the transactional publication
use [my_db]
exec sp_addpublication @publication = N'My Publication 1', @description = N'Publish employee LUA information.', @sync_method = N'native', @retention = 336, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'My Publication 1', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 1, @active_start_time_of_day = 210000, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'My Publication 1', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'My Publication 1', @login = N'NT AUTHORITY\SYSTEM'
GO
-- Adding the transactional articles
use [my_db]
exec sp_addarticle @publication = N'My Publication 1', @article = N'my_table_1', @source_owner = N'dbo', @source_object = N'my_table_1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000080A7, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_1', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbomy_table_1_equivalent]', @del_cmd = N'CALL [sp_MSdel_dbomy_table_1_equivalent]', @upd_cmd = N'MCALL [sp_MSupd_dbomy_table_1]'
GO
use [my_db]
exec sp_addarticle @publication = N'My Publication 1', @article = N'my_table_2', @source_owner = N'dbo', @source_object = N'my_table_2', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F7, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_2', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_2]', @del_cmd = N'CALL [sp_MSdel_my_table_2]', @upd_cmd = N'MCALL [sp_MSupd_my_table_2]'
GO
use [my_db]
exec sp_addarticle @publication = N'My Publication 1', @article = N'my_table_3', @source_owner = N'dbo', @source_object = N'my_table_3', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F7, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_3', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_3]', @del_cmd = N'CALL [sp_MSdel_my_table_3]', @upd_cmd = N'MCALL [sp_MSupd_my_table_3]'
GO
-- Adding the transactional subscriptions
use [my_db]
exec sp_addsubscription @publication = N'My Publication 1', @subscriber = N'SERVER_2', @destination_db = N'my_db', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'My Publication 1', @subscriber = N'SERVER_2', @subscriber_db = N'my_db', @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
-- Adding the transactional publication
use [my_db]
exec sp_addpublication @publication = N'My Publication', @description = N'Table replicated from SERVER_0 to SERVER_1 for use by My Websites', @sync_method = N'native', @retention = 336, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'My Publication', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 1, @active_start_time_of_day = 210000, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'My Publication', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'My Publication', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'My Publication', @login = N'Domain\SQLAccount2'
GO
-- Adding the transactional articles
use [my_db]
exec sp_addarticle @publication = N'My Publication', @article = N'my_table_1', @source_owner = N'dbo', @source_object = N'my_table_1', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F7, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_1', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_1]', @del_cmd = N'CALL [sp_MSdel_my_table_1]', @upd_cmd = N'MCALL [sp_MSupd_my_table_1]'
GO
use [my_db]
exec sp_addarticle @publication = N'My Publication', @article = N'my_table_2', @source_owner = N'dbo', @source_object = N'my_table_2', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F7, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_2', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_2]', @del_cmd = N'CALL [sp_MSdel_my_table_2]', @upd_cmd = N'MCALL [sp_MSupd_my_table_2]'
GO
use [my_db]
exec sp_addarticle @publication = N'My Publication', @article = N'my_table_3', @source_owner = N'dbo', @source_object = N'my_table_3', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F7, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_3', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_3]', @del_cmd = N'CALL [sp_MSdel_my_table_3]', @upd_cmd = N'MCALL [sp_MSupd_my_table_3]'
GO
use [my_db]
exec sp_addarticle @publication = N'My Publication', @article = N'my_table', @source_owner = N'dbo', @source_object = N'my_table', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030077, @identityrangemanagementoption = N'manual', @destination_table = N'my_table', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table]', @del_cmd = N'CALL [sp_MSdel_my_table]', @upd_cmd = N'MCALL [sp_MSupd_my_table]'
GO
-- Adding the transactional subscriptions
use [my_db]
exec sp_addsubscription @publication = N'My Publication', @subscriber = N'SERVER_1', @destination_db = N'my_db', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'My Publication', @subscriber = N'SERVER_1', @subscriber_db = N'my_db', @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
use [my_db]
exec sp_addsubscription @publication = N'My Publication', @subscriber = N'SERVER_3', @destination_db = N'my_db', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'My Publication', @subscriber = N'SERVER_3', @subscriber_db = N'my_db', @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'my_db1', @optname = N'publish', @value = N'true'
GO
exec [my_db1].sys.sp_addlogreader_agent @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
GO
--exec [my_db1].sys.sp_addqreader_agent @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @frompublisher = 1
GO
-- Adding the transactional publication
use [my_db1]
exec sp_addpublication @publication = N'My Publication 3', @description = N'Transactional publication of my_db1 database from Publisher SERVER_0 to be used by My servers', @sync_method = N'native', @retention = 336, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'My Publication 3', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 1, @active_start_time_of_day = 210000, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'My Publication 3', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'My Publication 3', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'My Publication 3', @login = N'Domain\SQLAccount2'
GO
-- Adding the transactional articles
use [my_db1]
exec sp_addarticle @publication = N'My Publication 3', @article = N'my_table_4', @source_owner = N'dbo', @source_object = N'my_table_4', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030077, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_4', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_4]', @del_cmd = N'CALL [sp_MSdel_my_table_4]', @upd_cmd = N'MCALL [sp_MSupd_my_table_4]'
GO
use [my_db1]
exec sp_addarticle @publication = N'My Publication 3', @article = N'my_table_5', @source_owner = N'dbo', @source_object = N'my_table_5', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030077, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_5', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_5]', @del_cmd = N'CALL [sp_MSdel_my_table_5]', @upd_cmd = N'MCALL [sp_MSupd_my_table_5]'
GO
use [my_db1]
exec sp_addarticle @publication = N'My Publication 3', @article = N'my_table_6', @source_owner = N'dbo', @source_object = N'my_table_6', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @identityrangemanagementoption = N'none', @destination_table = N'my_table_6', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_6]', @del_cmd = N'CALL [sp_MSdel_my_table_6]', @upd_cmd = N'CALL [sp_MSupd_my_table_6]'
GO
-- Adding the transactional subscriptions
use [my_db1]
exec sp_addsubscription @publication = N'My Publication 3', @subscriber = N'SERVER_1', @destination_db = N'my_db1', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'My Publication 3', @subscriber = N'SERVER_1', @subscriber_db = N'my_db1', @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
use [my_db1]
exec sp_addsubscription @publication = N'My Publication 3', @subscriber = N'SERVER_3', @destination_db = N'my_db1', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'My Publication 3', @subscriber = N'SERVER_3', @subscriber_db = N'my_db1', @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO
-- Enabling the replication database
use master
exec sp_replicationdboption @dbname = N'my_db2', @optname = N'publish', @value = N'true'
GO
exec [my_db2].sys.sp_addlogreader_agent @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
GO
--exec [my_db2].sys.sp_addqreader_agent @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @frompublisher = 1
GO
-- Adding the transactional publication
use [my_db2]
exec sp_addpublication @publication = N'My Publication 4', @description = N'Table replicated from SERVER_0 to SERVER_1 for use by My Websites', @sync_method = N'native', @retention = 336, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'My Publication 4', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 1, @active_start_time_of_day = 210000, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @publisher_security_mode = 1
exec sp_grant_publication_access @publication = N'My Publication 4', @login = N'sa'
GO
exec sp_grant_publication_access @publication = N'My Publication 4', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @publication = N'My Publication 4', @login = N'Domain\SQLAccount2'
GO
-- Adding the transactional articles
use [my_db2]
exec sp_addarticle @publication = N'My Publication 4', @article = N'my_table_7', @source_owner = N'dbo', @source_object = N'my_table_7', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030077, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_7', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_7]', @del_cmd = N'CALL [sp_MSdel_my_table_7]', @upd_cmd = N'MCALL [sp_MSupd_my_table_7]'
GO
use [my_db2]
exec sp_addarticle @publication = N'My Publication 4', @article = N'my_table_8', @source_owner = N'dbo', @source_object = N'my_table_8', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000030077, @identityrangemanagementoption = N'manual', @destination_table = N'my_table_8', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_my_table_8]', @del_cmd = N'CALL [sp_MSdel_my_table_8]', @upd_cmd = N'MCALL [sp_MSupd_my_table_8]'
GO
-- Adding the transactional subscriptions
use [my_db2]
exec sp_addsubscription @publication = N'My Publication 4', @subscriber = N'SERVER_1', @destination_db = N'my_db2', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'My Publication 4', @subscriber = N'SERVER_1', @subscriber_db = N'my_db2', @job_login = N'Domain\SQLAccount1', @job_password = N'<password>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'
GO

As you can see, the table in question my_table added to My Publication uses schema_option 0x0000000000030077 which should script off IDENTITY property, but when it is applied on my test server, it does not, even though you can see from the published table schema above on both the production and test servers, it exists.

Of some concern is also how it comes up with the schema option it uses, since on the production subscriber, it appears that the defaults [DF__my_table__<some_default>] also got applied as well as the IDENTITY property.  As far as I can tell, if it really were using schema_option 0x0000000000030077, it shouldn't have done that, and if it had used the BOL default 0x30F3 instead, then the IDENTITY property would have been excluded.  Since no schema option was specified in the original sp_addarticle command, I'd like to know what in fact was used as the default, since BOTH the IDENTITY property and the defaults were applied to the subscriber in production; and why then it picks schema_option 0x0000000000030077 when you script replication.

---

Diane

Answer 7

I also wonder if it has anything to do with how we restore our test environment.  We start by using the GUI on the publisher server to Disable Publishing and Distribution.  We do not disable distribution on our distributor server or remove publication from any of the subscribers either.  We then kill all connections, stop SQL Server Agent on the distributor server, restore databases on our publisher, rematch users, start SQL Server agent on the distributor server, and then re-apply the replication script above on the publisher.

Is it possible that there is something that needs to be cleaned up on the distributor or subscriber servers that might affect how a replication script is applied?  I can't think of how that should matter, but maybe someone out there knows better.

---

Diane

Answer 8

Anyone out there have any more clues?

---

Diane