| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 03/13/2002 : 09:01:18
|
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
|
You can use triggers and follow four part naming convention and access the table from other server .
Servername.databasename.owner.tablename
HTH
--------------------------------------------------------------
|
 |
|
|
shantha
Starting Member
Sri Lanka
2 Posts |
Posted - 03/27/2002 : 14:30:32
|
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.
|
|
|
|
chadmat
The Chadinator
USA
1961 Posts |
Posted - 03/27/2002 : 14:48:52
|
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
|
|
|
|
qchau_98
Starting Member
6 Posts |
Posted - 03/27/2002 : 15:47:19
|
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. |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 03/27/2002 : 17:17:44
|
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. |
|
|
| |
Topic |
|
Update a record in another SQL server
