.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Post New Web Links

Moving an Instance from non-clustered to clustered?

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server


There is a non-clustered SQL 2008 R2 instance on Windows 2008 R2 (node1). There is also another identical server (node2). The app owner wants us to make the existing instance clustered while keeping the instance name and minimum downtime. What would be the best way to do that?

If we are allowed to change the instance name, I would;

- Install "Failover Clustering" feature

- Config SAN

- Install a new named-clustered sql insance

- Copy databases, replication setup etc.

- Remove the local(non-clustered) instance


But, I can not think how we can keep the same instance name without removing the existing instance first.

Any idea?





View Complete Post

More Related Resource Links

SQL 2008 R2 clustered instance does not start in single user mode

Hi I am testing the startup of a SQL 2008 R2 clustered instance (Windows 2008 R2) using single user mode but it fails with : Error: 17058, Severity: 16, State: 1. initerrlog: Could not open error log file ''. Operating system error = 3(The system cannot find the path specified.) - I am running the following command from the default sql installation : C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL01\MSSQL\Binn>sqlservr.exe -c -m -s SQL01 - The cluster service on the passive node has been shutdown. - The SQL Server and SQL Agent services have been set to offline in Failover Cluster Manager. Any assistance greatly appreciated. Regards, PK    

The SQL Server failover cluster instance name 'Server02' already exists as a clustered resource. S

Hello, I have a node of 2 servers of Win 2008 R2 Enterprise and trying to install a 2nd instance in a SQL 2008 R2 Cluster. I had SQL_INSTANCE2 previously installed that I removed from the two nodes. Now when I attempt to install it again from scratch, in setup GUI where I specify the SQL Server Network Name and enter the previously entered name "Server02", I get a failure in the setup with the following error message: "The SQL Server failover cluster instance name 'Server02' already exists as a clustered resource. Specify a different failover cluster instance name." How do I fix this problem, I don't want to enter a new name like "Server03" in order to proceed with the setup, because when I do enter "Server03" the setup does continue without an error message. So what am I missing? Do I have to ask the DNS administrator to see if there's an A record for "Server03" and have him delete it so that the setup works? Or the fix is by doing something else? I would appreciate any help. Thank you  

Can't remove instance from clustered server

Hi ,

I'm using SQL Server 2008 and I have on Active-Active Cluster on
a Windows Server2008.

I have one SQL instance on that Server. The other node I had SSAs wich I want to uninstall and be with another instance of SQL.
I had SSIS has a clsutered here also.

I unistalled the SSAS and made Prepare SQL to Cluster intallation on Node 1 (that already had one SQL instance).

When I tried to Complete the SQL Failover clustered installation I cannot reach the instance and after a few atteps I cannot connect with SSIS on this clustered.

I cannot remove the SQL instance from ADD/Remove Programs because it gives me the error that this instance is used has a cluster.

So this is what I want to do:

-Remove the instancethat says that is clustered but I can't see the cluster on Failover Clustered Administration.
-Setup SSIS as a clustered service on the instance that was previous installed(I can reach the SSIS editing SQL Agent Job but I can't connect through Database Engine.Through the Agent all the packages are unreacheable but visible.)

I already See that I can't add features to a clustered installation but I read that it's possible to Add SSis if I stop both nodes. How can I stop both nodes to install SSIS.

Problems uninstalling a clustered instance

I upgraded a SQL 2005 named instance to 2008.
After this succeeded, I started adding two other instances.
After adding the first instance to the active node, I went to the passive node to add it there. The install detected that I had installed it as a default (MSSQLSERVER) instance instead of a named instance. So I went back to the active node, and was removing the instance so that it can be reinstalled.

The uninstall has stalled (it's now been 2 hours since the last log entry). On the "Remove Node Progress" screen, I have:
Performing Windows Installer actions.
{progress bar, about 55%}

In the C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20100929_165557\Detail.txt log file, I have this as the last few entries:

2010-09-29 17:07:00 Slp: Completed Action: SqlEngineConfigAction_remove_unconfignonrc_Cpu32, returned True
2010-09-29 17:07:00 Slp: ---- Flush datastore cache before calling configuration action 'cluster_ip_address_cluster_unconfig_Cpu32'
2010-09-29 17:07:00 Slp: ---- Flush datastore cache before calling configuration action 'cluster_ip_address_cluster_unconfig_Cpu32'
2010-09-29 17:07:00 Slp: ----------------------------------------------------------------------
2010-09-29 17:07:00 Slp: Running Action: cluster_ip_address

Renaming a clustered SSAS instance


Microsoft has posted advice here:  http://msdn.microsoft.com/en-us/library/ms143692(v=SQL.100).aspx   that says you can't use the 'Instance Rename' tool to change an clustered instance's name.  They mention something called the Rename Instance dialog box.

Do they the just mean change the DNS name for the instance via the general tab on the name's properties dialog?

Could it be that simple, just like changing the name of any other clustered virtual machine?




The action 'Move to node SQLN1' did not complete. An error occured moving the clustered service or


Hello All,

We have two nodes clustered 2008 SQL server running 5 instances (one default instance and the rest named instances). The default instance failed over to node 2 and now I cannot fail it back to node 1 and keep getting the following error when I try to move the instance I get the following error in Failover Cluster Managemen:

" The action 'Move to node SQLNV1' did not complete. An error occurred moving the clustered services or application 'SQL Server (MSSQLSERVER)'. the operation failed because either the specified cluster node is not the owner of the group, or the node is not possible owner of the group"

Thanks in advance!



Toolbox: Manage databases, easier FTP, and clustered caching


This month's products include DatabaseSpy, FileZilla, NCache, and more.

Scott Mitchell

MSDN Magazine October 2007

Databasemail error in clustered environment

Hi. I'm having trouble getting DBmail to work on a SQL 2005 SP3 STD. (64-bit) installation. I've setup database mail with the wizard. I've tested sending an e-mail using telnet -> OK!   But when I try to send a test e-mail from the sql server management studio I the message is queued, and the Windows Event Viewer logs an error like this: *************************************** Event Type: Error Event Source: DatabaseMail Event Category: None Event ID: 0 Date:  27-08-2010 Time:  10:01:23 User:  N/A Computer: <My_servername> Description: 1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: There was an error on the connection. Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), connection parameters: Server Name: <My_servername>\<My_named_instance>, Database Name: msdb Data: System.Collections.ListDictionaryInternal TargetSite: Void OpenConnection(Microsoft.SqlServer.Management.Common.SqlConnectionInfo) HelpLink: NULL

Does inserting a non clustered index reduce IO?

I ran a report on my prod server (Performance - Top queries by total IO) and found the following query the 2nd highest.   select * from Table1 where Col1 is null order by Col2, Col3, Col4 Table has 100,000 rows. 1 pk and about 30 columns After running it against engine tuning advisor it advised, if i created a non clustered index on Col1, Col2, Col3 and Col4 it would increase the performance by 77%. But when i run the command just by it self it runs and completes instantaneously. What am i missing? Should i create the index to reduce the IO's as the Advisor suggests?    

Clustered vs NC index storage differences?

Does clustered index takes more disk space than NC for the same filed(s) for large tables? Also is CI takes longer (time) to build than NC if you have large tables? Any microsoft articles supporting these aruguments.

Oracle clustered table to MS SQL Server 05 SSIS

Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

SQL2008 cannot detect clustered disk


I've created a sql2008 sql cluster on windows2008 with all the latest service pack when i add a new clustered disk recently; it can be added to the sql cluster but the SQL management studio cannot "see" the newly added clustered disk.

Any advise?

Thanks for any info

Changing Clustered SQL Server 2005 Default Server Collation


We are attempting to change the default collation of an SQL Server 2005 Clustered environment. We have created an "unattended install" script to perform this, and have modified it several times as errors have arisen repeatedly. The last time, however, we used the following script:


We are running this script from the primary (Active) node of the cluster. We did notice that the bootstrap logs created from this final execution did include logs from both nodes. When I look in the Summary.txt file, it tells me that Setup succeeded with the installation. After the script executed, we rolled the cluster from primary to secondary, and rebooted the primary node. We then rolled the cluster back to the primary, and rebooted the secondary node. Once that was complete, we cycled the SQL Server service from the SQL Configuration Manager. After all those steps, we opened SSMS client to look at the properties of master, and the collation was still CI_AI.

We are losing valuable time in

SQL Browser Services does not start - 64bit, Entp edition Clustered Envt


We have a 4 node active-active cluster catering to 8 instances of SQL. Recenly we had a issue when we tried to connect to the Management Studio, we were pointed with error stating that the instnace does not exist error -26 etc. The same was the scenario on all the 4 nodes and we observed that the browser serves was not starting and his was causing the connection to the server to fail.

On refering to the blogs we found that his was being caused by some 32bit registry entries in HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.9. We renamed this to .9x on all the nodes and are now able to start the borwser service.

The issue here is the above is only a temporary workaround. Each time the node restarts, these entries are created again.

Could anyone guide us on how to get this fixed andwhat programs is responsible for this as though browser srevice has started, i see many errors in the eventlogs which look as below. These entries are because of the change in the registry value but we need to fix them too..

Cluster service could not write to a file (C:\DOCUME~1\SRV-EA~1\LOCALS~1\Temp\CLS887.tmp). The disk may be low on disk space, or some other serious condition exists.


non-clustered index



Can we you create a non-clustered index over a column already having clustered index



Issue with SAN Drive Migration in master database in clustered SQL Server 2005 SP1




In our SQL Server 2005 SP1 cluster, I have to retire the existing SAN and move all my databases to new SAN drive. I have successfully moved all datafiles (user,system databases, resource) except master database.


Even when I change the parameters in start-up from configuration manager, I am getting error when starting up from cluster administrator / configuration manager.


But I am able to start the SQL Server successfully from command line (without any flags).


When I start the SQL Server from cluster administrator / configuration manager (after changing the parameters), I see that the start-up parameters are changing back to originalpath (old SAN) .


Please let me know incase you need any more information.

Do I really need Clustered Index??


I read in so many articles, some books and listen some expert's words and even microsoft recommended that, A table with Clustered Index will performs better than Heap in most of the cases. I too believed same until today. But today, I saw in my project, out of 2,000 tables we have, only 50-60 tables have CI only, and remaining tables are all heap only. Still our 90% of stored proc. always will takes less than 1 or 2 seconds, even when they tested with millions of data. 

            I never had chance to look into the DB, as i will not work directly with proj. dev. Today only I saw this. i asked one of my colleague about this. He replied that, when insertion/updation/deletions performed, CI will take more time, If it is heap, it wont take more time. Yes, What ever he is telling is true. It needs to adjust the indexes, page splits also might happens...  

a Heap has following advantages over CI.

1. Insertion/Updations/Deletions faster...

        Yes, as its an web application, which will be continously use by the people, no of DML operations will happen. 

2. Coming to Selections, I saw the tables have Non-CI, which almost covers all the queries.... and more over the Because of the Paging implementation, always, only 25-50 records will be selected,  

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend