Benjamin Wright-Jones

BI Service Applications in SharePoint 2010

superlatch — Tue, 29 Nov 2011 09:29:14 GMT

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

Two new SQLCAT papers available, Spinlock and Latch Contention

superlatch — Wed, 06 Jul 2011 08:21:41 GMT

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

Whitepaper Alert: Microsoft EDW Architecture, Guidance and Deployment Best Practices

superlatch — Fri, 06 May 2011 12:49:55 GMT

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

Creating custom reports against the System Center Operations Manager 2007 R2 data warehouse

superlatch — Mon, 24 Jan 2011 17:31:32 GMT

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.

 *

 ********************************************************************************************

 *

 *          The following parameters are required:

 *          pSlot            (nvarchar, 255)

 *            pCurrentDate    (datetime)

 *

 ********************************************************************************************

 *

 *          Stored Procedure Creation Script

 *          [usp_ReportPerfCounterHealth]

 *

 *===========================================================================================

 * Modification History

 *-------------------------------------------------------------------------------------------

 * Verion    Date        Author                Description

 *-------------------------------------------------------------------------------------------

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

 ********************************************************************************************/

 

CREATE PROCEDURE [Schema].[usp_ReportPerfCounterHealth]

    @pSlot nvarchar(255), 

    @pCurrentDate datetime

AS

  

    SET NOCOUNT ON;

  

    SELECT    

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

            [vPR].[ObjectName], 

            [vPR].[CounterName], 

            [vPRI].[InstanceName], 

            AVG(SampleValue) AS Average, 

            MIN(SampleValue) AS Minimum, 

            MAX(SampleValue) AS Maximum,

            SUM(SampleValue) AS SumOfValue

            

    FROM

            [dbo].[vPerformanceRule] vPR

            

            INNER JOIN    [dbo].[vPerformanceRuleInstance] vPRI 

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

            

            INNER JOIN    [Perf].[vPerfRaw] vPRW

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

            

            INNER JOIN    [dbo].[ManagedEntity] vME

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

                

    WHERE

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

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

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

  

    GROUP BY

            [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 

     SecondsToText = "0 seconds"

     Exit Function

End If

  

Seconds = Fix(Seconds)

  

If Seconds >= 86400 Then

  days = Fix(Seconds / 86400)

Else

  days = 0

End If

  

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

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

Else

  hours = 0

End If

  

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

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

Else

 minutes = 0

End If

  

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

   (days * 86400)

  

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

  

If minutes > 0 Then

    bAddComma = Result <> ""

    

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

    If bAddComma Then sTemp = sTemp & ", "

    Result = sTemp & Result

End If

  

If hours > 0 Then

    bAddComma = Result <> ""

    

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

    If bAddComma Then sTemp = sTemp & ", "

    Result = sTemp & Result

End If

  

If days > 0 Then

    bAddComma = Result <> ""

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

    If bAddComma Then sTemp = sTemp & ", "

    Result = sTemp & Result

End If

  

SecondsToText = Result

End Function

  

  

Function AutoS(Number)

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

End Function



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.

Using SQL Server 2008 R2 Best Practice Analyzer against Analysis Services

superlatch — Mon, 04 Oct 2010 19:51:36 GMT

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

Debugging Parameters in the SSIS Data Flow (Script Component)

superlatch — Mon, 04 Oct 2010 19:38:23 GMT

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.

Editing XML using PowerShell

superlatch — Wed, 14 Jul 2010 09:08:56 GMT

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)

SQL Server 2008 Service Pack 2 CTP

superlatch — Thu, 08 Jul 2010 07:21:05 GMT

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".

Using Visual Studio Test Tools to troubleshoot and reproduce database performance issues

superlatch — Thu, 24 Jun 2010 17:03:31 GMT

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.

Best Practice Analyzer is back

superlatch — Sat, 19 Jun 2010 18:40:18 GMT

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

Blogs from Microsoft Consultancy Services

superlatch — Wed, 02 Jun 2010 20:15:09 GMT

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.

SQL Azure vs. SQL Server

superlatch — Tue, 25 May 2010 18:09:10 GMT

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

Backing up to NUL(L)?

superlatch — Mon, 24 May 2010 13:59:58 GMT

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'

SQL Server 2008 R2 Cumulative Update 1

superlatch — Mon, 24 May 2010 13:59:19 GMT

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.

Hotfix Alert: Identifying poor I/O

superlatch — Thu, 06 May 2010 09:05:01 GMT

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

SQL Server 2008 R2 RTM BOL now available

superlatch — Sat, 24 Apr 2010 11:07:41 GMT

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

Using SSIS with SQL Azure databases

superlatch — Mon, 08 Mar 2010 22:02:52 GMT

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

Performance monitor, SQL Server and PAGE compression

superlatch — Thu, 11 Feb 2010 19:42:55 GMT

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…

New editions of SQL Server 2008 R2 announced

superlatch — Wed, 04 Nov 2009 10:58:26 GMT

SQL Server 2008 R2 Datacenter

SQL Server 2008 R2 Parallel Data Warehouse

http://www.microsoft.com/sqlserver/2008/en/us/R2-editions.aspx

Processing Excel files in SSIS 2008 – Lessons Learned

superlatch — Wed, 21 Oct 2009 20:22:40 GMT

I have recently been working on a project where we need to load and transform data held in Excel 2007 into a SQL Server database. The Excel sheets were fairly complex and had different structures. Fortunately, SSIS 2008 provides some good tools to handle this situation.

Lessons learned

1. It is possible to read the Excel metadata using the mechanism listed in this knowledge base article, http://support.microsoft.com/kb/318452 HOW TO: Retrieve Meta Data from Excel by Using GetOleDbSchemaTable in Visual C# .NET. You may be wondering why I want to do this. Handling Excel sheets with different structures can be tricky so reading the metadata can help determine control flow processing i.e. which data flow to use to process the file.

2. Remember, if you are testing on x64 then your package will not execute if you are using the Excel source since it is not supported on 64-bit so you need to disable the Run64BitRuntime property as below

3. The Script component is really great when it comes to data manipulation in the data flow. This can be used as either a source, transformation or destination and allows you to manipulate the rows in the pipeline using either VB.NET or C#.

4. As mentioned, Excel files can also be read using the Execute SQL task in the control flow, which is a nice feature e.g. SELECT * FROM $PivotData

5. The File System Task can make file handling a lot easier. Combine this with the use of variables and property expressions, dynamic file manipulation became a whole lot easier. For example, after processing I either move the file to another folder based on the outcome e.g. success or failure.

Changing the LISTENER_IP address in a database mirroring configuration

superlatch — Sat, 26 Sep 2009 00:20:05 GMT

This question came up today: how can I change the IP address between the principal and mirror in database mirroring?

Not a common operation but this procedure worked in an isolated lab environment where we had full control over the application and transaction activity. We wanted to introduce a WAN latency injector so needed to change the database mirroring IP addresses on the principal and mirror.

Stop application activity
Remove mirroring (SET PARTNER OFF)
Stop Mirroring endpoints (on principal and mirror)
Alter Mirroring endpoints to use new IP addresses e.g. ALTER ENDPOINT SET LISTENER_IP =
Start endpoints on principal and mirror
Enable mirroring (ALTER DATABASE <dbname> SET PARTNER = TCP://x.x.x.x)

I’ll try and find the exact scripts we used and upload them here.

New HP Resources on SQL Server 2008 Data Warehousing / Business Intelligence

superlatch — Wed, 16 Sep 2009 09:54:00 GMT

I haven’t had a chance to look through these yet so I can’t comment on the content but I thought I would post here to share these new resources.

HP Business Intelligence Sizer for Microsoft SQL Server 2005/2008

http://h71019.www7.hp.com/ActiveAnswers/us/en/sizers/microsoft-sql-bi.html

HP Whitepapers on SQL Server 2008 Data Warehousing / Business Intelligence

http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-5263ENW.pdf

http://h20195.www2.hp.com/V2/getdocument.aspx?docname=4AA2-8173ENW.pdf

http://h20195.www2.hp.com/V2/GetPDF.aspx/4AA2-7162ENW.pdf

Troubleshooting SQL Server Transactional Replication

superlatch — Thu, 10 Sep 2009 23:48:02 GMT

I often see questions about transactional replication performance problems, especially around latency/delays between the publisher and subscriber(s) so I’ve put a few pointers below on what to investigate. Latency between the publisher, distributor and subscriber(s) is, more often than not, the symptom of other causes for example, poor I/O capacity on subscribers, blocking/locking, hotspots on indexes, high number of virtual log files etc.

Troubleshooting tips:

Look at perfmon counters (disk reads and writes/sec, avg disk/sec read and avg disk/sec write) to ensure there is enough capacity and that the latency on the data and log drives are within our recommended boundaries.
Look at waitstats (use DMVstats – highly recommended) to see what resources are waiting. This will give you a good indication where the bottleneck is.
Look at the transactional replication performance monitor counters (pending Xacts, transactions/sec, latency etc.)
Check the number of VLF’s http://support.microsoft.com/kb/949523 as this can have a negative impact on log scanning if there are a very high number of VLF’s, I tend to ensure this value is below 1000.
Use tracer tokens to check latency from publisher to distributor to subscriber
Use agent logging to external files, -outputverboselevel 2 –output <dir\file> to troubleshoot data issues
Look in mslogreader_history, msdistribution_history & msrepl_errors in distribution database
Consider external factors e.g. consult the network/SAN specialists to check external issues such as network bandwidth/array performance issues etc.
If blocking is suspected then use the blocked process trace definition. I can highly recommend this as it provides incredibly valuable information about the blocked and blocking processes.
If you are using database mirroring in conjunction with transaction replication then the log reader may be have to wait for the record to be hardened on the mirror. This can be avoided by using trace flag 1448 on the publisher.

Optimisation tips:

Use agent profiles to optimise for workloads
Implement Read Committed Snapshot Isolation (RCSI) on subscribers to alleviate reader/writer blocking (when doing this consider the impact on tempdb as this is where the version store is located)
Ensure the distribution history clean-up job is correctly trimming the distribution database tables.
If there are data consistency issues, consider using tablediff to compare data in publisher/subscriber tables (warning: this may take a while with large volumes of data) however tablediff can in fact be used against a subset of the data using views.
Be careful about using –skiperrors to bypass consistency errors http://support.microsoft.com/kb/327817
Consider using –SubscriptionStreams on the distribution agent to use multiple threads to apply the data to the subscribers, read this http://support.microsoft.com/kb/956600 and this http://support.microsoft.com/kb/953199
If initialising from a backup/copy of the database, don’t enforce integrity on the subscribers. Drop the constraints or use the NOTFORREPLICATION option.

Fusion IO 640GB SSD PCI-Express Cards

superlatch — Thu, 10 Sep 2009 23:21:06 GMT

We recently had the opportunity to test a couple of the Fusion IO PCI-Express 640GB SSD cards http://www.fusionio.com/Products.aspx in a Dell R900 server, unfortunately time was against us and we were unable to do this. The Fusion IO SSD cards would dramatically increase the IOPS capacity and personally, I think they would be suited to storing tempdb. I’m a bit cautious about using SSD for data and transaction log so tempdb seems like the best solution.

SQL Server 2008 Books Online (July 2009) Update

superlatch — Sun, 30 Aug 2009 00:47:49 GMT

Only just noticed that a new revision of the SQL Server 2008 Books Online documentation has been published, the download is here http://www.microsoft.com/downloads/details.aspx?FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97&DisplayLang=en