locked
Setting up P2P with RMO - stored procedures not created RRS feed

  • Question

  • Hello, 

    I try to setup Peer-to-Peer replication with RMO, but ultimately it is not working because INSERT/UPDATE/DELETE stored procedures are not created. If anyone know what I am missing, I would much appreciate a response. Here's the code I use:

    Setting up publication and articles:

    using ( var connection = new PublicationContext( db ) )
    			{
    				var publicationDb = new ReplicationDatabase( connection.DatabaseName, connection );
    				if ( !publicationDb.LoadProperties() )
    				{
    					throw new ApplicationException( "The database doesn't exist." );
    				}
    				if ( !publicationDb.EnabledTransPublishing )
    				{
    					publicationDb.EnabledTransPublishing = true;
    				}
    
    				if ( !publicationDb.LogReaderAgentExists )
    				{
    publicationDb.LogReaderAgentPublisherSecurity.WindowsAuthentication = true;
    					publicationDb.CreateLogReaderAgent();
    				}
    				var publication = connection.Publication;
    				publication.Type = PublicationType.Transactional;
    				publication.Status = State.Active;
    				publication.ContinueOnConflict = true;
    				publication.Attributes = PublicationAttributes.AllowPull | PublicationAttributes.AllowPush | PublicationAttributes.EnabledForPeerToPeer
    				                         | PublicationAttributes.ImmediateSync | PublicationAttributes.IndependentAgent
    				                         | PublicationAttributes.SnapshotInDefaultFolder | PublicationAttributes.AutomaticGenerateSyncProcedures
    				                         | PublicationAttributes.AllowInitializationFromBackup;
    
    				publication.SetPeerConflictDetection( true, peerId );
    
    				if ( !publication.IsExistingObject )
    				{
    					publication.Create();
    				}
    
    				foreach ( string table in tables )
    				{
    					var article = new TransArticle
    						{
    							ConnectionContext = connection,
    							Name = table,
    							DatabaseName = connection.DatabaseName,
    							SourceObjectName = table,
    							SourceObjectOwner = "dbo",
    							DestinationObjectName = table,
    							DestinationObjectOwner = "dbo",
    							PublicationName = PublicationName,
    							CommandFormat = CommandOptions.BinaryParameters | CommandOptions.IncludeInsertColumnNames,
    							SchemaOption = CreationScriptOptions.PrimaryObject
    							               | CreationScriptOptions.CustomProcedures
    							               | CreationScriptOptions.Identity
    							               | CreationScriptOptions.KeepTimestamp
    							               | CreationScriptOptions.ClusteredIndexes
    							               | CreationScriptOptions.NonClusteredIndexes
    							               | CreationScriptOptions.DriPrimaryKey
    							               | CreationScriptOptions.UserTriggers
    							               | CreationScriptOptions.DriChecks
    							               | CreationScriptOptions.DriDefaults
    							               | CreationScriptOptions.Collation
    							               | CreationScriptOptions.DriUniqueKeys
    							               | CreationScriptOptions.MarkReplicatedCheckConstraintsAsNotForReplication
    							               | CreationScriptOptions.MarkReplicatedForeignKeyConstraintsAsNotForReplication
    							               | CreationScriptOptions.Schema
    						};
    
    					if ( !article.IsExistingObject )
    					{
    						article.Create();
    					}
    				}
    			}

    Setting up subscriptions:

    using ( var connection = new PublicationContext( publicationDb ) ) { var publication = connection.Publication; using ( var subscriptionConnection = new ConnectionContext( subscriptionDb ) ) { if ( pullSubscription ) { var subscription = CreatePullSubscription( subscriptionConnection, publication ); subscription.CreateSyncAgentByDefault = true; subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Continuously; subscription.Create(); if ( !publication.EnumSubscriptions().Cast<TransSubscription>() .Any( s => s.SubscriberName == subscriptionConnection.ServerInstance && s.SubscriptionDBName == subscription.DatabaseName ) ) { publication.MakePullSubscriptionWellKnown( subscriptionConnection.ServerInstance, subscription.DatabaseName, SubscriptionSyncType.ReplicationSupportOnly, TransSubscriberType.ReadOnly ); } } else { var subscription = CreatePushSubscription( subscriptionConnection, publication ); subscription.AgentSchedule.FrequencyType = ScheduleFrequencyType.Continuously; subscription.Create(); } } }

    Where CreatePullSubscription and CreatePushSubcription are defined as follows:

    private static TransPullSubscription CreatePullSubscription( ConnectionContext connection, TransPublication publication )
    		{
    			return new TransPullSubscription
    				{
    					ConnectionContext = connection,
    					PublisherName = publication.ConnectionContext.ServerInstance,
    					PublicationName = PublicationName,
    					PublicationDBName = publication.DatabaseName,
    					DatabaseName = connection.DatabaseName
    				};
    		}
    
    		private static TransSubscription CreatePushSubscription( ConnectionContext connection, TransPublication publication )
    		{
    			return new TransSubscription
    				{
    					ConnectionContext = publication.ConnectionContext,
    					SubscriberName = connection.ServerInstance,
    					PublicationName = PublicationName,
    					DatabaseName = publication.DatabaseName,
    					SubscriptionDBName = connection.DatabaseName,
    					SyncType = SubscriptionSyncType.ReplicationSupportOnly
    				};
    		}

    When I setup P2P replication on exactly the same databases and their state (using VM snapshot) through Sql Server Management Studio, the stored procedures are generated. 

    Best regards,

    Tadeusz Dracz


    Tadeusz Dracz

    Wednesday, December 4, 2013 2:12 PM

Answers

  • It eventually turned out to be a problem with the Distributor connection (I noticed errors in the related job history). Once that was fixed, all the stored procedures were successfully auto-generated.

    Nevertheless, thank you for looking into this.



    Tadeusz Dracz

    • Marked as answer by Tadeusz Thursday, December 5, 2013 3:26 PM
    Thursday, December 5, 2013 3:25 PM

All replies

  • This doesn't look like a valid deployment of P2p. P2p should start off with a backup. You create the publication, create the backup, restore the backup on the subscriber and then create the subscriptions. While creating the subscription you would use the backup file on the publisher.

    There is a way to do it without using a backup to deploy the subscription, but you need to quiesce your system between the time you create the publication and the subscription and it does not look like you are doing this either.

    Are you doing any of these?


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, December 4, 2013 5:37 PM
    Answerer
  • Yes. I take a backup, restore and then create publications and the system is quiesced (I didn't include all the code). Also with the restored database backup that was taken before publication, SSMS performs P2P setup just fine and doesn't complain about anything, so the problem seems to be elsewhere.

    Tadeusz Dracz



    • Edited by Tadeusz Wednesday, December 4, 2013 8:54 PM
    Wednesday, December 4, 2013 8:52 PM
  • You have the sequence wrong. Create the publication, do the backup, restore it on the subscriber, then when you create the subscriptions you need to tell it where the backup file is.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, December 4, 2013 9:54 PM
    Answerer
  • Changing of the sequence didn't help. Also as I already said, the SSMS sets up the topology just fine with my sequence and the required stored procedures are auto-generated.

    I don't specify where the backup file is as I implement with RMO the equivalent of the following option from the P2P topology wizard: "I created the peer database manually, or I restored a backup of the original publication database (...)". The "Browse" button to specify the backup file is disabled there in the Wizard when that option is selected as backup file is not needed in that case.


    Tadeusz Dracz

    Thursday, December 5, 2013 1:41 AM
  • It eventually turned out to be a problem with the Distributor connection (I noticed errors in the related job history). Once that was fixed, all the stored procedures were successfully auto-generated.

    Nevertheless, thank you for looking into this.



    Tadeusz Dracz

    • Marked as answer by Tadeusz Thursday, December 5, 2013 3:26 PM
    Thursday, December 5, 2013 3:25 PM