SQL in Stockholm

SQL Server thoughts from the support team in Stockholm

SQL Server troubleshooting thoughts from Graham Kent, (ex) Senior Support Escalation Engineer at Microsoft Sweden.

  • SQL in Stockholm

    Thoughts about building a SQL Server 2008 cluster in Hyper-V


    Having built a SQL Server 2005 cluster on virtualized hardware, using Virtual Server, i thought it was time to do this on hyper-v with SQL 2008. Here’s some thoughts to get you thinking about trying this. The one single most important point here, is how much easier the whole process it with Windows / SQL 2008. It just works. If you’re like me and just playing with it in test, then there are tons of simple wizards to speed you through many of the tasks.

    When I did the install on virtual server, I used this article as a starting point for building the actual cluster:

    Using Microsoft Virtual Server 2005 to Create and Configure a Two-Node Microsoft Windows Server 2003 Cluster


    I guess the equivalent article for Windows 2008 Hyper-V is this one

    Hyper-V Step-by-Step Guide: Hyper-V and Failover Clustering


    which is a good starting point. When I actually did this I did it without following any specific instructions, just to see if I could get it to work, and learn a few things as I went along, troubleshooting anything I came across myself. This is always my preferred way to learn. In my scenario I used iSCSI drives hosted on the DC.

    This in no way creates a scalable solution that you should use in production, it’s just something to knock together quickly to test stuff with, so I can play with a clustered SQL 2008 instance.

    The steps are in the attached document.

  • SQL in Stockholm

    Hypothesis Confirmed - Skiing in Sweden is better than SQL Server Support


    So as per my post on 2nd January, I'm happy to confirm that skiing in Sweden is definitely better than SQL Server support, however much I do enjoy my job at times :-) Here's a picture of me learning to cross country in Klövsjö, near Vemdalen in central Sweden.

    I'm a fairly experienced downhill skier, but what with my love of long distance walking and trekking, cross country seemed the next obvious step, and frankly, it was bloody great


    The day we arrived it was minus 34, yes thirty four, now that's cold. Fortunately it didn't stay like that all week and good skiing fun was had by all.

    I have nothing SQL Server to say today, apart from the fact that I've installed a Hyper-V based SQL 2008 cluster this week in my spare time, and I might post some details later on. It was an interesting experiment.

    Currently though I'm just dreaming of more skiing and luckily enough I'm going to Sälen for a week in just 4 weeks time.


  • SQL in Stockholm

    Problems executing maintenance plans in SQL Server 2008 - Resolved by SQL 2008 CU#3


    In November I wrote here about problems executing maintenance plans due to Integration Services edition compatibility issues. Last night we released cumulative update #3 for SQL Server 2008 RTM, and it contains a fix / design change for this problem. You can read about it and download the fix here :

    Cumulative update package 3 for SQL Server 2008


    FIX: Error message when you run a maintenance plan in SQL Server 2008: "The SQL Server Execute Package Utility requires Integration Services to be installed"


  • SQL in Stockholm

    Error 29528. The setup has encountered an unexpected error while Setting Internal Properties


    We've just published a change to KB925976 on how to work around the following setup error:

    Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

    We've also just published KB961662 which details how to workaround a problem you can encounter if you had used the original version of 925976.

    Msg 7644, Level 17, State 1, Line 2
    Full-text crawl manager has not been initialized. Any crawl started before the crawl manager was fully initialized will need to be restarted. Please restart SQL Server and retry the command. You should also check the error log to fix any failures that might have caused the crawl manager to fail.

    These problems are related to SID management and the corresponding registry keys. In the earlier version of KB925976 there was advice to delete a registry key containing corrupted SIDs. This workaround was successful for all scenarios in fixing the setup error and allowing setup to complete, but it potentially causes problems with FTS as noted above. The new version of 925976 provides a more future proof solution.

    What I also learnt whilst working on these articles was that there is a public sysinternals tool to get SIDs when you need to do maintenance such as this. You can download it here:

    PsGetSid v1.43 - http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx

    This can be useful for many other scenarios and is worth remembering.

  • SQL in Stockholm

    Sample Application to test database mirroring failover


    Here's the source for a tiny C# app I wrote to test database mirroring failover at the application level, including a sample database mirroring connection string. It assumes that you have already set up a mirroring partnership. It doesn't matter whether the partnership includes a witness or has auto or manual failover.

    I'll let you create your web or winform project as you see fit, and therefore have only included the actual C# code from the button click event, but basically you need a form with 3 controls:

    button1 - a button

    textBox1 - a multiline text box

    testBox2 - a text box

    In addition to this you need to create a stored procedure in your mirrored database called 'dbmtest' and get it to fetch something that can be converted to a string. The code from my procedure is also shown at the end, but it's not exactly rocket science!

    Here's a quick summary of what the event does:

    1. When you click the button, it attempts to connect to the server supplied in the connection string and call a stored procedure to get some data.

    2. If it gets the data, it displays it. If it gets a SQL Exception error it displays this.

    3. it attempts to display in a separate text box which server it is connected to, using the SqlConnection.DataSource property.

    4. If you failover your mirror you will receive an error when you click the button

    5. Then re-try by clicking again and you will connect to the other server automatically.

    6. If any other general errors are caught it shows them in the text box.

    Obviously in a real world situation you would make the re-try method somewhat more graceful, but this serves my purposes :-) It's nice sometimes to stick breakpoints in and see what is happening at what stages across the components.

    Here's the code:

    private void button1_Click(object sender, EventArgs e)
                    SqlConnection dbmconn = new SqlConnection();
                    dbmconn.ConnectionString = "server=server1;initial catalog = mymirrordb;failover partner=server2;user=domain\\user;trusted_connection = true";
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = dbmconn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = "dbmtest";

                        SqlDataReader dbreader;
                        dbreader = cmd.ExecuteReader();

                        while (dbreader.Read())
                            textBox1.Text = "Data fetched = " + dbreader[0].ToString();
                            textBox2.Text = dbmconn.DataSource.ToString();
                    catch (SqlException sqlex)
                        textBox1.Text = sqlex.Message.ToString();
                catch (Exception ex)
                    textBox1.Text = ex.Message.ToString();

    Stored Procedure Code

    create procedure dbmtest


    select top 1 blog_data
    from blog


  • SQL in Stockholm

    Starting SQLDIAG or PSSDIAG automatically using SQL Alerts


    This is something I was thinking about this week whilst chatting with a customer. I've done it before on occasions, but never really documented it. Consider the fact that you have a problem which occurs extremely intermittently, let's say on average every 3 months for the sake of argument. Based upon your initial analysis you decide you need to collect a SQLDIAG when the problem occurs, but for reasons specific to your environment, you do not want to employ the long term monitoring capabilities of SQLDIAG, such as "continuous mode" (/L switch), running a service node (/R and /U switches to register or unregister). So the question is that you want to start the collector when a condition is detected, let's say, a blocking chain occurs.

    I've just implemented this in a test system using the following methodology and without installing the SQLDIAG as a service:

    1. Configure your SQLDIAG as per your data capture requirements.

    2. Create a new alert, in this scenario I was looking for blocking chains, so I set it with the following properties

    Type - SQL Server Performance Condition Alert
    Object - ServerName:General Statistics
    Counter - Processes Blocked
    Alert if counter - rises above 5
    Options - delay between responses = 60 minutes (this one is optional but just stops you creating lots of multiple sessions and filling up disk space etc - alternatively you could just set the alert to disabled as soon as it has run once)

    3. For the response to the alert, create a new job

    4. This is a job which will start SQLDIAG from a cmdexec session, name it as such

    5. Create a new step, type = Operating system (cmdexec) - I used the following script

    start /min cmd.exe /C"sqldiag /E +00:20:00 /Q"

    bear in mind that the SQLDIAG directory is in my path variable, so you might alternatively have to type in the full executable location, which considering its length is why I have it as a path variable!

    This string translates as, start sqldiag in quiet mode, meaning that it will automatically overwrite previous output in the default output directory and shut it down after a time span of 20 minutes. You can change the switches you use (of and of course what data you actually collect) using the standard SQLDIAG methodology.

    6. Confirm and save everything and away you go.

    This particular example is easy to test as you can just create a simple blocking chain on a test table, and watch the collector start up. You can open the sqldiag.log or the console.log files from the output directory and watch its progress.

    This is not the only or the definitive way to do this, for example I have seen people register the collector as a service, and then get their monitoring software to issue a net start command. However it's one way to do it and keep it completely within the boundaries of SQL Server.

    (It also works for PSSDIAG if you're working with us using our internal version.)


  • SQL in Stockholm

    Come and meet the Stockholm PSS teams on 17th and 18th March


    Myself and several colleagues from various support teams (not limited to SQL) will be at Microsoft tech days on 17th and 18th March 2009 at Västerås in Sweden. We'll be doing a few mini presentations on the side of the main event, and just hanging out to meet and chat with anyone who wants to come by. If you're going to the event, please drop by and say Hi.

     You can read about the event at www.microsoft.se/techdays


  • SQL in Stockholm

    Is Skiing in Sweden better than SQL Server support?


    Of course it is! So, no posts for a few weeks right now as I'm hanging at home in Stockholm enjoying some family time and general relaxation. It's been below zero for about 3 weeks now here, and the skies have been stunningly clear and beautiful, leading to innumerable amazing sunrises and sunsets. There's no natural snow locally though, just a lot of frozen lakes.

    Therefore on sunday we're all heading for Vemdalen in the center of Sweden for some skiiing fun. I'd like to tell you that I'll be thinking lots about Katmai CU3 and it's upcoming release and other such matters, but in all truth I won't :-)

    Apparently it's gonna be minus 20 when we arrive, so we're getting ready for some real winter fun. Then sometime after the 11th I'm gonna have to start thinking about databases again :-)

Page 1 of 1 (8 items)