Solved

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

Posted on 2014-04-16
7
199 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
[X]
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
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

730 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