Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rkumar28
Starting Member
49 Posts |
 Posted - 2006-01-23 : 11:46:38
|
| Hi,We are using SQL Server 2000. We have a third party remote database and we have read only access to that database. This database has quite a few tables but we have have read only access to 9 tables in that database that we constantly use the data from. These 9 tables are Real Time -- data is Updated, Inserted and Deleted from these tables through out the day. I created an exact replica of this database locally on our company server and created exactly same 9 tables in our local database.I am trying to sync the data in my tables in our database with the data in the tables in the remote database. If the data is updated or deleted or inserted in remote database, I want to update/delete/insert data from our database as well. We are trying to get exactly the same mirror/replica of those 9 remote tables on our database all the time.We want to store data on our server locally, hence we did not create any VIEWs against remote database. Also, we don’t have permission to create triggers.I have created an SQL script that does the update, delete and Insert on our server. Is there a way to run this script every 30 minutes using DTS even when I am not logged on(As the Real-time tables gets updated in the night as well.). Will the Publication and Subscription service in SQL server can help us in achieving this service. Will appreciate any suggestions in achieving this.ThanksRaj |
|
|
Srinika
Master Smack Fu Yak Hacker
1378 Posts |
Posted - 2006-01-23 : 11:53:46
|
| U have exact replica of this same Q in Developer forum |
 |
|
|
rkumar28
Starting Member
49 Posts |
Posted - 2006-01-23 : 12:06:15
|
quote:
Originally posted by Srinika
U have exact replica of this same Q in Developer forum
I was not sure if the resolution of this is more onto developer side or Admin side, hence posted on both. Looks like I am not suppose to post same questions at two places. I am removing it from developer forum as to me this sounds more of Admin thing.Thanks for pointing this out.Raj |
|
|
|
rkumar28
Starting Member
49 Posts |
Posted - 2006-01-23 : 12:44:41
|
Will appreciate any suggestion on this.Thanksquote:
Originally posted by rkumar28
Hi,We are using SQL Server 2000. We have a third party remote database and we have read only access to that database. This database has quite a few tables but we have have read only access to 9 tables in that database that we constantly use the data from. These 9 tables are Real Time -- data is Updated, Inserted and Deleted from these tables through out the day. I created an exact replica of this database locally on our company server and created exactly same 9 tables in our local database.I am trying to sync the data in my tables in our database with the data in the tables in the remote database. If the data is updated or deleted or inserted in remote database, I want to update/delete/insert data from our database as well. We are trying to get exactly the same mirror/replica of those 9 remote tables on our database all the time.We want to store data on our server locally, hence we did not create any VIEWs against remote database. Also, we don’t have permission to create triggers.I have created an SQL script that does the update, delete and Insert on our server. Is there a way to run this script every 30 minutes using DTS even when I am not logged on(As the Real-time tables gets updated in the night as well.). Will the Publication and Subscription service in SQL server can help us in achieving this service. Will appreciate any suggestions in achieving this.ThanksRaj
Raj |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-01-23 : 21:42:08
|
| What is the requirement for the local server? is that a read-only DB so you guys can query it locally rather then remotely? if so, read more into trans replication or log shipping (given that you guys have permission to add that on the remote server and they both using Ent ed. of SQL)If you would like to have write access on the local server, DTS seems to be the only choice, (or merge repli, but don't think you guys can do that), but it might have a performance impact if the DB is big. |
|
|
|
rkumar28
Starting Member
49 Posts |
Posted - 2006-01-25 : 12:15:30
|
We have all the access to our local database and curently have readonly access on remote database. But can get write access to it. Will that help in transaction replication?quote:
Originally posted by Westley
What is the requirement for the local server? is that a read-only DB so you guys can query it locally rather then remotely? if so, read more into trans replication or log shipping (given that you guys have permission to add that on the remote server and they both using Ent ed. of SQL)If you would like to have write access on the local server, DTS seems to be the only choice, (or merge repli, but don't think you guys can do that), but it might have a performance impact if the DB is big.
Raj |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-01-25 : 12:36:55
|
| How about asking the third party if you can set up replication on their server? |
|
|
|
|
|
|
|
|
How to create an exact replica of a database Table