Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cross Domain Log Shipping Expand / Collapse
Author
Message
Posted Wednesday, February 11, 2009 2:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
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.
Post #654543
Posted Wednesday, February 11, 2009 12:04 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,442, Visits: 2,724
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;



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #655155
Posted Wednesday, February 11, 2009 2:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:27 AM
Points: 2,832, Visits: 8,518
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.


Post #655229
Posted Wednesday, February 11, 2009 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
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

Post #655410
Posted Thursday, February 12, 2009 1:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
Hi Robert,

sorry for that....
it does give me the names of the Primary Server and the secondary server with the
Post #655448
Posted Thursday, February 12, 2009 1:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
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.
Post #655451
Posted Thursday, February 12, 2009 8:41 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,442, Visits: 2,724
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.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #655778
Posted Sunday, February 15, 2009 1:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
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
Post #657303
Posted Sunday, February 15, 2009 6:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
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
Post #657317
Posted Monday, February 16, 2009 8:35 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,442, Visits: 2,724
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.


Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #657795
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse