September 25, 2010 at 5:55 am
Guys ,
My requirement application requirement goes like :
a) One few tables needs to be replicated from Prod to Dev environment. The flow is that whenever the table on the prod gets updated , immediately that should reflect on the development server.
The cofiguration is -> SQL 2005 64 bit ( Enterprise editions) on both the servers. The table which needs to be replicated it wont be bigger than say ~2GB
a) We thought abt conifuring the transaction replication at a moment , but is this reallly advisable to go for this option when we just need to replicate 2 tables . Taking in consideration the replication pros n cons , Iam confused about this option .
b) Log shipping could be the solutions ,, but since here we are just worried abt 2 table . This filer condition wont be available in log shipping . Please correct me if I am wrong .
c) I tried Tablediff utility on of my server . Can anyone please advise if which would be the most sutitable in this case. Can tablediff solve the purpose ???
September 25, 2010 at 5:42 pm
If you need changes to be reflected immediately, then transactional replication is probably your best bet if the connection is good. Tablediff is not a good choice. Tablediff is meant to show you differences if there are any between two tables. It can create scripts to update a table so that both tables match, but those scripts would then need to be run manually.
Since we are talking about updating a dev environment, what are you concerns that are prenting you from leaning towards replication?
Joie Andrew
"Since 1982"
September 26, 2010 at 2:38 am
Thanks Joie .. Only reason being being not to favour replication is bcz of followig doubt
a) If replication breaks bcz of any reason then .. how much impact would be there on the db level ..
Also developing an SSIS package to replicate the Change data capture functionality ( which exists only in SQL 2008 ) . Is this advisable?
September 26, 2010 at 6:34 am
Well, since you are loading your dev environment with your production environment if replication breaks down and you cannot get it back up you can reinitialize replication.
You could do it with a SSIS package, but that would have to be scheduled and would not be as immediate as replication. Also, your only options available with a SSIS package are to reload the entire table every time the package is run, or perform comparisons to see which rows need to be inserted/updated/deleted every time the package runs which would take longer to get data over then replication anyway.
Joie Andrew
"Since 1982"
September 27, 2010 at 7:57 am
If you have SQL Server 2008 Enterprise edition, then look at the new Change Data Capture feature. It depends on what purpose the table will serve in the development environment.
http://msdn.microsoft.com/en-us/library/cc645937.aspx
Do you need this table in dev for the purpose of unit testing data fixes, running audit reports, etc. ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply