Merge Replication on SQL Server 2008, over 256 columns

luminas
luminas used Ask the Experts™
on
Hi,

I know there's a limitation of 256 columns to the tables I can publish via replication, however I would like your help in solving my issue.

I need to provide for a replication between the main office application, and the website, sitting at different phsyical locations. Both locations have to update, among others, the "main" table which has over 256 columns. Why and how this table reached these columns is out of scope of this discussion, I know it's bad :)

Since it's only one table which is the problem - the rest (around 20) are much smaller - I'm trying to think of a creative solution.

The replication needs to be merge replication, since they have to be able to update from both locations, and the changes should be immediately visible on the other side. Both servers are SQL Server 2008, connected via a VPN.

Any ideas for a workaround? Also - if I just choose 256 columns to replicate (I don't need all columns on the website) - will this work well? Can I keep the other columns on the website, but just not replicate them?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Máté FarkasDatabase Developer and Administrator

Commented:
You can exlude not needed columns at the setting of the merge publication and you can set the change tracking to column level tracking (instead of row level tracking).
Apart from this you can use peer-to-peer transactional replication (with no restrictions) which is also real-time but asynchronous, so can be a few second difference between servers.
Howdy

Just a thought why did you rule out updatable transaction replication as this will give you 1000 columns. MSDN Article http://msdn.microsoft.com/en-us/library/ms151718.aspx

Merge will not give you instant updates in both places ive found continuous only gives you updates every minute depending on how much data you have to move.

Just a thought

James
Top Expert 2011
Commented:
you could also physically split the table into several smaller ones sharing the same key

then create a view over them to create the illusion of the original table...

have an instead of trigger on the view (named as the original table)
so that your existing code doesn't need to change

and then merge replicate the original tables
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
agux3e and james-ct16:
Not sure if you're both talking about the same kind of replication, but that one that james-ct16 is offering has a big notice saying "Microsoft will not be support this anymore" on the link you gave, which is not encouraging if planning to implement a new solution...

agux3e:
Will the replication not work without column level tracking (assuming i exclude columns? Isn't that supposed to be slower?

Lowfatspread:
That's an idea, but i'd like to be able to avoid that for a few reasons that have to do with the system itself, and leave it only as a last resort.
Apologies, yes i should have made that clear that this method has had a shadow cast over it for sometime. Part of your decision making process will have to include, how often do we upgrade after all if they drop it from the next version it will still be supported in some manner until 2008 is end of lifed, a very crude summary would be along the lines of
'mainstream support for most software products will expire five years after their initial release, or 12 months following the most current service pack' more details are here 'http://support.microsoft.com/lifecycle/?LN=en-au&x=10&y=10'

So having said all that the dropping of the feature does not necessarily scare me, its something i know I have to deal with in the future. The reason I suggested it is that in your scenario it should just work out of the box. You shouldn't have to change anything. I am making the assumption here that the office apps and the websites app are all written and you want to avoid changing them. So cost wise give the above assumption this is looking the cheapest option.

Now if the website components are not written you have a lot more flexibility and i would certainly be looking at Lofatspread's option or similar. Another option i would very seriously consider is using replication to push my 'master' data to the website do local updates to the tables which are not replicated back and then write transactions back using msmqs (http://msdn.microsoft.com/en-us/library/ms811051.aspx) beyond the scope here but an idea.

Column level tracking will put a greater burden on the server however given such a large table being updated in two locations i think you would want to use it. Remember different articles can use different tracking methods. The reason you would most likely opt for the column level tracking is lets assume your big table has a whole heap of personal data in it the website changes the phone number the office changes the address within the same synchronization window under row level tracking you will generate a conflict the conflict resolver will take over and pick a winner one of those changes will be 'lost' under column level tracking both changes would have been maintained. Have you come up with a strategy for conflict resolution? Even if it is just first change at the office always wins as long as everyone knows you avoid a lot of questions about why changes failed.

agux3e is talking about a different type of replication Peer to Peer (http://technet.microsoft.com/en-us/library/ms151196.aspx) it is really going to be the logical successor to transaction replication. I have no hands on experience with this so i dont feel I can comment. Previously this feature has been limited to enterprise edition of the sql server so you may want to check this.

Dropping columns from the table to be replicated would potentially be another quick solution however if you web app is already written I suspect there may be a large amount of testing to check this doesnt break anything when columns are simply found not to be there. It also leaves open the horrible answer of why cant we have this bit of data on the website because the technology doesnt support it.


Author

Commented:
Splitting the points since i will be splitting the table vertically, however james' answer was the most comprehensive.

Seems like peer-to-peer is the best, however this is Enterprise edition only.

Might re-visit this later... :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial