Modify the Publication Properties

After you have created a transactional publication in the production Anthology Student database, you can modify the publication properties.  To do so, open the Publication Properties dialog box using the following steps:

1. In the Microsoft SQL Server Management Studio window, click the (+) sign to expand the Replication folder and click the Local Publications folder.

2. Right-click the publication that you created and select Properties.

   

3. On the Articles page of the Publication Properties dialog box, click Article Properties, and select Set Properties of All Table Articles.

   

4. In the Properties for All Table Articles dialog box:

   1. Set the Copy nonclustered indexes value for all articles to True.  This action will ensure that nonclustered indexes are included whenever the snapshot of the tables is generated. It will also improve the performance of the reporting queries on the reporting database. 

   2. Set the Action if name is in use value to ‘Truncate all data in the existing object’. There are few views in the database that have schema binding. When the snapshots for these objects (views and functions created with schema binding) are being created or reinitialized due to a change in the schema, the objects will not be dropped; instead, the data is truncated to alter the schema.

   

5. Click OK to close the dialog box.

6. On the Subscription Options page of the Publication Properties dialog box:

   1. Set the Allow initialization from backup files value to True. This needs to be done to recreate the Subscriber from a database backup.

   2. Set the Replicate schema changes property to True. 

   

7. Click OK to close the window.

8. Execute the script below on the publication database to change the properties of the SyReplTablesAltered table article.

   
   SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
   GO
   Declare @Publication NVARCHAR(128)
   SELECT @Publication = p.NAME FROM dbo.SysArticles a WITH (NOLOCK)
   INNER JOIN dbo.sysPublications p ON a.Pubid = p.pubid
   WHERE dest_table = 'SyReplTablesAltered'
   
   PRINT @Publication
   
   EXEC sp_changearticle @publication = @Publication, 
   @article = N'SyReplTablesAltered', 
   @property = 'schema_option', 
   @value = '0x00000000084359DF'
   GO
   

9. Execute the script below on the publication database to configure the “max text repl size” server configuration option.

   
   --Display current run_value of 'max text repl size' server configuration value
   EXEC sp_configure 'max text repl size'
   GO
   --Set the value to -1 or a higher value if the current run_value is the default 65536 bytes. A value of -1 indicates that there is no size limit
   
   --Change the value accordingly if the limit is imposed by IT/DBA Team.
   EXEC sp_configure 'max text repl size', -1 ; 
   GO
   RECONFIGURE; 
   GO
   

10. Disable the following SQL Server jobs (right-click on the job name and click Disable:

    • Distribution cleanup job (Distribution clean up: distribution). First stop the job if it is currently running.

      Note: This job must be disabled before creating the backup in the “Synchronize from Backup” method of setting up replication. Otherwise, you may encounter the following error in the replication agents:

      The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup.

    • Replication Snapshot Agent job with the following name: <Publisher Server Name>-<Publication Database Name>-<Publication Name>-#.

      The Job Category would be ‘REPL-Snapshot’.

      Be sure to first stop the job if it is currently running.

      Example: In the image below, the Replication Snapshot Agent job is named CVUESQL-CVUE_DEMO -CVUE_DEMO_PUB-10.