locked
SQL 2008 geography type replication RRS feed

  • Question

  • Hi,

    I am attempting to get merge replication going between two SQL 2008 servers.  So far, everything has gone off without a hitch with one exception.  We use spatial data on a daily basis in my organization, so we implemented the geography data type.  This data type seems to be causing all of our replication issues.  I can easily create publications and subscriptions for each article in the entire database except the geography field.

    The error is as follows:

    "The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription."

    The real kicker is that the schemas are exactly the same.  If I set the publication to drop and recreate the subscription article, then to convert the geography data type, it works without issue, but then we run into issues with our publication database not matching exactly with our subscription database, which could cause many potential problems with our web front end.

     

    Is there a known issue with replicating the geography data type?  Any help is very much appreciated!

    - Chad

     

    Wednesday, May 19, 2010 8:34 PM

Answers

  • Hi, 

    Regarding replicating the geography data type, it is determined by the publication compatibility level. Also, since geography is a new data type in SQL Server 2008, merge replication handles the schema change of geography will be blocked when the publication compatibility lever is earlier than 100RTM.  In this case, you need to check and set the correct publication compatibility lever which should be 100RTM.
    http://technet.microsoft.com/en-us/library/ms152523.aspx
    Then, synchronize the subscription:
    http://technet.microsoft.com/en-us/library/ms151793.aspx
     
    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, May 21, 2010 3:18 PM

All replies

  • Hi, 

    Regarding replicating the geography data type, it is determined by the publication compatibility level. Also, since geography is a new data type in SQL Server 2008, merge replication handles the schema change of geography will be blocked when the publication compatibility lever is earlier than 100RTM.  In this case, you need to check and set the correct publication compatibility lever which should be 100RTM.
    http://technet.microsoft.com/en-us/library/ms152523.aspx
    Then, synchronize the subscription:
    http://technet.microsoft.com/en-us/library/ms151793.aspx
     
    If there are any more problems, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Friday, May 21, 2010 3:18 PM
  • i tried setting replication up between sql server 2008 express edition and sql server 2008 r2 enterprise.
     
    Now merge replicate works sweet except when i tried to replicate geometry data type then it could not synchronize with the subscriber.
     
    i get below error:
     

    The Merge Agent failed because the schema of the article at the Publisher does not match the schema of the article at the Subscriber. This can occur when there are pending DDL changes waiting to be applied at the Subscriber. Restart the Merge Agent to apply the DDL changes and synchronize the subscription. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199398)
    Get help: http://help/MSSQL_REPL-2147199398

      
    Now interesting thing is that if i change the article property "Convert spatial to MAX data type " to "true" then it works but then it changed the data type at subscriber to varbinary(max)
     
    is it possible to replicate geometry data type between these two editions without any convertions??
    Please help here
    Thursday, August 26, 2010 4:50 AM
  • Puneet77,

    Did you ever figure out your issue with replicating the schema changes when adding new geometry? I am trying to do the same but get the 'cannot replicate schema changes.....' error whenever a NEW geometry is added at the Publisher or Suscriber end and is then pushed to the other. I need the data type at the suscriber to remain 'Geometry' and therefore cannot us the "Convert Spatial to MAX....." rule.

    Any suggestions?

    Monday, December 12, 2011 4:02 PM