June 2, 2010 at 12:09 pm
i appreciate your help by the way.
i'm looking at: http://msdn.microsoft.com/en-us/library/ms191545.aspx under remove cluster node. It seems pretty simple, you suggestted there could be more to it. i would like to know what else you dod.
other than that, do i just follow the "remove node" and "add node" instructions?
thanks
June 2, 2010 at 12:36 pm
OK, here are my answers to your questions:
"1. I’m unclear on which node to remove it from. Node 1 is fine, and has it on the D: drive. Node 2 is not, and it is on the C: drive. I’m thinking I remove it from node 1 and reinstall – but that would mean I am trying to fix a problem on node 2 by uninstalling/reinstalling on node 1. Is that right?"
Answer: Remember that you want both nodes to be identical. Since you can only install to the C: drive on any node that you're ADDING to an existing cluster, you must remove Node 1 from the cluster. Bottom line is: You cannot install to D: on both nodes, so you have to settle for both being installed on the default C: location. This is just for the shared tools, not all of SQL Server is affected by this bug.
"2. You say you can’t move cluster resources in the gui. Can’t I fire up cluster administrator – right click on the group and select move group to move it to the other node? Or is what you’re talking about not the same. I believe it is from my understanding of clustering."
Answer: Yes you can fire up the cluster administrator gui, but the gui only allows you to move the SQL Server clustered resources. You cannot move quorum ("witness disk") resource using the gui. Just compare what you see in the gui with the output of "cluster group" from the command line and you'll see the differences. It's best just to use the command line to move all resources to the Node 2.
*edit* In my experience if you don't move the SQL Server clustered resources over they will get auto-moved when you remove the node from the cluster. I assume the other clustered resources like the witness disk and the shared storage will also get moved, but I didn't test that. It's better to move them all yourself beforehand.
"3. When you say remove the server entirely from the cluster – are you talking just about removing sql as a clustered service, or actually removing the server itself from the cluster. If so that requires powering down and up servers as you gracefully add cluster resources to prevent resource conflicts. (according to MS http://technet.microsoft.com/en-us/library/cc739757(WS.10).aspx I don’t see how that results in only 45 seconds of down time."
Answer: Wait a sec, are you running SQL Server 2008 on a Windows 2003 server or what? Those directions from Microsoft are old and no longer apply to SQL Server 2008 clusters running on Windows Server 2008 servers. Assuming you are really running on Windows Server 2008, your "cluster" exists as a single server when you start out. After that, you do not need to take down any SQL services when you add or remove additional nodes. This is one of the big improvements of SQL 2008/Windows 2008 over the old SQL 2005/Windows 2003.
"i'm looking at: http://msdn.microsoft.com/en-us/library/ms191545.aspx under remove cluster node. It seems pretty simple, you suggested there could be more to it. i would like to know what else you did."
Answer: Yes, those are the correct directions for removing and adding nodes under SQL Server 2008. But after you remove Node 1 from the cluster (leaving Node 2 as a single server in your cluster), you should uninstall SQL Server from Node 1 before trying to add it back in. Remember that SQL Server is not installed correctly on Node 1, right? I don't know what will happen if you add Node 1 back to the cluster without uninstalling SQL Server first. It may just check to see if all the SQL Server components are there (which they are) and do nothing to correct the file location, and you'd be back to where you started. So a complete uninstall of SQL Server, including an uninstall of all shared components, is important in this process.
*edit* To summarize, when you add a node to the cluster, it will install all the SQL Server components and files during the process. BUT when you remove a node from the cluster it does NOT uninstall all of SQL Server, it only removes the node from the cluster. Some SQL Server components and files are all still installed so you have to uninstall them as a manual step. I'll post another message for how we do that....
--Mr. SQL Guy
June 2, 2010 at 4:00 pm
OK, here's what we do after we remove a node from a 2008 cluster to clean it up. As you can tell this was a copy/paste:
(steps 1-10: remove node from the cluster)
11.Note that all the SQL Server Windows services EXCEPT for the SQL Server Integration Services service are now removed. Also, not all the SQL Server components are removed from the node--you can see that if you look in Control Panel/Programs and Features.
12.To uninstall all the remaining SQL Server 2008 components, first reboot the server, then uninstall "Microsoft SQL Server 2008 (xx-bit)" using the "Programs and Features" applet in the Control Panel. When you do that, the other components below will get uninstalled too. Be sure to check ALL the features of SQL Server during the uninstall so that they all get removed.
Below are the components that should also get removed (you have to refresh the Programs and Features applets to see that the components below were removed):
•Microsoft SQL Server 2008 Books Online (English)
•Microsoft SQL Server 2008 Policies
•Microsoft SQL Server 2008 Setup Support Files
•Microsoft SQL Server Compact 3.5 SP1/2/3 Query Tools English
13.Next, uninstall the following components:
•Microsoft Office 2003 Web Components
•Microsoft SQL Server 2008 Management Objects
•Microsoft SQL Server 2008 Native Client
•Microsoft SQL Server Compact 3.5 SP1/2/3 English
•Microsoft Visual Studio Tools for Applications 2.0 - ENU
14.Once all SQL related components have been removed, REBOOT THE SERVER.
15.IMPORTANT! Log back into the server and remove the following directories:
C:\Program Files\Microsoft SQL Server
C:\Program Files (x86)\Microsoft SQL Server
(... and any other directories on other drives where SQL Server was installed)
16.VERY IMPORTANT!!! DO THIS OR FUTURE INSTALLS WILL FAIL! Remove the following registry keys (and their subkeys):
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server
HKLM\SOFTWARE\Microsoft\MSSQLServer
HKLM\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server
--Mr. SQL Guy
June 4, 2010 at 6:31 am
this is a lot of info, but i think will get me off and running.
thanks for the help again. 🙂
October 6, 2010 at 7:41 am
Hey all,
I just made a post on this: http://qa.sqlservercentral.com/Forums/Topic999289-391-1.aspx and while it's good to know it's resolved with 2008 SP2 ... the issue is still very much so present in 2008 R2 in regards to secondary nodes not setting this up in a non-default location (ie, a D:).
I guess the only resolution in my case is to alter the location of the agent_exe for the time being. I say this because I already have 5 other instances running in this cluster and when I go to add the nodes to my new servers, it's always going to install to the default location.
October 6, 2010 at 7:58 am
Thanks for the info Adam.
I was told by Microsoft that they would get the online documentation for the install changed so that people would be warned of non-default locations for the shared components. But they never did that so people are still falling into this trap. :pinch: I even emailed them twice asking them to do it but they never did.
Step 9 of this doc is where the warning should be: http://msdn.microsoft.com/en-us/library/ms179530(v=SQL.100).aspx
--Mr. SQL Guy
October 6, 2010 at 8:05 am
I'm opening a ticket with Microsoft now ... while I could easily rebuild this instance, I have 5 others on the cluster that I am now worried about. I realize as of now the only issue is for powershell, but can easily be resolved by updating that .exe reference. I'm just worried about what other potential future issues I could be facing.
November 22, 2010 at 7:38 am
Hi,
could anyone tell me, that the issue was fixed with SP2?
Regards
InShaDan
November 29, 2010 at 2:48 pm
adam
Please let me know what they tell you. I would be most interested in what they have to say. Although SQLGuy has provided some really good info, I don’t want to rebuild a clustered instance if I can avoid it.
I'm a network admin who plays the part of a SQL admin on the weekends so I’m not 100% comfortable with the idea.
November 30, 2010 at 9:13 am
Sorry for the delayed response, I actually haven't even gone back and worked on this due to multiple other clustering issues I've been working on. Here was my latest response from the Microsoft tech:
Issue: You install the SQL shared tools in D directory on the 1st node in your cluster, when you add the 2nd node to your cluster, setup puts the shared tools in the default directory C and not on D Because of which Powershell subsystem will not work on the Second node.
Workaround:
Here is the Action Plan which we will be following after we resume:
Since we have the SQL Setup picking up unwanted registry values from the previous install , we need to evict the instance; here are the steps we could use to work around this issue.
a. Install just the shared components on the Node-1 in the D Drive (Instance binaries are already in the D drive)
b. Evict the instance (the one which caused the issue) from node 2 .
c. On Node-2 Install just the shared components on D drive.
d. On Node-2 perform the add node for that instance. (after this we will have all files in the same Drive i.e. ‘D’ on both the nodes.)
I plan on getting to this soon and will follow up with the path I took and the outcome. If I remember correctly, basically the best bet was to simply remove the instance from that server and reinstall appropriately as installing any of the components picks up the values from the registry.
Thanks
November 30, 2010 at 9:23 am
Sounds like a lot of work,,, given i understand why it is necessary.
Sadly the servers i need to fix are in a remote co-location facility. They run our entire production environment. So i have elected to opt for another approach outlined by SQLguy.
use msdb
go
delete from msdb.dbo.syssubsystemsexec
msdb.dbo.sp_verify_subsystems 1
This fixes the issue. The problem is that iof the cluster fails over, it blows up again and you have to run the above script once again. I am examining a way to have SQL run this script on the instance after startup. That way if it fails back and forth it will run the script on the server that is running the instance and it should always work. No need to uninstall and reinstall SQL required. Based on SQLguy's post this was a MS supported workaround too. It’s a lot less work (and a lot less dangerous in my mind compared to temporarily eliminating fault tolerance while uninstalling and trying to reinstall a SQL instance on the same server) and seems like a perfectly acceptable long term solution to me.
November 30, 2010 at 10:04 am
zaubut (11/30/2010)
Sounds like a lot of work,,, given i understand why it is necessary.Sadly the servers i need to fix are in a remote co-location facility. They run our entire production environment. So i have elected to opt for another approach outlined by SQLguy.
use msdb
go
delete from msdb.dbo.syssubsystemsexec
msdb.dbo.sp_verify_subsystems 1
This fixes the issue. The problem is that iof the cluster fails over, it blows up again and you have to run the above script once again. I am examining a way to have SQL run this script on the instance after startup. That way if it fails back and forth it will run the script on the server that is running the instance and it should always work. No need to uninstall and reinstall SQL required. Based on SQLguy's post this was a MS supported workaround too. It’s a lot less work (and a lot less dangerous in my mind compared to temporarily eliminating fault tolerance while uninstalling and trying to reinstall a SQL instance on the same server) and seems like a perfectly acceptable long term solution to me.
If that is the method you move forward with, you can make a start-up procedure in the master database to accomplish this.
November 30, 2010 at 10:46 am
i would love to know how to do that, if you can spare the time.
November 30, 2010 at 11:57 am
zaubut (11/30/2010)
i would love to know how to do that, if you can spare the time.
Create your procedure in the master db, and run this:
EXEC [master].[sys].[sp_procoption] 'schemaname.objectname', 'startup', 'true'
To verify it's setup:
USE [master]
SELECT
[name]
FROM [sys].[objects]
WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1
November 30, 2010 at 3:20 pm
Note that if this issue is really fixed in the latest service pack, you'd have to slipstream the SP into the main binaries to create an updated installation source to make the install work correctly.
How to update or slipstream an installation of SQL Server 2008
--Mr. SQL Guy
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply