How to add filtered table in replication - SQL Server by serverku

I wrote some of the articles related to replication e.g. adding tables, stored procedures, views and functions in transactional replication. Now I am sharing one another script to add a filtered table in the transactions table. In script I have applied filters on CreatedDate column SampleTable table. It helps to fit the need of only required rows at subscriber and reduce the overhead of data transfer.

Script :

USE [PublisherDB]
GO

-- Adding the transactional articles
EXEC sp_addarticle 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @source_owner = N'dbo', 
 @source_object = N'SampleTable', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x000000000803509F, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'SampleTable', 
 @destination_owner = N'dbo', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_dboSampleTable]', 
 @del_cmd = N'CALL [sp_MSdel_dboSampleTable]', 
 @upd_cmd = N'SCALL [sp_MSupd_dboSampleTable]', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000'''

-- Adding the article filter
EXEC sp_articlefilter 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @filter_name = N'FLTR_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate] between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1

-- Adding the article synchronization object
EXEC sp_articleview 
 @publication = N'FilteredTables', 
 @article = N'SampleTable', 
 @view_name = N'SYNC_SampleTable_1__51', 
 @filter_clause = N'[CreatedDate]  between ''2014-04-01 00:00:00.000'' and ''2014-04-15 00:00:00.000''', 
 @force_invalidate_snapshot = 1, 
 @force_reinit_subscription = 1
GO

UI :

Hope you like this post. Have a great day!

Add new articles in existing publications without Reinitialize All Subscriptions - SQL Server Replication by serverku

The last time we saw a script to add tables, stored procedures, functions and indexed views in a publication and hope you may like that post. Let us continue here one more addition to post and see how can we add a new article in existing publication without reinitialize all subscriptions in transaction replication. Following are the steps which drive to finish this post,
 
Step  1 :
First add articles through the scripts provided in an earlier post, Here for samples we will add tables in existing publication and run on publisher database.

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

Step 2 :
After running above script in publisher database, run following script in the publisher database too.

USE PublisherDB
GO
EXEC sp_refreshsubscriptions '<Publication Name>'
GO

Step 3 :
Final completion both above steps we will just start snapshot agent for that publication from Replication Monitor.

Go to Replication Monitor
Select publication,
Move to Agents tab,
Right click on snapshot agent and start agent. 

You will see number of added articles in last action message there after completion of snapshot agent. This is just I want to share with you and maybe help you a lot. Thanks for reading this post and may you like to.

Script to add articles in publication - SQL Server transactional replication by serverku

Recently I wrote a post which having a script to get articles details added in replication for all publications. Today I am sharing a script to add articles in the existing publication of transactional replication, which we can also add through user interface of publication property. Here I am sharing a query to add tables, views, stored procedures, indexed views and functions.

1. Table :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'TableName', 
 @source_owner = N'SchemaName', 
 @source_object = N'TableName', 
 @type = N'logbased', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x00000000080350DF, 
 @identityrangemanagementoption = N'manual', 
 @destination_table = N'TableName', 
 @destination_owner = N'SchemaName', 
 @status = 24, 
 @vertical_partition = N'false', 
 @ins_cmd = N'CALL [sp_MSins_SchemaNameTableName]', 
 @del_cmd = N'CALL [sp_MSdel_SchemaNameTableName]', 
 @upd_cmd = N'SCALL [sp_MSupd_SchemaNameTableName]',
 @force_invalidate_snapshot = 1
GO

2. View :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ViewName', 
 @source_owner = N'schemaName', 
 @source_object = N'ViewName', 
 @type = N'view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ViewName', 
 @destination_owner = N'schemaName', 
 @status = 16
GO

3. Stored Procedure :

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'ProcedureName', 
 @source_owner = N'SchemaName', 
 @source_object = N'ProcedureName', 
 @type = N'proc schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'ProcedureName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

4. Indexed View:

USE PublisherDB
GO
EXEC sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'Indexed View Name', 
 @source_owner = N'SchemaName', 
 @source_object = N'Indexed View Name', 
 @type = N'indexed view schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'Indexed View Name', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

5. Function :

USE PublisherDB
GO
exec sp_addarticle 
 @publication = N'PublicationName', 
 @article = N'FunctionName', 
 @source_owner = N'SchemaName', 
 @source_object = N'FunctionName', 
 @type = N'func schema only', 
 @description = N'', 
 @creation_script = N'', 
 @pre_creation_cmd = N'drop', 
 @schema_option = 0x0000000008000001, 
 @destination_table = N'FunctionName', 
 @destination_owner = N'SchemaName', 
 @status = 16
GO

Please note these queries run against publisher server and database. I will add one more post next to add articles in existing publications without initialize whole subscription. So stay tuned for more!

Script to get articles detail - SQL Server Replication by serverku

Before a some months, I shared a one script which is relevant to replication as below, Please have a read here. Sometime we need to have information of articles for reports or to check the article exist or not, in which publication it have and for which subscribers. So this kind of details we can achieve with below script which is run against in publication database,

USE PublisherDB
GO

SELECT sub.srvname, -- Subscriber Server name 
       sub.dest_db, -- Subscriber Database Name
       pub.name, -- Publication name
       art.name, -- Article name
       art.dest_table, -- Published Object name
       art.dest_owner -- Published Schema name
FROM   sysextendedarticlesview art 
       INNER JOIN syspublications pub 
               ON ( art.pubid = pub.pubid ) 
       INNER JOIN syssubscriptions sub 
               ON ( sub.artid = art.artid ) 

GO

Hope this script may be very useful to you and you enjoyed a lot. Have a great day and stay tuned for more!

Script to get transactional replication errors for given subscriptions - SQL Server by serverku

Recently, when I was working with one replication issue which had an error while distributing replicated pending commands to the subscriber. I got the error details from the replication monitor. But here I am sharing one script which will give the same information as replication monitor for a particular subscriber. Please find the following script to get transactional replication errors for giving  subscriber and executes againt distribution database.

USE distribution 
GO

DECLARE @PublisherServer  VARCHAR(50), 
        @PublicationDB    VARCHAR(50), 
        @SubscriberServer VARCHAR(50), 
        @SubscriberDB     VARCHAR(50), 
        @PublicationName  VARCHAR(50) 

SET @PublisherServer = '<Publisher>' 
SET @PublicationDB = '<PublisherDB>' 
SET @SubscriberServer = '<Subscriber>' 
SET @SubscriberDB = '<SubscriberDB>' 
SET @PublicationName = '<Publication>' 

EXEC sp_helpsubscriptionerrors 
  @PublisherServer, 
  @PublicationDB, 
  @PublicationName, 
  @SubscriberServer, 
  @SubscriberDB 

GO 

Replace needed variable's value in above script and run on subscriber side which will give you information on error date, error text and sequence number, etc. We can set up an alert when found any error within the specified time for subscriber. This is just for script to fetch error details for giving subscriber, further post will be have same transactional replication details for all subscribes, so stay tuned for it. Hope you liked this post.

The row was not found at the Subscriber when applying the replicated command - Replication error in SQL Server by serverku

Earlier I wrote a post for the same error at Review of some replication issues and workaround, but it is just overview with some other replication errors. Please read it if you have not visited those posts and hope you will enjoy and help you much. Let me elaborate the error with proper example. For example, I have already created objects and configured replication, just need to use those objects. Let me introduce them,

• Primary database : Test
• Secondary database : Test1
• Replicated table : dbo.sample1

I am using the same server in the example as publisher, distributor and subscriber. First need to check records in tables of both databases.

-- Selecting  records from table of publisher database 
SELECT * 
FROM   test.dbo.sample1 (nolock) 

-- Selecting  records from table of subscriber database 
SELECT * 
FROM   test1.dbo.sample1 (nolock) 

How to raise an error manually?
To raise mention error in tile I will remove one record from a table in subscriber database to create inconsistency, then will update same deleted records and insert one more record in table of publisher database. Let us see what will happen then,

-- Deleting one record from table in subscriber database 
DELETE FROM test1.dbo.sample1 
WHERE  id = 2 

-- Updating same record from table in publisher database 
UPDATE test.dbo.sample1 
SET    name = 'test5' 
WHERE  id = 2 

-- Inserting new record in table in publisher database 
INSERT test.dbo.sample1 
SELECT 4, 
       'test4' 

After running above script we will review replication monitor window and you will see error because it is trying update row at the subscriber side, but it is not exist and therefore it will raise an error. Due to this, new inserted records (id = 4) will not populate at subscriber side.

Solution 
Now we have to do some workaround to get it resolved, but first we need to get missing row details which was deleted at subscriber side. You can see sequence number in the above image from which we can get the same information. Run following script in distribution database and see the output,

USE distribution 

go 

SELECT * 
FROM   dbo.msarticles m 
WHERE  EXISTS (SELECT mc.article_id 
               FROM   msrepl_commands mc 
               WHERE  mc.xact_seqno = 0x0000002200000048000300000000 
                      AND mc.article_id = m.article_id) 

EXEC Sp_browsereplcmds 
  @xact_seqno_start = '0x0000002200000048000300000000', 
  @xact_seqno_end = '0x0000002200000048000300000000' 

(Click on image to enlarge)
The result is clearly showing article and missing row details. Let me apply that missing row at subscriber side and then review replication monitor again.

-- Inserting missing record in table in subscriber database 
INSERT test1.dbo.sample1 
SELECT 2, 
       'test2' 

After inserting above record the issue will get resolved, which you can see in the image below. Missing record and the newly inserted record (id = 4) also applied,

 Hope you enjoyed this post and it will be fine you put your innovative ideas here for any alternative solution and opinion

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up - Issue in SQL Server Replication by serverku

Hope you read my earlier post of "Replication components are not installed on this server" issue, you may like it. One day suddenly replication went to high latency and I clicked it during monitoring. I opened the replication monitor, during analysis I found one message which was showing latency from publisher to distributor due to some issue. The message is as follows,

“Replicated transactions are waiting for next Log backup or for mirroring partner to catch up”.

As per message i checked the transaction log backups were happened or not, checked it and log backups were happening . Finally one option is pending to review and it is mirroring. I checked the status of mirroring  and see the  principal database went to synchronizing mode. May be mirroring went in synchronizing mode due to heavy or so many transactions in the route to apply at mirror database. So replication is waiting  to be synchronized status of principal database. I exactly do not know why replication went on waiting even transaction log backups were happening. I checked online solution and received some of the solutions from here and it suggests following,

1. EXEC sp_replicationdboption 'PublisherDB','sync with backup',false
This means that not need to backed up of all transactions before being delivered to the distribution database. Please visit this option here, which sets a replication database option for the specified database. This stored procedure is executed at the Publisher or Subscriber on any database.

2. Enable trace flag 1448
After this setting the Log Reader Agent can continue replicating changes regardless of the mirroring state. Please read more here.

I never applied these suggestions. Replication and mirroring both are using transaction logs and this could be the reason why replication was in high delay status while mirroring was stuck or in process to synchronize the mirror database. So I turned off mirroring and monitored replication status. Finally replication was succeeded to remove the delay and applied all pending commands. Then I configured to mirror again after doing with replication sync.

Conclusion : We have two options, either to wait for synchronized status of principal database or turned of mirroring, getting replication synced properly and reconfigure mirroring again depends on priority and importance. It will be better to go for correct solution to avoid such issue. But question is here, why replication went on waiting for a synchronized of mirroring even transactions log backups happened while? I would like you to share if received such issue and solution or any opinion which you applied to resolve it. This may help to me and all people facing the same issue.

Replication components are not installed on this server - Error while adding subscriber in replication by serverku

Recently, when I was working on replication task. It has been to just add new subscribers to an existing publication. Some of their added successfully but some of failed due to replication component is missing. Before you go ahead into details I would like to read some other tips and posts related to replication,

1. Review of some replication issues and workaround
2. Finding objects replicated schema/data or schema only published in replication
3. Get Publications, Articles, Distributors and Subscribers for Replication
4. Finding Stored Procedures, Functions and Views published in replication
5. Find the details of publications, subscribers and articles at once in replication

Now moving here to go ahead with this post and the issue which I faced during adding new subscribers to publication. The issue is as the following which was raised as the message,

“Replication components are not installed on this server. Run SQL Server setup again and select the option to install replication.”

This message gives complete and clear information about exact issue and give suggestion too, as what need to do to complete this task. Just need to add that missing component for replication. Install replication component from SQL Server installation and select that component,

Adding feature to an existing instance,

After completing above steps of installation, I was succeeding to add subscribers to a publication. This is the common post, but hope you liked it.