Solved

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

Posted on 2014-04-16
7
200 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 eBook: Backup on AWS

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

Question has a verified solution.

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

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…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

689 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