June 25, 2009 at 3:18 pm
Hi,
We are planning to implement Log shipping in Active\Passive cluster setup, sql server 2005.
1. What happens when the sql group moves to Passive node because of fail over? does the log shipping breaks?
2. If yes, how to reconfigure the log shipping? do we need to configure from passive node? or bring the sql instance group to Active node and configure again?
how exactly Log shipping works in clustered sql server? Please provide me some documentation?
thank you very much
June 25, 2009 at 3:46 pm
Make sure the share you are dropping the transaction log backups to for the secondary server to grab is a clustered resource (which means it will need to be on a clustered disk as well - which it should be anyway, otherwise SQL Server wouldn't be able to back up to it). Have that resource in the SQL Server cluster resource group, so if SQL Server fails over, the share goes with it.
If the cluster fails over, the share will also fail over, which means the secondary server can continue to retrieve the log backups.
Attached are some screen dumps of the configuration for one of our a/p clusters that use log shipping.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
June 25, 2009 at 3:50 pm
In a cluster the SQL instance is a virtual instance, its name does not change when it fails over and there is only one set of database files. Therefore logshipping from a clustered instance is identical to that on a standalone instance and it is not affected by a failover.
---------------------------------------------------------------------
June 25, 2009 at 3:54 pm
good point Scott I forgot about making the share a cluster resource, sorry
---------------------------------------------------------------------
June 25, 2009 at 5:37 pm
Thank You Scott, George,
According to Scoot, We need to create a share LogShipLogDump$ on Node1(Active) on the cluster and as well as in secondary server right?
We have a Backup group in the cluster administrator having Z drive in it.(Currently using for keeping the backups)
Can I create a folder called Transaction Logs in Z drive(Z:\Transaction Logs) in Node1 and also same folder in secondary server in same drive i.e Z:\Transaction Logs and give these paths while configuring log shipping through the wizard? OR
Do I MUST need to create a share like you mentioned in your screen shots?
and give the share name and path as you mentioned in your last screen shot?
Currently the backup of all databases on node1 goes to Z drive(Z:\Backups), which is in Backup group.And its failing over to node2 along with sql group, some thing happens and the backups are going to same drive (Z:\Backups) after fail over to node2 with out problems
Please see the attachment how I currently configured and please help me in configuring log shipping in a better way.
Thank you
June 25, 2009 at 7:24 pm
On the primary server, you need to create a folder (or use an existing one) on a clustered disk to hold the log backups. This disk will need to be a dependency of SQL Server, otherwise SQL Server won't be able to write to it. Make sure you set the correct NTFS permissions so the secondary server can access the folder (from memory, the SQL Agent service account of the secondary server will need Read access).
On the secondary server, you will need to create a new folder or use an existing one to hold the backup files. If the secondary server is also a cluster, you will need to make sure this folder is on a clustered disk that is a dependency of SQL Server. It does not need to be shared. The SQL Agent service account of the secondary server will need read+write access to this folder.
Then use Cluster Administrator to create a new File Share resource in the same group as the SQL Server services. Set the correct share permissions so the secondary server can access it (as per the NTFS permissions). You must use this share name in the log shipping wizard to specify where the log backup files will be stored so the secondary server can access them.
See attached screenshots for creation of clustered share resource. Note: the share name can be whatever you want it to be, as long as it conforms to naming standards that Windows expects. Avoid using spaces in the share name. The $ on the end is optional, and makes it a hidden share, so people can't see it when they browse the network. In my example, all SQL Server resources are in the Disk Group 1 cluster group. The folder I created was S:\LogShipDump.
Don't forget to bring the resource online after you have created it.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
June 26, 2009 at 10:45 am
Thanks Scott,
This disk will need to be a dependency of SQL Server, otherwise SQL Server won't be able to write to it
.
We have the backup drive in backup group and it has no dependency on sql server, but still it writing the backups to the backup drive-----could you plz clarify this??
Sql group -> D, E, F, and T drives (data, secondary data, log and tempdb)
Backup group -> Z drive(backups)
Here I'm not able to add the Z drive as a dependency on sql group?
Please see my attached screen shots in the previous post. I'm trying to add Z drive as dependency on sql group by going to sqlserver(ins1)->properties->dependencies->modify->here there is no Z deive to add as a dependency? where I'm going wrong?
I’m planning to configure log shipping in the following way.
In cluster Administrator, we have 2 groups.
Primary Server is A/P cluster.
Secondary Server Standalone
1.Create a folder in Z drive called Logshipping_Backups in Primary Server
2.Create a folder in Z drive called Logshipping_Backups in Secondary Server
3.While configuring Log shipping, Give the path Z:\ Logshipping_Backups in Backup Settings
4.In Copy tab in Log shipping wizard, give the path as \\SecondaryServer\z$\ Logshipping_Backups
Please advice the above steps makes sense or do I need to correct any steps?
June 27, 2009 at 9:13 pm
Hi Scott,
Could you please clarify me the above so that I can get start the Log shipping configuration
thanks
kln
June 28, 2009 at 4:09 pm
That's pretty much the steps required.
In my experience with SQL Server 2005, SQL Server won't backup to a drive unless it is added as a dependency to the SQL Server service. You need to modify the properties of the SQL Server clustered resource to add the drive as a dependency (this will require SQL Server to be stopped). If your experience is different & working, then stick with what you've got.
To avoid confusion, I would call the folders something different between primary & secondary.
1. On primary, create Z:\LogShipping_Backups_Out
2. Create clustered file share on Z:\Logshipping_Backups_Out in SQL cluster group
3. On secondary, create Z:\LogShipping_Backups_In
4. Configure Primary server, provide share & path for backup_out folder (screenshot 1)
5. Configure Secondary server, provide path for backup_in folder (screenshot 2)
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
June 28, 2009 at 6:50 pm
Scott Duncan (6/28/2009)
In my experience with SQL Server 2005, SQL Server won't backup to a drive unless it is added as a dependency to the SQL Server service.
Not quite true - it is true that you cannot see the drive using the client tools if the drive is not a dependency, but that does not mean you cannot use it.
The problem comes into play when you have a different resource group that contains that drive. If that resource group exists on a different node from the node where SQL Server resides, then that drive is no longer accessible directly.
However, if you use UNC naming to access the drive then it wouldn't matter what node the group is hosted on.
I would recommend putting that drive in the same resource group as SQL Server. Whether or not you make SQL Server dependent upon that drive is up to you. As long as it resides in the same cluster group it will always be hosted on the same node and available by drive letter.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 28, 2009 at 7:29 pm
I messed up my SQL Instance which caused me to reinstall the cluster instance again.
I did the following:
I had deleted the Backup group and added that Z drive in SQL Group. Then I created Create clustered file share as below:
In clustered adminstrator->Resource->Right click new resource ->
Name: LogShipping
Resource Type: file share
In the parameters Tab:
Share name:LogShipping_Backups_Out
Path: Z:
Then in the cluster administrator resources, I had this new Z drive and aslo Logshipping file share.
Is this right?
So then I tried to delete this Logshipping file share resource, it give me the window which has the dependencies in this. SQL group aslo there in the list and saying the dependencies also will be deleted and I clicked Yes...thats it my sql instance gone!!!!!!!!!
Thank God..its a development environment...I'm reinstalling it now
please tell me the log shipping clustered file share must need to be created ?
June 28, 2009 at 9:42 pm
Can you please provide me the steps to create a clustered file share for log shipping?
1st we need to add the backup drive in sql group, then what are the steps exactly I need to so that I can not screw up my instance again.
I appreciate your help. We have separate backup drive in production too, before touching that I need achive this succesfully
and in Scott's previous reply(in screen shot1), in Log shipping wizard, on Backup settings, backup path has given as \\primaryServer\Z:\Logshipping_Backups_Out
lets say..Primary is Node1 now...
But if the sql group moves to other node node2, then the path \\primary will not be the same. It will be\ode2, then the Transaction log backups and any backups to that drive will fail right?
What exactly, we need to give in place of the path \\primaryServer\Z:\Logshipping_Backups_Out
thank you
June 29, 2009 at 12:23 am
Thanks for the links,
After reading the links, I did the following:
1.In the SQL group, we have Z drive for keeping backups. Now I created a folder Logshipping_Backups_out in Z drive ( Z:\Logshipping_Backups_out)
2.Created a file share with name Logshipping_Backups_out, and added the Z drive and Network name as dependencies.
3.On the File Share Parameters screen , I have given the
a)share name as Logshipping_Backups_out
b)The "Path" is Z:\Logshipping_Backups_out
and in the permissions tab, I added the cluster service account and sqlserverAgent service account and given the full control.
Questions:
1. Is the above steps are correct?
2.In the log shipping wizard, for Backup settings, what will be the network path and local path I should give to keep transaction log backups?
a) \\primaryserver\z$\Logshipping_Backups_out OR
b)\\primaryserver\Logshipping_Backups_out(share name) OR something else?
c)If the primary server(node1) completely down and the sql group moved to node2, then in the log shipping wizard, in backup settings the path will be \\primaryserver\Logshipping_Backups_out. In this case \\primary will not be available then the backups will fail. How to avoid this?
3.In the secondary server, I created a folder Logshipping_Backups_out in Z drive ( Z:\Logshipping_Backups_out). So in copy files option of Log Shipping wizard, what network path I should give?
a)\\secondary server\Z$\Logshipping_Backups_out? OR
b)Should I share the folder Logshipping_Backups_out in Secondary Server too? and give the share name like
\\secondary server\Logshipping_Backups_out
Please clarify me..
Finally...Is there any Article\Document\Book that puts all the steps together about How to Configure LogShipping in Clustered SQL Server 2005
thanks
June 29, 2009 at 11:36 am
Please advice me on above items....
and I configured the log Shipping on node1 successfully and everything is working. But when I moved the sql group to node2:
1.The transactional backup run fine to the path mentioned in the backup settings \\primaryserver(node1)\logshipping_backups_out, after moving to node2 also I'm able to see the backups are running to the file share logshipping_backups_out created in Z:\logshipping_backups_out.
2.The copy job, to the secondary failing with below error:
Error: The network name cannot be found.(in copy job view history..)
please advice
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply