September 17, 2009 at 12:15 am
Hi all,
We are planning to implement Log shipping in Active\Passive cluster setup, sql server 2005. We a SQL Group (which has D, E, F drives) and a Backup Group (Z drive for Backups) in Cluster Administrator. As you already configured Log shipping in Clustered SQL Server 2005, could you please answer the following:
1. I have created a share "Tlogs" & given full control to the service account under which SQLAgent is running on Z drive as and given the backp settings option as \ode1 name\Tlogs in Logshipping wizard. Is this right or do I need to mention the virtual server name i.e \\virtualservername\Tlogs ?
--for me backup failed if I use \ode1 name\Tlogs & it's working if I use \\virtualservername\Tlogs. So what backup settings (backup path) you have used??
2. What happens when the SQL group moves to Passive node because of fail over? does the log shipping breaks?
-- for me log shipping braked. i.e the copy and restore jobs failing.
3. What happens when the Backup Group group moves to Passive node because of fail over? does the log shipping breaks?
-- for me log shipping braked. i.e the copy and restore jobs failing
4. If both SQL Group & Backup group moves to node2, then log shipping working fine.
I'm NOT using a network share on a separate server, created the share on node1 Z drive. and a share folder on Secondary server to store the copied tlog files.
how exactly Log shipping works in clustered sql server? Please provide me some documentation if you already prepared?
thank you very much
September 17, 2009 at 9:23 am
please advice...
September 17, 2009 at 4:33 pm
Could you advice on my above questions? thanks
September 18, 2009 at 3:13 am
1. I have created a share "Tlogs" & given full control to the service account under which SQLAgent is running on Z drive as and given the backp settings option as \ode1 name\Tlogs in Logshipping wizard. Is this right or do I need to mention the virtual server name i.e \\virtualservername\Tlogs ?
If you have created the share as a resource in cluster administrator, then refer to it as \\clustername\sharename. Otherwise, you should be referring to it by the virtual server name. If you refer the the node, it will fail when the node is offline or when the disks are failed over to the other node.
2. What happens when the SQL group moves to Passive node because of fail over? does the log shipping breaks?
Log shipping is probably failing because you have reffered to the share using the node name.
3. What happens when the Backup Group group moves to Passive node because of fail over? does the log shipping breaks?
As for question 2.
how exactly Log shipping works in clustered sql server? Please provide me some documentation if you already prepared?
Log shipping works in exactly the same way in a clustered and non clustered environment. I think you just need to understand that you should refer to the virtual server not the node.
September 18, 2009 at 3:20 am
I suggest you do not have your backup in a separate group. Add the Z drive to your sql group so that when SQL server fails over the backup drive will fail over as well.
Logically if SQL fails over to the passive node then it is because you have a failure on the active node so your backup group should be failing over as well. Therefore I do not see the reason to have it in a separate group.
Alternatively leave it in a seperate group but create the backup folder as a shared network folder. So you will have 2 resources in the backup group 1 - the Lun and 2 being the shared backup backup folder.
have a look at this
http://technet.microsoft.com/en-us/library/cc787828%28WS.10%29.aspx
September 18, 2009 at 3:27 am
just to reiterate what has already been said. with a cluster, no matter what node you are on, primary or secondary, all sql resources should be accessible, so in theory all sql resources should be within the one group. so when you practise failover, all sql activities should continue.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 18, 2009 at 3:33 am
Electrix (9/18/2009)
I suggest you do not have your backup in a separate group. Add the Z drive to your sql group so that when SQL server fails over the backup drive will fail over as well.
Each situation is different, but some people advocate that the backup should be in a separate group, or that isn't a dependency of the SQL service... just because your backup disk is offline, you may not want to bring the whole service down.
September 18, 2009 at 3:35 am
Ian Scarlett (9/18/2009)
Electrix (9/18/2009)
I suggest you do not have your backup in a separate group. Add the Z drive to your sql group so that when SQL server fails over the backup drive will fail over as well.Each situation is different, but some people advocate that the backup should be in a separate group, or that isn't a dependency of the SQL service... just because your backup disk is offline, you may not want to bring the whole service down.
true but you can set that resource not to affect the group, so it can fail and not affect the group or failover.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 18, 2009 at 2:37 pm
suggest you do not have your backup in a separate group. Add the Z drive to your sql group so that when SQL server fails over the backup drive will fail over as well.
Logically if SQL fails over to the passive node then it is because you have a failure on the active node so your backup group should be failing over as well. Therefore I do not see the reason to have it in a separate group.
The reason to have a separate backup group is:
We have 3 instances on the node and we want to keep single Backup group to backup all the databases from 3 instances instead of having 3 backup drives in each group. Does this makes sense?
September 19, 2009 at 8:47 pm
Hi Ian,
If you have created the share as a resource in cluster administrator, then refer to it as \\clustername\sharename. Otherwise, you should be referring to it by the virtual server name. If you refer the the node, it will fail when the node is offline or when the disks are failed over to the other node.
Here, I DO NOT have the share as a resource in cluster administrator. So I have given the backup path settings as below:
On Node1(Active):
1. Created a share on Backup drive Z, called LSTlogs and given permissions to this share to the service account mssqlsrv, where databases engine & SQLAgent run under this account.
2. Given the below path in Backup settings in Log shipping wizard
\\sql1vs1\LSTlogs
Log shipping working fine.
Now, When I fail over the SQLGroup & Backup Group to node 2, then the Backup job is failing with the below message: from the backup job view history:
Message
2009-09-19 19:31:00.83 *** Error: Backup failed for Server 'SQL1VS1\INS1'. (Microsoft.SqlServer.Smo) ***
2009-09-19 19:31:00.83 *** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2009-09-19 19:31:00.83 *** Error: Cannot open backup device '\\sql1vs1\LSTlogs \WSS_Content_Mysite_20090920023100.trn'. Operating system error 21(The device is not ready.).
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
2009-09-19 19:31:00.87 ----- END OF TRANSACTION LOG BACKUP -----
Exit Status: 1 (Error)
Then, I went to Node 2 and investigated why the backup failing even if I'm giving Virtua Servername in the backup path. I find the below:
The share created on Z drive, LSTlogs (\\sql1vs1\LSTlogs) is NO more a share on NODE 2 and its looking like normal folder. And I checked the permissions for the folder & the mssqlsrv account is not there in the permissions list.
Now again I have made the folder LSTlogs as a share & given the permissions for mssqlsrv. After doing this Log Shipping started working again!!!
So what should I do in-order NOT to loose the folder as a share & the permissions for msssqlsrv account when the SQLGroup & BackupGroup fail over to NODE 2??
please advice.. If I resolve this, my Log shipping will work irrespective of Groups failover to Node 1 or Node 2..
many thanks
September 21, 2009 at 12:47 am
That is the problem with native folder share. When you fail over the folder share and permissions does not carry over to the new node. That is why you have to create the share as a cluster resource.
September 21, 2009 at 12:49 am
Electrix (9/21/2009)
That is the problem with native folder share. When you fail over the folder share and permissions does not carry over to the new node. That is why you have to create the share as a cluster resource.
As mentioned before, every resource that is needed for sql operation needs to be a cluster resource so that it can be accessed regardless of what node it is on.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 21, 2009 at 9:40 am
As mentioned before, every resource that is needed for sql operation needs to be a cluster resource so that it can be accessed regardless of what node it is on.
Here the Z drive is a cluster resource but it is a separate backup group to backup databases from 3 instances on the same node. I cannot add this Backup drive into a sqlgroup. Because, I have 3 instances on the node with 3 sqlgroups.
please advice....
September 21, 2009 at 12:35 pm
could you please advice me..
September 21, 2009 at 12:37 pm
Create a Share folder in your cluster adminsitratrator management console and point it to your backup and/or copy folder.
And refer the folder as //VirtualServerName/ClusteredShareName in your LS.
Although the folder is under your Z drive and Z is a clustering resource, that doesn't mean your folder is a clustering resource (i.e. a resource that can failover to another node automatically when failures happen). You have to create the share seperately with a name in cluster administrator so that the clustering service knows the folder is being shared among the nodes.
To create a clustering share...see this article.
Hope this helps..
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply