[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-04-16
7
Medium Priority
?
207 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 750 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 750 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 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 750 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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