luminas
asked on
Merge Replication on SQL Server 2008, over 256 columns
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?
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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... :)
Seems like peer-to-peer is the best, however this is Enterprise edition only.
Might re-visit this later... :)
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.