November 11, 2008 at 11:55 am
Is anyone nows any script, this is what I need to set up a job to runs everyday to compare to servers Production and staging, if any updated, inserts,delete with data are done put everyhing in staging server. Thank you
November 11, 2008 at 12:04 pm
Redgate makes tools to compare the databases, but I don't know if it will also apply the changes. How about using replication to keep them in sync ?
November 11, 2008 at 5:55 pm
How is repilication works?
November 11, 2008 at 6:30 pm
Check for Replication in BOL. But unless it is unidirectional replication (as in snapshot or transactional) you lose the ability to control the changes that you would want to go in production, because bidirectional (merge) replication compares both and updates in both directions. I would say snapshot replication would do, but whatever changes in production will be carried onto staging, if this is what you want..
One way if you do this you will have a better DR in place..:cool:
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
November 11, 2008 at 10:04 pm
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.
The choice of replication type is base on your production scenario... do you want the database updated from both ends (sites), do you have mobile users... is the database primarily used for reporting/data warehousing or is it updated actively by users on both servers?
You can find good help in the following URL
http://msdn.microsoft.com/en-us/library/ms151198.aspx
Andy
November 12, 2008 at 5:53 am
Thank you for your help, I don't have mobile users. I would need to compare two servers staging and production, if there is any changes with data in production, call store procedure to make a change. I have 2000 enviroment. How can I do it? Thank you
November 12, 2008 at 7:46 am
yulichka (11/12/2008)
Thank you for your help, I don't have mobile users. I would need to compare two servers staging and production, if there is any changes with data in production, call store procedure to make a change. I have 2000 enviroment. How can I do it? Thank you
If you want staging to always match production, and you do not make changes to staging, then replication would be a good solution. You should look at transactional replication if you have large tables, especially if they have many updates (customers, orders, invoices ...). Transactional replication creates an initial copy of the tables, copies them to staging, then on a schedule that you set (every hour, every 15 minutes, every 5 minutes ...), applies the table changes in production to the tables in staging .
There is also snapshot replication that copies the entire table from production to staging each time. This is ok for small tables that don't change much, such as lookup tables (country codes, currency codes, accounting periods...).
Replication does not automatically do the whole database. You select which tables to replicate: some or all if you want.
Read Books Online and search for articles here about replication.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply