April 21, 2003 at 5:28 pm
I am trying to implement log shipping with SQL Server 2k. I would like to use it to keep a secondary server up to date with the primary.
I get through the setup fine, but when it runs I get the following error:
"Unable to copy initialization file to the secondary server"
Is this because the agent cannot access the secondary server for some security reason? Is there a place I can look for a more descriptive explanation?
Thanks
April 22, 2003 at 7:23 am
The first step before you can begin log shipping is to ensure that the SQL Server login IDs are synchronized between the production and standby servers. This is important because SQL Server maintains separate security among servers, and you will be making a backup of a database on one server and restoring it to another. In order for the database user IDs (found in each database) to work on the standby server (where the database is restored), there must be a matching SQL Server login ID on that server, otherwise users will not be able to log into the database on the standby server.
April 22, 2003 at 7:58 am
You have to create a network share where database and transaction log backups will be and grant SQL Server services account full access to this network share.
April 22, 2003 at 2:08 pm
Thanks for the replies. Let me elaborate a bit.
The servers are on two different domains. When I go through the process, it appears I can browser the standby (remote) server. But, when the process begins, it gets that error above. Now, is this because the sqlserveragent on both boxes is started with different user/password. Does the prod server need drive mapping access to the remote server?
The share where the backups are stored on the production server are accessible by the sql server service.
Thanks for the help.
April 22, 2003 at 2:30 pm
Please ensure the share can be accessed by both SQL Server service accounts and enter 'network share name for backup directory' in UNC format, for exampe, \\tstsql01\logshipping, in the wizard.
April 22, 2003 at 3:16 pm
Allen, thank you for bearing with me.
I think I am being confused by the share. The share on the production server or remote server? Do the servers both need access to a share on the production server or the standby server, or both?
The message says unable to send initialization file to secondary server, it seems like may be having trouble accessing the share on the remote server? Is that correct?
Also, the production server is a cluster install. The standby is not and would have to use local Windows NT authentication (no domain) for services where the production is using Active Directory.
Edited by - ivy on 04/22/2003 3:55:26 PM
Edited by - ivy on 04/22/2003 3:55:58 PM
Edited by - ivy on 04/22/2003 4:22:56 PM
April 23, 2003 at 6:50 am
The share directory is the place your database backups have to be and those backups will be used to database restoration in standby server. The standby server has to be able to accessed it.
Because your standby server uses local service account and it will not be able to see the share directory in your production server. Change SQL Server services account to domain user account with proper NT/2000 privilege and ensure it can access the share directory in your production server. Your NT/2000 administrator will be able to help you.
In term of cluster installation, It does have impact to this share directory. You have to create a cluster file share resource in your production cluster servers and make sure it can be fail-over.
Again, you NT/2000 administrator will be able to help you on this too.
April 24, 2003 at 7:34 am
I see, thanks Allen. Much appreciated. So, my next question: How else can I achieve this. I cannot give access to production for mapping to shares from the standby server. Too much of a security risk to open firewall ports for mapping drives.
I would be happy with one update a day from production to standby. I currently get around it by ftp'ing the .bak files and restoring. Is this my best bet?
Thanks
April 24, 2003 at 7:54 am
The share doesn't necessary to be in your production server. It can in be your standby server too or even in third server as long as the SQL Server service accounts of both serevrs that involved the log shipping are able to access it.
The concept of log shipping is simple and you really don't have to use wizard to set it up and your method surely works.
April 24, 2003 at 11:05 am
I would recommend putting the share on your standby server. That way you have the tran log backups off the cluster also. The SQL services on the cluster need to be run under a domain account. The standby server will need to have a local account starting it's SQL services with the same name/password as the domain account used on the cluster. These accounts need to have DBO rights to the databases on both the cluster and the standby server. The account on the standby server will need to be added to the master DB and granted execute rights to the sp_applystandbylog stored procedure (or whatever proc. you are using for restore). The accounts will also need the following local security rights on all servers involved: 1)act as part of OS; 2)increase quotas; 3)log on as a batch job; 4)log on as a service; 5)replace a procedure level token; 6) bypass traverse checking; 7) lock pages in memory. Good luck...
April 24, 2003 at 12:03 pm
I believe I had trouble with this once. You don't have to map any shares. The key is that you can't share the log shipping folder to be the same place as the current folder for transaction log backups. Create a different folder location to share (like a subfolder) and then put that UNC in as the log shipping share.
April 25, 2003 at 9:18 am
That is great. Thanks for all your help. I will get this rolling and hopefully it will just click. Thanks again for all the help.
May 2, 2003 at 10:53 am
Ok, I think I am close. I need to explain where I am at.
The server service and agent service are starting up with the same uid/pwd on both servers (on the prd cluster the services are started up with a domain account..and on the standby server there is no domain..the services are started up with the same username and password but just a local account for that box)....I know both have access to the share on the standby server because I created a new db on the production, tried to setup log shipping (share is on standby)...told it to create a backup and initialize the db...When it started to run, I saw the .bak file being created on the standby server...so, prd has the ability to write at the share on the standby..but, on the next step, I still get the "Unable to copy initialization file to the secondary server"?
If I could, I'd like to go step by step on this from after I create a new db on production and have start the maintenance plan wizard:
1. Update data optimization information - no help needed here
2. Datbase Integrity check - no help needed here
3. Specify the database backup plan - no help needed here
4. Specify Backup Disk Directory - Does this need to use a share accessible by both servers or is this just the regular daily backup directory. I used a folder on the prd server since I don't think this needs to be the share accessible by both.
5. Specify Transaction Log Backup Disk Directory - Same question, does this need to be the share accessible by both? Again, I didn't use the share here...prd directory only accessible by prd
6. Specify the Transaction Log Share - Here, I put the share I created on the standby server with UNC \\machine\MSSQL\LogShipping\Test
7. Specify the Log Shipping Destinations - Clicked add...selected my standby server from the dropdown. It prefilled everything.
For the transactin log destination directory, should this be the same as the share above or should it be a seperate area? Also, all the directory stuff on this popup are using the regular directory names as you would see on that machine. e:\program files\mssql...etc I kept all the defaults
8. Initialize the destination database. I chose Perform a full database backup now
9. Log shipping scheduleds I left as defaulted for now.
10. Log Shipping Thresholds I left as defaulted for now.
11. Specify the Log Shipping Monitor - left as defaulted - uses the prd server and windows authentication
12. Reports to generate - I chose to write a report
13. Maintenance plan history - left as defaulted.
14. Database Maintenance Plan Wizard - Renamed and clicked next
15. Completing the Maintenance Plan Wizard - Clicked Finish
4 tasks are going to run now:
1. Backing up source database
2. Initializing Destination: StandbyServerName.LogShippingTest
3. Creating Plan for StandbyServerName.LogShippingTest
4. Creating DB Maintenance Plan
Ok, so after step 1 completes successfully, I see a LogShippingTest_logshipping_init.bak file on the remote share on the standby server. But when step 2 starts to run, it does for a few seconds, then the "Unable to copy initialization file to the secondary server machinename"? pops up and here we are.
Thanks for reading all of this, I appreciate the help you are giving.
Edited by - ivy on 05/02/2003 10:57:11 AM
Edited by - ivy on 05/02/2003 11:04:40 AM
May 2, 2003 at 11:41 am
I have a different configuration for my log shipping. Here are the elements to my setup:
1) Production DB - your maintenance plan can do anything you want except TranLog backups.
2) This stored proc. in master DB:
CREATE PROCEDURE sp_ShipLog
@DBName sysname, -- Name of the database to ship logs
@StandbyServer sysname, -- Standby server to receive/apply the logs
@BackupLocationPath nvarchar(256), -- UNC path to location of the log files
@UndoFile nvarchar(256), -- Undo file on the standby server
@MinuteInterval int, -- How often to ship the log in minutes
@HourDuration int -- How long to run the log shipping in hours
AS
-- ****************************************************
-- Desc: Makes a "hot" backup of a database by dumping the log
-- for the specified database to the standby server at
-- the requested interval. In addition, the log is then
-- applied at the target server. This procedure assumes
-- that execution under a domain account that has the
-- neccessary access to invoke remote stored procedure on
-- the standby server. The standby server must be setup as
-- a linked or remote server from the primary.
-- ****************************************************
DECLARE @SQLCmd nvarchar(510)
DECLARE @StartDateTime DateTime
DECLARE @EndDateTime DateTime
DECLARE @NextBackupDateTime DateTime
DECLARE @CutoffDateTime DateTime
DECLARE @DateTimeString nvarchar(20)
DECLARE @BackupFileName nvarchar(256)
DECLARE @ErrCapture int
DECLARE @ErrDesc nvarchar(510)
-- SET the start time to preceed the current date/time
SET @StartDateTime=current_timestamp
-- SET the end date to be the current hour plus the end hour parameter
SET @EndDateTime = dateadd(hour, @HourDuration, @StartDateTime)
-- Set the next backup time to the current time
SET @NextBackupDateTime = current_timestamp
-- Do until specified stop time
WHILE @StartDateTime < @EndDateTime
BEGIN
-- Generate the date/time stamp file format
SET @DateTimeString = convert(varchar(20),current_timestamp,120)
SET @BackupFileName =
@BackupLocationPath + '\' + rtrim(@DBName) +
'_' +
substring(@DateTimeString,1,4) +
substring(@DateTimeString,6,2) +
substring(@DateTimeString,9,2) +
'_' +
substring(@DateTimeString,12,2) +
substring(@DateTimeString,15,2) + '_LOG.BAK'
-- Backup the database log to the specified location
PRINT 'Backing up ' + @DBName + ' to DISK=' + @BackupFileName
BACKUP LOG @DBName TO DISK=@BackupFileName
SET @ErrCapture = @@ERROR
IF @ErrCapture <> 0
GOTO Error_Handler
-- Restore the database on the target server and leave it in standby mode
-- For this to work, the standby server must be setup as a linked or remote
-- server and must have the sp_ApplyShipLog stored procedure in the Master
-- database.
SET @SQLCmd = N'Execute [' +
rtrim(@StandbyServer) + '].master.dbo.sp_ApplyStandByLog ' +
'N''' + rtrim(@DBName) + ''',' +
'N''' + rtrim(@BackupFileName) + ''',' +
'N''' + rtrim(@UndoFile) + ''''
PRINT @SQLCmd
EXECUTE sp_ExecuteSQL @SQLCmd
SET @ErrCapture = @@ERROR
IF @ErrCapture <> 0
GOTO Error_Handler
-- SET the next iteration time to be the current time plus the minute interval
SET @NextBackupDateTime = dateadd(minute,@MinuteInterval,@NextBackupDateTime)
WAITFOR TIME @NextBackupDateTime
END
RETURN
Error_Handler:
SET @ErrDesc =
'sp_ShipLog Error ' + convert(varchar(9),@ErrCapture) + ' occurred on '
+ 'database %s to server %s using backup file %s'
RAISERROR (@ErrDesc,19,1,@DBName, @StandbyServer, @BackupFileName)
WITH LOG
RETURN
GO
***Pay attention to the input parameters. They will allow you to set up intervals and run durations.
3) A TSQL job set up for SQL Agent to run the stored proc in step 2. Here is an example:
exec sp_ShipLog
@DBName='Production DB name here',
@StandbyServer='SERVERNAME',
@BackupLocationPath='UNC to share on standby',
@UndoFile='UNC to share on standby',
@MinuteInterval=240,
@hourDuration=12
This example will is started every morning and runs for 12 hours, performing log shipping every 4 hours.
4)This stored proc needs to exist in standby master DB:
REATE PROCEDURE sp_ApplyStandByLog
@DBName sysname,
@BackupFileName nvarchar(120),
@UndoFile nvarchar(256)
AS
DECLARE @RestoreCmd nvarchar(510)
DECLARE @ErrDesc nvarchar(510)
SET @RestoreCmd =
'RESTORE LOG ' + @DBName
+ ' FROM DISK=''' + @BackupFileName
+ ''' WITH STANDBY=''' + @UndoFile
PRINT 'Executing ' + @RestoreCmd
RESTORE LOG @DBName FROM DISK=@BackupFileName WITH STANDBY=@UndoFile
IF @@ERROR <> 0
BEGIN
SET @ErrDesc =
'sp_ApplyStandByLog Error ' + convert(varchar(9),@@Error) + ' occurred on '
+ 'database %s restoring backup file %s'
RAISERROR (@ErrDesc,19,1,@DBName, @BackupFileName)
END
RETURN
GO
5) You will need to create 1 share on the standby server where your tranlogs will be shipped to and have the other items listed in my previous post present.
6) Create a standby DB on using a full backup. Take one Tlog backup manually and restore to standby db, leaving it in a recovery state where you can apply more tlogs.
7) When you run your TSQL job, it should create a Tlog backup on the stanby server, and apply the log to the standby DB which should be waiting in the correct recovery mode.
Hope this helps.
May 2, 2003 at 12:01 pm
quote:
6. Specify the Transaction Log Share - Here, I put the share I created on the standby server with UNC \\machine\MSSQL\LogShipping\Test
You created directory but you do not actually share it. Right click "Test" folder --> properties --> sharing tab --> choose 'shared as' and enter the share name, for example, name it as 'LogShippingTest'.
Once the share is created, you will be able to see it by going to 'Start' --> Run --> type \\machinename and shared directory name 'LogShippingTest' will be list.
In the setup of "Specify the Transaction Log Share" in the wizard, type shared name \\machine\LogShippingTest.
Edited by - Allen_Cui on 05/02/2003 12:11:00 PM
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply