Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Apply DML changes from one SQL Server 2008 to another (disconnected)

Posted on 2014-04-16
7
198 Views
Last Modified: 2014-05-05
Hello All,

I have a scenario in which one database (DB2) should be updated with the changes that are occurring in (DB1).  They are not connected and no connection between the two can occur.  Can a script from the transactional log from DB1 be applied against DB2?  If so, what is the best way to do create and achieve this?  I am looking for some commentary on approach -

Thank you in advance!
0
Comment
Question by:Mosquitoe
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 375 total points
ID: 40006149
You haven't specified if it's a one of practice or you want to keep the two DBs in sync on an ongoing basis. Anyhow the transaction log alone cannot be used in this scenario but if you don't care about overwriting the whole destination DB with the source DB (including schema and data) then of course you can use a full backup to synchronize the two databases.
If you don't want to overwrite the data, and only want to synchronize the schema of the two DBs, then you can script each database or just the relevant objects in each database on each server, save the scripts as files and compare the files using a file merge tool and then apply the necessary changes to the pertinent DB. There are also 3-rd party tools that can simplify the whole process for you but basically work on the same principle that I mentioned above.
0
 

Author Comment

by:Mosquitoe
ID: 40006164
Hello - it's a one way push db2 should be updated with db1 changes on a daily basis.  

Ok - thank you, I will look into the feasibility of doing it this way - there are access constraints and constraints on what we can install on the server where db2 resides.
0
 
LVL 8

Assisted Solution

by:ProjectChampion
ProjectChampion earned 375 total points
ID: 40006183
If the two DBs need to be kept in sync on a daily basis then:
1. If its only DML changes that you're concerned about, perhaps you can set up a process that all changes to the source to be scripted (if not already) and stored in an organised way for instance in a version controlled repository such s SVN so they can be applied on the destination server on a regular basis.

2. If want both DML and DDL changes to be applied on the destination server, then after the initial synchronization (e.g. restoring a full backup of the source) then manual log shipping could be potential option.

God luck with your project.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
ID: 40006668
Can a script from the transactional log from DB1 be applied against DB2?  If so, what is the best way to do create and achieve this?
This would appear a perfect fit for Red-Gate's SQL Compare.
0
 
LVL 8

Assisted Solution

by:ProjectChampion
ProjectChampion earned 375 total points
ID: 40006729
SQL Compare is very handy tool that can help with the initial synchronization, or occasional comparison and resynching... but if two databases are required to be kept in line on a regular basis, then a more systematic method is needed. This is actually the classic scenario in controlled change release which can be most efficiently implemented based on a version-controlled scripting approach. Red Gate have a tool for that too, which is called SQL Source Control.
However as mentioned you can setup an equally effective bespoke procedure based on free/open source tools such as SVN and a simple protocol for initiating and deploying DML changes where all changes to the DB should be done via applying pertinent scripts which are kept in the version controlled repository.
You can automate generation of the scripts via DML triggers etc. but the key is managing the scripts in an organised system.
0
 

Author Comment

by:Mosquitoe
ID: 40034137
Hello All,

OK - I m sorry I did not respond sooner - trying to iron out the scenario.  We may be able to create a connection between the two environments which leads me to think that transactional replication is the best way to go.  We are interested in the INSERTS and the UPDATES.  I can see that if we implement initially the INSERTS only, we can configure the replication to only publish the INSERTS.  Both database are read/write, DB1 and DB2 - DB2 will require all INSERTS from DB1 applied against it.  For UPDATES, we have SSIS to do the ETL and establish if there are any conflicts/do the mapping.  

I would like to hear your thoughts on this scenario and possible solution.
Thank you!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40043166
I would like to hear your thoughts on this scenario and possible solution.
I think I will pass.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question