SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Update a record in another SQL server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 03/13/2002 :  09:01:18  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Shantha writes "We are using SQL 6.5.

We have a SQL server and we want to seperate it into 2 servers. All the tables and SPs will be same. All the tables will be updated seperately except only one. This table should be updated at the same time in both servers. When this table is updated in one server updated record should be written to the other server at the same time. Is there a way to do it ?

Thanks

Shantha."

Nazim
A custom title

United Arab Emirates
1408 Posts

Posted - 03/13/2002 :  09:07:56  Show Profile  Reply with Quote
You can use triggers and follow four part naming convention and access the table from other server .

Servername.databasename.owner.tablename

HTH

--------------------------------------------------------------
Go to Top of Page

shantha
Starting Member

Sri Lanka
2 Posts

Posted - 03/27/2002 :  14:30:32  Show Profile  Reply with Quote
It is not accepting the 4 part naming convension as you described.
4 part naming convension can be used only for Stored procedures as
servername.Databasename.owner.SPname which we can run a SP in a remote server ? Please clarify.

Thanks

Shantha.

Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 03/27/2002 :  14:48:52  Show Profile  Visit chadmat's Homepage  Reply with Quote
Upgrade to 7.0, or better yet 2000 and use merge replication.

You might be able to do it in 6.5 with Merge replication, but I would seriously consider upgrading.

-Chad

Go to Top of Page

qchau_98
Starting Member

6 Posts

Posted - 03/27/2002 :  15:47:19  Show Profile  Reply with Quote
I agree with Chadmat doing Replication is your best bet. That's what I did to my DB Servers. It works very well.

Good Luck.
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/27/2002 :  17:17:44  Show Profile  Visit nr's Homepage  Reply with Quote
I wouldn't use sql server replication for this as it will give you a lot of administrative overhead for something simple - and may not even fulfil the requirement.
Also what do you do if you restore one of the databases?

You say you want them updated at the same time - i.e. you don't want the process to complete until both databases are updated? This means that if the remote server goes down you cannot update the local one either.
If that is the case then you are letting yourself in for a very difficult future and replication won't support this anyway. Better to redesign the system so that you can allow for a little lag between the two systems - a minute or so will make things a lot easier.

If you need the two in step then create an insert,update, delete SPs on the remote database

create insSP
@fld1 ... all fields
as
insert tblk (fld1) select @fld1, ...
go

create updSP
@fldPK ... PK fields
@fldn ... all fields
as
update tblk
set fldn = @fldn ...
where fldPK = @fldPK
go
similar for delete

in the source table include triggers
create trigger ins_tbl
as

declare @fldPK ... @fldn...

set @fldPK = min(@fldPK) from inserted
while @fldPK <= (select max(fldPK) from inserted)
begin
select @fldPK = max(fldPK) from inserted where fldPK > @fldPK
select @fldn = fldn ... from tbl where fldPK = @fldPK
exec svr.db.owner.insSP @fldPK,...,@fldn...
end
go

do similar things for deleted/updated.
The example here is for single PK field - will work for compound though.
Can build script to auto-generate this if you need it for other tables.

If you can cope with a slight delay in synchronisation though it is better to transfer the data via staging tables. This will have a minimal impact on the source server, give an audit trail of what has been sent across and also be self recovering for the destination server - you just have to be careful about restoring the source server.
Create a table on the local and remote servers with the same structure as the table to transfer. The local one has and additional identity column (id) and action column and also a status field default ' '. The remote has the same but the id column is not identity.
The triggers just insert into this table and include the action I,U,D.
An SP on the local server gets records to transfer given an id.

create procedure trStagingTbl
@id int
as

set rowcount 100
select *
from StagingTblSnd
where id > @id
order by id
go


the remote database has a sceduled task which calls an SP

create procedure GetStagingTbl
as
declare @id int
select @id = max(id) from StagingTblRcv
insert StagingTblRcv
exec svr.db.owner.trStagingTbl @id

while exists (select * from StagingTblRcv where status = ' ')
begin
select @id = min(id) from StagingTblRcv where status = ' '

do inserts/updates/deletes here
Can do all of a single type in one block as long as the PK is not repeated.

end







==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000