Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Hard limitation for merge replication article count?

I am using merge replication in SQL 2012. I am looking here:

Scroll to the bottom and under the 'Replication Objects' it says that the maximum number of articles in a merge publication is 256.

I wasn't aware of this apparent limitation, but in the mean time I have been running merge replication with 456 articles and everything seems to run fine.

I found these 3 links about it:

So to summarise, apparently in SQL 2000 there was a bug which caused this limit. It was fixed in a service pack, but was reintroduced in SQL 2005.

Does that limitation still exist or not? There doesn't seem to be any official information about SQL 2012.

I would rather management studio tell me 'you cannot add more then 256 articles' rather than having to guess whether this will be a problem for us or not.

share|improve this question

marked as duplicate by Jon Seigel, RolandoMySQLDBA, Leigh Riffel, Paul White, JNK Oct 25 '12 at 17:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
You don't have to break it up into multiple databases, so your foreign keys would be fine. We have run multiple publications for years, putting our lookup tables into one, and our transactional tables into another. –  user14752 Oct 25 '12 at 2:52
    
Just to put it out there. This is not a duplicate based on the fact that the other question has no answer. My answer below proves with reasonable certainty that this limitation does not exist anymore in SQL2012. I don't see anything like this on the other question, it remains unresolved. –  peter Nov 7 '12 at 1:09

2 Answers 2

The first link you provided is official documentation for SQL Server 2012.

http://msdn.microsoft.com/en-us/library/ms143432.aspx

The maximum number of articles supported in Merge Replication is 256. This means that Microsoft will ensure up to 256 articles will work as expected. I'm able to exceed 256 articles as well but I wouldn't do it in production. You may want to consider splitting this up into multiple publications for production purposes.

share|improve this answer
    
But splitting it up is a horrible alternative. That just sounds terrible. How do you deal with foreign keys for instance? Also how do you deal with having two databases at the subscriber end. I mean the client software will have to be changed to deal with this quite majorly. We are upgrading a legacy system. –  peter Oct 15 '12 at 3:15
    
Also what will break? Can you give me a specific example so that I can test it? –  peter Oct 15 '12 at 3:16
    
Just a quick point, the MSDN documentation gets reused every time, so how do we know that MS could be bothered checking it and updating it properly? –  peter Oct 15 '12 at 3:20
    
If certain tables are related or filtered with joins, they'll need to be in the same publication. This will require multiple subscriptions but it can be done with 1 Subscriber database, so no worries there. –  Brandon Williams Oct 15 '12 at 17:17
    
I cannot say what will break for sure. Agents would likely fail on sync under certain conditions. –  Brandon Williams Oct 15 '12 at 17:21
up vote 1 down vote accepted

I'm not convinced about the publication article limitation of 256. If we look here,

http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/f3f505ff-7a62-4131-badb-2ecf58f20808/

It is talking about a particular query that causes issues on SQL 2005 when you have more than 256 articles. First I ran the stored procedure sp_MSmakesystableviews.

It then generated a view called MSmerge_cont4F93AE6035D14E46ADA56D87F66E8962_90.

If I take the underlying query in the view it has more than 256 tables. I run that query against a SQL 2005 database and I get the error,

Message: Too many table names in the query. The maximum allowable is 256.

If I take that same query and run it on SQL 2012 it works fine.

My belief is that the MS documentation is outdated, and unless I find other issues with running merge replication with more than 256 articles I would have to take it that this limitation does not exist anymore from SQL2008 upward.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.