February 11, 2009 at 2:14 am
Hi
I have been tasked for doing Cross Domain Log Shipping and am pretty new to this one.
I Currently have the following setup
Domain1 - Primary Server 1(Windows Server 2003,SQL Server 2005, SP3)
Domain2 - Secondary Server 1( Windows Server 2003,SQL Server 2005 SP3)
I work in Domain1 and can log on to Secondary Server in Domain 2 locally as this is what i am allowed.
I have the database on Primary Server and would like to do Log Shipping to Server1 on Domain 2.
Steps that i have followed on primary Server.
1. Create a Shared Folder on Primary Server(D1) for backing up information from the Log Shipping.
2. Backed up the data manually on Primary Server .
Steps followed on Secondary Server(D2).
1. Restored the backup with Standby mode and i can see the tables and is accessible.
I have used SSMS on the Primary Server and enabled Log Shipping and set the network path to
\\Primary Server1\backup and the local path to D:\Backup.
Added the Secondary Server and choose Secondary Database is already initialized.
For the Copy Files chose the destination folder which is D:\Backup on the secondary server.
For the restore Files chose Standby Mode and disabled the Disconnect users in the database when restoring backups.
I have set the job to run every hour.
I did manually test the following.
1.Generate the transaction logs and put it in shared folder (Server1). This works.
2.Copying the logs from the Primary Server 1 to Secondary Server. Does not work.
Please find the error message
02/11/2009 12:53:16, LSCopy,Error,0,SeconServer\Instance,LSCopydata,(Job outcome),,The job failed. The Job was invoked by User sa. The last step to run was step 1 (Log shipping copy job step.).,00:00:15,0,0,,,,0
02/11/2009 12:53:16,LSCopydata,Error,1,SecondaryServer\instance,LSCopydata,Log shipping copy job step.,,Executed as user: SecondaryServer\localuser. The step failed.,00:00:15,0,0,,,,0
02/11/2009 12:53:16,LSCopydata,Unknown,1,SecondaryServer\Instance,LSCopydata,Log shipping copy job step.,,2009-02-11 12:53:31.21*** Error: Could not retrieve copy settings for secondary ID '00222c94-1201-4ff7-9d06-6e0ca1e39b8a'.(Microsoft.SqlServer.Management.LogShipping) *** Exit Status: 1 (Error),00:00:15,0,0,,,,0
02/11/2009 12:53:16,LSCopydata,Unknown,1,SecondaryServer\Instance,LSCopydata,Log shipping copy job step.,,Microsoft (R) SQL Server Log Shipping Agent 2009-02-11 12:53:16.68----- START OF TRANSACTION LOG COPY -----,00:00:00,0,0,,,,0
Please Help.
February 11, 2009 at 12:04 pm
My first question would be, can you connect to the windows share \\Primary Server1\backup from the secondary server via windows explorer? Are the 2 domains trusted?
I have, in the past, set up cross domain log shipping but will need to know your network setup before being able to provide suggestions.
please supply results of following scripts from all three log shipping servers(primary,secondary,monitor)
select primary_server_name from msdb.dbo.log_shipping_primaries;
select secondary_server_name from msdb.dbo.log_shipping_secondaries;
select @@SERVERNAME;
February 11, 2009 at 2:00 pm
If you're using 'sa', does they need to have the same passwords on both servers ?? Seems I recall that was the case replicating across domains. I'd suggest another account than 'sa' though.
February 11, 2009 at 11:16 pm
Hi Robert,
From the Secondary Server , i can access the shared folder \\Primary Server\Backup but i have not setup a trust relationship with the 2 domains. I did not want to.
That's Strange Robert when i used the queries it is not showing me any result,except for the last one
select@@servername it is showing me name of the secondary server with the instance or probably the Database name coz the instance and the DB name is the same.
I have set the sa password same on both the servers.I have created same local users on both the servers.
Thanks.
Liju
February 12, 2009 at 1:31 am
Hi Robert,
sorry for that....
it does give me the names of the Primary Server and the secondary server with the
February 12, 2009 at 1:46 am
Hi Robert,
It does give me the name of the servers but not from that command.
select * from [log_shipping_primary_databases]
select * from [log_shipping_primary_secondaries]
that does give me the names with the same primary id in both the tables.
February 12, 2009 at 8:41 am
Ok, when you connect to the share on the primary server from the secondary, Do you have to supply a username/password of a user on the primary domain? If so, you can try adding a stored username/password.
On the secondary server go to control panel => Stored User Names and Passwords, click add. Type in the name of your primary server, and username and password that you use when you are connecting to share. What this does is allows all connections that originate this server to use this username and password when prompted while connecting to the primary server.
Also, your results from the the statements I provided are not sufficient. I do not want to know the database name. I want to know the server name defined in log shipping If you setup log shipping using FQDN( .net) there may be some issues. When I set it up (its been over a year since I have done it) I set it up using FQDN, then had to manually change the values in some of these tables so the server name used by log shipping matched the actually server name, which would not be Fully qualified.
February 15, 2009 at 1:38 am
Hi Robert,
Thanks for the reply . Please find these results
When i am trying to access the Shared Folder on the Primary Server from the Secondary Server, it is not asking for any password but it is still opening the shared folder(Primary Server).
I did check if there are any stored password and there is none.But thats strange.i checked if the shared folder is showing up in "net use" and it is not showing up.I also restarted the secondary server and i am still able to access the shared folder.
The servername is without the FQDN.
select @@servername on Primary Server produces the following result.
KDatabase01\instance
select @@servername on Secondary Server produces the following result
PortalDatabase\instance
When doing logshipping i did not specify FQDN.
Just one thing Robert.
On 10th of February, when i started the Log Shipping and initially ran the BackupDatabase,it ran that. When i did the Copy Database initially i got an error message about Access Denied Error 5.then what i did was changed the log on properties for the SQL Server Agent so that a local user created on the secondary server could run the sql server agent.I ran the copy database again and the transcation file got copied from the primary server to the secondary server. But then only i remembered that i was playing on the live database so i deleted the settings for log shipping.Since then it has not worked.
Now i am wondering why did i delete it 🙁
Well i have been going through this all week.I have typed the first half of the mail in the morning and have been working with this to get this round up and working.Guess what....
now it is working.
Changes that i have made.
In the copy and restore jobs, the servername was referring as KDatabase01\InstanceName in the sqllogship.exe command but it should be KDatabase01. Changed the server name to match that.Did the same for Restore Job also.Ran both the jobs and i am able to see the change happening in the secondary database.
Thanks Robert for your help.
I have setup the replication to happen every 30 min and am monitoring it for 1 day. Will keep you updated Robert.
Thanks
February 15, 2009 at 6:06 am
Hi Robert,
All was working fine until i started to use the Portal Software on the secondary software to point to the Database(Read Only-the one which is used for Transaction Log Shipping). Then onwards for restoration it started saying that the database is in use. and it hasnt restored.
When setting up LogShipping,i know i did not select the option of Disconnect users in the database when restoring backups.Should i use that option.
will the users using the Portal be disconnected during that restoration process
February 16, 2009 at 8:35 am
I assume the "Portal software" you refer to has a persistent connection to the read-only database which would make it in use all the time. Thus, If you don't disconnect the users you probably will not be able to do a restore.
If you do disconnect users during restore, whether they will be disconnect from the Portal would really be dependent on how the portal software handles the disconnection.
February 16, 2009 at 9:08 am
Hi Robert
When doing transaction log shipping and choosing the Database Option as Standby/Readonly, the users will get disconnected when the last step of restore is being done.
I have tested this and had to restart the Portal Services and then everything works fine.
So i added another step in the Restore Job to restart the Portal Services and they do work fine.
I did set a time delay to run the restoration process every 4 hours and is working fine.
Now i have come to a situation.
Would i be able to use this read only database for doing some comparison against an oracle view and generate the result from that and insert it to an Oracle Database table.
Thanks
Liju
February 16, 2009 at 6:08 pm
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply