July 1, 2008 at 7:10 pm
Ok, I've been hunting around for a long while for this answer. I see that this same question has been asked on this forum, but I have a specific requirement which the solution hasn't been found or answered just yet. So I might as well ask:
I need to know if there's a means for a transactional replication coming from MySql to SQL Server 2005. I know very well about linked servers, as I currently have the MySql DB linked just fine. In this case, the MySql would need to act as the Master/Publisher, and the SQL Server 2005 needs to be the subscriber. This also needs to be Transactional/Real time data duplication, and not just a snap shot. However, I see that the only options with Publishers is through either your Local SQL Server, or through networked...SQL Server. Then there's always the Oracle Publisher....eh. No.
I know in theory that I could use one of the two methodologies:
1: Notification Services. However, this seems like over kill, and quite complex, and not exactly what I'd need. But I know I could make things work with Notifications. But the XML thing just seems to be tedious
2: SSIS package of some sort. While this is more complex than a clean replication/publisher scenario, this still makes more sense. However, finding resources which allows my linked server as a data flow type object has been semi-problematic.
3: I could always create a Agent Job. But from my real initial testing, I think I had the same problem as what I had found in the Replication Publishing scenario.
Here are some of the factors which I'm up against too:
1. The MySql DB is....MySql 4.1. Wonderful. No Stored Procedures, no triggers. Nothing.
2. I may not have enough room on the server to do an initial migration of MySql 4.1 to MySql 5.xx, where I'd find more tools to work with on that end if need be.
Any advice would be wonderful. I'm not looking for tutoirals, code snippets (well, maybe some SSIS pointers would help if that were the best means)...I'm just looking for higher view advice, in which case my best friend GOOGLE would help me find more answers....
Thanks in advance
(Just to inform as to why this is needed this way: I'm migrating a crusty old intranet sitting on a Unix box over to SharePoint 2007. However, the first step is refractor the initial application into ASP.net, point the URL to the windows box THEN migrate the .NET application into the SharePoint environment. The data needs to be real time, because there are reports which are daily, and I need to have the data coming in each day in sync with the live Intranet...)
July 2, 2008 at 6:27 am
Here are my thoughts.
Notification Services - I don't think this would help you at all. It is designed for notification of user-defined criteria being met on a SQL Server. The basic purpose was for something like a stock tracking system that would notify people when their criteria for selling was met. Also, MS dropped the entire feature with SQL 2008 because it was essentially unused, did not work all that well, and in most cases, could be completely be replaced with Service Broker applications that would be more efficient. Before you ask, Service Broker will also not help you.
Now, replication is heavily based on the idea that your source system can, in some way, provide a publication that lets replication know something has been inserted, updated, or deleted. I don't know if MySQL has anything to do this, but I suspect it does. However, MS SQL 2005 only really supports MS SQL and Oracle publications, so you would need to use something from MySQL if they had it, or possibly a third party product.
If you can determine which records have changed, it would be possible to create an SSIS package that copied the changed records to your SQL Server, but you will have to write custom packages to support it.
If MySQL has any publishing / replication tools, you should look into them.
July 2, 2008 at 11:26 am
Thanks for the insights. The absolute reason for dropping the Notification Services, is what you had stated: That it's being dropped.
The Notification Services did seem a bit over done, and obscure, as I thought the Business Layer in applications was supposed to handle pushing out update informations to a client subscriber.
Replication does seem to limit what you can publish and subscribe too. So you did confirm that.
SSIS seemed to be the best practice...So, I'll just go with that. However, my main question is how to have a Linked Server data object.
But now you've got me thinking I should try something out in Sql Server 2008...just for kicks.
July 2, 2008 at 11:31 am
With an SSIS solution, you should not have to use linked servers at all. You would use an OLEDB Source and an OLEDB driver for MySQL as the source for a data flow.
July 2, 2008 at 11:53 am
It's funny you should say that, as I just did a test using that exact method, and I see I have a MySql ODBC Data Connection. I guess now, it's just a matter of studying how to create a custom SSIS package from the beging...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply