Is using linked server the fastest way to snyc data between web server and local server.
I have a sql 2008 server in work and want to sync it with webserver 2008. At the moment I have attached the web server as a linked server. But seems slow, plus i am stuck when i try and do a delete query using a left join query.
DELETE
FROM [LinkedWebServer].DB_2454.
WHERE ((Discontinued=True))
GO
(i get an error of Incorrect syntax near the keyword 'LEFT'.)
I have tried microsoft's localdatabase cache datasync but then have to sync the .mdf file with my server then sync the mdf file with the webserver, which seems a waste of time plus have a few problems on a few computers where this method just hung. Can you do this directly with the 2 servers?
Please help if you have any ideas!
DELETE
FROM [LinkedWebServer].DB_2454.
WHERE ((Discontinued=True))
GO
(i get an error of Incorrect syntax near the keyword 'LEFT'.)
I have tried microsoft's localdatabase cache datasync but then have to sync the .mdf file with my server then sync the mdf file with the webserver, which seems a waste of time plus have a few problems on a few computers where this method just hung. Can you do this directly with the 2 servers?
Please help if you have any ideas!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
note that if you delete with LEFT JOIN, you basically will delete even if there is no match, so I would review that "join" ...
ASKER
Hi Thanks, that worked. Do you think this is the fastest way to sync servers? it took 30 mins to update 4000 records
Web server means Remote Database Server?
ASKER
Yes sorry a remote sql data base 2008r2 and an internal sql database sqlepress 2005
Usually when you are querying remote servers across the network, you will have performance problems. Also look for the indexes on the tables used in the above query on both servers.
ASKER
I have just written a insert query.
It took 25 minutes to insert 465 rows I then ran it again and it took 4 mins to return 0 records added. Why so long? The 2 tables both use ProductRef as a string(25) for the primary key.
It took 25 minutes to insert 465 rows I then ran it again and it took 4 mins to return 0 records added. Why so long? The 2 tables both use ProductRef as a string(25) for the primary key.
ASKER
Hi angelIII, thanks have just changed this to inner. any idea why its taking so long.
I have just written a insert query.
It took 25 minutes to insert 465 rows I then ran it again and it took 4 mins to return 0 records added. Why so long? The 2 tables both use ProductRef as a string(25) for the primary key.
I have just written a insert query.
It took 25 minutes to insert 465 rows I then ran it again and it took 4 mins to return 0 records added. Why so long? The 2 tables both use ProductRef as a string(25) for the primary key.