SQL 2012 :: Cluster Errors - No Auto Fail Over?
Jun 25, 2014
My environment is a 64 bit Windows Server 2012 Standard edition that is running SQL Server 2012 SP1 Standard Edition (64 bit) which has an Active Passive Cluster set up and I unfortunately had no involvement in this cluster build. The person who did is no longer with the company and is not responsive to questions.
This morning this following errors popped up:
Event ID: 1230 - Cluster resource 'Databases' (resource type 'Physical Disk', DLL 'clusres.dll') did not respond to a request in a timely fashion. Cluster health detection will attempt to automatically recover by terminating the Resource Hosting Subsystem (RHS) process running this resource. This may affect other resources hosted in the same RHS process. The resources will then be restarted.
The suspect resource 'Databases' will be marked to run in an isolated RHS process to avoid impacting multiple resources in the event that this resource failure occurs again. Please ensure services, applications, or underlying infrastructure (such as storage or networking) associated with the suspect resource is functioning properly.
Event ID: 1146 - The cluster Resource Hosting Subsystem (RHS) stopped unexpectedly. An attempt will be made to restart it. This is usually associated with recovery of a crashed or deadlocked resource. Please determine which resource and resource DLL is causing the issue and verify it is functioning properly.
When I saw these errors, I brought the "Databases", "Logs", and "Quorom" disks back online and all was well. I then proceeded to check Application and System Logs noticing there where some VMWare/SAN errors that caused the CPU spike resulting in the Cluster errors.
Regarding the questions, why didn't a failover occur and What can I do to mitigate this process are now what I am focusing on. Researching the above errors, I discovered that you could a policy based what happens in a situation like occurred this morning. You can set policies at the Role level, "Server" level, and at the disk level.
The Role Policy seems to be a little less involved, screenshot RoleProperties.png show these. I assume these are defaults. The server name properties are set to the following screenshot, Servername_Properties_Policy.png. This seems to be correct to me, but there is where I am lacking. I think this means that if it fails at this level all associated resources, disks, etc will failover to node 2.Now where I think the issue is, the third screenshot, Database_Properties_Policy.png. There is no policy set to for situation where a disk resource fails. Which I believe is what happened this morning. Some questions:
1. Do I need to set this similar to what is in the ServerName screenshot?
2. What happens if I check the box for "If restart is unsuccessful, fail over all resources in the role"? Will this only fail over this one disk? If so, it doesn't seem like that would be ideal. Would it fail over at the Role level and move over all associated resources?
I am also not sure what time limits I should set for Period restarts, maximum restarts, delay between restarts, etc...
View 3 Replies
ADVERTISEMENT
Jun 13, 2014
Is there such a thing called 'Load balancing' on fail over cluster?
View 2 Replies
View Related
Oct 22, 2015
I need to be able to restart an application service after the SQL Cluster fails over. how to accomplish this as a SQL job?
View 1 Replies
View Related
Feb 16, 2014
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
%userprofile%AppDataLocalTemp
View 2 Replies
View Related
Oct 14, 2007
We have Active/Passive cluster on Windows 2003. It is a 2 nodes cluster. The active server came down and the Cluster didn't failover. Then we had to manually failover. Can you help me to decipher the Cluster.log to find out why it didn't failover?
00000d08.000012a8::2007/10/12-04:04:40.847 ERR SQL Server <SQL Server>: [sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
00000d08.000012a8::2007/10/12-04:04:40.847 ERR SQL Server <SQL Server>: [sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Native Client]Query timeout expired
00000d08.000012a8::2007/10/12-04:04:40.847 ERR SQL Server <SQL Server>: [sqsrvres] OnlineThread: QP is not online.
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13522 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] ReadFile 928 (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] WriteFile 95c (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] ReadFile 928 (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsFlushBuffers 95c, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsCloseHandle 928, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsCloseHandle 95c, status 0
0000077c.000003dc::2007/10/12-04:04:43.785 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D2.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13523 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] ReadFile 95c (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] WriteFile 928 (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] ReadFile 95c (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsFlushBuffers 928, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsCloseHandle 95c, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsCloseHandle 928, status 0
0000077c.000003dc::2007/10/12-04:06:23.084 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D3.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13524 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] ReadFile 7e0 (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] WriteFile 95c (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] ReadFile 7e0 (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsFlushBuffers 95c, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsCloseHandle 7e0, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsCloseHandle 95c, status 0
0000077c.000003dc::2007/10/12-04:08:08.259 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D4.tmp, status 0
0000077c.00000844::2007/10/12-04:09:11.323 INFO [Qfs] GetDiskFreeSpaceEx Q:MSCS, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [CP] CppRegNotifyThread checkpointing key SoftwareMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLSERVER to id 4 due to timer
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsGetTempFileName C:DOCUME~1SVC_CL~1LOCALS~1Temp, CLS, 13525 => C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsRegSaveKey C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [CP] CpSaveData: checkpointing data id 4 to quorum node 1
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [CP] CppWriteCheckpoint checkpointing file C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp to file Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsCreateDirectory Q:MSCS 592a048-5457-426a-a46d-996441ef0182, status 183
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsOpenFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp => 3, 36cf960 status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsOpenFile Q:MSCS 592a048-5457-426a-a46d-996441ef0182 0000004.CPT => 2, 36cf8d0 status 183
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] ReadFile 928 (regf) 32768 16384, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] WriteFile 7e0 (regf) 16384, status 0 (0=>0)
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] ReadFile 928 (regf) 32768 0, (0=>0) 0 status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsFlushBuffers 7e0, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsCloseHandle 928, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsCloseHandle 7e0, status 0
0000077c.000003dc::2007/10/12-04:09:56.652 INFO [Qfs] QfsDeleteFile C:DOCUME~1SVC_CL~1LOCALS~1TempCLS34D5.tmp, status
View 5 Replies
View Related
Jan 3, 2007
Greetings,
First I am fairly new to SQL Server 2005 Clustering so this is why I was to see if any of you might be able to help me.
Are current setup is as follows:
CRM1 SERVER 2K3 R2 xxx.xxx.xxx.74
CRM2 SERVER 2K3 R2 xxx.xxx.xxx.75
HP MSA1000 xxx.xxx.xxx.75
MSDTC IP xxx.xxx.xxx.77
SQL INSTANCE xxx.xxx.xxx.78
Now are CRM guys are trying to connect to xxx.xxx.xxx.78 for there SQL instance or RDP into this ip address and they can’t…Now the question is should they be able yto logon to the SQL Instance via RDP?
Thanks,
CujoX
View 3 Replies
View Related
Apr 25, 2007
I have sql 2000 sp4 running on a cluster, we had to fail over to other cluster, and now the sql agent choice to use a mail profile is grayed out. I can go to sql mail and test the profile and it works, but I can't change it for the sql agent? Anyone seen this before
View 3 Replies
View Related
Sep 27, 2007
Hi,
A few months ago, I was trying to install SQL Server 2005 SP2 on a cluster (Active-Active), the Cluster has 3 instances, 2 lives in one node and the other one in the other node. When I try to make the deployment an error message appear, I do not have the error right now but it€™s something like €œCould not connect to the passive node, installation failed€?. The worst part of the error was all instances shut down and I couldn€™t bring on line and I have to reinstall all instances. I need to install SP2 but I€™m a little afraid fail again. Do you know the best way to deploy the SP2 on a cluster? Maybe move all instances in one node before install SP2? Can you help me with your comments. Do you think I have problems with the windows cluster?
Thanks and Regards,
View 3 Replies
View Related
Aug 14, 2015
I have had a serious issue with a production AlwaysOn cluster whereby the service did not successfully transition to the secondary node and I cannot find the root cause of the issue.
Some details: It is a 2 node cluster (same datacenter) with a shared disk quorum, Windows Server 2012, both are virtual machines running on VMWare vSphere  5.5. SQL Server version is 2012 Enterprise SP2 CU6
The failover occurred because of a network incident (a spanning tree recalculation caused a connection timeout between both nodes). Initial entries in the SQL Log look normal for this event, for example:
05/08/2015 11:18:06:Â A connection timeout has occurred on a previously established connection to availability replica 'FIN-IE-PA078' with id [6910F4A9-87E7-4836-BA79-0F41BE90266D]. Â Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.
05/08/2015 11:18:06:Â AlwaysOn Availability Groups connection with secondary database terminated for primary database 'UserManagement' on the availability replica with Replica ID: {6910f4a9-87e7-4836-ba79-0f41be90266d}. This is an informational message only. No user action is required.
[code]....
My interpretation of this is that the cluster failover attempts failed, because the network condition still persisted. The network interruption lasted approximately 2 minutes, and I would have expected the cluster to come back online at this point, after the restart delay period as suggested in the last entry in the error log. However this did not happen.
View 10 Replies
View Related
Mar 27, 2008
I've been trying to install the following update: Microsoft SQL Server 2005 Express Edition Service Pack 2 (KB 921896). I always recieve the following error: 2B22,
Please advise on a fix for this.
Regards,
View 5 Replies
View Related
Apr 10, 2014
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?
View 3 Replies
View Related
Feb 18, 2014
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 Related
Mar 12, 2015
I have a 2-node Windows Cluster and File Share Witness with an availability group. The cluster is set to sync and auto failover. I'm using SQL 2014 and Windows 2012R2. I'm trying to understand the steps to apply Windows Updates that require a restart. Are the steps something like this where ServerA is the primary replica and ServerB is the secondary replica.
Apply updates to ServerB.
Restart ServerB.
Fail ServerA availability group to ServerB using ALTER AVAILABILITY GROUP MyAvailGroup FAILOVER;. Server B is now the primary.
Apply updates to ServerA.
Restart ServerA.
Fail ServerB availability group to ServerA using ALTER AVAILABILITY GROUP MyAvailGroup FAILOVER;. Server A is now the primary.
What I don't understand is where Drain Roles on the Failover Cluster Manager fits in?
View 3 Replies
View Related
Jan 10, 2007
When the setup program tries to start the database service (the last step of installation), it indicates an error 29503.
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
The error is (17058)
Any help would be appreciated.
View 1 Replies
View Related
Jun 20, 2007
We are getting the following error messages on a regular basis.
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]TCP Provider: An existing connection was forcibly closed by the remote host.
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 2746; message = [Microsoft][SQL Native Client]Communication link failure
[sqsrvres] OnlineThread: QP is not online.
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Native Client]Communication link failure
[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed
I have windows server 2003 service pack 1 and sql server 2005 service
pack 1 - 64 bit. Sometimes it happens in the middle of the night. Sometimes it's in the middle of the day and sometimes it's multiple times in the same day. Usually the server fails over.
Any help is appreciated
View 1 Replies
View Related
Sep 25, 2007
I'm experiencing some frustration with my active/passive SQL cluster not running my .DTSX packages. I am hoping someone can shed some light on what I need to do.
I've created some .DTSX packages with the SQL Server Business Intel Dev Studio. I initially built & tested these packages in a non-SQL cluster environment without any problems. I'm now re-creating them to work on our SQL cluster. If I run the package through the Dev Studio it works great.
The packages basically grab .txt files from one of the shared drives (which is a resource of the sql cluster group) and imports the data into the one of the databases. The database does not have any special settings (right-click -> new database... -> enter name -> click ok).
I've setup a SQL Server Agent Job with 1 step with the following properties.
Step name: I Offices
Type: SQL Server Integration Services Package
Run as: SQL Agent Service Account
Package source: File system
Package: G:ImportRAGFLOffices.dtsx
When I run the SQL Server Agent Job through MSSQL Server Management Studio (right-click -> start job) I get an error on "Execute job 'RAGFL TestJob'".
These are the 2 messages that show up when I view the history of the SQL Server Agent job.
***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)
Step ID 0
Server BADBOYS
Job Name RAGFL TestJob
Step Name (Job outcome)
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User sa. The last step to run was step 1 (I Offices).
***********************************************************************************************
***********************************************************************************************
Date 9/25/2007 1:16:13 PM
Log Job History (RAGFL TestJob)
Step ID 1
Server BADBOYS
Job Name RAGFL TestJob
Step Name I Offices
Duration 00:00:01
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: THEISLANDAdministrator. The package execution failed. The step failed.
***********************************************************************************************
THEISLAND is our internal domain.
View 2 Replies
View Related
Sep 12, 2007
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 ?
View 25 Replies
View Related
Jul 27, 2015
I'm trying to figure out a way to automatically restart the SQL Agent service when an AG fails over to a secondary node. I've created a job on each AG node that runs every 15 seconds and detects when a fail over has happened. It then issues a SQL Agent stop command and then a SQL Agent start command. The problem is that once the stop command happens the start command never fires because the SQL Agent is now stopped.
View 3 Replies
View Related
Feb 13, 2015
I have configured three transactional replication one from DC - Data center & DB for database
DC1-DB1 to DC2-DB1 -- 1
DC2-DB1 to DC2-DB2 --2
DC2-DB2 to -DC2-DB3 --3
Before synching the data on DC2 databases I wanted to truncate / drop the objects on 1 and 2 setup as these are already participating in replication. Setup 1 and 2 are publisher as well as subscriber. I am not able to reinitialize these tables on these setup. I cannot use SSIS.
View 3 Replies
View Related
Mar 14, 2014
I am running SQL Server 2012 on a VM and am trying to pull data from a network drive (S: or misvnascfs0003).I can import data using the Import Wizard and save the package as an SSIS package. When I try to Run the Package I get The Acquire Connection method call to the connection manager "SourceConnectionExcel" failed with the error...
If I save the file to the VM local drive the SSIS package run fine.When I log into SSIS through SSMS i have to run as "Administrator".
View 2 Replies
View Related
Sep 23, 2014
SQL 2012 Standard on Windows Server 2012 Standard
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
View 3 Replies
View Related
Oct 14, 2014
I have configured two node cluster environment on vmware with windows 2008 r2 os.I want to configure sql clustering over it.but prior to that I ran failover clustering manager on node V1 to create two node cluster(node v1,node v2 and third node as a domain controller with shared storage using iscsi target).After I successfully created cluster with name SqlClus1 I shut down all my vmware.Now when I start them all and see clustering status I get error RPC server not found and Cluster server can not be connected.On my cluster manager page I see cluster events marked as critical for both nodes.and cluster staus down.
View 2 Replies
View Related
Oct 19, 2015
Every once in a while a scheduled restore of a production database backup to a development server will fail with the following error.
RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group
While it is true the production database is involved in database mirroring, the development server does not have database mirroring enabled. This error tells me something within the backup is telling the development server the database is configured for database mirroring.
However the perplexing part for me is that we only receive this error maybe 5% of the time, if that, and only on a couple of our databases. We have numerous other restores of mirrored production databases to development servers that have never produced this error. So my question is what is causing this error to occur, and why is it not happening all of the time? We get around this error by deleting the DEV database and re-running the restore job.
View 0 Replies
View Related
Aug 25, 2015
The import from Flat File Source fails: Error 0xc02020a1: Data Flow Task 1: Data conversion failed.
The data conversion for column "ArticleName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
I have changed the size of the column "ArticleName" (varchar) to max but the error comes up again.
The data i want to import came with multiple flat files. They all could import properly but this one is a problem.
View 1 Replies
View Related
Sep 15, 2014
When I have a record that I copy and paste (Access 2010 datasheet view) into a new record with one column having a unique constraint , I display message "Cannot insert duplicate code" then if not all values in the record are inserted , I get a #deleted in all columns Refreshing , I find that the id is incremented by 2 instead of one.
That is if the previous record has ID 20 , the #deleted record has ID 22 .
avoid the #deleted which happens if not all columns are filled.
View 9 Replies
View Related
Dec 29, 2014
My question: Is it okay to drop all the auto generated column statistics? (for the following scenario)
- I am cleaning up unnecessary objects (tables, unused indexes, overlapping statistics etc) from databases and found out there are more than 1400 auto generated column statistics on one database (lets call it A).
- Database A was used to be our reporting database but from last several years we are using database B for reporting. DB A has all the historical data while DB B only has valid records.
- We are updating all the column statistics with full scan nightly on database A and it is talking almost 2.5 hours to do that. Now I want to drop all the "unnecessary" statistics those were created when DB A was reporting database and they are no longer in use. There is no way to know the creation date of the column statistics that I know of. Statistics "last update date" is of no use because of our nightly job. So I was thinking of dropping all the auto generated column statistics and let the sql server create as it needs from now.
View 0 Replies
View Related
Oct 1, 2014
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
View 9 Replies
View Related
Nov 21, 2014
We have implemented a very small reporting database which has a main table that started off small and has now grown to around half a million rows. Initially, there were no indexes on the table apart from a clustered index, but as the data has grown, performance has dropped and so we have added a number of indexes. This has resolved the performance issues.
Before creating the indexes SQL Server had auto created a number of statistic objects (_WA_Sys_000... etc). After creating the indexes, new statistic objects where created for the new indexes. In some cases, there are duplicate statistics (auto and index) for the same columns.Should I go through and drop the duplicate auto statistics? Will having duplicates cause issues at all?
View 2 Replies
View Related
Jun 25, 2015
I have a table that has a primary key that is auto incremented by 1. This table's data is cleared out periodically and as data gets added the auto id primary key continues to increase in numeric value. Once the data is cleared from the table the auto id names could be used again(the eventId is not stored) Currently the eventID is at 26,581,399. I know the maximum int value is 2,147,483,647.
How should I handle this? or rebuild the table every time the data is cleared(problematically)?
View 3 Replies
View Related
Jul 15, 2015
We have SQL Server 2012 EE SP2 server that is getting hit by reports that time-to-time use up to 300GB of tempdb.
It's happening because our clients can use 'bad' parameters for some reports.
Is there a way to autokill sessions that overuse tempdb (for example, if a session uses more than 15Gb in tempdb)?
Killing can happen immediately or after regular check (5 min for example).
View 3 Replies
View Related
Apr 13, 2014
I want to find out the time out errors for a particular SP. How to check that?
View 2 Replies
View Related
May 31, 2014
There is a checkbox when you create an SSIS catalog which says "Enable automatic execution of Integration Services stored procedure at SQL Server startup". It's a good practice to have this option on. BUT! How do you verify whether this option is ON or OFF after you have created a catalog and how do you enable it if it wasn't enabled at the time of creating the catalog?
View 1 Replies
View Related
Mar 29, 2014
I have virtualbox installed on my notebook. For testing purpose, is it possible to setup SQL Server Fail-Over cluster using virtual box? I don't have a shared storage. How can I simulate that?
View 1 Replies
View Related