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

    Changes to management of 'TokenAndPermUserStore' in SQL 2005 SP3

    • 0 Comments

    SQL Server 2005 service pack 3 released today and you can download it from here:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

    One of the most interesting and useful bits which I am interested in (apart from the bug fixes rolled up from all the cumulative updates) is the new ability to manage to the 'TokenAndPermUserStore' cache with more control. Many people have come across these performance problems, which I won't recount in detail as they are well documented here and here.

    However one of the great new features in SP3 is the ability to control the size and entry count of this cache yourself, much like the feature that was introduced in SQL Server 2008 as part of the sp_configure settings.

    In SQL 2005 SP3 you have to use a trace flag and some registry entries as opposed to sp_configure, but the behavior is much the same, and should allow people to workaround this problem in a less aggressive way than just clearing out the whole cache. The exact details of how to do this are documented here:

    How to customize the quota for the TokenAndPermUserStore cache store in SQL Server 2005 Service Pack 3 - http://support.microsoft.com/kb/959823/

     

     

  • SQL in Stockholm

    SQL Server Database Mirroring error 1443 - Connection Handshake Failed

    • 0 Comments

    Here's another error that I picked from the forums today, which I see a fair number of occurrences of, both in the public forums on the web and internally in MS support as well.

    You attempt to enable database mirroring and you receive the following:

    Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (c0000413) 0xc0000413(error not found). State 67.'.

    Error: 1443, Severity: 16, State: 2.

    If you translate error 0xc0000413 you come up with the following:

    STATUS_AUTHENTICATION_FIREWALL_FAILED

    This may seem slightly strange, as you may not have a firewall in between these servers (well you might but even if you do this is not the problem, as if your firewall was blocking the traffic you would encounter error 1418 normally). But in truth this error comes from somewhere different. It's extremely likely that if you are experiencing this error you are mostly likely running mirroring with one of the following configurations:

    1. Using domain user logins for the service accounts, but different ones across the mirror partners

    2. Using multiple domains, as per the forums poster that I saw today.

    The chances are that you have not explicitly created logins and granted them CONNECT permissions to the endpoints, as per these 2 articles:

    http://msdn.microsoft.com/en-us/library/ms178029(SQL.90).aspx

    http://msdn.microsoft.com/en-us/library/ms179306(SQL.90).aspx

    If you are running in the same domain, with the same domain account as the SQL Server service account, then this is not required, but if you want to do your authentication in a slightly more complex way, then these are the articles for you.

    Alternatively of course you could run mirroring using certificates, if for some reason the above solution didn't fit in with your security policies.

    http://technet.microsoft.com/en-us/library/ms191477(SQL.90).aspx

    Which also is valid if you want to run your service accounts outside of a domain environment.

    Graham

  • SQL in Stockholm

    Database Mirroring Error 1418 Troubleshooter

    • 3 Comments

    I've been posting in the MSDN and TECHNET mirroring forums in the last few days and I've noticed a lot of occurrences of the 1418 error which I have referred to briefly in this blog before. Therefore I thought I'd post a 1418 specific troubleshooter for reference. The problem we're trying to solve is that you try to start database mirroring and you receive this error:

    Msg 1418, Level 16, State 1, Line 1
    The server network address "
    TCP://myserver.domain.domain.domain.com:5022" can not be reached or does not exist. Check the network address name and reissue the command.

    The following assumes that:

    1. You are using separate physical servers for the mirror partners. If you're not, you might this to read this about configuring mirroring on local instances and a typical problem you can come across.

    2. You're using domain accounts for SQL Server services

    3. You're using windows authentication and you're not using certificates

    4. You've already read this, or are familiar with the overall principal.

    So here are the things that I would check:

    1. Have you restored a fairly recent full backup to the planned mirror server, with NORECOVERY. Have you then restored a transaction log, also with NORECOVERY. If you haven't you won't be going anywhere with mirroring. Maybe you should read this first :-)

    2. Have you configured your endpoints correctly. To be specific, this means:

    a) Are they started

    b) Are they using the same form of authentication

    c) Are they using the same form of encryption

    d) Does the service account have permission to use them

    Even if you think or assume you have done this correctly (including if you've used the wizard to configure them) don't assume they are correct. You've encountered an error and you need to be sure. You need to script each endpoint and check that all the settings are correct. I've seen this happen too many times to mention, where people thought they were OK (because they "ought" to have been) but it was something simple like one of them wasn't started. Using the wizard does not guarantee that everything will always work. Remember all the wizard does is execute T-SQL, so mistakes or problems are still possible.

    3. Are the ports you have selected valid, and are they open? Are they being used by any other processes? Can you telnet to the port?

    4. Is there a firewall (hardware or software)? Have you created an exception / rule to the firewall? Windows firewall is no exception to this rule. It will block mirroring traffic just as much as any physical firewall will.

    5. Is the FQDN valid? Can it be resolved? Again, can you telnet to it? If you're not using FQDN and you're using direct IP addresses, the same rules still apply. Can you telnet to it? If you're not using FQDN or IP, have you checked the above post for resolving names locally.

    I hope this helps. It's my experience that these points will solve the majority of 1418 errors. If you have any questions about this topic feel free to post here or in the forums.

    Graham

  • SQL in Stockholm

    More blogs from the SQL Server Support team in Europe

    • 0 Comments

    I just noticed that my colleague João blogs as well. You can find his blog here:

    http://blogs.msdn.com/joaol/default.aspx

     

Page 1 of 1 (4 items)