Best way to move tables between SQL Servers/Machines? 2

[MarkB2]

I have a web server which needs to get a snapshot of data overnight. On the local machine I have stored procedures which create tables containing changes with a flag indicating that it is an (A)dd, (C)hange, (D)elete or (L)oad. The selection and creation of the change tables takes just minutes for many hundreds of thousands of records in 12 tables. My problem is what to do with these tables of changes which are to be used to update the web server (hosted).

My first attempt was to create text files, FTP them, bulk insert them into a temporary table to access the update flag, delete the records in the web tables based on PK and then do a bulk insert. Because of special characters I had problems dealing with the text files and abandoned this approach.

My second (current) attempt was to link the web server to the production server. I then issued a delete to the web server for anything in my changed tables and then did an insert into the web server from the production server. Because of limited bandwidth (I assume) I have timeout problems and performance problems. A procedure that is copying 100K of records fails after 2 hours...

I am looking for a method that I may not have thought of. Updating a web servers data has to be something that is popular. I think my main problem is the volumn of data.

Is there a way to export a table into a text file with formatting and then FTP that to the web server which in turn would import that table into a temporary table that could be used to do the update?

Any ideas would be greatly appreciated.

TIA
Mark

 

[SQLSister]

Just exactly how were you running this update? Were you using a cursor or a set-based statement. 100K records is not very much for SQL server and shouldn't take two hours. I regularly import the equivalent of that in 5 minutes or less.

Questions about posting. See faq183-874

 

[MarkB2]

Here is the relevant code.

DELETE t_remote
   FROM LinkedServer.Database.dbo.WEB_FROI t_remote
   WHERE EXISTS (SELECT fr_pk FROM web_froi t_local WHERE t_local.fr_pk = t_remote.fr_pk)

INSERT INTO LinkedServer.Database.dbo.WEB_FROI
   (
   Field1, Field2...
   )
   SELECT
      Field1, Field2...
      FROM web_froi AS t_local
      WHERE t_local.fr_u_fl <> 'D'

I tried to keep everything set based for speed sake. The fr_pk field is defined as integer and is a unique key. The fr_u_fl is the update flag which also is indexed.

Mark

 

[donutman]

I would try to setup that as a scheduled DTS job on the remote server. Have it FTP (download) the file to a local drive and import the data. Create the file with another DTS job. Why try to maintain a connection to the linked server when you are doing a do-it-yourself replication job?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Ok, so no cursor, then you must have a bandwidth problem or a connection problem or possibly a locking problem. You are absolutely sure you can connect to the linked server? Have you tried sending smaller batches? See the follwong FAQ for a method for this.
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes faq183-3141

At the time you tried to do the import and failed, what else was running on the system? Could another user be locking the relevant records? Did it fail on the delete or the insert?

Did you get any errors on either the SQl Server or network error logs?

YOu could run profiler on your prodution server while you attempt to do the import to see what is actually getting through to the SQL Server and wht else is happening at the same time.


Questions about posting. See faq183-874

 

[MarkB2]

Thanx for your response. See the answers burried in your questions.

>You are absolutely sure you can connect to the linked
>server?

Yes, if I use EM I can see the linked server, tables and browse the tables.

>Have you tried sending smaller batches?

Yes, a batch of 300 records goes through without a hitch, although not extreemly fast.

>At the time you tried to do the import and failed, what
>else was running on the system?

The changes are being extracted from the production tables into tables specific for this purpose. The web tables are being hit by the web application which is readonly. During the day it is hard to say what the load is at any one time. There are about 5 web sites on this server, only one of which is mine.

>Could another user be locking the relevant records?

The web application selects a result set, builds the web page (asp code) and releases the record set.

>Did it fail on the delete or the insert?

The insert fails. I was doing a (l)oad, which deletes and re-creates the web table, skips the delete and proceeds to doing the insert.

>Did you get any errors on either the SQl Server or
>network error logs?

Neither the local or remote SQL logs have anything in them. I am not secured to get at the system logs.

>YOu could run profiler on your prodution server while
>you attempt to do the import to see what is actually
>getting through to the SQL Server and wht else is
>happening at the same time.

I will give the profiler a try.
========================

I assume from your response that I am on the right track in doing this via the linked server. During the day I am competing with a 10 floor building of users. All internet access for this building is being accomplished with an 8MB connection. At my office I have a 1.5MB connection and I do not have as many problems. I do notice that the initial connection to the linked server takes a long time.

Thanx
Mark

 

[SQLSister]

Well I don't see any way to optimize the code you gave. It's already set-based and not very complex.

Sounds like the server is swamped or the network has too many users and is busy. I used to work one place where the network had twice as many users as its design capacity. It used to take half an hour just to log in.

Can you schedule this for off-hours in the middle of the night?



Questions about posting. See faq183-874

 

[MarkB2]

Off hours is when it will run on a regular basis. At night I run into other things that are out of my control like backups over the network...

I am reading the FAQ about using "Batch Size" which may help to split the job up. I don't know if it will make any difference because I think it is a bandwidth and timeout issue. As far as the remote servers log space, I would think that I would have seen something in the SQL log if it was having a problem. It may be worth a try because I was doing a "bulk insert with (tablock)" which treats the log differently.

I go back to my first question of, is this the best way to do this? It seems that I may be hitting a tack with a sledge hammer.

 

[SQLSister]

YOu might check to see if the log is set to autogrow. Sometimes things timeout because the log can't grow fast enough. But if 300 records were slow, I would doubt that is your problem.

Bulk insert might still work, it isn't logged so that should help the speed some. How were you setting up the text files? You could try fixed length fields if you have trouble finding a good delimiter. Or you could export to a a csv file and then try to load that.

Questions about posting. See faq183-874

 

[donutman]

I don't understand the insistence on an approach that requires a join of sorts to be performed across an internet connection with mega tables. Solve the special character problem and I'll bet your use of server resources will drop considerably.
I'm sure there are work around like FTPing a zipped access database, Excel file or even a detached SQL Server db file (last choice).
Solutions aren't necessarily elegant just because they're pure SQL.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[MarkB2]

Thank you sqlsister/donutman for working with me on this.

I agree that the linked server solution is giving me more problems than it is worth. The text based solution worked but kept breaking due to special characters in the text files. Special characters were mainly a problem in a notes file that basically had the primary key and 7000 characters of free form text which could contain CR, CRLF, tab characters... Regardless what I used for delimiters, the application would put something in the notes that broke the bulk insert.

To shift gears a bit here is what I was using and maybe an improvement in that technique is the way to go.

Following is the extract that created a text file:

DECLARE @Add_Load [char] (1)
SET @Add_Load = 'A'
IF NOT EXISTS (SELECT * FROM dbo.sysobjects  WHERE name = 'WEB_FSTATUS_PK' AND type = 'U')
   BEGIN
   CREATE TABLE [dbo].[WEB_FSTATUS_PK] (
      [FS_PK] [int] NOT NULL,
      [FS_CHG_DT] [datetime] NOT NULL )
   CREATE INDEX [FS_PK] ON [dbo].[WEB_FSTATUS_PK]([FS_PK]) ON [PRIMARY]
   CREATE INDEX [FS_CHG_DT] ON [dbo].[WEB_FSTATUS_PK]([FS_CHG_DT]) ON [PRIMARY]
   SET @Add_Load = 'L'
   END

DECLARE @last_change datetime
SELECT @last_change = MAX(fs_chg_dt) FROM web_fstatus_pk

-- create a blank web_fstatus file
EXEC sp_web_fstatus

-- bump the status database against last runs pk file
INSERT web_fstatus
   (
   [FS_U_FL],[FS_PK],[FS_CODE],[FS_DESC]
   )
   SELECT
      @Add_Load,[FS_PK],[FS_CODE],[FS_DESC]
      FROM fstatus
      WHERE
         NOT EXISTS (SELECT * FROM web_fstatus_pk WHERE web_fstatus_pk.fs_pk = fstatus.fs_pk)
   UNION
   SELECT
      'C',[FS_PK],[FS_CODE],[FS_DESC]
      FROM fstatus
      WHERE
         EXISTS (SELECT * FROM web_fstatus_pk WHERE web_fstatus_pk.fs_pk = fstatus.fs_pk AND web_fstatus_pk.fs_chg_dt <> fstatus.fs_chg_dt)
   UNION
   SELECT
      'D',[FS_PK],'',''
      FROM web_fstatus_pk
      WHERE
         NOT EXISTS (SELECT * FROM fstatus WHERE fstatus.fs_pk = web_fstatus_pk.fs_pk)

-- build the pk file needed for the next run
DELETE web_fstatus_pk
INSERT web_fstatus_pk
   SELECT fs_pk, fs_chg_dt
   FROM fstatus

The above used a seed table (web_fstatus_pk) from the prior run containing the primary key and a change date that is in the parent table. It basically selected any record which was changed since the last run into a table of changes (web_fstatus).

This table (web_fstatus) now contains the changes which need to be updated to the remote server. This is the table that is being used by the procedure updating the web server via the linked server that is causing the problem.

My first approach was to then create a text file from the extract, zip it and FTP it to the web server for processing by another procedure. Following is the way a text file was created.

DECLARE @retcode int, @xp_cmd varchar (500)
SET @text_file = @base_dir + 'Data\' + @text_file + '.txt'

-- build a text file from the web extract
SET @xp_cmd = 'BCP "SELECT * FROM web_fstatus" queryout "web_fstatus.txt" -c -r\0'
EXEC master..xp_cmdshell @xp_cmd

-- put the file into a zip file which will be ftp'd
SET @xp_cmd = 'PKZIP -ex -a extract.zip web_fstatus.txt'
EXEC master..xp_cmdshell @xp_cmd

The above then took the extracted text file and placed it into a zip file which contained all the extracted files. This zip was then FTP'd to the web server which then unziped the text files into a specific directory.

A procedure was then run as follows on the web server to process the text file and update the web database as follows:

CREATE TABLE [dbo].[wk_file]
   (
   [wk_update] [char] (1) NULL,
   [wk_pk] [int] NULL,
   [wk_junk] [varchar] (8000) NULL
   )

DECLARE @bulk_insert varchar (500)
SET @bulk_insert='BULK INSERT wk_file FROM "web_fstatus.txt" WITH (TABLOCK, MAXERRORS=10000, ROWTERMINATOR='+CHAR(39)+'\0'+CHAR(39)+')'
EXEC (@bulk_insert)

IF EXISTS (SELECT * FROM wk_file WHERE wk_update = 'L')
   BEGIN
   -- we are doing a load, create a blank web_fstatus table
   EXEC sp_web_fstatus
   GOTO SkipDelete
   END

CREATE INDEX [wk_pk] ON [dbo].[wk_file]([wk_pk]) ON [PRIMARY]

DELETE web_fstatus
   WHERE EXISTS (SELECT wk_pk FROM wk_file WHERE wk_file.wk_pk = web_fstatus.fs_pk)

SkipDelete:
DROP TABLE wk_file

BulkInsert:
SET @bulk_insert='BULK INSERT web_fstatus FROM "web_fstatus.txt" WITH (TABLOCK, MAXERRORS=10000, ROWTERMINATOR='+CHAR(39)+'\0'+CHAR(39)+')'
EXEC (@bulk_insert)

The above then bulk inserts the text file into a work table which is then used to delete any record bing updated in the web table. The procedure then bulk inserts the changes into the database.

I hope that I did not chop out anything critical but I think you should get the jest of what I am or was doing.

Maybe as donutman suggested, the text file is not the way to go. How would you suggest I change the above?

 

[donutman]

Create a transfer database on the local server. Insert the appropriate data into the tables within that database. Backup the database, zip it and FTP it to the remote server. Unzip, and restore. Finally insert it into the appropriate tables of the web server.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[MarkB2]

Thanx both donutman and sqlsister. Just an FYI, I abandoned the linked server idea. It had too many unpredictable problems and my procedures were breaking too often. I also had little control over many of the items that were causing performance and connection problems.

The solution I tried today and seems to be working was what donutman suggested. Create a transport database containing just the tables to be transferred. I created the transfer database and loaded it up with the tables/updates to be transferred. I then backed it up, zipped it and FTP'd it to the web server. 500MB of backup zipped to 100MB which FTP'd in a reasonable amount of time based on available bandwidth contention. On the receiving end I unzipped the backup, restored the database and ran the update against the web database.

A side benefit to this is I loaded the transfer database with all the stored procedures needed to define the tables, extract the updates from the production database and update the web server database. Any changes to the table definitions, or extracts are done on the local machine and automatically transferred to the web server with the database.

Thanx again to both of you, you both earned a star.

Mark

 

[donutman]

Mark, you probably already thought of this, but it would be best to set the recovery mode to simple for the transfer database at both ends.
Thanks for the star...it's been a rough week.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]