Configuring Transactional Replication

We'll now look at how to configure or set up transactional replication between two databases using T-SQL.

Exercise 7: Creating the Publication

The first step in configuring replication is to create the publication. Follow these steps to create a publication for transactional replication:

1. Navigate to C:\Code\Lesson02\Transactional and open 1_CreatePublication.sql in SQL Server Management Studio.
2. In SSMS, from the top menu, select Query and then select SQLCMD. This changes the mode of the 1_CreatePublication.sql query to SQLCMD from SQL.
3. Modify the parameters in the script (shown in the following screenshot) as per your environment:
   

   Figure 2.2: Parameters in creating a publication

   The preceding screenshot shows the parameters being used in the script. The parameters are self-explanatory. The publisher and distributor are the same, as the publisher acts as its own distributor.

4. To run the script and create the publication, change the parameters as specified earlier and press F5 or click on Execute in the top menu.

   You should get the following output:

Figure 2.3: Output of creating the publication

This creates three objects: the publication named AdventureWorks-Trans_Pub, the log reader agent job named WIN2012R2\SQL2016-AdventureWorks-17, and the snapshot agent job named WIN2012R2\SQL2016-AdventureWorks-AdventureWorks-Tran_Pub-17.

This completes this exercise.

Now, we'll dissect different parts of the script and understand what different procedures in the script do. You are, however, supposed to run the script immediately after modifying the parameters to create the publication.

The following command connects to the publisher server and changes the database context to the publisher database, that is, AdventureWorks:

-- connect to the publisher and set replication options

:CONNECT $(Publisher)

USE "$(DatabaseName)"

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

The following command enables the AdventureWorks database for publication:

-- Enable database for publication

EXEC sp_replicationdboption

  @dbname = "$(DatabaseName)",

  @optname = N'publish',

  @value = N'true'

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

The sp_replicationdboption procedure is a system procedure that enables or disables the publication, as specified by the @optname parameter.

The publish option specifies that the database can be used for transactional and snapshot publication.

Note

For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-replicationdboption-transact-sql?view=sql-server-2017.

The following command creates the log reader agent job:

-- Create the log reader agent

-- If this isn't specified, log reader agent is implicitly created

-- by the sp_addpublication procedure

EXEC sp_addlogreader_agent

  @job_login = NULL,

  @job_password = NULL,

  @publisher_security_mode = 1;

GO

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

The sp_addlogreader_agent procedure creates the SQL Server agent job that runs the log reader agent. The @job_login and @job_password parameters are the Windows username and password under which the log reader agent process runs. When NULL, the log reader process runs under the security context of the SQL Server agent service account.

@publisher_security_mode specifies the security mode used by the log reader agent to connect to the publisher. 1 is for Windows authentication and 0 for SQL Server authentication.

In this case, the log reader agent connects to the publisher by impersonating the SQL Server agent service account.

Note

For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addlogreader-agent-transact-sql?view=sql-server-2017.

The following command creates the publication:

-- Create publication

EXEC sp_addpublication

  @publication = "$(PublicationName)",

  @description =

N'Transactional publication of database ''AdventureWorks'' from Publisher ''WIN2012R2\SQL2016''.'

,

@sync_method = N'concurrent',

@retention = 0,

@allow_push = N'true',

@allow_pull = N'true',

@snapshot_in_defaultfolder = N'true',

@repl_freq = N'continuous',

@status = N'active',

@independent_agent = N'true',

@immediate_sync = N'true',

@replicate_ddl = 1

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

The sp_addpublication procedure creates a new publication for the transactional replication. The procedure has a lot of parameters; however, a description of the important parameters is provided here:

• @publication: The name of the publication to be created.
• @description: The optional description of the publication.
• @sync_method: This is the synchronization mode for the snapshot agent when generating the snapshot. A value of concurrent means that when producing .bcp files, the shared lock isn't taken on tables. This option is only available for transactional replication.
• @retention: This is the retention period of the subscriber. If the subscriber isn't active for the specified retention period, it's expired and should be reinitialized to activate again. A value of 0 specifies that the subscription never expires.
• @allow_push: This specifies whether or not a push subscription can be created for the publication.
• @allow_pull: This specifies whether or not a pull subscription can be created for the publication.
• @snapshot_in_defaultfolder: This specifies whether or not the snapshot files are stored in the default folder. If not, then the snapshot files are stored at the location specified by the @alt_snapshot_folder parameter.
• @repl_freq: This defines the type of replication frequency, either continuous or scheduled. A value of continuous specifies that the log reader agent runs continuously and transmits the logs as and when captured; scheduled specifies that the logs are provided as per the given schedule.
• @status: This defines whether or not the publication data is available to the subscriber immediately. A value of active means that the publication data is available to subscribers immediately as they subscribe and a value of inactive means that the data isn't available when the publication is created.
• @independent_agent: This specifies if the publication uses a standalone distribution agent or a shared distribution agent. A value of false specifies the shared distribution agent.
• @immediate_sync: This specifies whether or not the snapshot is generated each time the snapshot agent runs. A value of true means that the snapshot is generated each time the snapshot agent is started. New subscriptions that are created after the snapshot agent runs receive the latest snapshot generated or the last snapshot generated by the snapshot agent.
• @replicate_ddl: This specifies whether or not the schema changes are replicated. A value of 1 means that the schema changes are replicated.

  Note

  For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addpublication-transact-sql?view=sql-server-2017.

The following command adds the snapshot agent for the publication:

-- Create the snapshot agent

EXEC sp_addpublication_snapshot

  @publication = "$(PublicationName)",

  @frequency_type = 1,

  @frequency_interval = 1,

  @frequency_relative_interval = 1,

  @frequency_recurrence_factor = 0,

  @frequency_subday = 8,

  @frequency_subday_interval = 1,

  @active_start_time_of_day = 0,

  @active_end_time_of_day = 235959,

  @active_start_date = 0,

  @active_end_date = 0,

  @job_login = NULL,

  @job_password = NULL,

  @publisher_security_mode = 1

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

The sp_addpublication_snapshot procedure creates the snapshot agent job that is used to initialize the transactional replication. The important parameters for this procedure are as follows:

• @publication: The name of the publication the snapshot agent is created for.
• @frequency_type: The frequency with which the snapshot is executed. The value 1 specifies that the snapshot agent is only run once.
• @job_login and @job_password: These are the Windows username and password under which the snapshot agent process runs. When NULL, the snapshot agent process runs under the security context of the SQL Server agent service account.
• @publisher_security_mode: This specifies the security mode used by the snapshot agent to connect to the publisher; 1 is for Windows authentication and 0 is for SQL Server authentication.

In this case, the snapshot agent connects to the publisher by impersonating the SQL Server agent service account.

Note

For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addpublication-snapshot-transact-sql?view=sql-server-2017.

The following command adds an article to the publication:

EXEC sp_addarticle

  @publication = "$(PublicationName)",

  @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 = 0x000000000803509F,

  @identityrangemanagementoption = N'manual',

  @destination_table = N'Address',

  @destination_owner = N'Person',

  @vertical_partition = N'false',

  @ins_cmd = N'CALL sp_MSins_PersonAddress',

  @del_cmd = N'CALL sp_MSdel_PersonAddress',

  @upd_cmd = N'SCALL sp_MSupd_PersonAddress'

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

The sp_addarticle procedure adds a table called Address to the publication. One procedure call adds a single article. The script here shows only one article addition for the sake of explanation and brevity. The script, however, has multiple sp_addarticle calls to add other articles to the publication.

Note

Transactional replication requires a table to have a primary key constraint if it is to be replicated.

The following are the important parameters:

• @publication: This is the name of the publication.
• @article: This is the name of the article.
• @source_owner: This is the schema the article belongs to at the publisher database.
• @source_object: This is the name of the article at the publisher database.
• @type: This is the article type. There are multiple article types available. The default is logbased.
• @description: This is an optional description of the article.
• @creation_script: This is the full path of the optional article creation script.
• @pre_creation_cmd: This specifies the action to be taken if the article already exists at the subscriber when applying the initial snapshot. The default action is to drop the article at the subscriber.
• @schema_option: This is a bitmask that defines what schema generation options are applicable to the article.
• @identityrangemanagementoption: This specifies how identity columns are being handled in replication. A value of manual specifies that the identity column is marked as Not for Replication and is therefore not replicated to the subscriber.
• @destination_table and @destination_owner: This is the article schema and the article name at the subscriber database.
• @vertical_partition: This specifies whether or not to filter out columns from the table article. A value of false specifies that no column filtering is applied and that all columns are replicated. A value of true excludes all columns except the primary key. The columns to be replicated are then specified by the procedure sp_articlecolumn.
• @ins_cmd: This specifies the command to be used when replicating inserts. The default is to call the sp_MSins_tablename procedure. A custom stored procedure can also be used to replicate inserts.
• @del_cmd: This specifies the command to be used when replicating deletes. The default is to call the sp_MSdel_tablename procedure. A custom stored procedure can also be used to replicate deletes.
• @upd_cmd: This specifies the command to be used when replicating updates. The default is to call the sp_MSupd_tablename procedure. A custom stored procedure can also be used to replicate updates.

The sp_Msins_tablename, sp_Msdel_tablename, and sp_Msupd_tablename procedures are created for each table being replicated, as part of the replication setup, automatically. The procedures can also be modified if required.

Note

For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addarticle-transact-sql?view=sql-server-2017.

Check the @schemaoptions, @pre_creation_cmd, and @type parameter values.

The following command starts the snapshot agent so as to generate the snapshot. The snapshot is applied to the subscribers to initialize the subscriber database:

DECLARE @jobname NVARCHAR(200)

SELECT @jobname=name FROM [distribution].[dbo].[MSsnapshot_agents]

WHERE [publication]='$(PublicationName)' AND [publisher_db]='$(DatabaseName)'

Print 'Starting Snapshot Agent ' + @jobname + '....'

-- Start the snapshot agent to generate the snapshot

-- The snapshot is picked up and applied to the subscriber by the distribution agent

EXECUTE msdb.dbo.sp_start_job @job_name=@jobname

Note

You can also copy the code from the Code\Lesson02\Transactional _CreatePublication.sql file.

This command gets the name of the snapshot agent job that was created earlier in the script from the MSsnapshot_agents table in the distribution database. It then starts the job by calling the sp_start_job system-stored procedure in the msdb database.

Note

You can verify the status of the snapshot agent job so as to make sure that the job ran successfully and that the database snapshot was created at the provided snapshot share folder.

This completes the create publication script explanation.

You can now verify the following:

• The jobs, by going to the SQL Server Agent | Jobs node in SSMS.
• The publication, by going to the Replication | Local Publication node.
• The snapshot generated in the default snapshot agent folder. To find the snapshot folder, right-click the Replication node and select Distributor Properties.

  The job name will be different in your case. The number at the end of the job name is incremented every time a new distribution job is created.

The next step is to create the subscription for the newly created transactional publication.

Exercise 8: Creating the Subscription

In this exercise, we'll create a subscription for the transactional publication that we created earlier. To create a subscription, connect to a different SQL Server instance.

Follow these steps to create a subscription for transactional replication:

1. Navigate to C:\Code\Lesson02\Transactional and open 2_CreateSubscription.sql in SQL Server Management Studio.
2. In SSMS, from the top menu, select Query and then select SQLCMD. This changes the mode of the 2_CreateSubscription.sql query to SQLCMD from SQL.
3. Modify the parameters in the script, as per your environment:
   

   Figure 2.4: Parameters in creating the subscription

   The Publisher, Distributor, DatabaseName, and PublicationName parameters should be the ones that were used in Exercise 1: Creating a Publication.

   You will need to replace the Subscriber value with your subscriber's SQL Server instance name.

4. Before you run the script, make sure that you are in SQLCMD mode and the AdventureWorks database exists at the subscriber instance. Run the script by pressing F5 or clicking on Execute in the top menu.

   You should get the following output after successful execution of the script:

Figure 2.5: Output of creating the subscription

The script creates the subscription and the distribution pull agent. At this point, you have successfully configured transactional replication.

This completes this exercise.

Now, we'll dissect different parts of the script and understand what the different procedures in the script do. You are, however, supposed to run the script at once after modifying the parameters to create the subscription.

The following command connects to the publisher and adds the subscriber at the publisher:

-- Connect to the publisher

:CONNECT $(Publisher)

USE $(DatabaseName)

-- Add subscription at publisher

EXEC sp_addsubscription

  @publication = "$(PublicationName)",

  @subscriber = "$(Subscriber)",

  @destination_db = $(DatabaseName),

  @sync_type = N'Automatic',

  @subscription_type = N'pull',

  @update_mode = N'read only'

Note

You can also copy the code from the Code\Lesson02\Transactional\2_CreateSubscription.sql file.

The sp_addsubscription procedure adds the subscription at the publisher server. This tells the publisher that the given subscriber has subscribed for a particular publication and sets the subscription properties.

The following are the important parameters and their descriptions:

• @sync_type: This specifies the synchronization type. The default is Automatic, which means that the schema and data for the articles is first applied to the subscriber database.
• @subscription_type: This is the type of subscription: push or pull.
• @update_mode: This specifies whether or not the changes at the subscriber are applied to the publisher. A value of read only specifies that changes at the subscriber are not applied to the publisher.

  Note

  For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addsubscription-transact-sql?view=sql-server-2017.

  Check the other values that are available for the @sync_type and @update_mode parameters. Replication also supports memory-optimized tables. The @memory_optimized parameter, when set to 1, indicates that the subscription contains memory-optimized tables. You can read more about memory-optimized tables here: https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization?view=sql-server-2017.

The following query connects to the subscriber and creates the subscription:

:CONNECT $(Subscriber)

USE $(DatabaseName)

-- Create subscription at the subscriber

EXEC sp_addpullsubscription

  @publisher = "$(Publisher)",

  @publication = "$(PublicationName)",

  @publisher_db = $(DatabaseName),

  @independent_agent = N'True',

  @subscription_type = N'pull',

  @description = N'',

  @update_mode = N'read only',

  @immediate_sync = 1

Note

You can also copy the code from the Code\Lesson02\Transactional\2_CreateSubscription.sql file.

The sp_addpullsubscription procedure creates a new subscription for the given publication. The important parameters and their descriptions are given here:

• @independent_agent: This is the property of the publisher and should have the same value as given when creating the publication. Check the explanation in Exercise 1: Creating a Publication.
• @immediate_sync: This too is the property of the publisher and should have the same value as given when creating the publication. Check the explanation in Exercise 1: Creating a Publication.

The following query creates the distribution agent at the subscriber:

EXEC sp_addpullsubscription_agent

  @publisher = "$(Publisher)",

  @publisher_db = $(DatabaseName),

  @publication = "$(PublicationName)",

  @distributor = "$(Distributor)",

  @distributor_security_mode = 1,

  @distributor_login = N'',

  @distributor_password = NULL,

  @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 = 20180515,

  @active_end_date = 99991231,

  @job_login = NULL,

  @job_password = NULL,

  @publication_type = 0

The sp_addpullsubscription_agent procedure creates a SQL Server agent job that runs the distribution agent at the subscriber instance. An explanation of the important parameters is given here:

• @distributor_security_mode: This specifies the security method to use when connecting to the distributor. 1 means Windows authentication and 0 refers to SQL Server authentication. If you set it to 0, you need to specify the values for the @distributor_login and @distributor_password parameters.
• @publication_type: This specifies the type of replication: 0 is transactional, 1 is snapshot, and 2 is merge replication.

All other parameters with frequency as the prefix define the schedule of the distribution agent. The @frequency_type parameter defines the schedule's frequency, for example, daily, weekly, or monthly. A value of 64 means that the agent runs continuously.

Note

For details on the procedure and other options, go to https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addpullsubscription-agent-transact-sql?view=sql-server-2017.

This completes the script.

If you now look under the Replication | Local Publications node, you will see the new publication. Expand the publication and you will see all of the subscribers to that publication.

At the publisher:

Figure 2.6: The new publication at the publisher

At the subscriber:

Figure 2.7: The new subscription at the subscriber

Another quick way to verify replication is to navigate to the subscriber database and verify whether the articles have been created. If they have, then the snapshot agent ran successfully and has initialized the subscriber database.

To verify the continuous sync, make a transaction at the publisher database by deleting/updating/inserting a row in the replicated article. Check for the changes at the subscriber. If the changes are replicated, the log reader agent and distribution agent are working properly.

Another way is to look at the different transactional replication agents' job history.

Transactional Replication Agents

The transactional replication created three agent jobs: snapshot, log reader, and distributor agent. We have already looked at the snapshot agent job in the previous lesson. Therefore, we will not discuss it again. You can, however, check the agent job properties.

Log Reader Agent

The log reader agent job is created at the distributor or at the publisher if the publisher acts as its own distributor. The log reader agent job runs the log reader process. The log reader agent job is just a medium to run the log reader executable and calls the logread.exe file with the parameter values provided by us when configuring the replication.

It's advised to use the SQL Server agent job to run the log reader executable. However, it can be run through a different application as well. For example, logread.exe can be run from the command prompt or scheduled in Windows scheduler.

The log reader process (logread.exe) is an executable that reads the transaction logs of each database that is configured for transactional replication and inserts the transactions marked for replication into the distribution database.

First, let's explore the log reader agent job. You can connect to the publisher server in the Object Explorer and then go to SQL Server Agent | Jobs. Under the jobs, look out for the job with the following naming convention: [Publisher Server]-[Published database]-[Log Reader Agent ID].

The log reader agent ID is from the MSlogreader_agents table in the distribution database (at publisher).

You can also get the name from the PublicationName parameter in Exercise 1: Creating a Publication:

Figure 2.8: Our publication job name

The job name will differ on your system:

Figure 2.9: Selecting the job

You can double-click on the job to open its properties. In the Job Properties window, you can select the Steps page from the left-hand pane:

Figure 2.10: The Job Properties window

There are three steps in the job. These are similar to the steps in the snapshot agent job:

• Step 1 - Log Reader Agent startup message: You can double-click on step 1 to open its properties:

Figure 2.11: The Job Properties window – Step 1

This step runs the sp_MSadd_logreader_history procedure to log the startup message in the MSlogreader_history table in the distribution database.

• Step 2 - Run agent: In the Job Step Properties window, you can click on Next to navigate to the properties of step 2:

Figure 2.12: The Job Properties window – Step 2

This step calls the logread.exe executable (type: Replication Transaction-Log Reader) with the parameters specified under the Command section. Observe that the parameter values are the same as the ones we provided when configuring the replication.

Note

Know that logread.exe can be run separately from the command line by giving the same parameters as given in the log reader agent job. This is similar to running the snapshot agent from the command prompt as we did in Lesson 1, Getting Started with SQL Server HA and DR.

logread.exe accepts a lot more parameters than what's specified in the job. However, for all the parameters that aren't specified, the default value is considered. The important parameters are covered later in this book.

Note

To understand the other parameters of logread.exe, refer to https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-log-reader-agent?view=sql-server-2017.

• Step 3 - Detect nonlogged agent shutdown: In the Job Step Properties window, you can click on Next to navigate to the properties of step 3:

Figure 2.13: The Job Properties window – Step 3

This step uses the system-stored procedure sp_MSdetect_nonlogged_shutdown to check if the agent is shut down without logging any message to the MSlogreader_history table. It then checks and logs a relevant message for the agent being shut down to the MSlogreader_history table.

Distributor Agent

The distributor agent applies the transactions from the distribution database to the subscriber. The distributor agent job runs the distrib.exe executable process with the parameter values we provided when configuring the replication.

Let's look at the distributor agent job. You can connect to the subscriber server in the Object Explorer and then go to SQL Server Agent | Jobs. Under the jobs, look out for the job with the following naming convention: [Publisher Server]-[Published database]-[PublicationName]-[Subscriber Server]-[Subscriber Database]-[UniqueIdentifier].

The distribution agent details are stored in the MSdistributor_agents table in the distribution database (at the publisher).

You can also get the name from Exercise 2: Creating a Subscription:

Figure 2.14: Our subscription job name

The job name will differ on your system:

Figure 2.15: Selecting the job

You can double-click on the job to open its properties. In the Job Properties window, you can select the Steps page from the left-hand pane:

Figure 2.16: The Job Properties window

Double-clicking on the Run agent step opens its properties:

Figure 2.17: The Job Properties window – Step 1

Observe that this step calls the distrib.exe executable (type: Replication Distributor) with the parameter values, as specified under the Command textbox. The parameter values are the same as provided when configuring the replication.

distrib.exe accepts a lot more parameters than shown here. For all the other parameters that are relevant and have not been defined explicitly, the default value is considered. The important parameters are covered later in this lesson.

Note

To understand the other parameters of distrib.exe, refer to https://docs.microsoft.com/en-us/sql/relational-databases/replication/agents/replication-distribution-agent?view=sql-server-2017.

Exercise 9: Modifying the Existing Publication

You can modify the properties of an existing publication or subscription in a similar way to snapshot replication, as discussed in the previous lesson.

A common modification to the publication being done is to add or remove an article to or from the publication. Let's look at how we can add/drop an article to/from the publication in transactional replication. In this exercise, we'll learn to add/drop articles to/from an existing publication:

1. Run the following query in the publisher database to get the details of the articles that are replicated:

   USE AdventureWorks

   GO

   sp_helparticle @publication = 'AdventureWorks-Tran_Pub'

   You should get the following output:

   

   Figure 2.18: Replicated articles

   There is a total of 81 replicated articles in the publication.

   In this exercise, we'll drop the ErrorLog article from the publication. Make sure that the table exists in the publication by checking the preceding output.

2. To drop the ErrorLog table from the publication, we first have to drop it from the subscription. Connect to the publisher database and execute the following script to drop the ErrorLog table from the subscription:

   USE AdventureWorks

   GO

   sp_dropsubscription

   @publication = 'AdventureWorks-Tran_Pub',

   @article = 'ErrorLog',

   @subscriber = 'WIN2012R2\SQL2014',

   @destination_db = 'AdventureWorks';

   The sp_dropsubscription procedure removes the subscription for a particular article or publication. You should modify the value of the @subscriber parameter as per your environment.

3. The next step is to drop the article from the publication. Execute the following query to drop the article from the publication:

   EXEC sp_droparticle

        @publication = 'AdventureWorks-Tran_Pub',

        @article = 'ErrorLog',

        @force_invalidate_snapshot = 1;

   The sp_droparticle procedure removes the articles from the publication. If you don't set the @force_invalidate_snapshot parameter to 1, you will get the following error:

   Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 101 [Batch Start Line 11] Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

   This is because we already generated the snapshot when configuring transactional replication. If we don't invalidate the existing snapshot, the same snapshot will be applied to any new subscribers. It is therefore recommended to generate the snapshot after removing an article from the publication.

   Removing an article from the publication doesn't drop it from the publisher or the subscriber database. This will only stop replicating the schema and the data changes from the publisher to the subscriber.

4. To confirm this, execute the following query on the publisher database:

   sp_helparticle

   @publication = 'AdventureWorks-Tran_Pub',

   @article = 'ErrorLog';

   You should get the following output:

   

   Figure 2.19: Confirmation of removing an article

   If you provide an article that exists in the publication, the query will list its properties.

   Note

   Make sure that you check referential integrity before removing the article. For example, if you remove a parent table from the replication, then you may end up breaking the replication when a new row is added to the parent table and a new row is added to the child table that refers to the new row.

   When this change is replicated to the subscriber, it will break because the parent table isn't replicated, and it won't have that new row that was added to the publisher database.

5. Now, let's add an article to an existing publication.

   Execute the following query at the publisher database to verify the immediate_sync and allow_anonymous property values for the publication:

   USE AdventureWorks

   GO

   sp_helppublication

     @publication = 'AdventureWorks-Tran_Pub'

   You should get the following output:

   

   Figure 2.20: Output of adding an article

   The immediate_sync and allow_anonymous values are set to 1, as configured earlier in Exercise 1: Creating a Publication.

   The value for these properties should be set to 0 (false) so as not to generate the snapshot for all the articles when adding a new article. The value of false or 0 will result in the generation of the snapshot for the new article only.

6. Execute the following query to disable the immediate_sync and allow_anonymous properties:

   USE AdventureWorks

   GO

   EXEC sp_changepublication

   @publication = 'AdventureWorks-Tran_Pub',

   @property = N'allow_anonymous',

   @value = 'false'

   GO

   EXEC sp_changepublication

   @publication = 'AdventureWorks-Tran_Pub',

   @property = N'immediate_sync',

   @value = 'false'

   GO

   The sp_changepublication procedure sets the value of the mentioned properties to false. This can also be done from the GUI from the publication properties. You can verify if the property values have been correctly set by running the query in step 1 again.

   You should get the following output:

   

   Figure 2.21: Output of disabling the immediate_sync and allow_anonymous properties

7. Execute the following query to add the ErrorLog article to the publication:

   USE AdventureWorks

   GO

   EXEC sp_addarticle

         @publication = 'AdventureWorks-Tran_Pub',

         @article = 'ErrorLog',

         @source_object = 'ErrorLog',

         @force_invalidate_snapshot=1

   GO

   The sp_addarticle procedure adds the new article to the publication. This is the same as adding the articles to the publication when creating the publication. The difference is the @force_invalidate_snapshot parameter. This parameter has to be set to 1 so as to force the change and invalidate the existing snapshot. The new snapshot has to be generated - this includes the new article.

8. Execute the following command to refresh the subscription so as to include the new article that was added in the previous step:

   USE AdventureWorks

   GO

   EXEC sp_refreshsubscriptions 'AdventureWorks-Tran_Pub'

   GO

   The stored procedure sp_refreshsubscriptions adds subscriptions for the newly added articles in the publication for all subscriptions. This procedure is only valid for pull subscriptions.

   For push subscriptions, the following query needs to be run to include the new article to the subscription on the publisher database:

   EXEC sp_addsubscription

   @publication = 'AdventureWorks-Tran_Pub',

   @subscriber = 'Neptune\SQL2014',

   @destination_db = 'AdventureWorks',

   @reserved='Internal'

9. The next step is to run the snapshot and generate the snapshot for the new article. Execute the following query to run the snapshot agent. You can also use the GUI to run the snapshot agent, as explained in Lesson 1, Getting Started with SQL Server HA and DR:

   USE AdventureWorks

   GO

   DECLARE @jobname NVARCHAR(200)

   SELECT @jobname=name FROM [distribution].[dbo].[MSsnapshot_agents]

   WHERE [publication]='AdventureWorks-Tran_Pub' AND [publisher_db]='AdventureWorks'

   Print 'Starting Snapshot Agent ' + @jobname + '....'

   -- Start the snapshot agent to generate the snapshot

   EXECUTE msdb.dbo.sp_start_job @job_name=@jobname

   To view the snapshot agent history, locate the snapshot agent job under the SQL Server Agent | Jobs node. Right-click on the job and then select View History:

   

   Figure 2.22: The snapshot agent history

   Observe that the snapshot is generated only for one article.

10. Execute the following query to set the immediate_sync and allow_anonymous properties' values back to true:

    USE AdventureWorks

    GO

    EXEC sp_changepublication

    @publication = 'AdventureWorks-Tran_Pub',

    @property = N'immediate_sync',

    @value = 'true'

    GO

    EXEC sp_changepublication

    @publication = 'AdventureWorks-Tran_Pub',

    @property = N'allow_anonymous',

    @value = 'true'

    GO

Stopping Transactional Replication

Stopping or pausing transactional replication can be done by either stopping the log reader agent or the distribution agent. When the log reader agent is stopped, the transaction log is not scanned for transactions and transactions aren't inserted into the distribution database. This will cause the transaction log to grow, even if you have scheduled regular log backups. This is because the log backups don't remove the transactions from the transaction log that are marked for replication.

As the log reader agent isn't running, transactions that are marked for replication aren't scanned and the transaction log grows. It is therefore not recommended to stop the log reader agent.

Moreover, stopping the log reader agent stops the transactions from being replicated to all subscribers for the particular publication.

When the distributor agent is stopped, the transactions from the distribution database aren't applied to the subscribers. This results in the distribution database growing in size. However, this doesn't affect the publisher database, unlike stopping the log reader agent, as explained earlier.

Moreover, for pull subscriptions, stopping the distribution agent for a particular subscriber only stops the replication for that subscriber and not all subscribers for the particular publication.

Stopping the Log Reader Agent

The log reader agent can be either stopped by stopping the log reader agent job or by stopping the synchronization (from SSMS), which eventually stops the log reader agent job.

You can execute the following query on the publisher server to stop the log reader agent job:

DECLARE @Publisher_db nvarchar(100) =  N'AdventureWorks'

DECLARE @jobname NVARCHAR(200)

SELECT @jobname=name FROM [distribution].[dbo].[MSlogreader_agents]

WHERE [publisher_db]=@Publisher_db

Print 'Stopping Log Reader Agent ' + @jobname + '....'

EXECUTE msdb.dbo.sp_stop_job @job_name=@jobname

/* Execute below query to start the job */

EXECUTE msdb.dbo.sp_start_job @job_name=@jobname

The preceding snippet queries the MSlogreader_agents table to get the name of the log reader agent job. It then stops the job by calling the sp_stop_job stored procedure.

Note

You can verify that the job has been stopped by checking the job's status in Job Activity Monitor under the SQL Server Agent node in SQL Server Management Studio.

Another way to stop the log reader agent is by stopping the log reader agent synchronization from SSMS:

1. You can connect to the publisher server in the Object Explorer and then go to Replication Node | Local Publications.
2. You can then right-click on the publication and select the View Log Reader Agent Status option from the context menu:
   

   Figure 2.23: The View Log Reader Agent Status option

3. If you ran the previous query to stop the log reader agent, then the log reader agent status will be as shown here:

Figure 2.24: The View Log Reader Agent Status window

To start the log reader agent, you can click on Start. This will start the log reader agent job and the status will change, as shown here:

Figure 2.25: Starting the log reader agent

Stopping the Distribution Agent

Similar to the log reader agent, to stop the distribution agent, you can connect to the subscriber server and execute the following query:

USE AdventureWorks

GO

sp_stoppullsubscription_agent

  @publisher='Neptune\SQL2016',

  @publisher_db='AdventureWorks',

  @publication='AdventureWorks-Tran_Pub'

The stored procedure sp_stoppullsubscription_agent finds and stops the distribution agent job.

You should get the following output:

Figure 2.26: Output of stopping the distribution agent

To start the distribution agent, execute the following query:

USE AdventureWorks

GO

sp_startpullsubscription_agent

  @publisher='Neptune\SQL2016',

  @publisher_db='AdventureWorks',

  @publication='AdventureWorks-Tran_Pub'

The stored procedure sp_startpullsubscription_agent finds and starts the distribution job on the subscriber.

Another way to stop the distribution agent is to stop the synchronization from SSMS:

1. You can connect to the subscriber server in the Object Explorer and then expand Replication | Local Subscription. You can then right-click on View Synchronization Status from the context menu:
   

   Figure 2.27: The View Synchronization Status option

2. You can start and stop the distribution agent from the View Synchronization Status window:

Figure 2.28: The View Synchronization Status window

Exercise 10: Removing Transactional Replication

Removing transactional replication consists of the following steps:

1. Removing the subscription for the publication to be deleted
2. Removing the publication
3. Disabling the database for publication (optional)
4. Removing replication metadata from the subscriber (optional)

Let's remove the replication that was configured in this lesson by following the aforementioned steps:

1. Execute the following query on the publisher database to remove the subscription:

   DECLARE @publication AS sysname;

   DECLARE @subscriber AS sysname;

   SET @publication = N'AdventureWorks-Tran_Pub';

   SET @subscriber = 'Neptune\SQL2014';

   USE [AdventureWorks]

   EXEC sp_dropsubscription

     @publication = @publication,

     @article = N'all',

     @subscriber = @subscriber;

   GO

   You should get the following output:

   

   Figure 2.29: Output of removing the subscription

2. Execute the following query on the publication database to remove or drop the publication:

   DECLARE @Database AS sysname;

   DECLARE @Publication AS sysname;

   SET @Database = N'AdventureWorks';

   SET @Publication = N'AdventureWorks-Tran_Pub';

   -- Remove Publication

   USE [AdventureWorks]

   EXEC sp_droppublication

     @publication = @publication;

   This query removes the publication for the AdventureWorks database that was created earlier. You may verify this by going to SSMS | Object Explorer | Replication | Local Publication.