locked
RMO - Publication - Article Issues RRS feed

  • Question

  • When I configure a merge-publication by the management studio gui, I will come to a step called "Article Issues" right after I choose the articles that are going to be published.. It just says that I will have to change my application, because UniqueIdentifiers will be inserted into the tables. After the wizard finished, every table has a new rowguid-column (guid, not null). I recon this is pretty important for the (merge)replication.

    So my question: is there a option in the publication-object for creating rowguid-columns in each article or do I need to configure right at the article-creation?

    at the moment, I create the publication, then I loop with smo the tables of the database and create articles? Is there a easier way to do it?

    thanks in advance basto

    Monday, December 9, 2013 3:20 PM

Answers

  • RMO automatically creates the rowguid column when you call MergeArticle.Create().

    Here is an example of adding a Merge article using RMO which automatically creates the rowguid if it is not already there when you call MergeArticle.Create().

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    
    // These namespaces are required.
    using Microsoft.SqlServer.Replication;
    using Microsoft.SqlServer.Management.Common;
    
    namespace MergeAgentRMO
    {
        class Program
        {
            static void Main(string[] args)
            {
                AddMergeArticle();
            }
    
            static void AddMergeArticle()
            {
                // Define the server, publication, and database names.
                string publisherName = "P47-THUNDERBOLT";
                string publicationName = "TestMergePub1";
                string publicationDbName = "TestDB";
    
                string articleName1 = "configuration";
                MergeArticle article1 = new MergeArticle();
    
                // Create a connection to the Publisher.
                ServerConnection conn = new ServerConnection(publisherName);
    
                try
                {
                    // Connect to the Publisher.
                    conn.Connect();
    
                    article1.ConnectionContext = conn;
                    article1.Name = articleName1;
                    article1.DatabaseName = publicationDbName;
                    article1.SourceObjectName = articleName1;
                    article1.SourceObjectOwner = "dbo";
                    article1.PublicationName = publicationName;
                    article1.Type = ArticleOptions.TableBased;
    
                    // THE ROWGUID COLUMN IS AUTOMATICALLY CREATED HERE!
                    if (!article1.IsExistingObject)
                        article1.Create();
    
                }
                catch (Exception ex)
                {
                    // Implement appropriate error handling here.
                    throw new ApplicationException("The article could not be added.", ex);
                }
                finally
                {
                    conn.Disconnect();
                }
            }
        }
    }

    Brandon Williams (blog | linkedin)

    Tuesday, December 10, 2013 5:38 PM

All replies

  • It is possible to precreate the rowguid columns

    Here is how to do it using tsql.

    alter table mytable alter column pk uniqueidentifier not null
    GO
    alter table mytable alter column pk  add ROWGUIDCOL
    GO
    alter table mytable add constraint mytablePK primary key  (pk)

    Now the constraint can be a unique constraint or a primary key constraint.


    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

    • Proposed as answer by Sofiya Li Tuesday, December 10, 2013 7:45 AM
    Monday, December 9, 2013 5:19 PM
    Answerer
  • hey,

    thanks and yes I know that I could precreate the rowguid-columns by tsql-script, but I thought there might be an option/object in the rmo-assembly to do it in c#?

    alright, I would leave the question-state as open, maybe someone else knows something about a rmo-way?

    Tuesday, December 10, 2013 8:24 AM
  • No, there is no single method call you can use in RMO that will do this. You will need to do it via 3 calls in SMO.

    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

    • Marked as answer by basto2 (MCSA) Tuesday, December 10, 2013 3:14 PM
    • Unmarked as answer by basto2 (MCSA) Tuesday, December 10, 2013 4:33 PM
    Tuesday, December 10, 2013 12:25 PM
    Answerer
  • Ok now there is one more question.. I realized when I configure the replication by the management studio gui, that constraints had been created like this:

    ALTER TABLE dbo.groups WITH NOCHECK ADD CONSTRAINT [repl_identity_range_32234627_8D69_45E0_888F_8FA6F426545D] 
    CHECK NOT FOR REPLICATION (([id]>(87) AND [id]<=(10087) OR [id]>(10087) AND [id]<=(20087)))
    GO
    
    ALTER TABLE dbo.groups CHECK CONSTRAINT [repl_identity_range_32234627_8D69_45E0_888F_8FA6F426545D]
    GO

    Do I need to create these constraints as well? Cause I recon I would need to update these constraints, when they reach their maximum and it looks like the replication does it automatically. So do I need it and if yes how should I create these constraints, by smo/rmo or tsql?

    thanks!




    Tuesday, December 10, 2013 4:32 PM
  • RMO automatically creates the rowguid column when you call MergeArticle.Create().

    Here is an example of adding a Merge article using RMO which automatically creates the rowguid if it is not already there when you call MergeArticle.Create().

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Text;
    
    // These namespaces are required.
    using Microsoft.SqlServer.Replication;
    using Microsoft.SqlServer.Management.Common;
    
    namespace MergeAgentRMO
    {
        class Program
        {
            static void Main(string[] args)
            {
                AddMergeArticle();
            }
    
            static void AddMergeArticle()
            {
                // Define the server, publication, and database names.
                string publisherName = "P47-THUNDERBOLT";
                string publicationName = "TestMergePub1";
                string publicationDbName = "TestDB";
    
                string articleName1 = "configuration";
                MergeArticle article1 = new MergeArticle();
    
                // Create a connection to the Publisher.
                ServerConnection conn = new ServerConnection(publisherName);
    
                try
                {
                    // Connect to the Publisher.
                    conn.Connect();
    
                    article1.ConnectionContext = conn;
                    article1.Name = articleName1;
                    article1.DatabaseName = publicationDbName;
                    article1.SourceObjectName = articleName1;
                    article1.SourceObjectOwner = "dbo";
                    article1.PublicationName = publicationName;
                    article1.Type = ArticleOptions.TableBased;
    
                    // THE ROWGUID COLUMN IS AUTOMATICALLY CREATED HERE!
                    if (!article1.IsExistingObject)
                        article1.Create();
    
                }
                catch (Exception ex)
                {
                    // Implement appropriate error handling here.
                    throw new ApplicationException("The article could not be added.", ex);
                }
                finally
                {
                    conn.Disconnect();
                }
            }
        }
    }

    Brandon Williams (blog | linkedin)

    Tuesday, December 10, 2013 5:38 PM
  • Regarding your constraints, these will be automatically created as well.  By default, the MergeArticle IdentityRangeManagementOption property will be set to Automatic which means replication will automatically assign the identity ranges, manage the constraints, etc..

    Brandon Williams (blog | linkedin)

    Tuesday, December 10, 2013 5:45 PM
  • FU**, the only difference I had: I wasn't setting the "SourceObjectOwner" and if this property is not set, the article will be created, but without all the magic-stuff (constraints, rowguid,...)

    I'm sorry for all the trouble! Now it seems to be working like it should... thank you again!

    Wednesday, December 11, 2013 8:06 AM
  • Very nice.  Sorry I didn't get to this earlier but I have been swamped in projects lately.  I'm glad it's working for you now.

    Unfortunately they have slated to deprecate RMO.  If you get a chance, I've created a Microsoft Connect item to have MS reconsider to the deprecation which can be found here:  http://connect.microsoft.com/SQLServer/feedback/details/774485/rmo-api-deprecated-in-sql-server-2012

    Please upvote the item if you think it is important.


    Brandon Williams (blog | linkedin)

    Wednesday, December 11, 2013 8:12 AM