SQL Server 2005 Standard On 2 Nodes Of A 4 Node Cluster
May 23, 2006
My question is, I'd like my actual cluster to have 4 servers. The virtual server that will be running SQL 2005 will only be installed on 2 of those nodes. Will this work or do I have to split my cluster in to 2 to meet the SQL Server 2005 2 node failover limitation? A basic config for the cluster would be like below....
ClusterSQLNode
Active SQL 2005 Server
ClusterExchNode
Active Exchange Server
ClusterAppNode
Active Application Server
ClusterFailoverNode
Failover SQL 2005
Failover Exchange
Failover Application
Will SQL 2005 install in a 4 server cluster if I only install it on to two of the nodes? Any potential gotchas?
Current set-up: three node cluster running Windows Server 2003 Enterprise Edition SP2 with two instances of SQL Server 2005 Standard Edition SP2.
Virtual node 1: Standard Edition, default instance, Database Engine only
Virtual node 2: Standard Edition, named instance, Database Engine, Analysis Services and Integration Services
Physical node A: Prefered node for virtual node 1
Physical node B: Failover node for both virtual node 1 and 2 (thus having two installations of Standard Edition)
Physical node C: Prefered node for virtual node 2
We started off with this set-up because there was no need for Enterprise features, and SQL 2005 supports failover clustering with two nodes in Standard Edition. Saved a bundle of cash, so everybody's happy. Now we need to run a solution on virtual node 2 that requires Enterprise features in Analysis and Integration Services, so an edition upgrade is required on virtual node 2. I have done some research and found several edition upgrade examples, but none that match the above scenario. We are running production on these machines, so I can't risk experimenting with command line options, I have to be right the first time.
Is it possible to mix two editions of SQL Server 2005 in one cluster? Can I upgrade virtual node 2 to Enterprise but leave virtual node 1 at Standard? What procedure do I have to follow to upgrade all three components of virtual node 2 to Enterprise Edition on both physical node B and C?
We had an existing 2 node active / active cluster, 1 running a default instance of Sql Server 2005 Enterprise Edition 9.0.3152 (SP2 + Hotfixes) and the other running a named instance of the same version.
We recently added 2 new nodes to the cluster, they were successfully added and we tested the cluster group failover successfuly to the new nodes.
Last night we tried to install Sql Server 2005 Enterprise edition on the new nodes.
I followed to proper proceudure of modifying the installation for both instances and selecting the 2 new nodes to apply them to. This went 100%. Sql Server 2005 successfully installed for both instances on the 2 new nodes, all log files were successful.
We then tried to apply SP2, we tried the following:
1. We ran SP2 from the active node, but when we go to the screen to select what you want to apply SP2 too we could not select anything, if you clicked on database engine the message said that these instances were already at a later version and we could not proceed. This is how i successfully applied SP2 to the original 2 node cluster but it does not work for additional nodes to an exisitng cluster.
This is also what all the documentation we could find said, refer to SP2 release notes under the topic "Failover Cluster Installation", it is also the method we found when googling.
2. We then tried what is described under SP2 release notes "Rebuild a SQL Server 2005 SP2 Failover Cluster Node". We ran SP2 from the new nodes while they were passive, but when we got the screen where you select what to apply the SP2 too we could not select database engine the message at the bottom said that SP2 must be run from the active node and that we were attempting it from the passive node, this is what we tried in step 1 described above.
3. This was a last resort. We were advised to try failing over the instance to the new node and then running SP2. Personally i thought this was a bad idea, one should never fail over a instance of sql server to a node with incompatible binary versions and secondly when we installed sql server on the new nodes a warnng popped up before hand stating that the instances were at a later version and that the new nodes must be at this version before attempting fail over. I thought that sql would not even start, to my surprie we successfully failed over the sql group to the new node, when we ran SP2 it looked good we could select the database engine on the new node to apply SP2 too, BUT after clicking next after a few seconds the SP2 installation just closed, NO INFORMAITONAL MESSAGES NO ERRORS NO WARNINGS it just closed an never came back.
I had never seen this happen on a cluster before, needless to say this made me very nervous so we failed the sql group back to the original nodes and gave up.
PLEASE can some tell me how to apply SP2 to 2 new nodes in a 4 node cluster all methods descibed in SP2 release notes and other documentation as descibed above in step 1 and 2 do not work !
But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?
I've installed MSCS on two Win2k3 x64 Enterprise systems. On the first I installed SQL 2005, and all went well. All resources are online and SQL works, migrated 25 databases into the new install. Installing SQL 2005 on the second node is problematic. If I select install cluster resources, I have to define new stuff for a new group. If I don't install cluster resources, then the install is all local (nothing on the unseen shared disk resource.)
It does not see the shared disk resource during install, the Quorum is good. I can move the Cluster Group between servers with no problems. None of the SQL resources is available on the second node. Error is basically "a cluster node is not available for this operation".
The Sql Server and Sql Server Agent do not even acknowledge the existence of the second node, and it is not available as a possible owner. i can't find any specific instructions on installing the second node. The other SQL resources say the second server is an available possible owner. None of them will take ownership and run the resource.
We are planning to change all IPs of PRODUCTION Failover Cluster Setup. In my cluster setup ... we have 2 Physical Nodes with windows-2008, Roles are MSDTC and SQL-2008R2.
IP change for:
1. Both Nodes(Physical) 2. MSDTC 3. SQL Server 4. windows Cluster
So Almost... All IPs are going to change.
Im DBA here, I need to take care of SQL cluster and MSDTC. But I haven't performed this activity before.So I'm worrying about Impacts and consequences of this change. steps how should I perform this activity.
I'm contemplating running two availability groups on a two node WSFC. The WSFC is setup with a file share witness (i.e. no shared storage). Can I safely run 1 AG on one primary node, and the other AG on the other node (as primary). Each AG would have replicas on the passive node. This would effectively allow both servers to be in use at the same time. In a failover event, I understand that both workloads would transfer to a single server - so the box needs to be sized appropriately.
We are in the process of building a 3 node SQL Server Cluster (Server 2012/ SQL Server 2012), and we have configured the quorum so that all 3 nodes have a vote (no file share witness as we already have an odd number of nodes).
As I understand it, this should allow the cluster to run as long as 2 of the nodes remain online.
However, the validation report states that 2 node failures would be acceptable and, when we tested this by powering off two of the nodes, the cluster did indeed continue to run on a single node.
Could someone give me an ideal of what it takes to upgrade the memory in my cluster. Is it as easy has upgrading the passive node, switching the nodes, then upgrading the other server. And do I need to re-configure the windows cluster. And will both server need to be down at the same time at any point.
Or is their more to the process? Thanks for any assistance.
Hi,I haev the following code:DECLARE @x xmlSET @x='<Root><row id="1"><name>Larry</name><oflw>some text</oflw></row><row id="2"><name>Joe</name></row><row id="3" /></Root>'exec sp_xml_preparedocument @idoc OUTPUT, @xSELECT * FROM OPENXML(@idoc, '/Root')This gives the following detailsidparentidnodetype localnameprefixnamespaceuridatatypeprev textI want to get the same details using XQuery, please let me know how togo about it.Regards,Shilpa
I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.
We have been working on a project to upgrade the servers in our 2-node SQL Server environment. I evicted a node after removing it from the instance. We added the new node under a new server name. I then start the Add remove programs, choose to change the SQL 2005 environment, type the virtual server name. Choose to maintain virtual server, pick to add new node. All seems well, I enter all prompted questions, and when the install begins I get the error below.
Product: Microsoft SQL Server 2005 -- Error 1706. An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'.
So I copied the SS2K5 Enteerprise Edition software to the local C: drive, point it too the 'SqlRun_SQL.msi' in the setup folder and still get the error.
During the installation of Adding node to a SQL Server failover cluster(On passive node) getting error like.. The MOF compiler could not connect with the WMI server. This is either because of a semantic error such as an incompatibility with the existing WMI repository or an actual error such as the failure of the WMI server to start.We run the below commands but didn’t get any resolution & got the same above error .
1<sup>st</sup> Method…
1. Open console command (Run->CMD with administrator privileges).
2. net stop winmgmt
3. Rename folder %windir%System32WbemRepository to other one, for backup purposes (for example _Repository).
We have 2 nodes window Server 2012 R2 and SQL Server 2012 Enterprise Version cluster setup. We can switch roles and Node to one node to another and revert back to previous node with out any issues. But we are facing when one Node is restarted. We could not restart that Node in cluster Service start in Failover cluster Manager. Error Details is displayed as below inside double code."Cluster node NODE1 could not to join the cluster because it failed to communicate over the network with any other node in the cluster. Verify the network connectivity and configuration of any network firewalls."
I checked windows firewall. windows firewall is all of in Node1, Node2, SAN and DC.I have disabled and enabled the Internal and private network of Node 1. I have validated the cluster. it is showing no error though.
Node1: Public IP: 10.10.0.11 SubNet Mask:255.255.255.0 Default Getway: 10.10.0.1 Prefered DNS: 10.10.0.10 (Ip of DNS)
[code]....
Private Network: Not configured.pinging to each other ip is successful from one node to another.
While I installed on the active node I got the following error: A recently applied update, KB921896, failed to install
After that installation finished , but query the server version I get: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
9.00.1399.06 RTM Standard Edition
On the Visual Studion Manager Help About I get 9.00.3042
Error logs are below. Can you please tell me how can I overcome this?
TIA.
I paste here the summary and the hotfix logs of the primary node. There is also the Redist9_Hotfix_KB921896_SqlSupport.msi.log but it is too long to paste here.
summary.txt:
Time: 07/29/2007 02:46:28.253 KB Number: KB921896 Machine: DB1 OS Version: Microsoft Windows Server 2003 family, Enterprise Edition Service Pack 1 (Build 3790) Package Language: 1033 (ENU) Package Platform: x86 Package SP Level: 2 Package Version: 3042 Command-line parameters specified: Cluster Installation: Yes Log Location on Passive Nodes: (DB1) C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix (DB2) C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGHotfix
********************************************************************************** Prerequisites Check & Status SQLSupport: Passed
********************************************************************************** Products Detected Language Level Patch Level Platform Edition Setup Support Files ENU 9.1.2047 x86 Database Services (MSSQLSERVER) ENU RTM 2005.090.1399.00 x86 STANDARD Integration Services ENU SP1 9.00.2047.00 x86 STANDARD SQL Server Native Client ENU 9.00.2047.00 x86 Client Components ENU SP1 9.1.2047 x86 STANDARD MSXML 6.0 Parser ENU 6.00.3883.8 x86 SQLXML4 ENU 9.00.2047.00 x86 Backward Compatibility ENU 8.05.1704 x86 Microsoft SQL Server VSS Writer ENU 9.00.2047.00 x86
I invoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed on I need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example I know my active node is server2, but the host_name() function is returning server1. The only thing that I could possible explain this is that the MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?
Thanks.
Edit:
Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and on server 2 I get server 2, all the while server2 is the active node. I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...
Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.
I ran into problem while trying to install SQL Server 2008 to join to another node in a failover cluster ,you will find here the configuration doc and installation details. URL...
When adding a node to a SQL Server 2012 Standard edition cluster, how I do I identify the location for SQL server shared components and the rest of the SQL Server installation binaries?
When adding a node to a SQL Server 2012 Standard edition cluster all the binaries went to the C: drive default location. We put those files on a different drive when installing the first node. What needs to be done so both nodes have the binaries on the same drives and folders?
On first Node A: The server has 16 GB of physical RAM. On second Node B: The server has 10 GB of physical RAM.
Now, this being Active Active, Node A can be clustered on failure onto Node B..Now reporting server is configured under these two nodes, with defined MAX and MIN server memory of 12 as MAX and 0 is min IN GB.Now with this setting on SQL whenever the cluster moves, such config make OS goes low on node for 10 GB.I am only left with option of switching this MAX and MIN to a default setting or is there any other alternative such as script which can change this setting accordingly when cluster moves to respective server.
I have destroyed the cluster in failover cluster manager and then i am trying to remove node from the sql server installation centre.I am facing the cluster node and cluster service verification errors.I am not able to start the cluster service in services as well.
I'm setting up a cluster to test a new deployments of SQL2K14 on a WSFC cluster (W2K12R2). Starwinds VS. the built in iSCSI service?Will either one allow me to build a cluster consisting of two nodes? Do I need to dedicate a third node to run the iSCSI storage?
I can't seem to install SQL Server 2005 (x64 version) on an x64 Win2k3 two node cluster.
I get all the way through the configuration and setup fails because it cannot start a task on the remote node. The error message indicates to check the task scheduler log file, which I have and I cannot find any 'errors' in the file.
Google/MSDN/Technet turn up nothing on multiple searches. Has anyone else run into this problem?
My installation account is a local admin on both machines, so is the sql cluster account. For the life of me I cannot figure out what's different for an x64 install vs 32bit....
We currently have a 2 node Active/Passive 2 named instance SQL 2000 cluster. We will be chaning the configuration to Active/Active, basically moving 1 instance to the passive node (so we can take advantage of the resources on the passive node).
We would also like to add 3 nodes to the cluster making it a 5 node SQL cluster. What we are thinking of doing is basically making it an Active/Active/Active/Active with the 5th server being passive. The question I have is will I be able to add and install the 3 new nodes without having to redo the SQL cluster? Should I install the two new active nodes with the default SQL instace or do I have to install SQL with named instances? (actually I think the named instances is the way to go but then I wouldn't be posting here if I was sure about the answer).
How many nodes can you have in a cluster with SQL 2012 alwaysOn.
I understand that availability groups are limited to 5 nodes but if you had a 10 node cluster and decided to create multiple availability groups using various nodes within the 10 nodes but never exceeding 5, is that possible?
Or is there a counter or some validation from SQL AlwaysOn that actually hard limits to a grand total of 5 nodes in a cluster?
I had a cluster running on 3 nodes (windows failover cluster) with server 2012 and SQL server 2012 running in the cluster. Well, I thought I'd just go ahead and rename all 3 of the hosts with new hostnames and it apparently broke the cluster pretty bad. Now when I open failover cluster manager I cannot see anything nor can I connect to the cluster object in AD. I re-named all of the hosts back to their original names but that didn't work.
Is it possible to create a cluster configuration, where 2 (or more) SQL Server instances running on different servers will simultaneously serve the same database attached to the same storage? Something like this:
Instance A Instance B Instance C (active) (active) (active) | / | / | / SAN for Database and Quorum
The point would be to improve reliability and performance at the same time. All nodes would share load. If a node fails, the other nodes still work and take over the load.
I know a failover cluster can be done, where 1 instance is active and others are passive (active/passive) which improves reliability, but in this configuration just 1 instance is serving at the same time.
(As far as I understand, the active/active configuration is meant to run different databases on 2 (or more) instances such that the databases on a failing node are taken over by any other instances in the cluster.)
