February 9, 2023 at 3:11 pm
I am having two databases A and B Both are logging in Same DATA at the same interval of Time But in case of power failure system A it should copy missing data from system B and Vice Versa In case of power failure of System B it should copy the missing data from System A.Please Update
February 10, 2023 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 10, 2023 at 6:31 pm
I can't answer this directly because I haven't used SQL Server 2022 however a question which might help other people answer:
You've said that both databases are getting the same data. How is that happening? Is an application writing to both databases or do you have something setup in SQL Server which normally copies the data between the databases?
February 10, 2023 at 7:50 pm
lalitprg2017, please confirm whether my understanding is correct.
When things are running both DB-s hold the same data. What you want is that after an outage happens in one DB, lets say A has the outage and B has remains up, after A comes back up, you want the data that was saved to B but not A to be copied over to A.
Also, can you tell us more about the data? is it a single table?(is so describe) many tables? Is there a natural key in the source data? (such as a date/time that could be used to identify the missing records).
February 11, 2023 at 5:38 am
Yes You understand it correctly there will be multiple tables which are identical in both the DBs
February 11, 2023 at 6:43 pm
lalitprg2017, for giving you a solution I would have to make some assumptions. Please tell me if you see then as reasonable for you situation. The assumptions are:
If this is realistic for you I can put down my suggestion.
February 11, 2023 at 11:23 pm
Based on the stated assumptions I would suggest the following.
/*--- Initial setup
--- Cretae synonyms to reference the remote table\s
I use a synonym to reference objects on remote servers to avoid directly referencing remote objects in the code
That makes that when implementing on different environments only the synonyms need to be updated and not code
*/
create synonym RedundantTable1 for RedundantServer.RedundantDB.RedundantSchema.Table1
create procedure sp_CopyFromRedundantServer
@FromDatetime datetime
, @ToDatetime datetime
as
/*
Procedure to run on server that expiriences the outage after it comes back from the outage
It requres a time range for the outage. The range does not need to be precise. It should be
sometime before the outage to sometime after transactions start processing on the down server
The more narrow the range is the faster the process will run. In most scenarios where the amount of data
is not enormeos it won't be material for the range to be somewhat larger than the actual outage
*/
--- we first copy data from remote table into a temp table to avoid joining local and remote table in following step
select *
into #RedundantTable1
from RedundantTable1
where DateTimeColumn >= @FromDatetime
and DateTimeColumn <= @ToDatetime
and isnull(OriginalServer,'') <> 'remote'
--- insert into into the local table any record found in the data from remote table that does not exist in the local table
insert into Table1
(
OriginalServer
, DateTimeColumn
, NaturalKeyColum
--, --- other columns on table
)
select
OriginalServer = 'remote'
, DateTimeColumn
, NaturalKeyColum
--, --- other columns on table
from #RedundantTable1 i
left outer join Table1 t
on i.NaturalKeyColum = t.NaturalKeyColum
where t.NaturalKeyColum is null
February 12, 2023 at 2:54 pm
This was removed by the editor as SPAM
February 12, 2023 at 3:28 pm
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply