May 13, 2016 at 4:11 pm
Hello -
I have been researching ways to get data from point A to point B, without using SQL Data Compare from Redgate. Part of the issue I am running into right now is that the Source database has design flaws that I am attempting to correct on the Target database side (e.g. file system design and table design).
I tried both Transactional Replication and Log Shipping, but both methods end up making the Target database exactly like the Source (e.g. data, table design, and file system). The only way I have found to get the data from my Source to Target database with relative ease, and none of the issues of concern happening, are with the Redgate tools. The problem there is that the data compare process takes a very long time, as does the whole deployment process. In trying to balance this out so that I can declare an appropriate amount of time needed to perform the migration, I'm searching for a better/best way to go about this. I am not an ETL expert, so SSIS is off the table.
Want to see if anyone knows of a better/best way without changing the Target database's file system or table design. Specifically - by table design, I mean that my Target database now has the PKey appropriately paired with the Clustered Index, where the Source database does not, and by file system design, the Source database has an absolutely nutty design of multiple log files and data files, with no file-grouping for the data nor any logic for why they were created to begin with, and all the files (for the data and logs) are on the same drives. My Target database(s) do not have this issue now, and have a much better file and Filegroups design.
Any and all input would be appreciated.
Thank you in advance
May 13, 2016 at 7:57 pm
I tried both Transactional Replication and Log Shipping, but both methods end up making the Target database exactly like the Source (e.g. data, table design, and file system).
That's basically the purpose. With transactional replication you may be able to Replicate Indexed Views as Table
[/url] but I've never done this so I won't say if that's a viable choice. Log shipping nor mirroring will solve your problem either.
I'm searching for a better/best way to go about this. I am not an ETL expert, so SSIS is off the table.
What you are trying to do is Extract data from a source system, Transform it into something better and Load it somewhere else. With that in mind, SSIS may be the way to go. That's how I'd do it. You don't have to be an ETL expert to use SSIS, especially for the kind of thing you're describing. Read up on it and bring your questions here. SSIS is not rocket science.
Alternatively you can do this with good ol' fashioned T-SQL, the SQL agent and some way to move the data to & fro (linked server, cmdexec). I personally would go the SSIS route but there's two routes.
-- Itzik Ben-Gan 2001
May 14, 2016 at 4:28 pm
Hi Alan -
Thank you for your response and confirmation. I figured there was a reason why the Redgate tools existed (specifically for schema and data compare and pushes).
I did my testing a while back, and neither Replication nor Mirroring will work without the Target database being exactly like the Source/Publisher.
So - Redgate it is (SSIS I will muddle though another time when I actually have the time).
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply