Questions on Active/Active Node

1. How to install/update Service pack on Active/Active Sql Cluster.

2. Is it necessary to install SQL instances on both the nodes or 2 instances on single node (Active/Active Cluster).

3. How to monitor Active/Active SQL Clustering?

Hello,

Your terminology is confusing. You say "Active/Active" but this doesn't explain what you're really attempting. Is it going to be 6 instances you're going to use? 5? 3?

Instead, please start to use multi-instance clustering as the terminology with the number of nodes and number of instances. It explains what you're looking to do in a better and easier way.

1. The same way it would be done with a single instance, though there are some windows level items that should be done as well: http://support.microsoft.com/kb/958734

2. I can't tell you from your description. Each node that *could* own the instance needs to have that instance installed on.

3. The same way that SQL Server is monitored now. What will change is the windows level monitoring that would need to be added such as resource failures and failovers. Unfortunately there aren't many clustering specific errors that can be trapped inside of SQL Server - they are mostly on the windows level.

In addition to Sean's points.....You can monitor it using Powershell 

http://www.mssqltips.com/sqlservertip/3154/monitor-a-sql-server-cluster-using-powershell/

--Prashanth

• Proposed as answer by Sofiya LiMicrosoft contingent staff, Moderator Thursday, April 17, 2014 8:22 AM

Questions on Active/Active Node

1. How to install/update Service pack on Active/Active Sql Cluster.

2. Is it necessary to install SQL instances on both the nodes or 2 instances on single node (Active/Active Cluster).

3. How to monitor Active/Active SQL Clustering?

1. By A/A i understand multi instance cluster .Patching always involve first applying patch on node which is not the owner of resource.In multi instance you need to make sure both the instances are on same node which would be active node .Apply patch on passive node for instance you would like .Also two instances with different patch level can reside on same node.Restart patched node ( restart may be required after you patch node). After restart move resources to this *restarted*  node and apply patch on on other.

2.No matter how many instances you create SQL installation will be done on each node.On active node you install SQL server in cluster aware mode and then add other nodes to it by installing SQL server on each node and selecting add node to cluster

3. Windows event viewer and SQL server error logs.Win event viewer will tell you lot of information about cluster.For 2005 cluster logs were stored at C:\windows\cluster.

For 2008 and above you need to generate verbose log from command prompt ,below link will be helpful

Create cluster log in Win server 2008

Best practice for patching SQL Server

Installing Multi-instance (Active/Active)

I don't know if this helps on your case but try this ......Installing a Multi-instance cluster is almost similar to adding a Single-instance cluster, except for the need to add a few resources along with a couple of steps here and there. So, while explaining the setup, I will skip some common screenshots.

1. We will need to procure additional disks so that we can keep our data on it. So, let's arrange additional disk resources for the Multi-instance Failover Cluster.Go to Start | Administrative Tools | iSCSI Initiator and click Discover Portal under the target panel, as shown in the preceding screenshot. Here, we will provide the name of the target system on which we have procured additional disk resources for our clusterin our case, SQLNode2.
   1. Select the Target tab and we will be able to see that some resources are available but they are Inactive.
   2. Select any of the devices and click Connect. This will open the following screen. Click OK to make the device Connected.
   3. Select the Volumes and Devices tab. We will see the only four volumes; the one that we added in step 2 is still not available here.
   4. Click Auto Configure and this will list the recently added devices.
   5. Come back to the Favorite Targets tab and we will see the name of all the devices that are available for use in the cluster.
   6. this point, we are all set to go ahead and install SQL Server. Insert the disk and click setup.exe. This will bring us the SQL Server Installation Center screen. Select the New SQL Server Failover Cluster Installation under the Installation section.
   7. Here we will have to provide the key that we received with the installation media. In the next step, we have to accept the license terms and run the setup support files; here we have to be a little careful when making sure that no error comes up.
   8.  In the feature selection window, select the feature we want to install. The Shared Feature directory will be the local disk, whereas Database Engine will use another diskthe shared one. Please make sure that the disks where we are installing the SQL Server Shared Features have ample space.
   9. There can only be one default instance per node, and to avoid confusion, we will have a named instance this time, say SQL2008Inst1. We will keep the same name for the network name of the SQL Server, Named Instance, and Instance ID just to avoid confusion. We can also notice that in the following screenshot, there is an entry in the Detected SQL Server Instances and Features on this Computer section. This entry shows the name of the SQL Server Failover instance we have created in the Installing Single-instance Failover Cluster section.
   10. In the Disk Space Requirements section, verify that we have ample space to install a second instance.
   11. In the Cluster Disk Selection dialog box, we will select the disk that we have added and will keep the same resource group or we can type the new resource group in the drop-down box to create a new resource group.
   12. In the Cluster Network Configuration dialog box, provide the new IP address.
   13. In the Cluster Security Policy section, we will keep the recommended settingUse services SIDs.
   14. In the Server Configuration dialog, provide the credentials.
   15. In the Database Engine Configuration section, select Mixed Mode and provide a strong password for the SQL Server System Administrator a.k.a. SA account. If we wish, we may add the current user as a SQL Server Administrator by clicking Add Current User.
   16.  In the Database Engine Configuration dialog box, we will see the drive letter for the disk that we have selected in the Cluster Disk Selection. If we wish to use the FILESTREAM feature, you may do so by clicking on the FILESTREAM tab and providing the appropriate configuration.
   17. In the Error Reporting dialog box, click Next.
   18. Please check and verify that the cluster installation rule doesn't have any errors.
   19. In the Ready to Install dialog, verify that the configuration is what we intend to install for the new instance.
   20. In the Complete dialog box, please verify that there is no error reported. If the box shows the installation is successful, it is an indication that we have successfully installed a named instance of SQL Server failover cluster.

Monitoring

1. Take note of any onscreen error messages. Be sure that you take screen captures of any messages for reference. Some DBAs have the habit of clicking OK after an error message without recording its exact content. Often, the exact content of a message is helpful if you need to search the Internet to learn more about it.
2. There are a variety of logs you may be able to view, depending on how far along you are in the cluster setup process. These include the three operating system logs: the cluster log (located at c:\windows\cluster\cluster.log); the SQL Server Setup log files (located at  %ProgramFiles%\Microsoft SQL Server\100\Setup Bootstrap\LOG\Summary.txt); and the SQL Server 2008 log files.

• Edited by Abubeker Refaw 4 hours 7 minutes ago