0

We have two servers, one is very slow and geographically far away. Setting up distributed queries is a headache because it does not always work (sometimes we receive a The semaphore timeout period has expired) and if the query works it can be slow.

One solution was to setup a job that populates temporary tables on the slow server with the data we need and then writes INSERT, UPDATE and DELETE statements to our server tables from the temporary tables so we have updated data on our faster servers. The job takes about one minunte and 30 seconds and are setup to run every 2 minutes. Is this bad practice and will it hurt our slower SQL Server box?

EDIT The transactions are happening on the slow server agent (where the job is running) and using distributed queries to connect and update our fast server. If the job runs on the fast server we get that timeout error every now and then

As for the specifics, if the record exists on the faster server we perform an update, if it does not exist we insert and if the record no longer exists on the slow server we delete...I can post code when I get to a computer

jmzagorski
  • 1,135
  • 18
  • 42
  • You need to clarify what you are currently doing. I think you are saying you have "insert, update and delete" triggers on the fast sever tables and applying the same transactions to slow server. Is this so? If not, what are you doing. The fact that you need 90 seconds of time out of every 2 minutes for you table duplication sounds like a bad idea. There may be significant opimization opportunities in the details of how this is done. – Gary Walker Jul 24 '14 at 21:06
  • Is only 1 server the source of the changes, or can changes be initiated on both servers? If both, is it reasonable to re-enginer so that all changes occur on the fast server and then propogate to the slow sever eventually. – Gary Walker Jul 24 '14 at 21:28
  • The changes occur on the slow server, the new server just needs to get the data from the slow server so our new application that uses the new server can consume the data. Setting up a connection string to the old server on our new application is slow as well – jmzagorski Jul 24 '14 at 21:40
  • Are you collecting the delta's via triggers as I suggested. About how many rows per minute, about how many bytes per. Approx percentage of Inserts vs. Delete vs Update. One Updates are you usually changing only a small percentage of the record. – Gary Walker Jul 24 '14 at 21:48
  • I originally had triggers collecting the deltas into a change table, but it was getting more complicated than I wanted to because I had to make sure each record was processed in order, which would have to be done with a cursor, which I wanted to avoid. Maybe I should revisit that? There should be more updates, then inserts, then deletes. Not sure specific numbers – jmzagorski Jul 24 '14 at 22:00
  • So you are sending the whole table each time? About How many rows. – Gary Walker Jul 24 '14 at 22:07
  • Server versions for the slow and fast server. If pre-sql 2008 is versin update a possible option. – Gary Walker Jul 24 '14 at 22:09
  • Set up SQL Server Replication for the table. – JodyT Jul 24 '14 at 22:47
  • Yes, replication is so obvious, I never even asked why they could not use it. See http://www.techrepublic.com/blog/how-do-i/how-do-i-configure-transactional-replication-between-two-sql-server-2005-systems/ for a nice article on doing just this. I just assumed they were on SQL2K or suchlike, did not sound like a novice question. – Gary Walker Jul 24 '14 at 23:24
  • Wow, SQL server has had replication a lot longer than I remembered. I can see it is mentioned in server 6.5, who knows, maybe Fred Flintstone used it too. – Gary Walker Jul 24 '14 at 23:40
  • Well replication was an idea but we were not copying all the data over , just the changes and we're not sure the speed with replication since these are busy and not well organized databases (prior developers were not friendly). We figured the less data sent the faster. Plus some of these tables dont have a primary key from the prior developer (erk!) We are on 2008 R2. I may just stick with the connection string in the new application and query from there. – jmzagorski Jul 24 '14 at 23:44

0 Answers0