October 20, 2008 at 8:16 pm
Hi,
We currently have a SQL server which acts as a centralized feed to feed data to a number of subscribers, which either reports this data directly in an application or further processes the data to other reporting applications. The current design uses transactional replication to replicate real-time data to its subscribers and the database schemas are pretty much the same across all subscribers, which is fine for now.
However, as new data requirements come up for different applications, the subscribers' database schemas will begin to vary greatly from each other, and it will be much more difficult to manage this with existing SQL replications. In addition, if we need to make a schema change at the feed server level which requires re-creating the table itself (ie. changing column orders, making an existing column an identity field, turning on NOT FOR REPLICATION options for an existing column, etc) this means we need to drop the table from all publications and re-add it after the schema change. Even though we can still do this with SQL Server replication, we are looking for an alternative solution which allows greater flexibility, ie. not so much bounded by existing database schemas, while maintaining near real-time data delivery. SQL 2008 change data capture (cdc) is not an option for now as it'll probably take us a good couple years before we'll start upgrading to SQL 2008 across the board.
We've been looking into a couple options, including SSIS and BizTalk. SSIS seems good at first since it's quite flexible and we can change schemas at the feed/subscriber without affecting the SSIS package itself. Problem with SSIS is that it's not designed to move incremental data, and although it's possible to do so (ie. http://www.sqlservercentral.com/articles/SSIS/62063/), it performs very slowly with large sized tables and is not the best choice for real time data delivery. BizTalk is another option which we're looking into, but the SQL adapter is a 'pull' adapter and needs some logic to 'tell' the adapter what is being changed (ie. last changed datetime column) or otherwise it won't be able to pick up the changes.
So I guess the question is, is anyone aware of, or worked with other 3rd party software which does exactly the same thing as SQL Server replication but allows for the type of flexibility which SSIS offers? Any suggestions on which software out there is suitable for what we need to achieve?
Thanks in advance
October 20, 2008 at 10:29 pm
In my experience, heterogeneous replication is best implemented in SQL Server with Service Broker.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 20, 2008 at 11:29 pm
service broker is also a possibility although there are some concerns (ie. monitoring, initial synchronization, if data becomes out of sync over time, etc) but definitely worth investigating
i guess, to summarize my first post, i'm looking for a data replication technology that provides a 'loose coupling' between the source and its destination compared to replication, which is more of a 'tight coupling'
any other ideas?
October 20, 2008 at 11:36 pm
Interestingly, "Loose Coupling" is how Microsoft describes Service Broker's use in applications development.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 21, 2008 at 9:50 am
yep, therefore service broker looks promising so far
anyone else with any other ideas?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply