Transaction Log Shipping Copy job update

I need to configure TransactionLogShipping of a database between two database servers. Primary database is on production server that is SQL Server 2008 Standard installed on Windows Server 2003 x32 (used as web server as well). Secondary database is on SQL Server 2008 Developer installed on Windows Server 2008 x64 (I’ll use it as development database with almost up-to-date data for developing and testing heavy statistics reports). And both servers are in DIFFERENT networks.

OK. Let’s configure it!

I would not describe all the steps in detail that are required for TransactionLogShipping (you can find overview here) just step that I had problems with. My problem was copying transaction log over network. Different networks remember. Everything I googled was about having both database servers in the same network and creating user account with the exactly same name on both machines and full access rights for folders used for creating and restoring backups. Creating user accounts on both with full access rights especially on primary server that is a web server as well did not sound like a good idea for me. I tried to reuse something that I have and that is configured, tested and working 24/7 without opening potential security flaw.

I have an FTP server on primary server. And I know that LogShippingCopy job could be adjusted to reuse SSIS package for example that will take care of copying transaction log files from primary database server. Sounds like an idea!

Backup job requires network path for the folder where it will put log backup. It’s compulsory parameter for it. I have created a shared folder with FTP server folder and provided the path to BackupJob configuration dialog.

RestoreJob on secondary server is configured as in every other case.

Copy Job

Final configurations looks like:

FtpCopy package: That was the biggest problem for me.

I have created simple SSIS package with single FtpTask.

I tried it on my development machine locally (Vista x32, BIDS 2008) and it is working ok. Deployed to secondary database server started copy job manually – nothing is copied !? OK. A have BIDS installed on secondary server (since it’s a development one). Opened the same package from there and I’m getting "0xC001602A at FtpCopy, Connection manager "FTP Connection Manager": An error occurred in the requested FTP operation. Detailed error description: The operation timed out" every time. !?!? OK. I’m trying to reuse Scripting Task to copy files over Ftp in order to get detailed exception or at least step through code and see the flow. Wrote the code in C# and placed a breakpoint. I’m pressing F5 hoping that I’m seconds away from the detailed error description thus the solution. But the Output window writes: “Warning: 0x7 at Script Task: Cannot debug script tasks when running under the 64 bit version of the Integration Services runtime.” LLL

OK. Let’s check FTP logs from the Ftp server. When running either FtpTask or ScriptTask from my development machine (Vista x32) I see log entry code 226 which means “Transfer Complete” but when running the same FtpTask or ScriptTask from secondary server (WS 2008 x64) log entry is 426 Connection closed; transfer aborted. Error: 64.

Let’s google, maybe it’s specific x64 runtime problem. I have founds this MS forum entry that confirmed my suspicions. It’s related to running package on x64 runtime.

My last idea was to reuse ExecuteProcessTask and will start bat file with ftp commands for copying files over ftp.

Now everything is OK. Ftp log entries have 226 code and I see transaction log files in destination folder!

Tried complete TransactionLogShipping sequence – works like a charm! J But almost full working day was spent in order to make it working.

You can see the flow diagram below:

PS:

• Average transaction log file is 2.5MB.
• If you have existing transaction log backup jobs either stop them or re-point them to save transaction log to the same folder as log shipping backup job does as Log Sequence Number(LSN) must me continuous in oder for log shipping restore to be successful.

I hope this post will help someone.

Regards,

Oleh