synchronize the database

[umeshpotdar]

hi frdz
i'm doing web site
i have to use two databases
one is for web site (suppose call it as web db server-A) &
another is for some trasaction(call it as trans db server-B)
so i want to synchronize these databases.
means i want to do updation to these db [once in the day ]
[whtever changes made in B will be update db A from last updation point
at the end of day ]
so can u tell me how can i synchronize the databases.
thnks.........

[iburyak]

You can write a stored proc to update all tables in a database and run it by a scheduled job nightly.



Thank you.

[umeshpotdar]

can u give me the procedure how to write the Syncronization of database
or any link that can refer to syncronise database...............

thanks for ur's reply.

[iburyak]

Do you update both databases independently or one is primary and another secondary?
If yes the easiest way besides replication just delete everything from tables and reload new data.

On server B

[PHP]BEGIN TRAN

DELETE FROM TABLE1

INSERT INTO TABLE1
SELECT * FROM ServerA.dabase_name.dbo.TABLE1

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN


BEGIN TRAN

DELETE FROM TABLE2

INSERT INTO TABLE2
SELECT * FROM ServerA.dabase_name.dbo.TABLE2

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN[/PHP]

If tables are big you have to reload them in a loop taking chunks of data depend on a size.
This way you will not overload transaction log at insert time but you will loose ability to roll back data to original in case of a problem.

[PHP]SET ROWCOUNT 2000
While @@ROWCOUNT > 0
BEGIN
BEGIN TRAN
DELETE FROM TABLE1
COMMIT TRAN
END
SET ROWCOUNT 0

While @@ROWCOUNT > 0
BEGIN
BEGIN TRAN
INSERT INTO TABLE1
SELECT TOP 2000 * FROM ServerA.dabase_name.dbo.TABLE1
WHERE primery_key_column not in(select primary_key_column From TABLE1)
COMMIT TRAN
END[/PHP]

[umeshpotdar]

thanks for ur suggetion.
ur idea is very good but i don't want to delete the tables.
will database mirroring solve this problem?
i'm trying this thing but i got errors
so plz can u tell me abt any other idea.
or how to use db mirroring concept?
thanks ....