Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for the ‘SQL High Availability’ Category

Setting up WMI query and getting error SQLServerAgent Error: WMI error: 0x80041003.

Posted by rahmanagoro on June 13, 2011


I was trying to setup database mirroring alerts on one of my production database servers and I looked into the article on the MS site, see http://technet.microsoft.com/en-us/library/cc966392.aspx on mirroring alerting best practices. I then proceeded in trying to setup same, I ran the script below.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Mirroring_Alert',
  @enabled=1,
  @delay_between_responses=1800,
  @include_event_description_in=0,
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
  @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 5 ',
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Mirroring_Alert', @operator_name=N'DBA_GROUP', @notification_method = 1
GO

 

I then got the error message below.


Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: WMI error: 0x80041003.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 300
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

 

I ran thesame code on my desktop and it was fine, but I was thinking what could be the problem here.

I checked everything to make sure that the syntax was right and there wasnt anything wrong, bumped across a few KB’s about thesame problem, but it was primarily being caused by server names being longer than 15 characters and this applied to SQL 2005, my SQL instance was SQL 2008 and I then thought what could be going wrong. After spending the hour battling this, and I have a rule that any problem taking longer than an hour to fix, I need to get my head cooled out, pop out for a 10 minute break and fresh air and then give it another go. It seem to work for me time and time again, I came back, thought !! Hang on a minute, lets check the SQL error log to see if there are any pointers to the root cause, on there I saw errors as follows.


Date  6/13/2011 11:45:33 AM
Log  SQL Server (Current - 6/13/2011 10:01:00 AM)

Source  Logon

Message
Login failed for user 'NT AUTHORITY\SYSTEM'. Reason: The account is disabled. [CLIENT: <local machine>]


Ahh, this could be it. When I setup SQL servers I often disable the builtin/Administrators login as this is a security risk. It looks like the WMI service is using the local system account and this is obviously disabled and thus causing the problem highlighted.

I changed the WMI service account such that its a domain account and permissioned it on SQL server and pesto !! The problem is solved.

Posted in Database Mirroring, Management, SQL 2008, SQL High Availability, Tips, WMI | 1 Comment »

Move System model database in SQL 2008 R2 Failover Cluster

Posted by rahmanagoro on April 12, 2011


Move System database on a SQL 2008 R2 Failover Cluster

In order to do this, run the following steps

  • Modify the location of the system databases
  • Open up the failover cluster manager.
  • Run the code below with the SQL resource online

Alter database msdb modify file (name=msdbdata,filename='F:\Database\msdbdata.mdf')

Alter database model modify file (name=modellog,filename='L:\Logs\modellog.ldf')

  • Bring the SQL resource offline

  • Confirmation from the failover cluser manager

  • The resource has now been taken offline


  • Now move the files onto the new destination, i.e the location of where the system data and log file should be.
  • Bring the SQL service and sQL agent resource online.

Posted in Clustering, Management, SQL 2008, SQL Administration, SQL High Availability, Tips | Leave a Comment »

SQL Failover Clustering Requirements

Posted by rahmanagoro on April 12, 2011


When installing a SQL failover cluster, its a complicated process and we DBA’s are always looking for ways to make our lives easier. Also as a SQL failover installation would require cooperation between the following groups of people.

  1. Database Administrators
  2. Windows Administrators
  3. Network Administrators
  4. Storage Administrators.

I find that creating a small little spreadsheet and sending it around for everyone to fill in their details or perhaps state your requirements often helps and avoids delays and ambiguity.

I have put together a spreadsheet with same information as it might be of use to others.

SQL Failover Cluster_Requirements_v1.0

Posted in Clustering, Management, SQL 2008, SQL Administration, SQL High Availability | Leave a Comment »

SQL 2008 Failover Cluster Installation Pre-Staging

Posted by rahmanagoro on April 12, 2011


As a DBA, I can find myself cross between being able to do my job and also some kind of wrangling with system administrators, they seem to believe that anything to do with installations ought to be the work of a system administrator. I do not believe that life has to operate in that fashion. I think DBA’s should be allowed to do what they are very good at and what they were hired for and system administrators should focus on core operating system tasks. I have encountered crossroads which has led to disagreements etc, once I had a system administrator saying that a project would be stalled unless he installed SQL 2008.

Anyway, away from the ranting. I had to install and configure a SQL 2008 R2 failover cluster the other day, and ran into some difficulties when it came to registering the virtual SQL network name in Active Directory, of course I am not a domain administrator. I do not need to be a domain administrator to do my job. On speaking to one of the system administrators, he seemed to think that it was the job of a system administrator to install SQL on a failover cluster and a DBA can sit back and watch. I disagree, I think DBA’s should work with system administrators and work together but yet focus on areas in which they specialise.

I managed to speak to the head of the windows administrators, who agreed to pre stage the windows cluster name for me in Active directory, so that the installation can go ahead. This was a sensible approach to me, after all system administrators have already clustered the underlying windows operating system and DBA’s can take a back seat when it comes to that, SA’s on the other hand should allow DBA’s focus on SQL server which is their area of specialism. The windows clustername and the SQL network name were pre-staged in active directory following the steps below.

The specific error message appearing is as follows

Cluster network name resource ‘SQL Network Name (SQLCLUSTER1)’ failed to create its associated computer object in domain ‘domain.local’ for the following reason: Unable to create computer account.
The text for the associated error code is: Access is denied.

Please work with your domain administrator to ensure that:
– The cluster identity ‘WIN-CLUSTER-1’ can create computer objects. By default all computer objects are created in the ‘Computers’ container; consult the domain administrator if this location has been changed.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘WIN-CLUSTER-1$’ has ‘Full Control’ permission to that computer object
–2
Cluster resource ‘SQL Network Name (SQLCLUSTER1)’ in clustered service or application ‘SQLCLUSTER1-SQL’ failed.

–3
The Cluster service failed to bring clustered service or application ‘SQLCLUSTER1-SQL’ completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered service or application.

To prestage a cluster name account

  1. Make sure that you know the name that the cluster will have, and the name of the user account that will be used by the person who creates the cluster. (Note that you can use that account to perform this procedure.)
  2. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
  3. In the console tree, right-click Computers or the default container in which computer accounts are created in your domain. Computers is located in Active Directory Users and Computers/domain node/Computers.
  4. Click New and then click Computer.
  5. Type the name that will be used for the failover cluster, in other words, the cluster name that will be specified in the Create Cluster wizard, and then click OK.
  6. Right-click the account that you just created, and then click Disable Account. If prompted to confirm your choice, click Yes.

The account must be disabled so that when the Create Cluster wizard is run, it can confirm that the account it will use for the cluster is not currently in use by an existing computer or cluster in the domain.

  1. On the View menu, make sure that Advanced Features is selected.

When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.

  1. Right-click the folder that you right-clicked in step 3 , and then click Properties.
  2. On the Security tab, click Advanced.

10.  Click Add, click Object Types and make sure that Computers is selected, and then click OK. Then, under Enter the object name to select, type the name of the computer account you just created, and then click OK. If a message appears, saying that you are about to add a disabled object, click OK.

11.  In the Permission Entry dialog box, locate the Create Computer objects and Read All Properties permissions, and make sure that the Allow check box is selected for each one.

12.  Click OK until you have returned to the Active Directory Users and Computers snap-in.

13.  If you are using the same account to perform this procedure as will be used to create the cluster, skip the remaining steps. Otherwise, you must configure permissions so that the user account that will be used to create the cluster has full control of the computer account you just created:

  1. On the View menu, make sure that Advanced Features is selected.
  2. Right-click the computer account you just created, and then click Properties.
  3. On the Security tab, click Add. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
  4. Use the Select Users, Computers, or Groups dialog box to specify the user account that will be used when creating the cluster. Then click OK.
  5. Make sure that the user account that you just added is selected, and then, next to Full Control, select the Allow check box.

In our case, we specifically followed the step below.

Steps for prestaging an account for a clustered service or application

It is usually simpler if you do not prestage the computer account for a clustered service or application, but instead allow the account to be created and configured automatically when you run the High Availability wizard. However, if it is necessary to prestage accounts because of requirements in your organization, use the following procedure.

Membership in the Account Operators group, or equivalent, is the minimum required to complete this procedure. Review details about using the appropriate accounts and group memberships at http://go.microsoft.com/fwlink/?LinkId=83477.

To prestage an account for a clustered service or application

  1. Make sure that you know the name of the cluster and the name that the clustered service or application will have.
  2. On a domain controller, click Start, click Administrative Tools, and then click Active Directory Users and Computers. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.
  3. In the console tree, right-click Computers or the default container in which computer accounts are created in your domain. Computers is located in Active Directory Users and Computers/domain node/Computers.
  4. Click New and then click Computer.
  5. Type the name that you will use for the clustered service or application, and then click OK.
  6. On the View menu, make sure that Advanced Features is selected.

When Advanced Features is selected, you can see the Security tab in the properties of accounts (objects) in Active Directory Users and Computers.

  1. Right-click the computer account you just created, and then click Properties.
  2. On the Security tab, click Add.
  3. Click Object Types and make sure that Computers is selected, and then click OK. Then, under Enter the object name to select, type the cluster name account, and then click OK. If a message appears, saying that you are about to add a disabled object, click OK.

10.  Make sure that the cluster name account is selected, and then, next to Full Control, select the Allow check box.

There is an excellent kb article which can be found on the link below.

http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx

 References: http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx

Posted in Clustering, SQL Administration, SQL High Availability | Leave a Comment »

Restoring a database when an application or user is still connected

Posted by rahmanagoro on November 2, 2010


Restoring a database whilst application connections persists to the database. I have a .NET application which continually needs to query the database to fetch data therefore opening a connection to the database, the problem is that I need to refresh the data on testing with recent data from production. If one tries to restore the database, you get the error.

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In order to get around this problem, what I tend to do is set the database offline, do the restore and set it back online. This is to prevent the application from continuously connection to the database thereby failing the restore. The scripts are shown on this post.

Posted in Database development, Scripts, SQL High Availability | Leave a Comment »