Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for April, 2011

SQL 2008 Management Datawarehouse Overview

Posted by rahmanagoro on April 13, 2011


 

With the release of SQL 2008, Microsoft has developed a warehouse framework for database administrators to have an idea of what is actually going in within SQL server. A lot of the times during my career, I have heard the phrase “The database was slow between 3AM and 4AM) This is especially true if you work within a global framework whereby the database server is serving requests 24/7. I would have users in the Asia pacific region using the database from around midnight GMT (UK time), the as they finish the UK users start around 8AM, and as they wrap up the US users kick in around 1PM BST. So it’s a never ending circle.

To buttress the point, management data warehouse gives me the high level and detailed view of what is happening on the database server, I like to use the waits and queues methodology to see what SQL server is waiting for, and to also keep tabs on my queues as well. If a process is queing up waiting for resources, then there must be a reason for it and as a database administrator, I want to check and understand why we have the waits and queues on the database server.

This post assumes that management data warehouse has already been installed on the database server, I am still running SQL 2008 RTM SP1 cumulative update 5 on my systems and till date, there is no way to remove management data warehouse once it has been installed. I installed management data warehouse on the dedicated DBA database on the production system, the database schema gets installed on the database as well.

To launch management data warehouse, follow the steps below.

Management data warehouse works by collecting information from a number of DMV’s and using connection points to upload the data onto the database and then store it within  data warehouse style tables.

 

Memory section

 

If you click on the memory graph, it drills down to show you the key memory counters. Here you can see things like page life expectancy, SQL server internal memory consumption by type

Reviewing waits, within management data warehouse you can check your wait statistics and further drill into what percentage the composition is.

Memory counters based on page life expentancy.

 

 

 

 

 

Disk IO statistics information can also be found below.

On the IO specific information, you can drill down on top queries based on duration, total IO, physical disk reads and also logical writes.

Advertisements

Posted in Management, SQL 2008, Tips, Uncategorized | Leave a 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 »