Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

Posts
  • Benjamin Wright-Jones

    Processing Excel files in SSIS 2008 – Lessons Learned

    • 1 Comments

    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

    image

    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. 

  • Benjamin Wright-Jones

    Changing the LISTENER_IP address in a database mirroring configuration

    • 0 Comments

    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. 

    1. Stop application activity
    2. Remove mirroring (SET PARTNER OFF)
    3. Stop Mirroring endpoints (on principal and mirror)
    4. Alter Mirroring endpoints to use new IP addresses e.g. ALTER ENDPOINT SET LISTENER_IP =
    5. Start endpoints on principal and mirror
    6. 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.

  • Benjamin Wright-Jones

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

    • 0 Comments

    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

  • Benjamin Wright-Jones

    Troubleshooting SQL Server Transactional Replication

    • 0 Comments

    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. 
  • Benjamin Wright-Jones

    Fusion IO 640GB SSD PCI-Express Cards

    • 0 Comments

    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. 

  • Benjamin Wright-Jones

    SQL Server 2008 Books Online (July 2009) Update

    • 0 Comments

    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

  • Benjamin Wright-Jones

    Using an SSIS package to monitor and archive the default trace file

    • 3 Comments

    I find it frustrating that the SQL Server 2005/2008 default trace is continually overwritten and there is no way to store x number of files or x MBs of data.  As a workaround, I developed an SSIS package to monitor the \LOG folder and automatically archive the default trace file whenever a new file is created.

    This consists of a FOR LOOP container, a Script Task and a File System Task plus a whole bunch of variables and property expressions.

    The guts of the package is really in the Script Task as this is where I use a WMI query to monitor the \LOG folder for .trc files.  The file is then renamed (date-time-servername-file) to another folder\share which can be a UNC structure e.g. \\server\share.  This way I have a permanent record of the basic server activity for root cause analysis/troubleshooting. 

    The screenshot below shows the basic structure of the package.

    image

  • Benjamin Wright-Jones

    SQL Server 2008 R2 Master Data Services

    • 1 Comments

    In case you missed the Tech-Ed 2009 announcement, you can find the info here http://www.microsoft.com/sqlserver/2008/en/us/MDS.aspx

    “On initial scoping, it was determined that 'Bulldog' would ship as part of Microsoft Office SharePoint in the O14 wave.  At TechEd 2009, we announced a change in packaging for the new MDM capabilities. Project 'Bulldog' will now ship as part of the next release of SQL Server codenamed ‘Kilimanjaro’ as 'SQL Server Master Data Services. 

    This means that in addition to new capabilities such as Self Service BI and multi-server management, SQL Server ‘Kilimanjaro’ will also provide customers with a rich platform for MDM through SQL Server Master Data Services. Customers who have purchased Software Assurance (SA) should view this as net new value and innovation that they will have access to as a result of their investments in SA.”

  • Benjamin Wright-Jones

    Initializing a Transactional Replication Subscriber from an Array-Based Snapshot

    • 2 Comments

    After a few late nights, some coffee and a few review cycles, a new article has just been published on the SQLCAT site which provides an overview of the subscriber initialisation techniques for transactional replication and, more specifically using an array-based snapshot http://sqlcat.com/technicalnotes/archive/2009/05/04/initializing-a-transactional-replication-subscriber-from-an-array-based-snapshot.aspx

  • Benjamin Wright-Jones

    Technet Webcast: An Early look at SQL Server ‘Kilimanjaro’ and project ‘Madison’

    • 1 Comments

    http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032413070&EventCategory=4&culture=en-US&CountryCode=US

  • Benjamin Wright-Jones

    SQL Server 2005 Service Pack 3 Cumulative Update 3

    • 1 Comments

    Just released http://support.microsoft.com/kb/967909/

  • Benjamin Wright-Jones

    Using a C# script task in SSIS to download a file over http

    • 3 Comments

    I recently had a requirement to automate downloading a file from an website and then perform ETL on the data in the file.  Fortunately, this is possible via the script task in SSIS (note that this is using SQL Server 2008 Integration Services).  I found a couple of web references to do this in VB.NET but I prefer C# so modified the code and made some adjustments to suit my (debugging) needs.  I set two package variables, RemoteURI and LocalFileName, to store the URL (source) and filename (destination).

    This works really well and I can change the variables at run-time using property expressions

    public void Main()
           {
               WebClient myWebClient;
               string RemoteURI;
               string LocalFileName;
               bool FireAgain = true;

               Dts.Log("entering download..", 999, null);

               try
               {
                   myWebClient = new WebClient();

                   RemoteURI = Dts.Variables["User::vPipeline"].Value.ToString();
                   LocalFileName = Dts.Variables["User::vLocalFileName"].Value.ToString();

                   Console.WriteLine(RemoteURI);
                   Console.WriteLine(LocalFileName);

                   MessageBox.Show(RemoteURI);
                   MessageBox.Show(LocalFileName);

                   // Notification
                   Dts.Events.FireInformation(0, String.Empty, String.Format("Downloading '{0}' from '{1}'", LocalFileName, RemoteURI), String.Empty, 0, ref FireAgain);

                   // Download the file
                   myWebClient.DownloadFile(RemoteURI, LocalFileName);

                   Dts.TaskResult = (int)ScriptResults.Success;

               }

               catch (Exception ex)
               {
                   // Catch and handle error
                   Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
                   Dts.TaskResult = (int)ScriptResults.Failure;
               }

           }

  • Benjamin Wright-Jones

    Microspotting?

    • 1 Comments

    [Non-SQL related] I’ve just discovered an interesting site called Microspotting which came to my attention courtesy of the grapevine. At first I wasn’t sure what this was about but after a little bit of digging, it would appear that the site is dedicated to sharing stories about Microsoft FTE’s. As the site proclaims, it is a bit like having an internal paparazzi but provides some good insight.  Take a look here http://www.microspotting.com/

  • Benjamin Wright-Jones

    SQL Server 2008 CU4 released

    • 1 Comments

    Just published here http://support.microsoft.com/kb/963036

  • Benjamin Wright-Jones

    Why is sp_dbmmonitorresults failing with invalid object?

    • 1 Comments

    I recently setup a simple database mirroring config on my latpop (two instances SQL Server 2005 SP3 etc.).  The databases were configured for HIGH SAFETY but with no witness.  Everything was working fine.  I wanted to simulate a communication failure so I could see the send queue growing.  I just shutdown the mirror instance and then inserted lots of rows into the principal.  Great, so now I executed:

    EXEC    sys.sp_dbmmonitorresults @database_name=N'dbm1', 6, 0

    But it kept failing with invalid object on dbm_monitor_data.  Looking at sp_dbmmonitorresults I could see that it was failing on line 130.  This was really confusing me so I looked in msdb for the object.  It didn’t exist.

    I was suprised to find that the object was only created AFTER launching the database mirroring console.  This creates the necessary monitoring objects in msdb.  Problem solved, after much frustration.

  • Benjamin Wright-Jones

    SQL Server 2008 System Views Map in PDF or XPS

    • 1 Comments

    Just published - download from here http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en#filelist

  • Benjamin Wright-Jones

    SQLCAT publish new article on transaction log performance..

    • 1 Comments

    Read the publication here http://sqlcat.com/technicalnotes/archive/2008/12/09/diagnosing-transaction-log-performance-issues-and-limits-of-the-log-manager.aspx

    Also note the new SQL Server 2008 transaction log specification:

    1. Amount of “in-flight” I/O Limit.

    a. SQL Server 2008: limit of 3840K at any given time

    b. Prior to SQL Server 2008: limit of 480K at any given time

  • Benjamin Wright-Jones

    Readtrace updated

    • 1 Comments

    In case you haven’t seen this, readtrace has been updated to also support the SQL Server 2008 trace format and new data types.  There are lots of other fixes and changes in there so go and get the latest version to take a look:

    x64 http://www.microsoft.com/downloads/details.aspx?familyid=B60CDFA3-732E-4347-9C06-2D1F1F84C342&amp;displaylang=en&displaylang=en

    OR

    x86 http://www.microsoft.com/downloads/details.aspx?FamilyId=7EDFA95A-A32F-440F-A3A8-5160C8DBE926&amp;displaylang=en&displaylang=en

  • Benjamin Wright-Jones

    Support for more than 64 logical processors

    • 1 Comments

    This is a big announcement made at WinHEC http://www.microsoft.com/presspass/features/2008/nov08/11-06winserverr2.mspx 

    “[…] we continue to invest in scalability. In Windows Server 2008 R2, we have built in support for up to 256 logical processors, which will allow our customers to more fully exploit today’s powerful CPUs, deploying only the features they choose and scaling those solutions to meet their organization’s needs.”

    You can watch the recording here http://www.microsoft.com/presspass/events/winhec/default.mspx

    [UPDATE] - Go to about 32 minutes; I particularly like the shot of task manager with 256 processors (about 38 minutes in)

  • Benjamin Wright-Jones

    New performance monitor counters for SQL Server 2008

    • 1 Comments

    I have been looking at some of the new performance counters that are shipped with SQL Server 2008.  Unfortunately some of these are either not well documented or not documented at all.  The list so far consists of:

    1. SQL Server Databases: Tracked transactions/sec - Number of committed transactions recorded in the commit table for the database.
    2. SQL Server Databases: Write Transactions/sec - Number of transactions which wrote to the database in the last second.
    3. SQL Server Buffer Node: Remote node page lookups/sec - Number of lookup requests from this node which were satisfied from other nodes.
    4. SQL Server Buffer Node: Local node page lookups/sec - Number of lookup requests from this node which were satisfied from this node.
    5. SQL Server General Statistics: Connection reset/sec - Total number of connection resets per second.
    6. SQL Server General Statistics: Tempdb recovery unit id unit - Number of duplicate tempdb recovery unit id generated
    7. SQL Server General Statistics: Tempdb rowset id - Number of duplicate tempdb rowset id generated
    8. SQL Server SQL Statistics: Misguided plan executions/sec - Number of plan executions per second in which a plan guide could not be honored during plan generation
    9. SQL Server SQL Statistics: Guided plan executions/sec - Number of plan executions per second in which the query plan has been generated by using a plan guide.
    10. Deprecated Features: Usage <feature> – this is a really nice counter which lists feature usage since last SQL Server startup

    I know there are lots more e.g. resource governor etc. but these are the counters which caught my eye.

  • Benjamin Wright-Jones

    SQL Server 2008 CU1 has just been posted

    • 1 Comments

    In case you haven’t seen this…http://support.microsoft.com/kb/956717

  • Benjamin Wright-Jones

    Making a database clone using SMO

    • 1 Comments

    I’ve been playing around with SMO a bit more over the past few days as I’m finding it really useful in some situations.  Following on from previous test with SMO and indexes, I was wondering if I could script the database statistics and histograms in SMO rather than use Management Studio.  As a quick reminder, it is possible to make a ‘clone’ of the database statistics and histograms in SQL Server 2005 (with SP2).  This option is buried in the generate scripts window.  A database clone can be very useful when troubleshooting query plans rather than have to rely on a full copy of the database (data+objects+stats).  I actually think using SMO directly in a C# console window is faster than using the SSMS interface but I need to do more testing to validate this. 

    You’ll see below that I generate scripts of the CREATE DATABASE statement, tables plus statistics and histograms, stored procedures, user-defined functions, partition schemes and partition functions, whilst excluding all system objects. 

    using System;
    using System.Collections.Generic;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using System.Text;
    using System.Collections.Specialized;

    namespace SQLSMO
    {
        class SMOClone
        {
            static void Main(string[] args)
            {
                string servername;
                string databasename;

                servername = "servername\\instance";
                databasename = "databasename";

                Server server = new Server(servername);
                Database db = server.Databases[databasename];


                //include the database create syntax
                ScriptingOptions dbso = new ScriptingOptions();
                dbso.FileName = "e:\\" + databasename + "-create.sql";
                dbso.AppendToFile = true;

                Console.WriteLine("Scripting database: " + databasename + ". Please wait...");

                db.Script(dbso);


                //scripting options
                ScriptingOptions tso = new ScriptingOptions();
                tso.ScriptDrops = false;
                tso.Indexes = true;
                tso.ClusteredIndexes = true;
                tso.PrimaryObject = true;
                tso.SchemaQualify = true;
                tso.NoIndexPartitioningSchemes = false;
                tso.NoFileGroup = false;
                tso.DriPrimaryKey = true;
                tso.DriChecks = true;
                tso.DriAllKeys = true;
                tso.AllowSystemObjects = false;
                tso.IncludeIfNotExists = false;
                tso.DriForeignKeys = true;
                tso.DriAllConstraints = true;
                tso.DriIncludeSystemNames = true;
                tso.AnsiPadding = true;
                tso.IncludeDatabaseContext = false;
                tso.FileName = "e:\\" + databasename + "-clone.sql";
                tso.AppendToFile = true;
                //include statistics and histogram data for db clone
                tso.OptimizerData = true;
                tso.Statistics = true;

                foreach (Table t in db.Tables)
                {
                    if (!t.IsSystemObject)
                    {
                        Console.WriteLine("Scripting Table & Statistics: " + t);
                        t.Script(tso);
                    }
                    Console.WriteLine();
                }

                foreach (StoredProcedure sp in db.StoredProcedures)
                {
                    if (!sp.IsSystemObject)
                    {
                        Console.WriteLine("Scripting Stored Procedure: " + sp);
                        sp.Script(tso);
                    }
                }

                foreach (UserDefinedFunction udf in db.UserDefinedFunctions)
                {
                    if (!udf.IsSystemObject)
                    {
                        Console.WriteLine("Scripting Function: " + udf);
                        udf.Script(tso);
                    }
                }

                foreach (PartitionFunction pf in db.PartitionFunctions)
                {
                    Console.WriteLine("Scripting Partition Function: " + pf);
                    pf.Script(tso);
                }
                foreach (PartitionScheme ps in db.PartitionSchemes )
                {
                    Console.WriteLine("Scripting Partition Scheme: " + ps);
                    ps.Script(tso);
                }

                Console.Write("Scripting completed. Press any key to exit.");
                Console.ReadKey();
            }
        }
    }

  • Benjamin Wright-Jones

    How can I generate a T-SQL script for just the indexes?

    • 5 Comments

    I was trying to work out the best way to generate a T-SQL script for only the indexes and I thought that this would be really easy in the Management Studio interface.  I was mistaken.  SQL Server Management Studio also generates scripts for the tables when you want the indexes.  This is not great so I looked at other methods.  Method 1) Use T-SQL to generate a script using the metadata or method 2) use SMO (SQL Server Management Objects).  Although I like doing things with T-SQL, I thought I’d give SMO a try and below is the result.  I just hope this is made easier in future releases.

    using System;
    using System.Collections.Generic;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;
    using System.Text;
    using System.Collections.Specialized;

    namespace SQLRMOSMO
    {
        class SMOTest   // Scripts all indexes for a specified database
        {
            static void Main(string[] args)
            {
                string servername;
                string databasename;
                servername = "<server\\instance>";
                databasename = "<database>";

                Server server = new Server(servername);
                Database db = server.Databases[databasename];
                ScriptingOptions so = new ScriptingOptions();
                so.ScriptDrops = false;
                so.Indexes = true;
                so.IncludeIfNotExists = false;
                so.DriForeignKeys = false;
                so.FileName = "c:\indexes.sql";
                so.AppendToFile = true;
                foreach (Table t in db.Tables)
                {
                    foreach (Index i in t.Indexes)
                        i.Script(so);
                }

            }
        }
    }

  • Benjamin Wright-Jones

    Finally some guidance on SQL Server and Hyper-V

    • 1 Comments

    Just noticed this KB http://support.microsoft.com/kb/956893 which outlines the support policy for SQL Server on Hyper-V.  Bottom line is that SQL Server 2005 is not supported as is failover clustering for both SQL Server 2005 and SQL Server 2008. 

  • Page 2 of 7 (167 items) 12345»