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 |
|
helmus
Starting Member
2 Posts |
 Posted - 2011-04-14 : 13:13:22
|
| hello people,I have to make a synchronization's script that will get data from one server (odbc), validate it, and put it to another mssql server.I would like to do this using a linked server and then write all the code in TSQL in one stored procedure.This link needs to be "as realtime as possible", we can not modify the database that is accessed trough the odbc link.I'm not sure how to do this in this set up.Should i use a windows service for this, that calls the stored procedure every phew seconds, or can i use a maintenance plan that calls the stored procedure every 10 seconds.I feel like using a maintenance plan might be a wrong approach since they are mostly used for back ups etc...I'm relatively unexperienced, so please feel free to slap me in the face if i'm having stupid ideas here, or going at it all wrong.I would really appreciate feedback on this, thank you for your time ! |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-04-14 : 15:21:19
|
| If you need as close to real-time as possible, you really should look at replication instead.If you absolutely must use a linked server, then you should schedule the stored procedure through an agent job.Jeff |
 |
|
|
helmus
Starting Member
2 Posts |
Posted - 2011-04-15 : 03:41:10
|
| i'm not sure if i can use replication, the remote server can only be accessed trough odbcI can also do all the data acces in a windows service, but i feel that doing everything at the sql server would be a more integrated solution.A linked server isn't required, but it's the only way i know to access a remote server from with in a stored procedure. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-04-15 : 16:28:01
|
| The problem with using a linked server is going to be performance. Depending on how the queries are written, you could find that the process takes a long time to extract the data from the source system.If you cannot add replication, there really isn't anything you are going to be able to do that is near real-time. The performance overhead is going to make this almost impossible to extract and load without causing issues on either the source system or the destination system.You have a couple of options - one is the use linked server and process everything in a stored procedure. Another option would be to use SSIS to build an ETL process. Either of these will need to be scheduled to run using a SQL Server Agent job.I believe the smallest increment available in an agent job is 1 minute - but, I could be wrong. I wouldn't schedule something like this that often though, because doing so will lock the destination tables - and block on the source system. It really depends on how long it takes to get the data from the source system, verify it - and load it into the destination.Jeff |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-04-15 : 16:47:58
|
| What kind of database are you accessing? DB2? Oracle? Something else? ODBC is a generic library and not the best performance-wise, if you can use OLEDB to access it it will perform better.And I agree with both of Jeff's statements: this really is a scenario for replication, and if that's not an option then SSIS is probably the best route. Whoever is asking to make this "as real time as possible" doesn't understand the limitations if an ODBC linked server is the only access you have. |
|
|
|
|
|
|
|
|
maintenance syncronisation