October 6, 2009 at 12:57 pm
Hi,
We have A\P cluster setup for SQL Server 2005 EE x64 on Windows 2003 EE x64 R2. We have 2 SQL Server instances on Node 1.
We have the below groups in Cluster Administrator:
1. sqlgroup1 -----> D, E, F
SQL IP Address (vs1)
SQL Network Name (vs1)
SQL Server (ins1)
SQL Server Agent (ins1)
SQL Server Fulltext (ins1)
2. sqlgroup2 ------> G, H, I
SQL IP Address (vs2)
SQL Network Name (vs2)
SQL Server (ins2)
SQL Server Agent (ins2)
SQL Server Fulltext (ins2)
3. Backup group ---> Z
Here, on Z drive(in backup group), both ins1 & ins2 databases are backing up.
Now, we are configuring Log shipping for VS1\Ins1 & VS2\ins2 to a Seocndary Standalone server(which has ins1 & ins2). Here vs1\ins1 databases should log ship to ins1 on secondary and vs2\ins2 databases should log ship to ins2 on secondary.
For that, I have created a file share on Z drive and configured log shipping between vs1\ins1 and ins1 on secondary. Now I want to configure log shipping between vs2\ins2 & ins2 on secondary. For this can I create another file share on Z drive from Cluster Administrator and configure loh shipping?
Is the above configuration makes sense? Is anything to correct?
please help!!
October 6, 2009 at 1:06 pm
Is your network share still visible from a network after falover?
Did you check it?
October 6, 2009 at 1:38 pm
Mani.
Are sqlgroup1 and sqlgroup2 linked in the cluster? That is to say, can one of the sqlgroups failover without taking the other sqlgroup with it?
If the answer is yes, you will need a second backup disk and share for sqlgroup2. You can then use this to ship your logs to your DR server.
Hope this helps.
October 6, 2009 at 3:08 pm
Are sqlgroup1 and sqlgroup2 linked in the cluster? That is to say, can one of the sqlgroups failover without taking the other sqlgroup with it?
If I manually Failover sqlgroup1 to passive node, only sqlgroup1 is moving but not sqlgrop2 & backupgroup.
If I manually Failover sqlgroup2 to passive node, only sqlgroup2 is moving but not sqlgrop1 & backupgroup.
If I Restart the node 1, then all the groups are moving to Node2.
October 6, 2009 at 6:46 pm
Hi,
I did the following:
On Backup drive, which is in Backupgroup, I have created 2 file shares, Logshipping1 & Logshipping2 from Cluster Administrator. One for ins1 & one for ins2.
Configured Log shipping between VS1\ins1 & ins1 on Secondary server and log shipping between VS2\ins2 & ins2 on Secondary server
I have given \\virtual servername\sharename (vs1\Logshipping1) in backup settings of Log shipping wizrad.
If both the sqlgroup1 and Backup group are on same node, then Log shipping working without any issues.
If I move Backupgroup manually to node1 and keep the sqlgroup on node1, then the backup job is failing with below message: (Is this happens in real time scenario? i.e sqlgroup on one node & backupgroup on other node??)
Message
2009-10-06 16:47:01.42*** Error: Backup failed for Server 'VS1\INS1'. (Microsoft.SqlServer.Smo) ***
2009-10-06 16:47:01.42*** Error: An exception occurred while executing a Transact-SQL statement or batch.(Microsoft.SqlServer.ConnectionInfo) ***
2009-10-06 16:47:01.42*** Error: Cannot open backup device '\\vs1\LogShipping1\WSS_Content_20091006234701.trn'. Operating system error 53(The network path was not found.).
BACKUP LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
Thanks for your help
October 7, 2009 at 1:22 am
Mani.
What is happening is that your backup drive is not on the same node as your SQL group and is therefore not available.
You need two backup drives, one in each SQL server group which SQL Server is dependent on.
When the SQL group fails over, your backup drive fails over with it and log shipping resumes.
October 7, 2009 at 10:43 am
What is happening is that your backup drive is not on the same node as your SQL group and is therefore not available
.
We have 3 sqlgroups for 3 sql server instances on node1. Are there any scenarios, where only one group out of 3 sqlgroups will failover to node2??
I'm assuming, if something happens to node1 , then all the 3 instances sqlgroups will failover to node2? Is that right? OR
are there any possible scenarios that can cause individual sqlgroup to move to node2??
please clarify me..
thanks
October 7, 2009 at 12:39 pm
Hi Mani.
You are correct. If you have a failure of node1, all resources will failover to node2.
A resource group in a cluster will, by design, act as an independent entity. It is logical to split your SQL Instances over both nodes to balance the load over two servers, not just one; the the other completely idle. This gives you maximum performance under normal operation and reduced performance on failover.
You could also experience a problem with one of the binaries of an instance so that it will not run on a given node. This will cause one of the three nodes to failover and break log shipping.
October 7, 2009 at 1:08 pm
A resource group in a cluster will, by design, act as an independent entity. It is logical to split your SQL Instances over both nodes to balance the load over two servers, not just one; the the other completely idle. This gives you maximum performance under normal operation and reduced performance on failover
.
So what is the Best way to configure log shipping having multiple instances and all need to configure Log shipping?
Having Single Backupgroup and creating 3 fileshare resuorces on that backup drive for 3 instances and configure log shipping does not work in all scenarios right?
Log shipping works fine only when the 3 sqlgroups & the backupgroup are on either node1 or node2 right?
whats the Best way then? Is there any documnetation on how to handle Log shipping having Multiple sql instances (sqlgroups) and a single backup group?
thank you so much
October 7, 2009 at 1:51 pm
Mani-584606 (10/7/2009)
Having Single Backupgroup and creating 3 fileshare resuorces on that backup drive for 3 instances and configure log shipping does not work in all scenarios right?
Log shipping works fine only when the 3 sqlgroups & the backupgroup are on either node1 or node2 right?
That's correct.
I cannot point you to any specific documents but here's what I would advise you to do in cluster administrator.
For each SQL cluster group:
* Add a backup disk
* Add the backup disk into the dependencies for SQL Serevr
* Create a share for your backup disk and add the backup disk into the dependencies for the share
* Change log shipping to write the log files into an appropriate directory in the backup disk
Job done. You can now access your logs via \\SQLInstanceName\ShareName
If any member of the SQL resource group fails, all will be failed over. As the backup disk and share follows SQL server, log shipping will continue from the other node.
Hope this helps. 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply