Sql-server – Transaction Replication Without Snapshot

sql servertransactional-replication

So I have looked up many other people trying to get Transaction Replication Without Snapshot working and I'm stuck. I've followed what everyone has said so I don't know what I'm missing.

Prod server and Report server.

On the prod server I configured the Distributor from the UI.
Then I setup a transnational publication from the UI.
I made sure not to check either snapshot options.
Created the publication.

Changed 'Allowed initialization from backup file' to true in the Subscription options in the UI.

Now I create a full backup of the database on the prod server and restore it to the report server. I disable all triggers and constraints on the report server.

Now also on the prod server I want to create the subscription for the report server.
I choose to run all agents at the Distributor.

I choose 'Add Subscriber' and point it to the report server.
Agent Schedule is set to Location: Distributor and Schedule: Run continuously

Subscription Properties are set as follows
Initialize: checked
Initialize When: At first synchronization

I generate the script file and edit it as below

use [Demo]
exec sp_addsubscription 
@publication = N'Demo_Publication', 
@subscriber = N'mo-rptsvr', 
@destination_db = N'Demo', 
@subscription_type = N'Push', 
@sync_type = N'initialize with backup', 
@backupdevicetype ='disk',
@backupdevicename = '\\backup device path.bak',
@article = N'all', 
@update_mode = N'read only', 
@subscriber_type = 0

And next I left unmodified

exec sp_addpushsubscription_agent 
@publication = N'Demo_Publication', 
@subscriber = N'mo-rptsvr', 
@subscriber_db = N'Demo', 
@job_login = null, 
@job_password = null, 
@subscriber_security_mode = 1, 
@frequency_type = 64, 
@frequency_interval = 0, 
@frequency_relative_interval = 0, 
@frequency_recurrence_factor = 0, 
@frequency_subday = 0, 
@frequency_subday_interval = 0, 
@active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, 
@active_start_date = 20140206, 
@active_end_date = 99991231, 
@enabled_for_syncmgr = N'False', 
@dts_package_location = N'Distributor'

Log Reader Agent Status once in awhile says 'x transaction(s) with x command(s) were delivered'

Now looking at the logs this morning I see the following error

'Cannot insert an explicit value into a timestamp columns. Use INSERT with a column list to exclude the timestamp column, or a default into the timestamp column'

Now I have read some posts saying this is because the row timestamp column.
I have 1,000's of tables. If this is the issue what is the fastest way to change correct it?

Also after the Synchronization status errors and starts back up, Why does it always say Initializing? This alone takes over an hour before it errors out again.

Update
I found in article properties that I can change timestamp to binary(8).
Do I need to take another backup and restore it after this change?
How will this effect the production server application along with the reporting application?

Best Answer

What version of SQL Server are you using ?

I just setup a publication and subscription in the method you describe on SQL Server 2012 (11.0.2218) and it appears to be working fine.

I tested inserting, updating, and deleting records from the source - and all transactions made it successfully to the destination.

It is true that the timestamp column is of interest but I didn't have to use the option you mentioned in your update. From this SQL 2000 TechNet article note that

"The literal values for a timestamp column are replicated, but the data type for the replicated values is changed to binary (8) on the Subscriber."

Since this was for SQL 2000 I can only imagine things may have changed... as my subscriber table, just like the publication, has a timestamp column not a binary(8).

Therefore the values that are there when you backup and restore will match. Future inserts will have different timestamp values in the source and destination.

I scripted the entire thing out into one file. The order of events:

  • create the publication (with @allow_initialize_from_backup = N'true');
  • backup the source database (with format, init);
  • restore the database to the destination;
  • create the subscription (with @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = '\myUNCPath\Backup\Backup.bak').

I ran into Msg 21397 during my first try - and an interesting read, explanation, solution are in this blog post.

Related Question