Category Archives: Replication

How to add article in Transactional Replication

Let’s consider a scenario we have a database in which we have 3 tables 2 of them are already added in replication and we want to add a third table in publication.

This is more important when we have a larger database and we don’t want to reinitialize the replication.

To avoid complete snapshot of the Publisher database and to avoid re-initialization we need to make some changes in the existing replication. We need to set 2 properties to False as shown below

  1. allow_anonymous
  2. Immediate_sync

Execute below commands

Now add table T3 again from Publisher properties and press ok.

  1. USE
  2. GO
  3. EXEC sp_changepublication @publication = ‘PubDB’,
  4. @property = ‘allow_anonymous’,
  5. @value = ‘false’
  6. GO
  7. USE
  8. GO
  9. EXEC sp_changepublication @publication = ‘PubDB’,
  10. @property = ‘immediate_sync’,
  11. @value = ‘false’
  12. GO

addtable3

Now add the new articles in existing publication

addtable3_pub

Now start Snapshot Agent

startsnapshot

Now you can notice that it only creates a snapshot of one article instead of all articles, now start log reader agent if it’s in the stopped state.

snapshot

Now as you can see a snapshot was generated but only for one article, so this article will be replicated to all subscriber without impacting existing replication.

Don’t forgot to execute below command at last to enable the disabled properties.

  1. Use
  2. GO
  3. EXEC sp_changepublication @publication = ‘PubDB’,
  4. @property = ‘allow_anonymous’ ,
  5. @value = ‘True’
  6. GO
  7. Use
  8. GO
  9. EXEC sp_changepublication @publication = ‘PubDB’,
  10. @property = ‘immediate_sync’ ,
  11. @value = ‘True’
  12. GO

replsyncoption

Now you can verify the article on all your subscribers

If you want to know more about immediate sync command follow below link

  1. Immediate_sync

 

 

Advertisements

One Way Merge Replication

Recently I received a requirement from application team to configure one way replication although it sound weird but yes this is possible and Microsoft provides some parameters to tweak normal behavior of Merge Replication.

Before we proceed further let’s discuss about normal behavior of Merge Replication and when to use Merge Replication.

Merge replication is one of the modes available in the Microsoft SQL Server for distributing data to various servers from a primary server. Merge replication is one of three types of replication, along with snapshot replication and transactional replication. Which type is used depends on the database’s needs, how frequently changes are made to it and the SQL Server version being employed.

Merge

https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx

Merge replication is the most complex type of replication because it allows both publisher and subscriber to independently make changes to the database. In this scenario, it is debatable whether the publisher is strictly the primary server, because other servers can also make changes to the data. At any rate, the changes are then synchronized by merge agents that sit on both servers, as well as by a predetermined conflict resolution mechanism in case of clashing data changes. Such clashes may arise because merge replication does not require a real-time network connection between the publisher and the subscriber, which raises the very real possibility of one server changing data, and another server later changing the very same data to a different value.
Here is an article which explains about conflict detection and resolution.

https://technet.microsoft.com/en-us/library/ms151749(v=sql.105).aspx

Merge replication is commonly used by laptop and other mobile users who cannot be constantly connected to the publisher, but still need to carry around a copy of the database that they can make changes to.

Are there any disadvantages for Merge Replication?

  • It takes lot of time to replicate and synchronize both ends.
  • There is low consistency as lot of parties has to be synchronized.
  • There can be conflicts while merge replication if the same rows are affected in more than once subscriber and publisher. There is conflict resolution in place but that adds complication.

Why there is a need for one way merge are there any advantages?

Consider a scenario where application needs lesser number of jobs less administration of jobs, distribution database and want to get rid of Publisher to Distributor latency and specifically for handheld\mobile devices\Read only data stores who cannot be constantly connected to the publisher, but still need to carry around a copy of the database.

It is also quite possible that you need this kind of setup as there are some constraint adding primary keys in your database\application and still you need some specific set of data proliferation to other sites. But you have t be careful on this as this will add additional RowGuid columns in table, and that you have to take care on front end.

From the advantages point of view I only see that with this One Way Merge you can get rid of Log Reader Agent, you will not see issues like log reader agent is stuck while scanning thousands of VLF (virtual Log files) when log grows too much in certain scenario and you don’t have to bother much about conflict resolution.

How to configure One Way Merge Replication?

There are 2 ways to achieve this kind of setup where we want data to be pushed from Publisher to Subscriber.

Method 1

The First way is at the article level where we can decide that a merge table article is download-only with an option whether you want to make changes at the subscriber but they will not be uploaded to the Publisher and another is changes are not allowed at the subscriber at all. This is achieved by using below property.

When adding an article, there is an option to define the subscriber_upload_options“:

sp_addmergearticle @subscriber_upload_options= subscriber_upload_options

This defines restrictions on updates made at a Subscriber (with a client subscription). The parameter “subscriber_upload_options” is a tinyint, and can have one of the following values.

0 No restrictions. Changes made at the Subscriber are uploaded to the Publisher.
1 Changes are allowed at the Subscriber, but they are not uploaded to the Publisher.
2 Changes are not allowed at the Subscriber.

@subscriber_upload_options =0

@subscriber_upload_options =1 

@subscriber_upload_options =2 

 

Merge1

Hence to achieve this functionality we have an option to choose 1 (Changes are allowed at the Subscriber, but they are not uploaded to the Publisher) or  2 (Changes are not allowed at the Subscriber).

 

Merge3Merge2

 

@subscriber_upload_options =1  is defined as “download only, but allow subscriber changes”. In this option there will be no triggers at the subscriber so there will be no firing of triggers to unnecessarily log metadata at the subscriber, which makes both subscriber data changes and the subsequent synchronization significantly faster.

@subscriber_upload_options =2 disallows all subscriber changes. In this case there is a special trigger – MSmerge_downloadonly*  which will rollback any attempt to change subscriber data.

Msg 20063, Level 16, State 1, Line 1
Table into which you are trying to insert, update, or delete data has been marked as read-only. Only the merge process can perform these operations.

Merge4

Once you are done with the configuration of Publication add subscriber where you want to download these articles and subsequent transactions with subscription type property as “Client”.

Merge5

This is how you can achieve one way merge replication by changing the property of published articles sp_addmergearticle @subscriber_upload_options= subscriber_upload_options

Method 2

There is another way by which you change the normal behavior of merge replication and force the merge replication into Unidirectional Merge. This can be achieved by changing normal behaviour of Merge Agent using property “EXCHANGETYPE” 

The value of -EXCHANGETYPE determines the direction of merge replication changes. This can be done by manually editing the Merge agent job step by adding -EXCHANGETYPE parameter with value 2

  • UploadOnly (1): Only changes originating at the Subscriber are merged with the Publisher. The Publisher’s changes stay in the Publisher. Use a value of 1 in your agent properties.
  • DownloadOnly (2): Only changes originating at the Publisher are merged with the Subscriber. Use a value of 2 in your agent properties.
  • Bi-Directional (3 – Default): Changes originating at the Publisher and Subscriber are merged. Use a value of 3 in your agent properties.
UPLOAD 1 Only merge Subscriber changes with the Publisher.
DOWNLOAD 2 Only merge Publisher changes with the Subscriber.
BIDIRECTIONAL 3 Merge all changes between the Publisher and Subscriber (default).

As soon as we configure with parameter with value of 2 it means that changes to a replicated article at the subscriber are not prohibited, are recorded in the merge metadata tables via merge triggers, and are subsequently filtered out when the merge agent synchronizes. This means there may be a huge amount of metadata unnecessarily recorded, slowing down data changes and synchronization.

 

Merge6

Conclusion: One Way or Unidirectional replication can be achieved easily in Merge Replication with minor tweaking in SQL 2005 and later version even at very granular level.

sp_addmergearticle @subscriber_upload_options =1 parameter defines restrictions on updates made at a subscriber. The parameter value of 1 is described as download only, but allow subscriber changes and seems equivalent to the -EXCHANGETYPE = 2 setting mentioned previously, but in the SQL Server 2005 case there are no triggers at all on the subscriber table. Another distinction is that this setting is made at the more granular article level rather than set for the entire publication. This means that although the -EXCHANGETYPE and sp_addmergearticle methods are logically equivalent, the implementation has become much more sophisticated in SQL Server 2005 and later versions. Triggers that unnecessarily log metadata at the subscriber are no longer fired; therefore both subscriber data changes and the subsequent synchronization are significantly faster.

@subscriber_upload_options =2 disallows all subscriber changes. In this case there is a special trigger MSmerge_downloadonly* which will rollback any attempt to change subscriber data. 

However you have to be very sure while using this option as this option is depreciated feature list and about Re-initialization of subscription with this kind of setup.

Deprecated Features in SQL Server Replication

I would highly appreciate feedback and comments on this article and would love to know more about any advantages you are getting from this kind of setup if you are already using this Unidirectional Merge.

 

 

 

Unable to add new Article in Replication

I encounter an interesting issue on Transaction Replication where I was unable to add a newly added table into some subscriber. My replication setup consists of 1 Publisher and 7 subscribers.

I followed the below step to add a new article in my existing Publication which is working fine in my Development environment which is almost similar to Production.

  1. To avoid complete snapshot of the Publisher database I set allow_anonymous and Immediate_sync to False by default its set to TrueEXEC sp_changepublication @publication = ‘PubDB’,
    @property = ‘allow_anonymous’,
    @value = ‘false’
    GO
    EXEC sp_changepublication @publication = ‘PubDB’,
    @property = ‘immediate_sync’,
    @value = ‘false’
    GO
  2. Added new table into exiting publication properties
  3. Start Snapshot Agent
  4. Snapshot Agent created a Snapshot for one article and the scripts can be seen in repl data folderSnapshot_generation
  5. Started Distribution Agent (Ignore if its already running) to deliver this snapshot on the subscriber and it should look like this as shown belowSnapshotDelivered1

Now here I noticed that on 3 of the sites out of 7 Distribution Agent did not apply snapshot on the subscriber and after some time I noticed that the folder gets deleted for this snapshot containing schema, index ,BCP file etc.

This is because my distribution agent cleanup job was configured to run after every 5 min to control the size of distribution database and shared drive containing snapshots.

EXEC dbo.sp_MSdistribution_cleanup  @min_distretention = 0, @max_distretention = 72

Initially I focused on one of the properties immediate sync = TRUE, which I believe could be responsible for deleting the snapshot before applying it on the subscriber.

But after looking into my distribution agent cleanup job properties and after referring to Article my assumption start getting wrong here the difference is that in my case its set to @min_distretention = 0 so do not wait and delete the transaction once it delivered to subscriber case

If “immediate sync” = TRUE, do not keep transaction EVEN IF they have been replicated to the subscriber but delete any transaction older the 72 hours.  Metadata that is younger than 72 hours, but older than 4 hours will be kept. This is conservative.

If “immediate sync” = FALSE, then keep transaction for at least 4 hours but if the transaction has already been replicated (distributed) to the subscriber go ahead and delete EVEN IF not older then 72 hours. Metadata that is younger than 72 hours, but older than 4 hours may be deleted quite aggressively.

Later on I focused on Replication Script and noticed that there is a small change in the Subscription properties of these 3 subscribers as shown below but the impact is big.

@sync_type = N’replication support only’

subprop

This property allows you to manually synchronize your data and schema on your publisher and your subscriber, but then the support objects needed to make replication work are pushed automatically to the subscriber. The benefit is that you can still take advantage of the replication tools to setup replication, but you also have more control over getting your data and schema in sync.  This is great if you already have a database in place and you only want to setup replication. Instead of having to drop and recreate the tables, this option will just push out the objects needed for replication and not affect your existing schema or data.

But you will not be able to add new article in publication once you configure subscriber with this option and this is by design “Replication Support Only” 

Property

Work Around:-

There is no direct way to fix this as this property and this property is not available in GUI and can not be modified, so you have to drop and recreate the subscription with sync_type = N’automatic’. But in a mission critical application this is not possible so one of the approach is to manually apply the script on subscriber. If you have the script copied from the repl data folder then execute that script on all the subscriber which are having this problem.

legal

Important:- While you are manually adding this article ensure that no transaction are made into this table else you will encounter errors like “Object Note Found” or “Row Not Found”.

Resolution:-

  • Create a new database with same name on another SQL instance and make changes in web.config to point to this database, meanwhile you can reconfigure your subscriber using re-initialization method with sync_type = N’automatic’
  • If your application is accessing subscriber database using Synonyms from other database then create a database with same name (using restoration)on same SQL instance and point synonym to this database and meanwhile you can reconfigure your subscriber using re-initialization method with sync_type = N’automatic’

When this option was enabled on my subscription and why?

Well there is no direct way to identify when this configuration was done so you have to rely on distribution agent creation job date or check for subscriber database restoration date. This option might give you some idea if  in past the replication was synchronized with this option to avoid any down time.