Solved

Immediate Update of Data to Two Seperate Databases (one internal, one external)

Posted on 2014-04-08
6
259 Views
Last Modified: 2014-04-10
I have the need to have a real time replica of a few of our tables that are stored in an off site location for disaster recovery.

What we would like would be whenever there is a change on table Orders on our internal sql 2000 database, that the same change is then made to the table Orders on a database stored externally, probably by GoDaddy or some similar host.

All changes currently are completed via web pages using classic asp, so if needed, I can just add update / insert statements to the code of the pages, but I'd prefer to do a trigger on the sql side.

So, is it possible to do a sql trigger from a local database to an external database hosted by, say, GoDaddy?  Do we need to do any upgrades of our existing sql-2000 server?

Are there any other solutions you can think of to achieve what we are looking for?

In a nutshell, we want to have a real time replica of some tables we choose, we could do the entire database is that is easier also, but it isn't needed.
0
Comment
Question by:dzirkelb
  • 3
  • 2
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39986278
you could add a linked server and then do the triggers that way.  All depends on the latency and what you're willing to go with.


--enable identity insert
insert into
linkservername.database.schema.table
select * from inserted

--disable identity insert
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39986543
A directly linked trigger could cause severe problems.

The best option might be a column with a type of "rowversion".  SQL will increment that value every time a row is inserted or changed.  Thus, you can determine which row(s) have changed since the last time you pulled data.

Or you could use a trigger(s) to copy modified data to another "staging" table.  Then periodically push the data in the staging table to the external db.

Either of those approaches allows internal updates to continue even if, for some reason, the external db is temporarily unavailable.
0
 

Author Comment

by:dzirkelb
ID: 39987168
The sync needs to be instant (relatively instant), so I won't be able to do batch pushes to the external database.  I do plan on doing daily syncs, however, to ensure everything is correct.

If the external database isn't available, and I try to do an update on the internal database table, are you saying the internal update will fail due to the trigger failing to the external database?  If so, that is not good.  We are fine, however, with skipping the trigger if the external database is unavailable as it will get updated in a morning batch.

In regards to latency, will it affect the speed of the update on the sql internal server?  I'm hoping the user will go to a web page, make a change, and it saves the same speed, just in the background the sql server then sends an update to the external database, not affecting the user's speed on the web browsing.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39987203
If you need near-instant sync'ing, replication is probably best.
0
 

Author Comment

by:dzirkelb
ID: 39987217
What would be needed for the replication in terms of bandwidth?  We are very limited at this time, but are upgrading soon.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39987334
Depends on the volume of data.

But SQL replication would be less overhead than any method you would write yourself, since it can use internal data formats.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Linked Server Issue with SQL2012 3 26
sql query to calculate avaerage 21 38
transaction in asp.net, sql server 6 33
SQL Server Error 21 8 23
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

773 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