GAPTHEGURU

Geek with special skills

Use PowerShell to gather SQL Server database physical file sizes

Problem

I am hired as an DBA at a customer and one of the SQL servers has a database that grows very rapidly (1 GB per week). I needed an easy way to monitor the growth of this database. I also needed an easy way to monitor all my servers for growth trending.

Solution

I know there are tools that will monitor database file size, but I wanted something easy and something I could use to gather and retain historical data. It also had to be free. After a little research I decided to try using PowerShell. I did some searching on the internet and found some sample scripts for connecting to servers, getting data from those servers and then writing the data to an Excel spreadsheet.

Prepare the environment

Before we start let me say that I am not a PowerShell guru, so some of the terminology I use may not be exact. The first thing you will need to do is start PowerShell. In Windows 7 click the Start button and type PowerShell in the Search field. When I do this on my system I am presented with a couple choices. The first two items in the list will start PowerShell, Windows PowerShell ISE is a GUI interface and Windows PowerShell is the command line interface. I like to use Windows PowerShell ISE.

Program Selection

The next thing you will need to do to use PowerShell on your system is to change the execution policy. By default the execution policy is set to Restricted, this means that no scripts will run, not any at all. To check what the current execution policy is set to, open PowerShell and enter the command Get-ExecutionPolicy. I have set my execution policy to RemoteSigned, this allows scripts I have written to run and to run scripts from the internet only if those scripts have been signed by a trusted publisher. To set the execution policy enter the command Set-ExecutionPolicy RemoteSigned. Note: there are other options for execution policy, check the documentation for the appropriate policy. Now that I have the execution policy set I am ready to go.

What I wanted to accomplish was to look at all my SQL servers and get the actual data file and log file size in megabytes. The script shown below reads a .txt file that lists all my SQL servers and gets the file name, file path, file size, and used size for both data file (.mdf) and log file (.ldf) for each database on each server in the .txt file. The data is then written to a spreadsheet on my local machine.

#Create a new Excel object using COM 
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True 
$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)
$intRow = 1
 ForEach ($instance in Get-Content "C:\Users\dkelly\Documents\PowershellScripts\sqlservers.txt")
    {
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
    $dbs=$s.Databases

     $intRow++
     #Create column headers
     $Sheet.Cells.Item($intRow,1) = "Server: $s"
     $Sheet.Cells.Item($intRow,1).Font.Size = 12
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True

     $intRow++
     $Sheet.Cells.Item($intRow,2) = "Database"
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True
     $Sheet.Cells.Item($intRow,3) = "Data Name"
     $Sheet.Cells.Item($intRow,3).Font.Bold = $True
     $Sheet.Cells.Item($intRow,4) = "Data File"
     $Sheet.Cells.Item($intRow,4).Font.Bold = $True
     $sheet.Cells.Item($intRow,5) = "Data Size (MB)"
     $Sheet.Cells.Item($intRow,5).Font.Bold = $True
     $Sheet.Cells.Item($intRow,6) = "Data Used Space (MB)"
     $Sheet.Cells.Item($intRow,6).Font.Bold = $True
     $Sheet.Cells.Item($intRow,7) = "Log Name"
     $Sheet.Cells.Item($intRow,7).Font.Bold = $True
     $Sheet.Cells.Item($intRow,8) = "Log Size (MB)"
     $Sheet.Cells.Item($intRow,8).Font.Bold = $True
     $Sheet.Cells.Item($intRow,9) = "Log Used Space (MB)"
     $Sheet.Cells.Item($intRow,9).Font.Bold = $True
     $Sheet.Cells.Item($intRow,10) = "Log File"
     $Sheet.Cells.Item($intRow,10).Font.Bold = $True

    foreach ($db in $dbs) 
        {

          $dbname = $db.Name
          $fileGroups = $db.FileGroups

          ForEach ($fg in $fileGroups)
            {
       #   write-host $fg.files | select name
            If ($fg) 
                {

                    $intRow++

                    $mdfInfo = $fg.Files | Select Name, FileName, size, UsedSpace
                    $Sheet.Cells.Item($intRow,2) = $dbname
                    $Sheet.Cells.Item($intRow,3) = $mdfInfo.Name
                    $Sheet.Cells.Item($intRow,4) = $mdfInfo.FileName
                    $Sheet.Cells.Item($intRow,5) = ($mdfInfo.size / 1000)
                    $Sheet.Cells.Item($intRow,6) = ($mdfInfo.UsedSpace / 1000)

                    $logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace
                    $Sheet.Cells.Item($intRow,7) = $logInfo.Name
                    $Sheet.Cells.Item($intRow,8) = ($logInfo.Size / 1000)
                    $Sheet.Cells.Item($intRow,9) = ($logInfo.UsedSpace / 1000)
                    $Sheet.Cells.Item($intRow,10) = $logInfo.FileName
                }
            }
        }
        $intRow++
    }
    $Sheet.UsedRange.EntireColumn.AutoFit()

The sqlserver.txt file is just a list of server names, one name per line with no punctuation as shown here;

testserver1 testserver2 testserver3

The first four lines in the script create the Excel spreadsheet. Then for each server in the sqlserver.txt file we use the Microsoft.SQLServer.Management.SMO.Server class to create a server object of SQL Server. From the server object we can get the databases, $dbs=$s.Databases, on each server. The next several lines in the script set the values in the columns of the header row in the spreadsheet. The section of the script starting with foreach ($db in $dbs) is the part of the script that actually gets the database file data and writes it to the spreadsheet.

Shown below is the resulting spreadsheet. I broke the spreadsheet image into 3 images for readability.

The first four lines in the script create the Excel spreadsheet
The next several lines in the script set the values in the columns of the header row in the spreadsheet
the resulting spreadsheet

In my next tip I will describe the process for getting the data into a database using SSIS then reporting on that data. Please stayed tuned.

Advertisements

05/23/2012 Posted by | Powershell, Sql Server | , | 2 Comments

SQL Server 2008 R2 Build List

A list of all the builds that I can find and install on my Build VM. If you find a build not listed here, please let me know.

All builds are listed in reverse order, so the newest are at the top and the earliest at the bottom. You can find your build number with:

select @@Version

in a query window. This gives you the current version you are running and it should match up with one of the builds listed below.

Major Builds

Build KB   Article Title Description
10.50.2500.0 Service   Pack 1 Service Pack 1 for SQL Server 2008 R2 The first roll-up of patches into a service pack for SQL Server 2008   R2. Note this IS NOT for SQL Server 2008, only the R2 version.
10.5.1600.1 RTM Release of Manufacturing of SQL Server 2008 R2 The first public, supported version of SQL Server 2008 R2

Note that Service Packs include all builds below that Service Pack level. So any builds listed below Service Pack 1 are included in Service Pack 1. Those below Service Pack 2 (including Service Pack 1), are included in SP2, etc. Hotfixes may or may not include updates below them because there are two trees of code. The cumulative updates do include all hotfixes before them.

You can also download a CSV of the builds attached to this article.

Build Kb   Article URL Description
10.50.2811 Cumulative update package 6 for SQL Server 2008 R2 Service Pack 1 http://support.microsoft.com/kb/2679367/ A total of 20 fixes for this CU that are listed on the support page.
10.50.2806.0 Cumulative update package 5 for SQL Server 2008 R2 Service Pack 1 http://support.microsoft.com/kb/2659694/ Fixed for identity issues, CPU spikes, SSRS, Replication and more.
10.50.2796 Cumulative update   package 4 for SQL Server 2008 R2 Service Pack 1 http://support.microsoft.com/kb/2633146 The fourth update for SP1 includes fixes for replication, deadlocks,   and MDX query issues.
10.50.2789 CU3   for SQL Server 2008 R2 SP1 http://support.microsoft.com/kb/2591748/en-us This article describes cumulative update package 3 for SQL Server   2008 R2 Service Pack 1. This update contains hotfixes for issues that were   fixed after the release of Microsoft SQL Server 2008 R2 Service Pack 1 (SP1).
10.50.2772.0 CU2   for SQL Server 2008 R2 SP1 http://support.microsoft.com/kb/2567714 CU 2 for SQL Server 2008 R2 SP1 includes fixes for T-SQL query results, trace erros with TVPs, data collector network port issues among others.
10.50.2769.0 CU 1   for SQL Server 2008 R2 SP1 http://support.microsoft.com/kb/2544793/en-us   CU 1 for SQL Server 2008 R2 SP1 includes fixes for TDS, timeouts, CDC, and BIDS among others.
10.50.2500.0 Service Pack 1 for SQL Server 2008 R2 http://support.microsoft.com/kb/2528583   SP 1 for SQL Server 2008 R2 RTM includes fixes for Powershell, spatial data types, locking, and multi-CPU parallel queries among others.
10.50.1810 Cumulative   update package 12 for SQL Server 2008 R2 http://support.microsoft.com/kb/2659692/ Three fixes listed in the KB article.
10.50.1809 Cumulative   update package 11 for SQL Server 2008 R2 http://support.microsoft.com/kb/981356 The eleventh update for RTM includes fixes for replication,   deadlocks, and MDX query issues.
10.50.1807 CU10   for SQL Server 2008 R2 http://support.microsoft.com/kb/2591746/en-us CU10 includes fixed for deadlocks, various query issues, CDC   problems, and XML data used in replication.
10.50.1804 CU9   for SQL Server 2008 R2 http://support.microsoft.com/kb/2567713 CU9 includes fixes for SSIS, MDX, XML, and SSRS
10.50.1797.0 Cumulative   Update package 8 for SQL Server 2008 R2 http://support.microsoft.com/kb/2534352   CU 8 for SQL Server 2008 R2 RTM includes fixes for BIDS, replication issues with large   stored procedures, timeouts with snapshot isolation, and SSRS dynamic images among others.
10.50.1777.0 Cumulative   Update package 7 for SQL Server 2008 R2 http://support.microsoft.com/kb/2507770   CU 7 for SQL Server 2008 R2 RTM includes fixes for better performance   for some bcp operations, merge replication issues, SQL Agent job scheduling, and SSRS performance among others.
10.50.1765.0 Cumulative   Update package 6 for SQL Server 2008 R2 http://support.microsoft.com/kb/2489376/en-us   CU 6 for SQL Server 2008 R2 RTM includes fixes for better performance   for some self joins, Filestream with third party filters, and a number of   SSAS filters.
10.50.1753.0 Cumulative   Update package 5 for SQL Server 2008 R2 http://support.microsoft.com/kb/2438347 CU 5 for SQL Server 2008 R2 RTM includes fixes for Reporting   Services, replication with XML columns, and a number of engine fixes for   various items
10.50.1746.0 Cumulative   Update package 4 for SQL Server 2008 R2 http://support.microsoft.com/kb/2345451 CU 4 for SQL Server 2008 R2 RTM includes fixes for TDE and full-text   search with compression enabled.
10.50.1734.0 Cumulative   Update package 3 for SQL Server 2008 R2 http://support.microsoft.com/kb/2261464/ CU 3   for SQL Server 2008 R2 RTM
10.50.1720.0 Cumulative   Update package 2 for SQL Server 2008 R2 http://support.microsoft.com/kb/2072493 CU 2   for SQL Server 2008 R2 RTM
10.50.1702.0 Cumulative   Update package 1 for SQL Server 2008 R2 http://support.microsoft.com/kb/981355/en-us CU 1   for SQL Server 2008 R2 RTM
10.50.1600.1 N/A RTM
10.50.1450.3 N/A RC 0

Again, if you find something not listed, let me know.

 

05/23/2012 Posted by | Sql Server | Leave a comment

How to setup Active Directory Federation Services

Part 1

Active directory federation service is Windows component which enables authentication of users on sites beyond its administrative domain. Example for this type of authentication is when users from one site have to access resources on some external site such as resources in partner network (e.g. Partner web sites etc.) When resource on remote site needs authentication for accessing, but “local” credentials should to be used, that is point where Active Directory Federation Service (AD FS) takes place.

Active Directory Federation Services enable using your AD (Active Directory) service to authenticate its users when they access resources belonging to other domains and placed on remote locations. To enable service which allows this type of authentication Active Directory federation should de established between two remote sites. There should be Active Directory Federation servers placed on both locations.

User authentication on site where resources reside and where user tries to access are based on token issued by federation services server on user location. Next picture shows AD FS architecture:

On user side, where Account Federation server resides is AD controller which authenticates users. On remote location in resources site is Resource Federation Server which participate in user authentication on remote site.

Follow scenario happens when user tries to access recourse on remote side:

1. User send request to access resource

2. Application server(SharePoint on picture) contacts Resource Federation server to authenticate user

3. Resource Federation server claim Account Federation server providing user identities

4. Account Federation server send user identities to Active Directory which authenticates user

5. Account Federation server creates token for user and send it to Resource federation server

6. When receive token for user Receive Federation server creates service token and forward it to resource server (SharePoint) and authentication process is completed.

On described way is provided SSO (single sign on) mechanism for users accessing resources on locations which are out of its administration boundary. Trust between two organizations is established though Active Directory Federation services.

To establish federation between two sites a few steps should be performed:

– Installation AD Federation Services on Account and Resource Federation servers

– Configuration of resource server(web server or other application server to which resources clients access)

– Configuration of Federation Servers(both account and resource) to establish trusted relationships

–  Client configuration

Installation AD Federation Services on Account and Resource Federation servers

AD FS services can be installed as role on Windows Server 2008. To begin installation go to Start->Administrative Tools->Server Manager. Then right click on roles and Add Role. Opens Add roles wizard:

Click on Active Directory Federation Services and then Next. Next window is Role Services. Click on check box by component you want to install (in this case Federation Service):

Maybe you will be prompted to install additional services such as IIS or some components of IIS needed for AD FS working. Confirm installation additional services (click on Add Required Role Services). When installation finishes click Next. Window for choosing SSL certificate for AD FS server appears. This certificate will be used for securing communication between clients and federation server.

As certificate for SSL connection you can choose existing certificate issued by your enterprise CA authority or create self signed certificate created on federation server. In this example self signed certificate will be used. For that click Create a self-signed certificate for SSL encryption and click Next. Opens window for token signing certificate:

Token signing certificate is used for signing tokens issued for client authentication on remote site where are resources which client want to access. When client make request to access resources application server (SharePoint on picture) request Resource Federation server to identify client. Resource Federation Server then contacts Account Federation Server on client side. When request for client authentication comes to Account Federation Server this server contacts AD controller which authenticates client. After client authentication  by AD is finished, Account Federation Server generates token signed by Token-Signing Certificate and sent it to Resource Federation Server which then generates service token and send it to resource server. When this process is completed client can access resources.

After Token-signing certificate is generated click next. Appears window for trust policy generation:

Trust policy defines rules applied when request from partner Federation Server comes to authenticate user. It defines situations in request should be accept or denied and types of information should be included in token issued to Federation Server on the side of partner organization. Trust policy can be created for this purpose or existing can be used. In this example we will create new policy. Click on Create new policy and then Next. Appears window with list of services which will be installed:

In this case AD Federation service and IIS server will be installed.

When verify which services will be installed click Install to begin installation process. After finishing window which shows results of installation is displayed:

With this last step AD FS server role is installed. On the same way this role installs on both Recourse and Account Federation server. After installing AD FC roles servers should be properly configured for trust relationship and communication establishing. Also resource server should be configured to be aware of federation server.

PART 2

When you have Federation Services installed as server roles on both sides of federation (account and resource) you have to properly configure servers to establish trust between them. Configuration includes configuring trust policy on both servers, create and configure group claim and AD account store and establish trust by importing policy from one federation server to another, on partner side. In this article I will describe process of AD FS server configuration. Configuration of both federation servers (account and resource) will be described.

Configuring Federation Services on federation servers

 Trust policy configuration

First thing to configure is trust policy configuration. To do that go through next steps:

1. Open AD FS configuration console. Go to Start->Administrative Tools and click Active Directory Federation Services. Opens next window:

2. On console tree double click Federation Services and then right click on Trust Policy and then Properties:

3. On General tab in Federation Services URI type URI of AD Federation Service. This URI is used to identify federation service on federation server. If AD FS is installed on server farm this URI should be same for all servers in farm. Also, in partner organization this URI should be same in trust policy imported from partner.

4. In the Federation Service endpoint URL text box URL of federation service appear. There is default URL, you can change it.

5. On Display Name tab type trust policy name and click OK.

On the same way trust policy should be configured on both federation servers, with differences in policy names, URLs and AD FS URIs.

Create group claim for claim-aware application

1. For authentication requests from partner side to be handled group claim should be created. To create group claim Go to Start->Administrative Tools and click Active Directory Federation Services

2. On console tree double click Federation Services, double click Trust Policy, double click My Organization, right click Organization Claims, go to New and click Organization Claim

3. In the Create a New Organization Claim dialog box in Claim name type name of new organization claim. It is claim from AD FS service on other (partner) side, let say Partner Claim.

4. Ensure that Group claim is selected and click OK

Claim should be configured on both sides, Account Federation server and Resource Federation Server

Add AD account store

When group claim is created and configured account store should be added. It is store in which are placed user credentials authenticated during resource accessing . In this case Active Directory will be used as account store. AD is most efficient and most used store for users when AD FS services are used.

To add AD as account store do next steps:

1. Go to Start->Administrative Tools and click Active Directory Federation Services

2. On console tree double click Federation Services, double click Trust Policy, double click My Organization, right click Account Stores, go to New and click Account Store:

3. On the Welcome to the Add Account Store Wizard page, click Next.

4. On the Account Store Page Active Directory Domain Services should be selected. Click Next

5. Enable this Account Store page appears. Enable this account store check box should be selected. Click Next

6.  On the Completing the Add Account Store Wizard page, click Finish

Adding account store in configuring AD FS server should be performed on both federation servers (Account and Resource)

Map a global group to the group claim for claims aware application

1. Go to Start->Administrative Tools and click Active Directory Federation Services

2. On console tree double click Federation Services, double click Trust Policy, double click My Organization , double click Account Stores, right click Active Directory, go to New and click Group Claim Extraction.

3. In Create a New Group Claim Extraction dialog box, click Add, type name of your mapping, for example partnerclaimusers, and then click OK.

4. Map to this Organization Claim menu should display group claim for partner organization, in this case Partner Claim we configured earlier. Click OK

Mapping a global group to the group claim should be configuring only on account federation server side because user that accessing resources are on that side, in same domain as account AD FS server. On this side of resource federation server claims aware application should be added and configured.

Adding and configuring claims aware application on AD FS of resource federation server

When configuring account federation server map group claim to group of users in AD is needed. On the other side, on resource federation server mapping of claims aware application should be done to connect application to which clients access with federation service. To add claim aware application do next steps on Resource Federation server:

1. Go to Start->Administrative Tools and click Active Directory Federation Services

2. On console tree double click Federation Services, double click Trust Policy, double click My Organization, right click Application, go to New and click Application.

3. On the Welcome to the Add Application Wizard page, click Next

4. On the Application Type page, click Claims-aware application, and then click Next.

5. On the Application Details page, in Application Display Name, type Claims-aware Application

6. In application URL type URL of your web application to which client access (e.g. http://web.domain.com/application

7. On the Accepted Identity Claims page, click User principal name (UPN), and then click Next.

8. On the Enable this application page check Enable this application and click Next

9. Click finish on Completing the Add Application Wizard page

After adding claims aware application group claim should be added to application. For this go to Applications folder, click to Claims aware application, right click to your group claim and click Enable.

When described settings are done federation servers are configured for federation but for establishing full trust between servers exporting and importing trust policies between servers is needed. When policies are exchanged trust between servers is established and AD FS service is configured between organizations.

05/23/2012 Posted by | Active Directory, ADFS, Federation, Security, SSO, Windows Server | , , | 1 Comment

SSMS Timeout Expired Error When Making Changes To a Table

Problem

I was required to add a new integer type column with an identity property to an existing table through SSMS designer. The operation failed and generated a timeout message. The table was large and I expected it would take some time for this task, but I didn’t think it would have a timeout issue.  In this tip we look at how to set this timeout value as well as look at other timeout values that can be set for SQL Server.

Solution

To simulate the timeout error, let’s create a table with 500,000 rows. We will use this for the examples.

-- Create the table for demo
IF EXISTS (SELECT * FROM sys.objects where name = 'TestingTimeOut')
DROP TABLE TestingTimeOut
GO
create table TestingTimeOut (EmpName varchar(75), Designation varchar(50), Department varchar(50))
GO

-- Populate the table with 500000 rows 
-- Multiple executions by providing number with GO is not ANSI standard 
-- Should not be used in production environment
INSERT INTO TestingTimeOut VALUES ('Atif Shehzad', 'DBA', 'Human Resource')
GO 500000

Transaction timeout for SSMS Designer operates only when SQL Server object modification is performed through the SSMS Designer. It is not a server level parameter and may be viewed or modified in the ‘Designers’ link in the Options menu of SSMS. Its value can be different for each SSMS client and is not stored as a server level configuration.

To check or change this setting, within SSMS go to Tools > Options and click on the Designers link in the left panel as shown below.

Time-out for SSMS designers

The default value is 30 seconds, but for the timeout simulation let’s change it to 1 second and save the changes.

In SSMS, find the table that we just created, right click and select Desgin.  Add an integer type column EmpID as a primary key with an identity property and try to save the modifications. Since we changed the timeout to 1 second, this should cause a timeout error as shown below. If the error is not generated you may need to increase the number of rows in the demo table and try again. (Note if you get this error “Saving changes is not permitted” then check out this tip.)

ssms designer timeout designer expired for operation

This is the T-SQL that SSMS generates and is trying to execute.  We can see here that it is just doing an ALTER TABLE with the ADD option.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TestingTimeOut ADD
 EmpID int NOT NULL IDENTITY (1, 1)
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

If we try to add the EmpID as the first column, as shown below we can see that SSMS generates a pre-save warning that this will take some time. .

This is the T-SQL that SSMS generates and we can see that this is a much more intensive script that drops and recreates the table.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_TestingTimeOut
 (
 EmpID int NOT NULL IDENTITY (1, 1),
 EmpName varchar(75) NULL,
 Designation varchar(50) NULL,
 Department varchar(50) NULL
 )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_TestingTimeOut SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_TestingTimeOut OFF
GO
IF EXISTS(SELECT * FROM dbo.TestingTimeOut)
  EXEC('INSERT INTO dbo.Tmp_TestingTimeOut (EmpName, Designation, Department)
  SELECT EmpName, Designation, Department FROM dbo.TestingTimeOut WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.TestingTimeOut
GO
EXECUTE sp_rename N'dbo.Tmp_TestingTimeOut', N'TestingTimeOut', 'OBJECT' 
GO
ALTER TABLE dbo.TestingTimeOut ADD CONSTRAINT
 PK_TestingTimeOut PRIMARY KEY CLUSTERED 
 (
 EmpID
 ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT

Summary

So if you face these timeout issues when using SSMS to make changes you now know the exact parameter to manipulate. The solution to this problem is to increase the timeout limit for Designer. The specified limit would be implemented for any sever connected via that SSMS connection.

05/23/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , , | Leave a comment

SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem

Will SQL Server database mirroring stop working if the user used to create the endpoints is removed?  In this tip I will walk through the steps to find out.

Solution

Before I am able to provide with a definitive answer to the above problem, I created the following test environment.

  • A Windows 2008 VM (Virtual Server) named SQLTEST1 with SQL 2008 Developer installed
  • A Windows 2008 VM (Virtual Server) named SQLTEST2 with SQL 2008 Developer installed
  • Since these servers are not in a domain I created a Windows user WinUser on both VMs and used this account to setup mirroring.
  • I created a test database called mytest and mirrored it between the two SQL Servers SQLTEST1 and SQLTEST2.

Check Mirroring Status

Let’s start Database Mirroring Monitor to verify that database mirroring is working. Below we can see that the databases are synchronized.

use database mirroring monitor to check status

Drop SQL Server Login

Now, let’s try to drop SQLTEST1\WinUser login by executing the following. As expected, the SQL login cannot be dropped because it owns the Mirroring endpoint. Since this failed I did not bother to try this on SQLTEST2.

tsql code to drop sql server login

Drop Windows User

Now let’s try to drop the Windows user WinUser on both servers.  This was successful on both servers.

steps to delete a windows login

Check Mirroring Status

Now that WinUser has been deleted on both servers, let’s check if database mirroring is still working.  With a bit of surprise, database Mirroring is still working. To further verify it, I created tables and inserted data on the mytest database on the principle server SQLTEST1 and verified that such information was successfully replicated to the mirroring server SQLTEST2.

use database mirroring monitor to check status

Restart SQL Server

Now, let’s restart the SQL Server service on both servers.

restart sql server services

Check Mirroring Status

If we check the mirroring status again we can see that it has failed.

use database mirroring monitor to check status

Conclusion

In order to avoid a possible mirroring outage it is appropriate to verify that a Windows user does not own mirroring endpoints, because if the user is removed from Active Directory or a local group, mirroring will break the next time the server is restarted.  If you do use a Windows user you need to make sure that your accounts are well documented and not accidently deleted.

05/23/2012 Posted by | SQL Scripting, Sql Server, T-SQL | , | Leave a comment

How to verify MPIO setup on the iSCSI Initiator

To verify all the disks have two paths, I opened the iSCSI Initiator control panel applet, and checked the device path:

clip_image003

As you can see each disk listed in the Devices pane had 2 paths associated with it, as well as the MPIO policy. You can change the policy by click the dropdown box on the Device details page.

There is also a report you can generate by:

  • Open Microsoft iSCSI Initiator, and then click the Configuration tab.
  • Click Report.
  • Enter the file name, and then click Save.

My report file looks like:

iSCSI Initiator Report
=======================
List of Discovered Targets, Sessions and devices
==================================================
Target #0
========
Target name = iqn.1991-05.com.microsoft:svr1-target
Session Details
===============
Session #1 <= first session to the target
===========
Number of Connections = 1
Connection #1
==============
Target Address = 10.10.0.51
Target Port = 3260
#0. Disk 2
========
Address:Port 3: Bus 0: Target 0: LUN 0
#1. Disk 4
========
Address:Port 3: Bus 0: Target 0: LUN 1
#2. Disk 5
========
Address:Port 3: Bus 0: Target 0: LUN 2
Session #2 <= second session to the target
===========
Number of Connections = 1
Connection #1
==============
Target Address = 10.10.0.51
Target Port = 3260
#0. Disk 2
========
Address:Port 3: Bus 0: Target 1: LUN 0
#1. Disk 4
========
Address:Port 3: Bus 0: Target 1: LUN 1
#2. Disk 5
========
Address:Port 3: Bus 0: Target 1: LUN 2

How to verify MPIO setup on the iSCSI Target

To view the session/connection information on the Target server, you need to use WMI. The easiest way to execute WMI queries is the WMIC.exe in the commandline window.

C:\>wmic /namespace:\\root\wmi Path WT_HOST where (hostname = “T2”) get /format:list

Where T2 is my target object name.

A sample output is listed below with minor formatting changes. Comments have been added to help understand the output and a prefixed with “<=”:

instance of WT_Host
{
    CHAPSecret = "";
    CHAPUserName = "";
    Description = "";
    Enable = TRUE;
    EnableCHAP = FALSE;
    EnableReverseCHAP = FALSE;
    EnforceIdleTimeoutDetection = TRUE;
    HostName = "T2";
    LastLogIn = "20110502094448.082000-420";
    NumRecvBuffers = 10;
    ResourceGroup = "";
    ResourceName = "";
    ResourceState = -1;
    ReverseCHAPSecret = "";
    ReverseCHAPUserName = "";
    Sessions = {
instance of WT_Session    <= First session information from initiator 10.10.2.77
{
    Connections = {
instance of WT_Connection    <= First connection information from initiator 10.10.2.77, since the iSCSI Target supports only one connection per session, you will see each session contains one connection.
{
    CID = 1;
    DataDigestEnabled = FALSE;
    HeaderDigestEnabled = FALSE;
    InitiatorIPAddress = "10.10.2.77";
    InitiatorPort = 63042;
    TargetIPAddress = "10.10.2.73";
    TargetPort = 3260;
    TSIH = 5;
}};
    HostName = "T2";
    InitiatorIQN = "iqn.1991-05.com.microsoft:svr.contoso.com";
    ISID = "1100434440256";
    SessionType = 1;
    TSIH = 5;
}, 
instance of WT_Session    <=  Second session information from initiator 10.10.2.77 (multiple sessions from the same initiator as above
{
    Connections = {
instance of WT_Connection
{
    CID = 1;
    DataDigestEnabled = FALSE;
    HeaderDigestEnabled = FALSE;
    InitiatorIPAddress = "10.10.2.77";
    InitiatorPort = 63043;
    TargetIPAddress = "10.10.2.73";
    TargetPort = 3260;
    TSIH = 6;
}};
    HostName = "T2";
    InitiatorIQN = "iqn.1991-05.com.microsoft:svr.contoso.com";
    ISID = "3299457695808";
    SessionType = 1;
    TSIH = 6;
}, 
instance of WT_Session    <= First session information from initiator 10.10.2.69
{
    Connections = {
instance of WT_Connection
{
    CID = 1;
    DataDigestEnabled = FALSE;
    HeaderDigestEnabled = FALSE;
    InitiatorIPAddress = "10.10.2.69";
    InitiatorPort = 60063;
    TargetIPAddress = "10.10.2.73";
    TargetPort = 3260;
    TSIH = 10;
}};
    HostName = "T2";
    InitiatorIQN = "iqn.1991-05.com.microsoft:svr2.contoso.com";
    ISID = "2199946068032";
    SessionType = 1;
    TSIH = 10;
}, 
instance of WT_Session    <= Second session information from initiator 10.10.2.69

{
    Connections = {
instance of WT_Connection
{
    CID = 1;
    DataDigestEnabled = FALSE;
    HeaderDigestEnabled = FALSE;
    InitiatorIPAddress = "10.10.2.69";
    InitiatorPort = 60062;
    TargetIPAddress = "10.10.2.73";
    TargetPort = 3260;
    TSIH = 11;
}};
    HostName = "T2";
    InitiatorIQN = "iqn.1991-05.com.microsoft:svr2.contoso.com";
    ISID = "922812480";
    SessionType = 1;
    TSIH = 11;
}};
    Status = 1;
    TargetFirstBurstLength = 65536;
    TargetIQN = "iqn.1991-05.com.microsoft:cluster-yan03-t2-target";
    TargetMaxBurstLength = 262144;
    TargetMaxRecvDataSegmentLength = 65536;
};

As you can see in the above session information, each node (as the iSCSI initiator) has connected to the target with 2 sessions. You may have also noticed both sessions are using the same network path. This is because, when you configure iSCSI initiator, by default, it will pick the connection path for you. In the case of one path failure, another path will be used for the session reconnection. This configuration is easy to setup, and you don’t need to worry about the IP address assignment. It is good for failover MPIO policy.

image

If you want to use specific network paths, or want to use both network paths, you will need to specify the settings when you connect the initiators. You can do this by going to the “Advanced” setting page.

clip_image002

This configuration allows you to use specific IPs, and can utilize multiple paths at the same time with different MPIO load balancing policies.

A word of caution on using the specific IP for Initiator and Target, if you are using DHCP in the environment, and if the IP address changes after the reboot, the initiator may not be able to reconnect. From the initiator UI, you will see the initiator is trying to “Reconnect” to the target after reboot. You will need to reconfigure the connection to get it out of this state:

  1. Remove the iSCSI Target Portal
  2. Add the iSCSI Target Portal back
  3. Connect to the discovered iSCSI Targets

05/10/2012 Posted by | Clustering, iSCSI, MPIO | Leave a comment

Step-by-Step: Configuring Windows Server 8 Beta iSCSI Target Software for Use in a Cluster

If you just download the bits for Windows Server 8 Beta and you are anxious to try out all the great new features including Windows Storage Spaces, Continuously Available Fail Servers and Hyper-V Availability. Many of those new features are going to require you become familiar with Windows Server Failover Clustering. In addition, things like Storage Spaces are going require that you have access to additional storage to simulate JBODS. Windows iSCSI Target Software is a great way for you to provide storage for Failover Clustering and Spaces in a lab environment so you can play around with these new features.

This Step-by-Step Article assumes you have three Windows Server 8 servers running in a domain environment. My lab environment consists of the following:

Hardware
My three servers are all virtual machines running on VMware Workstation 8 on top of my Windows 7 laptop with 16 GB of RAM. See my article on how to install Windows Server 8 on VMware Workstation 8.

Server Names and Roles
PRIMARY.win8.local – my cluster node 1
SECONDARY.win8.local – my cluster node 2
WIN-EHVIK0RFBIU.win8.local – my domain controller (guess who forgot to rename his DC before I promoted it to be a Domain ControllerJ)

Network
192.168.37.X/24 – my public network also used to carry iSCSI traffic
10.X.X.X /8– a private network defined just between PRIMARY and SECONDARY for cluster communication

This article is going to walk you through step-by-step on how to do the following:

The article consist mostly of screen shots, but I also add notes where needed.

Install the iSCSI Target Role on your Domain Controller

Click on Add roles and features to install the iSCSI target role.

You will find that the iSCSI target role is a feature that is found under File And Storage Servers/File Services. Just select iSCSI Target Server and click Next to begin the installation of the iSCSI Target Server role.

Configure the iSCSI Target

The iSCSI target software is managed under File and Storage Services on the Server Manager Dashboard, click on that to continue

The first step in creating an iSCSI target is to create an iSCSI Virtual Disk. Click on Launch the New Virtual Disk wizard to create a virtual disk.

Connect to the iSCSI Target using the iSCSI Initiator

Format the iSCSI Target

Connect to the shared iSCSI Target from the SECONDARY Server

Configure Windows Server 8 Failover Clustering

05/10/2012 Posted by | Cluster Configuration, Clustering, iSCSI, Windows Server | , , | 2 Comments

Step-by-Step: Configuring a 2-node multi-site cluster on Windows Server 2008 R2 – Part 1

Creating your cluster and configuring the quorum: Node and File Share Majority

Introduction

Welcome to Part 1 of my series “Step-by-Step: Configuring a 2-node multi-site cluster on Windows Server 2008 R2″. Before we jump right in to the details, let’s take a moment to discuss what exactly a multi-site cluster is and why I would want to implement one. Microsoft has a great webpage and white paper that you will want to download to get you all of the details, so I won’t repeat everything here. But basically a multi-site cluster is a disaster recovery solution and a high availability solution all rolled into one. A multi-site cluster gives you the highest recovery point objective (RTO) and recovery time objective (RTO) available for your critical applications. With the introduction of Windows Server 2008 failover clustering a multi-site cluster has become much more feasible with the introduction of cross subnet failover and support for high latency network communications.

I mentioned “cross-subnet failover” as a great new feature of Windows Server 2008 Failover Clustering, and it is a great new feature. However, SQL Server has not yet embraced this functionality, which means you will still be required to span your subnet across sites in a SQL Server multi-site cluster. As of Tech-Ed 2009, the SQL Server team reported that they plan on supporting this feature, but they say it will come sometime after SQL Server 2008 R2 is released. For the foreseeable future you will be stuck with spanning your subnet across sites in a SQL Server multi-site cluster. There are a few other network related issues that you need to consider as well, such as redundant communication paths, bandwidth and file share witness placement.

Network Considerations

All Microsoft failover clusters must have redundant network communication paths. This ensures that a failure of any one communication path will not result in a false failover and ensures that your cluster remains highly available. A multi-site cluster has this requirement as well, so you will want to plan your network with that in mind. There are generally two things that will have to travel between nodes: replication traffic and cluster heartbeats. In addition to that, you will also need to consider client connectivity and cluster management activity. You will want to be sure that whatever networks you have in place, you are not overwhelming the network or you will have unreliable behavior. Your replication traffic will most likely require the greatest amount of bandwidth; you will need to work with your replication vendor to determine how much bandwidth is required.

With your redundant communication paths in place, the last thing you need to consider is your quorum model. For a 2-node multi-site cluster configuration, the Microsoft recommended configuration is a Node and File Share Majority quorum. For a detailed description of the quorum types, have a look at this article.

The most common cause of confusion with the Node and File Share Majority quorum is the placement of the File Share Witness. Where should I put the server that is hosting the file share? Let’s look at the options.

Option 1 – place the file share in the primary site.

This is certainly a valid option for disaster recovery, but not so much for high availability. If the entire site fails (including the Primary node and the file share witness) the Secondary node in the secondary site will not come into service automatically, you will need to force the quorum online manually. This is because it will be the only remaining vote in the cluster. One out of three does not make a majority! Now if you can live with a manual step being involved for recovery in the event of a disaster, then this configuration may be OK for you.

Option 2 – place the file share in the secondary site.

This is not such a good idea. Although it solves the problem of automatic recovery in the event of a complete site loss, it exposes you to the risk of a false failover. Consider this…what happens if your secondary site goes down? In this case, your primary server (Node1) will go also go offline as it is now only a single node in the primary site and will no longer have a node majority. I can see no good reason to implement this configuration as there is too much risk involved.

Option 3 – place the file share witness in a 3rd geographic location

This is the preferred configuration as it allows for automatic failover in the event of a complete site loss and eliminates any the possibility of a failure of the secondary site causing the primary node to go offline. By having a 3rd site host the file share witness you have eliminated any one site as a single point of failure, so now the cluster will act as you expect and automatic failover in the event of a site loss is possible. Identifying a 3rd geographic location can be challenging for some companies, but with the advent of cloud based utility computing it is well within the reach of all companies to put a file share witness in the clouds and have the resiliency required for effective multi-site clusters. In fact, you may consider the cloud itself as your secondary data center and just failover to the cloud in the event of a disaster. I think the possibilities of cloud based computing and disaster recovery configurations are extremely enticing and in fact I plan on doing a whole blog post on a just that in the near future.

Configure the Cluster

Now that we have the basics in place, let’s get started with the actual configuration of the cluster. You will want to add the Failover Clustering feature to both nodes of your cluster. For simplicity sake, I’ve called my nodes PRIMARY and SECONDARY. This is accomplished very easily through the Add Features Wizard as shown below.

Figure 1 – Add the Failover Clustering Role

Next you will want to have a look at your network connections. It is best if you rename the connections on each of your servers to reflect the network that they represent. This will make things easier to remember later.

Figure 2- Change the names of your network connections

You will also want to go into the Advanced Settings of your Network Connections (hit Alt to see Advanced Settings menu) of each server and make sure the Public network is first in the list.

Figure 3- Make sure your public network is first

Your private network should only contain an IP address and Subnet mask. No Default Gateway or DNS servers should be defined. Your nodes need to be able to communicate across this network, so make sure the servers can communicate across this network; add static routes if necessary.

Figure 4 – Private network settings

Once you have your network configured, you are ready to build your cluster. The first step is to “Validate a Configuration”. Open up the Failover Cluster Manager and click on Validate a Configuration.

Figure 5 – Validate a Configuration

The Validation Wizard launches and presents you the first screen as shown below. Add the two servers in your cluster and click Next to continue.

Figure 6 – Add the cluster nodes

A multi-site cluster does not need to pass the storage validation (see Microsoft article). Toskip the storage validation process,click on “Run only the tests I select” and click Continue.

Figure 7 – Select “Run only tests I select”

In the test selection screen, unselect Storage and click Next

Figure 8 – Unselect the Storage test

You will be presented with the following confirmation screen. Click Next to continue.

Figure 9 – Confirm your selection

If you have done everything right, you should see a summary page that looks like the following. Notice that the yellow exclamation point indicates that not all of the tests were run. This is to be expected in a multi-site cluster because the storage tests are skipped. As long as everything else checks out OK, you can proceed. If the report indicates any other errors, fix the problem, re-run the tests, and continue.

Figure 10 – View the validation report

You are now ready to create your cluster. In the Failover Cluster Manager, click on Create a Cluster.

Figure 11 – Create your cluster

The next step asks whether or not you want to validate your cluster. Since you have already done this you can skip this step. Note this will pose a little bit of a problem later on if installing SQL as it will require that the cluster has passed validation before proceeding. When we get to that point I will show you how to by-pass this check via a command line option in the SQL Server setup. For now, choose No and Next.

Figure 12 – Skip the validation test

The next step is that you must create a name for this cluster and IP for administering this cluster. This will be the name that you will use to administer the cluster, not the name of the SQL cluster resource which you will create later. Enter a unique name and IP address and click Next.

Note: This is also the computer name that will need permission to the File Share Witness as described later in this document.

Figure 13 – Choose a unique name and IP address

Confirm your choices and click Next.

Figure 14 – Confirm your choices

Congratulation, if you have done everything right you will see the following Summary page. Notice the yellow exclamation point; obviously something is not perfect. Click on View Report to find out what the problem may be.

Figure 15 – View the report to find out what the warning is all about

If you view the report, you should see a few lines that look like this.

Figure 16 – Error report

Don’t fret; this is to be expected in a multi-site cluster. Remember we said earlier that we will be implementing a Node and File Share Majority quorum. We will change the quorum type from the current Node Majority Cluster (not a good idea in a two node cluster) to a Node and File Share Majority quorum.

Implementing a Node and File Share Majority quorum

First, we need to identify the server that will hold our File Share witness. Remember, as we discussed earlier, this File Share witness should be located in a 3rd location, accessible by both nodes of the cluster. Once you have identified the server, share a folder as you normally would share a folder. In my case, I create a share called MYCLUSTER on a server named DEMODC.

The key thing to remember about this share is that you must give the cluster computer name read/write permissions to the share at both the Share level and NTFS level permissions. If you recall back at Figure 13, I created my cluster and gave it the name “MYCLUSTER”. You will need to make sure you give the cluster computer account read/write permissions as shown in the following screen shots.

Figure 17 – Make sure you search for Computers

Figure 18 – Give the cluster computer account NTFS permissions

Figure 19 – Give the cluster computer account share level permissions

Now with the shared folder in place and the appropriate permissions assigned, you are ready to change your quorum type. From Failover Cluster Manager, right-click on your cluster, choose More Actions and Configure Cluster Quorum Settings.

Figure 20 – Change your quorum type

On the next screen choose Node and File Share Majority and click Next.

Figure 21 – Choose Node and File Share Majority

In this screen, enter the path to the file share you previously created and click Next.

Figure 22 – Choose your file share witness

Confirm that the information is correct and click Next.

Figure 23 – Click Next to confirm your quorum change to Node and File Share Majority

Assuming you did everything right, you should see the following Summary page.

Figure 24 – A successful quorum change

Now when you view your cluster, the Quorum Configuration should say “Node and File Share Majority” as shown below.

Figure 25 – You now have a Node and File Share Majority quorum

The steps I have outlined up until this point apply to any multi-site cluster, whether it is a SQL, Exchange, File Server or other type of failover cluster. The next step in creating a multi-site cluster involves integrating your storage and replication solution into the failover cluster. This step will vary from depending upon your replication solution, so you really need to be in close contact with your replication vendor to get it right.

Other parts of this series will describe in detail how to install SQL, File Servers and Hyper-V in multi-site clusters. I will also have a post on considerations for multi-node clusters of three or more nodes.

05/10/2012 Posted by | Cluster Configuration, Clustering | , , | Leave a comment

Windows Server 2008 and 2008R2 Failover Cluster Startup Switches

I am here today to discuss the troubleshooting switches used to start a Windows 2008 and 2008 R2 Failover Cluster. From time to time, the Failover Cluster Service will not start on its own. You need to start it with a diagnostic switch for troubleshooting purposes and/or to get it back to production.

In Windows 2003 Server Cluster, we had the following switches:

image

More detailed information on the above switches can be found in KB258078. However, the above switches have changed for Windows 2008 and 2008R2 Failover Clusters. The only switch that is available for Windows Server 2008 Failover Cluster is the FORCEQUORUM (or FQ for abbreviation) switch. The behavior differs from the FORCEQUORUM (or FO abbreviation) that was used previously in Windows Server 2003.

So for our example, let’s say we a 2-node Failover Cluster that is set for Node and Disk Majority. That means that we have a total of three votes. To achieve “quorum”, it needs a majority of votes (two) for fully bring all resources online and make it available to users.

In Windows 2008 Failover Cluster, when you tell the Cluster Service to start, it just immediately starts. The next thing it does is send out notifications to all the nodes that it wants to join a Cluster. It is also going to calculate the number of votes needed to achieve “quorum”. As long as there is another node running or it can bring the Witness Disk online, it will join and merrily go on its way. If there is not another node up and it cannot bring the Witness Disk online, the Cluster Service will start; however, it will be in a “joining” type mode. This means it will be sitting idle waiting for another node to join and achieve “quorum”. If this is the case, you would see something like this:

image

As discussed, we need at least 2 votes to achieve “quorum”. We currently have one node up, so we have one vote. The other node is down and the Witness Disk is unavailable which would account for the other two votes. But you can see that the Cluster Service itself is started. The reason it stays started is that is sitting there just listening for another node to join and give it a majority. Once it does, the Cluster resources will be made available for everyone to use. If you were to run the command to get the state of the nodes, you would see this:

image

This is where the FORCEQUORUM switch comes into play. When using this, it will force the Cluster to become available even though there is no “quorum”. There are multiple ways of forcing the Cluster Service to start. However, please keep in mind that there are some implications when running this. The implications are explained in this article.

1.  Go into Service Control Manager and start the Cluster Service with /FORCEQUORUM (or /FQ)
2.  Go to an Administrative Command Prompt and use:
          a.  net start clussvc /forcequorum
b.  net start clussvc /fq

3.  In Failover Cluster Management, highlight the name of the Cluster in the left pane, and
on the far right pane in the Actions column, there is a FORCE CLUSTER START option that
you can select shown below.

image

This switch differs from Windows 2003. When you use it on Windows 2003 Server Clusters, you must also specify all other nodes that will be joining while in this state. If I was to just use the commands above and not specify the additional nodes, the other nodes will not be allowed to join the Cluster. I would need to basically fix the problem of the other nodes not being up, then stop the Cluster Service and start it again without the switch. This causes downtime and no one wants that. In Windows 2008 Failover Cluster, the switch will remain in effect until “quorum” is achieved. All you would need to do is start the other node Cluster Service and it will join. Once “quorum” is achieved, mode of the Cluster dynamically changes.

In Windows Server 2008 R2 Failover Cluster, there is the same FORCEQUORUM (or FQ) switch as well as a new switch.

This new switch is /IPS or /IgnorePersistentState. This switch is a little different in what it does. What this switch does is to start the Cluster Service as well as make the resources available; but, all groups and resources will be in an offline state.

Under normal circumstances, when the Cluster Service starts, the default behavior is to bring all the resources online. What this switch does is ignore the current PersistentState value of the resources and leave everything offline. When you go into Failover Cluster Management and look at the groups, you will see all resources offline.

image

I do need to bring up a couple of important notes about this switch.

1. The Cluster Group will still be brought online. This switch will only affect the Services
and Applications groups that you have in the Cluster.

2. You must still be able to achieve “quorum.” In the case of a Node and Disk Majority,
the Witness Disk must still be able to come online.

This switch is not one that would be used that often, but when you need it, it is a blessing. Here are a couple of scenarios where the /IPS switch would come in handy.

SCENARIO 1

I have a Failover Cluster that held the limit of 1000 Hyper-V Virtual Machines. If you are trying to troubleshoot an issue, you can use the switch and then manually bring online only a couple of them. Do whatever troubleshooting you need to accomplish without the stress that all these machines coming online would put on the node. Once your troubleshooting is complete, you can then start the other nodes, bring the other virtual machines online, go about your business, etc.

SCENARIO 2

I am the administrator of the Failover Cluster and get called that my Cluster node that holds the John’s Cluster Application resource is in a pseudo hung state. Both Explorer and Failover Cluster Management hang up while the rest of the machine is real slow. If I try and move this group over to another node, that node experiences the same problems and errors. So I reboot them and when the Cluster Service starts, the machine goes into this pseudo hung state again. Looking through the event logs, I see that the Cluster Service starts fine. But I do see that John’s Cluster Application is throwing errors in the event log and those were the last things listed. I do some research on the errors and see that it is caused by a log file this application uses as being corrupt. All I have to do is delete this file and the application will dynamically recreate the file, start fine, and no longer hang the machine. That seems simple enough. But wait, I do not have access to the Clustered Drive that this application is on as Explorer hangs and I also cannot get to it from a command prompt.

In the days before Windows 2008 R2 Failover Cluster, I would have to:

  • Power off all other nodes.
  • Set the Cluster Service to MANUAL or DISABLED
  • Disable the Cluster Disk Driver
  • Reboot this machine
  • Delete the file
  • Re-enable the Cluster Disk Driver
  • Set the Cluster Service to AUTOMATIC and start it
  • Power up all other nodes

The above was the only way I was going to be able to get access to the drives. Something like this can be painful and time consuming. If the nodes take about 15 minutes to boot because of the devices and the memory, it just adds to the frustrations.

This is where the /IPS Switch comes in. Your steps would now be:

  • Stop the Cluster Service on all other nodes
  • Reboot this one node since it is hung
  • While that node is rebooting, on the other node, start the Cluster Service with the IPS Switch:

Net start clussvc /ips

  • Go to the group that has the disk
  • Bring the disk online
  • Delete the file
  • Bring the rest of the group online

For those who like to see stuff on MSDN, you can get a little more information on the /IPS switch here.

So as a recap, these are the only switches available for Windows Server 2008 and 2008 R2 Failover Clusters.

image

The switches can make things easier, less frustrating, and causes less downtime. This can mean production/dollars lost are more at a minimum and that makes everyone happy.

05/10/2012 Posted by | Cluster Configuration, Clustering, Windows Server | | Leave a comment

Configuring iSCSI MPIO on Windows Server 2008 R2

I have recently gone through the process of wiping out one of my lab environment and rebuilding it from scratch on Windows Server 2008 R2 Enterprise.  During this process, I recorded the steps I used to configure MPIO with the iSCSI initiator in R2.  Just to make life more complex, my servers only have 2 NICs, so I am balancing the host traffic, virtual machine traffic, and MPIO across those two NIC devices.  Is this supported?  I seriously doubt it.  🙂  In the real world you would separate out iSCSI traffic on dedicated NICs, cables, and separate switch paths.  The following step-by-step process should be relatively the same though.

Foundation

The workflow I am following assumes that when starting out one NIC is configured for host traffic and the other for a VM network.  On the WSS the secondary NIC was already configured not to register in DNS.  Also, since I am using WSS and the built-in iSCSI Target I don’t have to configure a DSM for the storage device.  If your configuration is different than that, you may have to ignore or add to a few parts of the below instructions.  Sorry about that.  I can only document what I have available for testing…

First I just want to show a screenshot of the iSCSI target on our Windows Storage Server, to indicate that it does have two IPs.  Once again, I am cheating the system here.  These are not dedicated TOE adapters for iSCSI on a separate network.  This is a poor man’s environment with 1 VLAN and minimal network hardware.  My highly available environment is anything but!  To view this information on your own WSS, right-click on the words “Microsoft iSCSI Software Target” and click Properties.

image

Enable the MPIO Feature on the initiating servers

Next I needed to enable MPIO on the servers making the iSCSI connections.  MPIO is a Feature in Server 2008 R2 listed as Multipath I/O.  Adding the Feature did not require a reboot on any of my servers.

image

Configuring MPIO to work with iSCSI was simple.  Click Start and type “MPIO”, launch the control panel applet, and you should see the window below.  Click on the Discover Multi-Paths tab, check the box for “Add support for iSCSI devices”, and click Add.  You should immediately be prompted to reboot.  This was consistent across 4 servers where I followed this process.

image

image

After rebooting, if you open the MPIO Control Panel applet again, you should see the iSCSI bus listed as a device.  Note on my servers, the Discover Multi-Paths page becomes grayed out.

image image

Check the IP of the existing connection path

Now click Start and type “iSCSI”.  Launch the iSCSI Initiator applet.  Add your iSNS server or Target portal.  There is plenty of documentation on how to do this on TechNet if you need assistance. I want to stay focused on the MPIO configuration.

Once you are connected to the target, click the button labeled “Devices…”.  You should see each of the volumes you have connected listed in the top pane.  Select a Disk and click the MPIO button.  In the Device Details pane you should see information on the current path and session.  If you click the Details button, you can verify the local and remote IPs the current connection is using.  It should be the IPs that resolve from the hostnames of each server.  See my remedial diagram below.

I recommend taking note of this IP, to make life easier later on!

image

So everything is setup for MPIO but you are only using a single path and that’s not really going to accomplish much now is it?  Since I only have 2 NICs in my test server I need my host to share the second NIC with the VM network.  This is not ideal but again I am using what I have and this is only a test box.

Setting a second IP on my hosts

In R2 the host does not communicate by default on a NIC where a virtual network is assigned.  To change this, open the Hyper-V console and click “Virtual Network Manager…”.  Check the box “Allow management operating system to share this network adapter”.

image

This will create a third device in the network console (to get there click Start, type “ncpa.cpl”, and launch the applet).  You should see the name of the new device matches your Virtual Network name.  In my case Local Area Connection 4 has a device name “External1”.  Right click on the connection and then click Properties.  Select “Internet Protocal Version 4 (TCP/IPv4)” and click the Properties button.  Configure your address and subnet but not the gateway as it should already be assigned on the first adapter.  You also shouldn’t need to set the DNS addresses in the new adapter.  You will however, want to click the “Advanced…” button followed by the DNS tab and uncheck the box next to “Register this connection’s address in DNS”.  This really should be the job of your primary adapter, no need to have multiple addresses for the same hostname registering and causing confusion unless you have a unique demand for it.

image

Add a second path

Back in the iSCSI Initiator Applet, click the Connect button.  I know you already have a connection.  In this step we are adding an additional connection to the Target to provide a second path.

In the subsequent dialogue make sure you check the box next to “Enable multi-path” and then click the Advanced… button.  In the Advanced Settings dialogue you will need to choose the IP for your second path.  In the drop-down menu next to “Local adapter:” select Microsoft iSCSI Initiator”.  In the drop-down next to “Initiator IP:” select the IP on your local server you would like the Initiator to use when making a connection for the secondary path.  In the third drop-down, next to “Target portal IP:” select the IP of the iSCSI Target server you would like to connect to.  This should be the opposite IP of the session we observed a few steps back when I mentioned you should take note of the IP.

image

Check your work

Just one more step.  Let’s verify that you now have 2 connections available for each disk, that they are using separate paths, and have the opportunity to choose the types of load balancing available.  Once you have hit OK out of each of the open dialogues from the step above, click on the Devices… button again and check out the top pane.  On each of my servers I see each disk listed twice, once per Target 0 and once per Target 1, as seen below.  If you follow my remedial diagrams one more time and select a disk, then the MPIO button, you should now see two paths.  Select the first path and click the Details button.  It should be using the local and remote IPs we took note of earlier.  Click OK.  Now select the second path and then the Details… button.  You should see it using the other adapter’s IP on BOTH the local and remote hosts.

image

05/10/2012 Posted by | Clustering, iSCSI, MPIO, Windows Server | , , | Leave a comment

%d bloggers like this: