Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Archive for the ‘Clustering’ Category

Configuring and Installing SSIS on a SQL 2008 R2 failover cluster infrastructure.

Posted by rahmanagoro on October 23, 2011


SSIS isn’t cluster aware out of the box, so there is some bit of config changes to make in order to make it work on a cluster.

C

Select just the Integration service.

On the Client Access point screen, type in the name and also the IP address as well looking at the requirement spreadsheet.

Click on Next

Since the SSIS service has been installed in its own resource group, we now need to set the dependencies.

Set the disk dependency for the SSIS service

Add the disk resource dependency

Requirement: Change SSIS configuration, custom configuration

SSIS Custom Configuration

Navigate to the path: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

Create a folder on the root of the SSIS cluster disk called SSIS_Config

Create a folder on the SSIS cluster disk called Packages

Copy the file from the location C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml to S:\SSIS_Config\



Right Click the SSIS service and select properties and then go onto Registry replication and add the registry key below.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\


Click on OK and then Apply.

Take the resource offline and bring it back online for the changes to take effect.



Perform the following steps for SSIS on the second node.

Change the MsDTSSrvr.ini.xml file on the location defined above and change the packages folder to point to S:\Packages

Make the registry entry changes on the second node as well to finish off the configuration.

Test the failover by failing over the SSIS resource group by NodeA to NodeB.

Copy a .dtsx file onto the S:\Packages folder and open up management studio.

In the illustration below, you can see that we have connected to the SSIS instance and we can see the packages.

102311_1839_Configuring28


Advertisements

Posted in Clustering, SQL 2008, Tips, Uncategorized | 5 Comments »

Be Careful ! SSAS Analysis services cannot be uninstalled from a SQL cluster once installed

Posted by rahmanagoro on June 6, 2011


Read the rest of this entry »

Posted in Clustering, SQL 2008, Tips | 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 »