One of my colleagues, Chris Bailiss, has written a number of great articles describing how BI service applications authenticate in SharePoint 2010.  If you are interested then head over here to part 1 (link below) now:

http://blogs.msdn.com/b/mcsukbi/archive/2011/11/26/bi-service-applications-in-sharepoint-2010-authentication-classic-vs-claims-and-identity-delegation-kerberos-part-1.aspx

Diagnosing and Resolving Spinlock Contention on SQL Server

http://sqlcat.com/whitepapers/archive/2011/07/05/diagnosing-and-resolving-spinlock-contention-on-sql-server.aspx

Diagnosing and Resolving Latch Contention on SQL Server

http://sqlcat.com/whitepapers/archive/2011/07/05/diagnosing-and-resolving-latch-contention-on-sql-server.aspx

http://msdn.microsoft.com/en-us/library/hh147624.aspx

It is possible to create custom reports, using SQL Server 2008 Reporting Services, against the System Center 2007 R2 data warehouse. 

Reports can be designed in SQL Server 2008 Reporting Services to provide the end user with a better representation of the data since the built-in System Center reports are not ideal. 

Fortunately the OperationsManagerDW database schema is documented on MSDN http://technet.microsoft.com/en-us/library/gg508713.aspx along with code samples which makes the task of creating a custom report a little easier. 

Stored procedures were implemented to return the data from the OperationsManagerDW schema.  I prefer this approach as it provides more control over the code rather than embedding logic in the report itself.  Incidentally, the stored procedures were hosted in a separate database to avoid support issues embedding these in OperationsManagerDW database.

An example of the stored procedure logic to query the OperationsManagerDW is below:

   1: /********************************************************************************************

   2:  *

   3:  *    (c) Microsoft 2010  All rights reserved

   4:  *

   5:  *    The code contained in this file is provided "as is" without any warranty of any kind.

   6:  *    The code is for reference purposes only and must not be relied on in connection with 

   7:  *    any operational purposes.

   8:  *    Please refer to the terms and conditions which cover the provision of consulting

   9:  *    services to you.

  10:  *

  11:  ********************************************************************************************

  12:  *

  13:  *          The following parameters are required:

  14:  *          pSlot            (nvarchar, 255)

  15:  *            pCurrentDate    (datetime)

  16:  *

  17:  ********************************************************************************************

  18:  *

  19:  *          Stored Procedure Creation Script

  20:  *          [usp_ReportPerfCounterHealth]

  21:  *

  22:  *===========================================================================================

  23:  * Modification History

  24:  *-------------------------------------------------------------------------------------------

  25:  * Verion    Date        Author                Description

  26:  *-------------------------------------------------------------------------------------------

  27:  * 01.00.00  10/01/2011  B Wright-Jones        Created

  28:  ********************************************************************************************/

  29:  

  30: CREATE PROCEDURE [Schema].[usp_ReportPerfCounterHealth]

  31:     @pSlot nvarchar(255), 

  32:     @pCurrentDate datetime

  33: AS

  34:  

  35:     SET NOCOUNT ON;

  36:  

  37:     SELECT    

  38:             [vME].[Path] AS [ServerName], 

  39:             [vPR].[ObjectName], 

  40:             [vPR].[CounterName], 

  41:             [vPRI].[InstanceName], 

  42:             AVG(SampleValue) AS Average, 

  43:             MIN(SampleValue) AS Minimum, 

  44:             MAX(SampleValue) AS Maximum,

  45:             SUM(SampleValue) AS SumOfValue

  46:             

  47:     FROM

  48:             [dbo].[vPerformanceRule] vPR

  49:             

  50:             INNER JOIN    [dbo].[vPerformanceRuleInstance] vPRI 

  51:             ON            vPR.[RuleRowId] = vPRI.[RuleRowId]

  52:             

  53:             INNER JOIN    [Perf].[vPerfRaw] vPRW

  54:             ON            [vPRI].[PerformanceRuleInstanceRowId] = [vPRW].[PerformanceRuleInstanceRowId] 

  55:             

  56:             INNER JOIN    [dbo].[ManagedEntity] vME

  57:             ON            [vPRW].[ManagedEntityRowId] = [vME].[ManagedEntityRowId] 

  58:                 

  59:     WHERE

  60:             [vPRW].[DateTime] BETWEEN DATEADD(Hh, -24, @pCurrentDate) AND @pCurrentDate            -- Filter for the last 24 hours

  61:             AND vPR.[CounterName] = '<Performance Counter goes here>'                            -- Filter for the specific performance monitor counter

  62:             AND vPRI.[InstanceName] = '<Instance goes here>'                                    -- Filter for the specific slot

  63:  

  64:     GROUP BY

  65:             [vME].[Path], [vPR].[ObjectName], [vPR].[CounterName], [vPRI].[InstanceName];        

The design time report is shown below.  The line graph shows specific counter values over the period of a month e.g. processor utilisation, web service requests etc. whereas the tablix controls display critical events, uptime, hits and so on for each server in the farm. 

A datetime parameter was implemented in order to allow the user to select a date from the calendar control

The uptime field displays the result of a custom counter which is the number of seconds since the service was started.  I implemented a vb function (shown below) on the report body to transform this into days, hours, minutes which is more readable for the end user. 

The expression references the custom code using the following syntax:

=Code.SecondsToText(Fields!AppPoolUpTimeSec.Value)

The Visual Basic function to format the seconds as days, hours, minutes is below:

   1: Function SecondsToText(Seconds) As String

   2: Dim bAddComma As Boolean

   3: Dim Result As String

   4: Dim sTemp As String

   5: Dim days As String

   6: Dim hours As String

   7: Dim minutes As String

   8:  

   9: If Seconds <= 0 Or Not IsNumeric(Seconds) Then 

  10:      SecondsToText = "0 seconds"

  11:      Exit Function

  12: End If

  13:  

  14: Seconds = Fix(Seconds)

  15:  

  16: If Seconds >= 86400 Then

  17:   days = Fix(Seconds / 86400)

  18: Else

  19:   days = 0

  20: End If

  21:  

  22: If Seconds - (days * 86400) >= 3600 Then

  23:   hours = Fix((Seconds - (days * 86400)) / 3600)

  24: Else

  25:   hours = 0

  26: End If

  27:  

  28: If Seconds - (hours * 3600) - (days * 86400) >= 60 Then

  29:  minutes = Fix((Seconds - (hours * 3600) - (days * 86400)) / 60)

  30: Else

  31:  minutes = 0

  32: End If

  33:  

  34: Seconds = Seconds - (minutes * 60) - (hours * 3600) - _

  35:    (days * 86400)

  36:  

  37: If Seconds > 0 Then Result = Seconds & " second" & AutoS(Seconds)

  38:  

  39: If minutes > 0 Then

  40:     bAddComma = Result <> ""

  41:     

  42:     sTemp = minutes & " minute" & AutoS(minutes)

  43:     If bAddComma Then sTemp = sTemp & ", "

  44:     Result = sTemp & Result

  45: End If

  46:  

  47: If hours > 0 Then

  48:     bAddComma = Result <> ""

  49:     

  50:     sTemp = hours & " hour" & AutoS(hours)

  51:     If bAddComma Then sTemp = sTemp & ", "

  52:     Result = sTemp & Result

  53: End If

  54:  

  55: If days > 0 Then

  56:     bAddComma = Result <> ""

  57:     sTemp = days & " day" & AutoS(days)

  58:     If bAddComma Then sTemp = sTemp & ", "

  59:     Result = sTemp & Result

  60: End If

  61:  

  62: SecondsToText = Result

  63: End Function

  64:  

  65:  

  66: Function AutoS(Number)

  67:     If Number = 1 Then AutoS = "" Else AutoS = "s"

  68: End Function

  69:  

The report can be executed directly by passing parameters in the URL as documented here http://msdn.microsoft.com/en-us/library/ms155391(v=SQL.100).aspx.  This was useful to test the report execution.  I used two parameters, an example of this URL structure is shown below:

&rs:Command=Render&pName=Test&pDate=11/01/2011

This can be achieved both in native and SharePoint integrated mode. 

The fact that the OperationsManagerDW schema is documented has made the whole process of creating a custom report a lot easier and the flexibility of Reporting Services provides a better representation of the data to the end-user.

SQL Server 2008 R2 BPA is an excellent and often under-used, tool.   The title is a little misleading but it does work against SQL Server 2008 instances, not just R2 instances.

I recently had the chance to use BPA against SQL Server 2008 Analysis Services.  SQL Browser is disabled by default.

I entered the server\instance with the port number and ran the BPA tool only to be told that the incorrect SQL Server version was in use.  Very strange. 

I checked PowerShell 2.0 was installed.   I also had administrative permissions on both the OS and SSAS instance.

It turns out that the SQL Server Browser service must be running in order to discover the instance.  This is now filed as a bug which should hopefully be fixed in the next release. 

The output of BPA is really useful and the rules point to knowledge base articles

I had unfortunately picked up the (bad) habit of debugging my SSIS packages by using messagebox.show at design time.  The SSIS data flow Script component does not allow debugging using this approach so I had to find an alternative.  I discovered that it is possible to output parameters at design time using the FireInformation in the Script component.  This puts the parameters in the progress window.

// Output parameters into the SSIS progress window
bool FireAgain = true;
this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of time is " + Row.ParamValueTime.ToString(), "", 0, ref FireAgain);
this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of environment is " + Row.ParamValueEnvironment.ToString(), "", 0, ref FireAgain);

I like this approach although I should probably output the parameters to one of the other console windows.

I recently had a requirement to edit an XML document using PowerShell  Fortunately, this is a relatively easy task as shown below.  I’ve used a system environment variable %USERPROFILE% to populate the path to the file.

The script modifies a value in the XML document (highlighted in the example below) and ensures this is persisted using the .Save method.  The intention is to execute this script during user logon to ensure the value is always disabled in the application, ProClarity Web Professional 6.3 SP3.

REM Code changes CheckQuerySize=”Yes” to CheckQuerySize=”No”

REM Windows XP
$xmlFile = “$env:userprofile\Application Data\ProClarity\Client\Options.xml”
$xmlDoc = [XML](gc $xmlFile)
$xmldoc.ProClarity5Options.ProClarityApp.CheckQuerySize = "No"
$xmldoc.Save($xmlFile)

REM Windows Vista and Windows 7
$xmlFile = “$env:userprofile\AppData\Roaming\ProClarity\Client\Options.xml”
$xmlDoc = [XML](gc $xmlFile)
$xmldoc.ProClarity5Options.ProClarityApp.CheckQuerySize = "No"
$xmldoc.Save($xmlFile)

Just noticed that we have posted SQL Server 2008 Service Pack 2 CTP on microsoft.com, here is the link http://www.microsoft.com/downloads/details.aspx?FamilyID=65606fdd-093f-4c70-91f6-dc1f24520e8f&displaylang=en

What's New:

• SQL Server Utility The SQL Server 2008 R2 Utility supports SQL Server 2008 instances that have SP2 installed. The SQL Server Utility models SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in SQL Server Management Studio provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP). For more information about the SQL Server Utility, see Managing the SQL Server Utility.
• Data-tier Applications SQL Server Management Studio in SQL Server 2008 R2 supports all data-tier application (DAC) operations for SQL Server 2008 instances that have SP2 installed. DAC is an entity that contains all databases and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems through the utility control point. For more information about data-tier applications, see Understanding Data-tier Applications.
• Reporting Services in SharePoint Integrated Mode (Feature Pack) The Microsoft SQL Server 2008 Service Pack 2 (SP2) Reporting Services Add-in for Microsoft SharePoint Products 2007 allows you to connect to SQL Server 2008 R2 Report Servers. The new features include:
  • Improved handling of credentials and shared data sources.
  • Utilization of the existing instances of Report Builder configured on the Report Server.
  • Support for Report Part Gallery, shared datasets, and shared report items.
  • Report Builder 3.0
  • ATOM feed renderer. For more information, see the ‘Rendering Reports to Data Feeds’ section in "What's New in SQL Server 2008 R2".

Visual Studio test suite contains a very useful set of tools to help troubleshoot and reproduce performance issues.  From a SQL Server perspective, I have found the tools invaluable as it allows me to replay HTTP workloads at volume against web servers and therefore the underlying database server.  

The Visual Studio tools provide a number of great features such as test mixes, step load plans, real-time visualisations, support for performance monitor counters and SQL Server tracing. 

Fiddler is a HTTP capture tool which also supports the ability to save the output to a .webtest file http://www.fiddlertool.com/Fiddler/help/WebTest.asp.  Client activity can be captured easily using Fiddler and this can then be imported into a Visual Studio Test Project as a workload. 

This workload can be replayed against servers using a step pattern or constant load.  There are many different configuration options which are conceptually referred to as a scenario.

Options such as warm-up or cool-down can be configured via the Run settings, as can the ability to capture SQL trace data although, when enabled, this captures a pre-defined set of event classes as defined here http://msdn.microsoft.com/en-us/library/ms404658.aspx.

SQL Server performance monitor counters can be added in the load test configuration as shown below.  Performance counters for named instances must be added manually (for some reason they are not

Real-time test data is displayed in Visual Studio and the results are saved either into a SQL Server Express database (installed as part of Visual Studio) or an existing SQL Server instance.  The latter requires manually configuring the data store connection string. 

Load balancers can also introduce some interesting behaviour such as binding your IP address to one web server however it is possible to avoid this using the Visual Studio Test Load Agent software to circumvent this behaviour: How to: Use IP Switching with Agents http://msdn.microsoft.com/en-us/library/ms404667(VS.90).aspx

As a side note, here are a few tips:

• Be careful when using SQL Server Express Edition to store the results as the database has a size limitation (SQL Server 2005 and SQL Server 2008 have a 4GB limitation whilst, SQL Server 2008 R2 10GB limitation)
• Review the SQL Sizing Considerations information in the Load Test Agent readme. e.g. The SQL Express database is license-limited to store 10 GB of data, which is around 24 hours of load test data for a typical load test.
• Install SQL Server Management Studio Express to manage the instance
• Schema differs for LoadTest between VS 2005 and VS 2008
• Schema for LoadTest exists in C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE. (or Visual Studio 8.0 if you’re using Visual Studio 2005)
• If you want to use a different instance of SQL Server to host the database and store the test results then execute the loadtestresultsrepository.sql (stored in folder listed above) and creates the database called LoadTest
• You will need to change the connection string in Visual Studio, this can be found in Test\Administer Test Controllers.
• The LoadTest schema differs between Visual Studio 2005 and Visual Studio 2008 so you can’t point Visual Studio 2008 at a previous LoadTest schema

I also used implemented DMV_Stats, a blocked process trace and performance monitor counter logs on the database server as I like to have a record of waitstats, blocking/deadlocking and also any (perfmon) queues.   From a web server perspective, I recorded a counter log of the key IIS metrics as defined here http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/7898b860-462c-4846-a3a8-1179f287ad88.mspx?mfr=true. 

In summary, Visual Studio and Fiddler provide the ability to capture and replay HTTP traffic at load.  This can be useful for investigating and recreating web server and database performance issues.

BPA is back for SQL Server 2008 and SQL Server 2008 R2 http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591

Microsoft Consultancy Services (UK) has recently started blogging.  These are team-based blogs rather than individual blogs.   I have listed a few of these blogs below

MCS UK Microsoft Business Intelligence Team Blog http://blogs.msdn.com/b/mcsukbi.  This blog focuses on the Microsoft business intelligence technology stack e.g. SSIS, SSAS, PerformancePoint rather than transactional-based systems.

MCS UK Solution Development Team Blog http://blogs.msdn.com/b/mcsuksoldev.  This blog focuses on application development.

MCS UK SharePoint Team Blog http://blogs.msdn.com/b/uksharepoint.  This blog focuses on SharePoint solutions.

I’ve just read the SQL Azure vs. SQL Server whitepaper http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=86f12b41-1eba-4567-9ac8-02eaa7d12034 its only a few pages so one of the lighter documents.

Some interesting and important points which are summarised in the paper e.g. you cannot control where your database files are placed. and also referenced in the Books Online Transact-SQL Support (SQL Azure Database) http://msdn.microsoft.com/en-us/library/ee336250.aspx

The following Transact-SQL features are not supported by SQL Azure:

• Common Language Runtime (CLR)
• Database file placement
• Database mirroring
• Distributed queries
• Distributed transactions
• Filegroup management
• Global temporary tables
• Spatial data and indexes
• SQL Server configuration options
• SQL Server Service Broker
• System tables
• Trace Flags

50GB databases will also be here soon http://blogs.msdn.com/b/sqlazure/archive/2010/04/16/9997517.aspx

Some of you may be wondering why you would want to do this.  We had a real requirement in a performance benchmark to use FULL recovery and backup the transaction log but we did not want to retain the log changes.  We already had a gold backup which we restored after each test.

I was wondering if it was still possible to BACKUP to NULL in SQL Server 2008 R2, and the answer is yes.  Warning: don’t ever do this in a production environment.

BACKUP LOG sandbox TO DISK = 'NUL'

I am unsure why the NUL keyword is missing a ‘L’.

Interesting, if I try to backup the log using the NUL keyword then this fails

BACKUP LOG sandbox TO DISK = 'NUL.bak'
 
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '…\Backup\NUL.bak'. 
Operating system error 2(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally
 
But, this works and persists the backup file in the default backup directory (look in msdb.dbo.backupmediafamily)
 
BACKUP LOG sandbox TO DISK = 'NULL.bak'

Hot on the heels of the RTM release of SQL Server 2008 R2, we have just announced the first cumulative update.  The knowledge base article is here http://support.microsoft.com/kb/981355 which lists all the relevant fixes, which are essentially fixes from SQL Server 2008 Service Pack 1 Cumulative Update 5 to 7.

As always, please review the knowledge base article and list of fixes before you consider deploying this update.

We have just released a hotfix to help identify poor I/O issues on Windows Server 2008 and Windows Server 2008 R2. This is particularly relevant for SQL Server environments as it will the administrator to determine if I/O performance issues are caused by the SAN.

Whilst the output is not particularly easy to consume, it is a good step towards identifying I/O issues.

The knowledge base articles:

Add a fix to improve the logging capabilities of the Storport.sys driver to troubleshoot poor disk I/O performance in Windows Server 2008 R2 http://support.microsoft.com/kb/978000

A hotfix is available that improves the logging capabilities of the Storport.sys driver to troubleshoot poor performance issues for the disk I/O in Windows Server 2008 http://support.microsoft.com/kb/978000

The full details are available here http://blogs.msdn.com/ntdebugging/archive/2010/04/22/etw-storport.aspx

Here http://www.microsoft.com/downloads/details.aspx?FamilyID=C18BAD82-0E5F-4E82-812B-5B23E5D52B9C&displaylang=en

I’m quite surprised how easy it is to setup an SSIS package to read and write data to a SQL Azure database.  A simple data flow task with an ADO.NET Destination is easy to setup, just make sure the SQL Azure table has a clustered index otherwise writing data will not work (as this is a pre-requisite). 

The SSIS team have added a BULK INSERT option on the ADO.NET Destination in SQL Server 2008 R2 to improve performance, this blog entry covers the details: http://blogs.msdn.com/mattm/archive/2009/11/12/something-new-for-ssis-in-sql-server-2008-r2-november-ctp.aspx

On the topic of indexes, it looks like there is a bug in the scripting engine in SQL Server 2008 R2.  Scripting the index as DROP to an SSMS window returns the following however the ONLINE syntax is not supported with SQL Azure

I tend to automate perfmon collection with logman.exe logging to a SQL Server 2008 database via the System DSN (ODBC data source) as follows:

@echo off
REM create the counter log, counters specified in .config file
REM stores the counter log in a .blg format, this can alos be stored in a SQL Server database if required

REM set environment variables
SET SERVER1=\\benjones01
SET CONFIGFILE=SQL2005BaselineCounters.config
SET START=09/02/2010 16:00:00
SET END=09/02/2010 17:00:00

ECHO    ====================================================
ECHO    Creating performance monitor counter sets on %SERVER1%
ECHO    ====================================================

logman create counter MicrosoftSSIS2005Perf -s %SERVER1% -f bin -b %START% -E %END% -si 5 -v mmddhhmm -o "Perfmon_SSIS_Performance" -cf %CONFIGFILE%

PAUSE

In this example, the config file contains a list of the counters I am interested in:

"\\SERVERNAME\LogicalDisk(*)\*"
"\\SERVERNAME\Memory\*"
"\\SERVERNAME\MSSQL:Access Methods\*"
"\\SERVERNAME\MSSQL:Buffer Manager\*"
"\\SERVERNAME\MSSQL:Buffer Node(*)\*"
"\\SERVERNAME\MSSQL:CLR\*"
"\\SERVERNAME\MSSQL:Databases(*)\*"
"\\SERVERNAME\MSSQL:Exec Statistics(*)\*"
"\\SERVERNAME\MSSQL:General Statistics\*"
"\\SERVERNAME\MSSQL:Latches\*"
"\\SERVERNAME\MSSQL:Locks(*)\*"
"\\SERVERNAME\MSSQL:Memory Manager\*"
"\\SERVERNAME\MSSQL:Plan Cache(*)\*"
"\\SERVERNAME\MSSQL:SQL Statistics\*"
"\\SERVERNAME\MSSQL:Transactions\*"
"\\SERVERNAME\MSSQL:Wait Statistics(*)\*"
"\\SERVERNAME\Network Interface(*)\*"
"\\SERVERNAME\PhysicalDisk(*)\*"
"\\SERVERNAME\Process(sqlservr)\*"
"\\SERVERNAME\SQLServer:SSISPipeline\*"
"\\SERVERNAME\Processor(*)\*"
"\\SERVERNAME\System\Context Switches/sec"
"\\SERVERNAME\System\Processor Queue Length"
"\\SERVERNAME\System\Threads"

Logging can generate quite a lot of data depending on the threshold specified in the –si parameter for logman.exe.

Logging to a SQL Server database makes it easier to query the data as the binary (blg) files can be awkward to deal with when they are very large.

SQL Server 2008 also provides an added benefit in that I can compress the main table, [dbo].[CounterData] quite significantly using PAGE compression.  The stored procedure shown below provided an estimate of the space saving, this was about 1/3 of the original table size.

EXEC sp_estimate_data_compression_savings 'dbo', 'CounterData', NULL, NULL, 'PAGE' ;
GO

The screenshot below illustrates rebuild procedure in order to apply compression at the page level.  My laptop is definitely struggling whilst the data is being compressed…