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?
View 4 RepliesWe 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.
Can I build a cluster by adding the cluster service, then the SQL instances, then add the other nodes and their passive SQL instances?I would lean to building the cluster first, the add the SQL instances.
View 4 Replies View RelatedHI There
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 !
Thank You
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....
Hello,
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).
Thx!
-Albert-
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.
View 5 Replies View RelatedIs 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.)
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?
Can we add physical nodes and VMs to windows cluster?
View 7 Replies View RelatedHi There
I have found no real documentation on this, so any links would be greatly appreciated.
We have an existing 2 node active active cluster.
A default and and named instance SS2005 Enterprise Edition are installed on the cluster.
Recently we acquired new hardware, 2 servers, which we added to the cluster so we now have a 4 node cluster.
The 2 new nodes have OS etc installed.
Now i have installed sql on a new multi node cluster, i have upgraded sql server in an existing cluster BUT i have never installed sql on an existing cluster for new nodes for multiple instances.
Now i presume you simply run the install on one of the new nodes, do this for a default instance, selecting both 2 new nodes to installl on. And do this again for the named instance.
But in the past I have never been promoted for which nodes to install sql on, it simply does all the nodes BUT i do not want this.
And how will the cluster know where the binary files are for each instance on the new nodes?
In a nutshell, how do i install sql server 2005 for 2 new nodes in an existing 4 node cluster for multiple instances ?
Thanx
I have encountered issue by add a node to SQL cluster
Server 2012 R2 Datacenter version
SQL 2012 Enterprise edition
Error Status>
number of cluster nodes supported for edition - failed
I have failed to bypass with Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode
I'm just starting to work with AlwaysOn Availability and WSFC.
I have in my environment (in Azure) a DC, WSFC and to SQL instances, so I have 3 nodes in my Failover Cluster:
WSFC
SQL1
SQL2
If I simulate failure by shutting down one of the SQL boxes my Availability group seamlessly fails over to the other SQL instance - which is great.
However, I'm starting to look into the workings of the Quorum, my envt has the default settings and when I shutdown both of my SQL servers I expected the Cluster itself to go offline as 2 out of the 3 votes will be negative, but the Cluster is still up - Screenshot below when SQL1 and SQL2 are shutdown:
Going through the Wizard (but not changing anything) it shows following config:
Hi,
I've just installed SQL 2005 SP2 Rollup 3 Package (Build 3186) on a 2 node X64 W2K3 Cluster.
Everything went fine, although after the install, the SQLAgent Services of my instances started to complain about delegation not enabled for the domain account used for the SQLAgent Service.
SPN's were already registered, so I've enabled unconstrained delegation & no errors anymore..
Apparently we're obliged to enable delegation as soon as this hotfix is installed
(maybe due to fix 938086 included in it ?)
To make this setup more secure, we would like to enable constrained delegation.
This does not seem to work, as soon as we choose constrained delegation by adding the SPN of the clustername to the domain user account we're running with & restart the sql agent, it fails with the same error as when no delegation was configured:
! [298] SQLServer Error: 22022, CryptUnprotectData() returned error -2146892987, 'The requested operation cannot be completed. The computer must be trusted for delegation and the current user account must be configured to allow delegation.' [SQLSTATE 42000]
! [442] ConnConnectAndSetCryptoForXpstar failed (0).
Summary:
With Unconstrained delegation enabled for computer account & user account, everything goes fine.
As soon as constrained delegation is chosen, by adding the SPN's to the domain user account of SQL Agent, SQL agent fails to start.
We use a domain account for the SQL Agent.
This account has an 2 SPN registered on it
MSSQLSvc/<FQDN SQL instance network name>:1433
MSSQLSvc/<FQDN SQL instance network name>
Connections to SQL go fine, authorization scheme is Kerberos even when SQL Agent fails to start due to this delegation failure.
Domain account has "act as part of operating system" & "impersonate a client after authorzation"
Anyone an idea ?
I have no idea how to apply "fixes" or updates to SQL Server when it is running on a cluster.
We are currently running SQL Server 2005 with SP2. The version would be Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I am hoping that someone can tell me of a document that exists, that includes the very basics, on how to do this. I need something that says go to the cluster and do these steps in this order, etc. from start to finish.
Thank you.
hi friends,
I have search in my project based on strings(surname, firstname).. but it does'nt works wen name consists of only two letters.... could somebody please help me on this.
Thanks sandeep
Is this possible?, i hope it is:). Your help is very much appreciated.
View 2 Replies View RelatedHello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
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).
4. net start winmgmt
2<sup>nd</sup> Method..
1. Disable and stop the WMI service.
a) Command : - sc config winmgmt start= disabled
b. Command : - net stop winmgmt
2. Run the following commands.
a). Command: Winmgmt /salvagerepository %windir%System32wbem
b). Command: Winmgmt /resetrepository %windir%System32wbem
3. Re-enable the WMI service
Command: sc config winmgmt start= auto
Last command to run after above steps
4. Command: mofcomp "%programfiles(x86)%Microsoft SQL Server100Sharedsqlmgmproviderxpsp2up.mof"
File not found Error for above command.
I am in the process of moving databases from a SQL 2005 Standard version to a 2-node 2014 cluster.All of my 2005 databases back up successfully.They all restore without issue except for one database that has a full text catalog. I get this message
Msg 7610, Level 16, State 1, Line 2
Access is denied to "fileStoragedataMSSQLSERVERFullTextCatalog", or the path is invalid.
Msg 3156, Level 16, State 50, Line 2
File 'sysft_FTCatalog' cannot be restored to 'fileStoragedataMSSQLSERVERFullTextCatalog'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 2
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
[code]....
I went as far as giving the folder full access to everyone temporarily and received the same error.
I ran the Advanced cluster preparation for a new sql instance on an existing cluster.
Slq Server 2012.
After is completed, it was successful, I realized I specified the wrong Instance Root directory.
Is it possible to remove what the preparation installed? Or is it possible to change the root directory?
I´ve been reading that SQL Server 2012 Always On is dependent on having a Windows Failover Cluster setup. Is that correct ?
View 6 Replies View RelatedI have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512),
@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX),
@column NVARCHAR(MAX);
DECLARE @indexname NVARCHAR(512);
SELECT name As 'Table'
[Code] ....
can we join a node in a windows cluster which is already in a different cluster?
We have this requirement as we need to setup readable secondary ( always on AG) on the third node.
Hi all,
We have many tables which have cluster index on column with datatype 'Char(200)'.
Does anyone have script to change cluster index to noncluster for all user tables which have clustered index on a column with 'char(200)' datatype.
Thanks,
Deepak
Wanted to know about difference between cluster and non cluster index with example.
When to use cluster index and non cluster index .
CREATE TABLE [dbo].[Access](
[AdminID] [int] NOT NULL,
[FnID] [int] NOT NULL,
CONSTRAINT [PK_AccessFn] PRIMARY KEY NONCLUSTERED
[Code] ....
why the cluster and non cluster indexed created on the same key?
Hi
i have 3 master tables 1)RoleDetails(Roleid(PK),name,masterroleid(fk) ref:RoleDetails roleid)
2) PositionDetails(positionid(PK), name,MasterPositionid(FK) ref:PostionDetails postionid,Roleid(fk) ref:Roledetails roleid)
3) Userdetails(userid(pk), loginid,pwd,roleid(fk) ref:roledetails roleid,positionid(fk)ref:postionDetails positionid,fname,address)
how to Create two functions one return child nodes as per Case 1 and another one is return Parent Nodes
as per case 2
(Manager) a -- r1 (roledetails)
/
(ROL)a1 (ROL) a2 -- r2
/ | / | |
(RO)b1 b2 b3 b4 b5 b6 -- r3
Case 1:
On passing the User ID of (a) , should get the Output as User id of ( b1,b2,b3,b4,b5,b6) along with their Role ID.
On passing the User ID of (a1), should get the Output as User ID of (b1,b2,b3)along with their Role ID.
Case 2:
On passing the Role ID of (R3), should get the User ID of all the Parent roles( a1 and a2 (R2), a(R1)long with their Role id
Case 3: on passing role id of child node , should get only all particular parent userid and roleid's.
thanks in adv.,
chakri
I have a situation that can be summarized like this :
DECLARE @XML XML = '<X C="0" I="1"><E D="CODE1" A="0" /><E D="CODE2" A="0.03" /><E D="CODE2" A="0.04" /></X>'
SELECT
E.value('@D','varchar(MAX)') AS Code,
E.value('@A','varchar(MAX)') AS Rate
FROM @XML.nodes('./X/E') AS T(E)
The order of appearance is of capital importance as the rates do apply on the previous ones. Is there a way to generate a sequential ID based on the order of appearance in the XML string ? In this case, I want :
1 CODE1 0
2 CODE2 0.03
3 CODE2 0.04
I though of using a temp table with Identity column... But it's not the best way for my need. I have multiple lines each with a XML String. The ROW_NUMBER() windowed function needs a ORDER BY clause that I can't provide.
I am looking for information on building a 4 node, multi-instance, SQL cluster on Win2K3 Datacenter server. I've consulted BOL, google, and Microsoft's "techinfo" pages for Datacenter server clustering, but everytime I think I'm going to get some good information, I end up with info on a default, 2-node cluster setup. Any guidence, links, etc. would be highly prized.
Thanks very much.
I have a tree structured table like the ff.
which looks like the one below in tree view.
I'd like to get the rows under a specified elementnumber which may have unlimited sub-nodes.
i.e. If i want only 'Level1' i would get results from row 2-5.
any help is greatly appreciated.
Dear all,I have table called CATEGORY, which is defined as follows:CREATE TABLE CATEGORY(CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL,CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINTUC__CATEGORY__CATEGORY_NAME UNIQUE,PARENT_CATEGORY_ID INTEGER,CATEGORY_ICON IMAGE,DEPTH INTEGER,CONSTRAINT PK__CATEGORY PRIMARY KEY (CATEGORY_ID))Supposly, the following snap shot was taken later:================================================== ============| CATEGORY_ID | CATEGORY_NAME | PARENT_CATEGORY_ID | DEPTH |================================================== ============| 1 | PC | NULL | 1 |--------------------------------------------------------------| 2 | Networks | 1 | 2 |--------------------------------------------------------------| 3 | Audio | 1 | 2 |--------------------------------------------------------------| 4 | Video | 1 | 2 |--------------------------------------------------------------| 5 | TV Cards | 4 | 3 |--------------------------------------------------------------| 6 | Graphics Cards | 4 | 3 |--------------------------------------------------------------| 7 | AGP | 6 | 4 |--------------------------------------------------------------| 8 | PCI | 6 | 4 |--------------------------------------------------------------| 9 | Input Devices | 1 | 2 |--------------------------------------------------------------and I'd like to create out of this hierarchy the following desired XMLfile:<?xml version="1.0" encoding="utf-8" ?><Hardware><Catgeory name="PC" id="1"><Catgeory name="Networks" id="2" /><Catgeory name="Audio" id="3" /><Catgeory name="Video" id="4"><Catgeory name="TV Cards" id="5" /><Catgeory name="Graphics Cards" id="6"><Catgeory name="AGP" id="7" /><Catgeory name="PCI" id="8" /></Category></Category><Catgeory name="Input Devices" id="9" /></Catgeory></Hardware>The reason for this file is that it will be a datasource of theTreeView Control new in asp.net 2.0.Now, programmateiclally using C#.net i started using the XmlDocument,XmlTextWriter and XmlTextReader Namespaces and started using susingrecurrsion to genearete this desired XML file out of the records in thesnapshot, but ...Is there an easy way of doing this using SqlServer 2005 with the newdatatype XML?*Any hint would also be ok*Best regards
View 12 Replies View Related