Best Procedure to replicate and update tables to another database
-
Friday, December 07, 2012 8:43 AM
Hi
I would like to know what is the best practice to copy/update a set of tables from one database to another.
We have a product testing Application that does its job however some of our engineers need to analysis data directly into the data itself (not offered by the application). Since upgrading the application the database has become more and more complex and thus the data that was freely available from views is suffering more and more from timeouts due to the amount of data now kept in the database.
What I intended doing was creating a number of select into queries and updates that ran periodically into a second static database thus allowing the anaylsis to run freely. Is this the best way? and if I needed to be updating these tables on a hourly/1/2 hourly basis would this be a good way of carrying this out?
All Replies
-
Friday, December 07, 2012 11:16 AM
Hi Saxon,
There are number of ways to transfer your dynamic tables data into static database tables. you can either use replication or any other high availability database methods depend upon your requirement and sql server editions.
Beside this, you can also triggers/SSIS packages to transfer data from one database to another.
- Edited by Somesh Monga Friday, December 07, 2012 11:16 AM
-
Friday, December 07, 2012 2:47 PMModerator I think the best fit here is transactional replication. Make the Source Server a publisher and your destination database the subscriber.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
- Proposed As Answer by Allen Li - MSFTModerator Monday, December 10, 2012 8:13 AM
- Marked As Answer by Allen Li - MSFTModerator Thursday, December 13, 2012 1:23 AM
-
Friday, December 07, 2012 2:48 PMModeratorSomesh - triggers are a bad choice over the network as it adds latency and if the network is down the database become read only.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
-
Monday, December 10, 2012 8:57 AM
Hi Hilary,
Thanks for your advice on triggers. I suggested this to "Saxon" as a alternative option of replication depend upon production environment.
- Edited by Somesh Monga Tuesday, December 11, 2012 10:35 AM
-
Monday, December 10, 2012 6:02 PMModerator
You need a store and forward mechanism. Changes are tracked and then stored somewhere and then replayed when the connection is up. Replication has this built in, you can also roll out your own solution using SQL Server Service Broker - or another queuing mechanism.
With a trigger the change will not be committed until the write occurs on the source and destination. if this change is written over the network it will add considerably to the latency and slow the application making the changes down. If the network is down, the change which the application is trying to make on the source system will fail - the tables will in effect become read only.
looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
.png)
