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

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!
MosquitoeAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ProjectChampionConnect With a Mentor Commented:
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
 
MosquitoeAuthor Commented:
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
 
ProjectChampionConnect With a Mentor Commented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Anthony PerkinsConnect With a Mentor Commented:
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
 
ProjectChampionConnect With a Mentor Commented:
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
 
MosquitoeAuthor Commented:
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
 
Anthony PerkinsCommented:
I would like to hear your thoughts on this scenario and possible solution.
I think I will pass.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.