Welcome to MSDN Blogs Sign in | Join | Help

How to install/enable .Net 3.5 SP1 on Windows Server 2008 R2 for SQL Server 2008 and SQL Server 2008 R2

The .NET Framework 3.5 SP1 (also referred to as .NET Framework 3.5.1) is a prerequisite for SQL Server 2008. SQL Server 2008 Setup for standalone instance will install the .NET Framework 3.5 SP1 if it is not already installed. In Windows Server 2008 R2, the .NET Framework is a feature and installing it is different when compared to older versions of Windows Operating System. In previous versions, one could either download the .NET Framework 3.5.1 install binaries from Microsoft download site or use the install binaries from the redist folder of the SQL Server 2008 installation media. Starting with Windows Server 2008 R2, the method of installing .NET Framework 3.5.1 has changed. This document explains how to verify that .NET Framework 3.5.1 is installed and if it is not installed how you can add it.
  
How to verify if .NET Framework 3.5 SP1 is installed:

Here are the steps to verify that .NET Framework 3.5.1 is installed on Windows Server 2008 R2.

  1. Click the Start button in the lower left hand corner of the display.
  2. Highlight Administrative Tools and select Server Manager.
  3. In the Server Manager interface, click Features to display all the installed Features in the right hand pane. Verify that .NET Framework 3.5.1 is listed.

If .NET Framework 3.5.1 feature is not listed, you can use either of the following methods to install it:

Method 1: Using Server Manager Interface

  1. In the Server Manager interface, select Add Features to displays a list of possible features.
  2. In the Select Features interface, expand .NET Framework 3.5.1 Features.
  3. Once you expand .NET Framework 3.5.1 Features, you will see two check boxes. One for .NET Framework 3.5.1 and other for WCF Activation. Check the box next to .NET Framework 3.5.1 and click Next.
    Note: If you do not expand .NET Framework 3.5.1 Features and check it, you will get a pop-up titled Add Features Wizard  as shown below.
    Click Cancel and expand .NET Framework 3.5.1 Features and then check .NET Framework 3.5.1 check box below it.
     
    You cannot install .NET Framework 3.5.1 Features unless the required role services and features are also installed.
  4. In the Confirm Installation Selections interface, review the selections and then click Install.
  5. Allow the installation process to complete and then click Close.

Method 2: Using PowerShell

  1. Click the Start button in the lower left hand corner of the display.
  2. Highlight All Programs and select Accessories
  3. Expand Windows PowerShell and right click Windows PowerShell and select Run as administrator. Click Yes on the User Account Control box.
  4. At the PowerShell command prompt, type the following commands, and then press ENTER after each command:
    • Import-Module ServerManager
    • Add-WindowsFeature as-net-framework

Note: A screenshot is shown below:

 

Posted by sqlblog | 0 Comments

Creating HTTP endpoint fails with 7850 error.

Creating a HTTP endpoint in SQL Server 2005 or SQL Server 2008 may fail with the following error messages:

Msg 7850, Level 16, State 1, Line 1

The user 'domain\myuser' does not have permission to register endpoint 'training_sql_endpoint' on the specified URL.  Please ensure the URL refers to a namespace that is reserved for listening by SQL.

Msg 7807, Level 16, State 1, Line 1

An error ('0x80070005') occurred while attempting to register the endpoint 'training_sql_endpoint'.

 

The error message is actually incorrect in this context, it instead should report that it is the SQL Service account and also direct you to reserve the namespace explicitly, such as (this is what SQL Server 2008 now has):

The SQL Server Service account does not have permission to register the supplied URL on the endpoint '%.*ls'.  Use sp_reserve_http_namespace to explicitly reserve the URL namespace before you try to register the URL again.

When you run CREATE ENDPOINT to create a HTTP endpoint, this is done under the context of the SQL Server service account.  If the namespace reservation does not already exist, then SQL will implicitly create the reservation.  However, this requires that the SQL Server service account have local administrator privileges on the computer.  If the SQL Service account does not have local administrator, on SQL Server 2005 it will fail with the message noted earlier.

 To resolve this you have two options:

1.        Add the SQL Server service account to the local administrators group, restart and then run the CREATE ENDPOINT again.

2.       Or, explicitly reserve the name space while logged on as a Windows Authentication user that has Local Administrator on the computer and sysadmin on SQL,  *before* you run CREATE ENDPOINT.  For example:

sp_reserve_http_namespace N'http://*:2050/sql/myfolder'

Then when you run the CREATE ENDPOINT, the SQL Service account will not have to reserve the namespace because it already exists, and proceed with creating the endpoint.  Note that when you reserve a namespace explicitly, you need to be sure that the string of the namespace you reserve matches the parameters in the CREATE ENDPOINT statement.  So for the namespace above, the CREATE ENDPOINT would need to look like the following for SQL to match it up correctly:

 CREATE ENDPOINT [myendpoint]

          STATE=STARTED

AS HTTP (PATH=N'/sql/myfolder', PORTS = (CLEAR), AUTHENTICATION = (NTLM, KERBEROS, INTEGRATED), SITE=N'*', CLEAR_PORT = 2050, COMPRESSION=DISABLED) 

              

The following link has more on this in “Identifying the Namespace for an Endpoint” http://msdn.microsoft.com/en-us/library/ms190614.aspx

 

Posted by sqlblog | 0 Comments
Filed under:

Tools of the Trade: Part IV - Developing WinDbg Extension DLLs

A WinDbg extension DLL is set of exported callback functions for implementing user defined commands to extract specific customized  information from the memory dump(s).  Extension DLLs are loaded by debugger engine and can provide extra functionality of automation tasks while performing user-mode or kenrel-mode debugging.  An extension DLL may export any number of functions that are used to execute extension commands.  Each function is explicitly declared as an export in the  DLL's definition file or .def file and function names must be in lowercase letters

WinDbg (DbgEng ) extension DLL must export DebugExtensionInitialize. This will be called when the DLL is loaded, to initialize the DLL. It may be used by the DLL to initialize global variables.

An extension DLL may export an optional function DebugExtensionUninitialize. If this is exported, it will be called just before the extension DLL is unloaded.

An extension DLL may export a DebugExtensionNotify. If this is exported, it will be called when a session begins or ends, and when a target starts or stops executing. These notifications are also provided to IDebugEventCallbacks objects registered with a client.

An extension DLL may export KnownStructOutput. If this is exported, it will be called when the DLL is loaded. This function returns a list of structures that the DLL knows how to print on a single line. It may be called later to format instances of these structures for printing.

So, how to develop   your own Windbg Extension DLL? Lets follow these steps:

1. Download & Install debugging tools for windows from http://www.microsoft.com/whdc/devtools/debugging/installx86.Mspx

2. Create "Win32 Console Application" using VS 2008

3. Select Application type as "DLL" and click "Finish" .

Step 3

4. Add a "Module-Definition File (.def)" called "wdbrowser" to the project. One way to export your extension function is by specifying the function names in the EXPORTS section of the .def file. You may you use other ways of exporting functions, such as __dllexport

Step 4

5. Configure the project "Additional include Directories" to point to header files that comes with Windbg. Default folder for x86 is "C:\Program Files\Debugging Tools for Windows (x86)\sdk\inc"

6. Configure the project "Additional Library Directories" to point to library files that comes with Windbg. Default folder  for x86 libraries  is  ""C:\Program Files\Debugging Tools for Windows (x86)\sdk\lib\i386""

7. The library, "dbgeng.lib " & "dbgeng.dll" has the implementation of the Debug engine exported functions. So, add "dbgeng.lib" in "Additional Dependencies".

8. Add name of the module definition file created at the above Step #3

Step 8

9. Now Include the following required headers files in "stdafx.h"

#include <windows.h>

#include <imagehlp.h>

#include <wdbgexts.h>

#include <dbgeng.h>

#include <extsfns.h>

10. Declare following two global variables in your extension project main implementation file.

//Version.

EXT_API_VERSION g_ExtApiVersion = {1,1,EXT_API_VERSION_NUMBER,0} ;

WINDBG_EXTENSION_APIS ExtensionApis = {0};

11. Declare following debug engine COM interface pointers.

IDebugAdvanced2*  gAdvancedDebug2=NULL;

IDebugControl4*   gDebugControl4=NULL;

IDebugControl*    gExecuteCmd=NULL;

IDebugClient*                 gDebugClient=NULL;

12. Next step is to declare and implement WinDbgExtensionDllInit function in your DLL main implementation source file. In this example that is "wdbrowser.cpp" . The WinDbgExntensionDllInit is the first function that will be called by windbg . So, this function is the idle for implementing any extension specific initialization or related functionality. Please refer http://msdn.microsoft.com/en-us/library/cc267872.aspx for more details about this function.

VOID WDBGAPI WinDbgExtensionDllInit (PWINDBG_EXTENSION_APIS lpExtensionApis, USHORT usMajorVersion, USHORT usMinorVersion)

{

                  ExtensionApis = *lpExtensionApis;

                  HRESULT hResult = S_FALSE;

                        if (hResult = DebugCreate(__uuidof(IDebugClient), (void**) &gDebugClient) != S_OK)

                         {

                                                dprintf("Acuqiring IDebugClient* Failled\n\n");

                                                return;

                         }

                         if (hResult = gDebugClient->QueryInterface(__uuidof(IDebugControl), (void**) &gExecuteCmd) != S_OK)

                         {

                                        dprintf("Acuqiring IDebugControl* Failled\n\n");

                                                return;

                         }

                         if (hResult = gDebugClient->QueryInterface(__uuidof(IDebugAdvanced2), (void**) &gAdvancedDebug2) != S_OK)

                         {

                                              dprintf("Acuqiring IDebugAdvanced2* Failled\n\n");

                                                return;

                         }

                         if (hResult = gDebugClient->QueryInterface(__uuidof(IDebugControl4), (void**) &gDebugControl4) != S_OK)

                         {

                            dprintf("Acuqiring IDebugControl4* Failled\n\n");

                                                return;

                         }

}

13. Declare another exported function ExtensionApiVersion to report the version of your extension to windbg. Please refer to http://msdn.microsoft.com/en-us/library/cc267873.aspx for detailed information about this function.

LPEXT_API_VERSION WDBGAPI ExtensionApiVersion (void)

{

    return &g_ExtApiVersion;

}

14. Define Debug engine's interface pointers, so that your extension module can interact with debug engine. For more information please refer

http://msdn.microsoft.com/en-us/library/cc265976.aspx  - IDebugClient, http://msdn.microsoft.com/en-us/library/cc266102.aspx - IDebugControl

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

IDebugAdvanced2* gAdvancedDebug2=NULL;

IDebugControl4* gDebugControl4=NULL;

IDebugControl* gExecuteCmd=NULL;

IDebugClient*               gDebugClient=NULL;

15. Next step is to - implement debug engine's callback interface IDebugOutputCallbacks . Debug engine callbacks your implementation of IDebugOutCallbacks::Output() with output as a result of the commands that are executed by your extension function.

Refer to http://msdn.microsoft.com/en-us/library/cc265716.aspx   for detailed information about IDebugOutputCallbacks::Output()

16. Add the following new class in a header file that inherits the IDebugOutputCallbacks interface .

#ifndef __OUT_HPP__

#define __OUT_HPP__

#include <string>

#include <sstream>

class StdioOutputCallbacks : public IDebugOutputCallbacks

{

private:

                        std::string m_OutputBuffer;

                        //

                        //This buffer holds the output from the command execution.

                        //

                        CHAR m_OutPutBuffer[4096];

public:

                        void InitOutPutBuffer();

                        std::string GetOutputBuffer()

                        {

                                                return m_OutputBuffer;

                        };

                        void ClearOutPutBuffer()              

                        {

                                                m_OutputBuffer = "";

                        };

    STDMETHOD(QueryInterface)(

        THIS_

        IN REFIID InterfaceId,

        OUT PVOID* Interface

        );

    STDMETHOD_(ULONG, AddRef)(

        THIS

        );

    STDMETHOD_(ULONG, Release)(

        THIS

        );

    // IDebugOutputCallbacks.

    STDMETHOD(Output)(

        THIS_

        IN ULONG Mask,

        IN PCSTR Text

        );

};

extern StdioOutputCallbacks g_OutputCb;

#endif // #ifndef __OUT_HPP__

17. Add the following code that implements the IDebugOutputCallbacks interface methods, especially Output()

#include "stdafx.h"

#include <stdio.h>

#include <windows.h>

#include <dbgeng.h>

#include "OutputCallBack.h"

StdioOutputCallbacks g_OutputCb;

STDMETHODIMP

StdioOutputCallbacks::QueryInterface(

    THIS_

    IN REFIID InterfaceId,

    OUT PVOID* Interface

    )

{

    *Interface = NULL;

    if (IsEqualIID(InterfaceId, __uuidof(IUnknown)) ||

        IsEqualIID(InterfaceId, __uuidof(IDebugOutputCallbacks)))

    {

        *Interface = (IDebugOutputCallbacks *)this;

        AddRef();

        return S_OK;

    }

    else

    {

        return E_NOINTERFACE;

    }

}

STDMETHODIMP_(ULONG)

StdioOutputCallbacks::AddRef(

    THIS

    )

{

    // This class is designed to be static so

    // there's no true refcount.

    return 1;

}

STDMETHODIMP_(ULONG)

StdioOutputCallbacks::Release(

    THIS

    )

{

    // This class is designed to be static so

    // there's no true refcount.

    return 0;

}

STDMETHODIMP

StdioOutputCallbacks::Output(

    THIS_

    IN ULONG Mask,

    IN PCSTR Text

    )

{

    UNREFERENCED_PARAMETER(Mask);

                        m_OutputBuffer += Text;

    return S_OK;

}

void StdioOutputCallbacks::InitOutPutBuffer()

{

                        m_OutputBuffer.erase();

}

18. Add implementation of your extension function. In this example, we choose to implement an extension that displays the variable names, types in the frame 2 of the current thread. The implementation is:

DECLARE_API (dvf3)

{

//

// Install output callbacks.

//

if ((gDebugClient->SetOutputCallbacks((PDEBUG_OUTPUT_CALLBACKS) &g_OutputCb))!= S_OK)

{

dprintf("*****Error while installing Outputcallback.*****\n\n");

return;

}

//

// Since frame count starts from 0 index, we have to pass 2 as parameter for .frame command for the frame# 2

//

//Execute command to extrac 2nd frame.

if (gExecuteCmd->Execute(DEBUG_OUTCTL_THIS_CLIENT | //Send output to only outputcallbacks

DEBUG_OUTCTL_OVERRIDE_MASK |

DEBUG_OUTCTL_NOT_LOGGED,

".frame 2",

DEBUG_EXECUTE_DEFAULT ) != S_OK)

{

dprintf("Executing .frame 2 failled\n");

return;

}

//Execute command to extrac 2nd frame.

if (gExecuteCmd->Execute(DEBUG_OUTCTL_THIS_CLIENT | //Send output to only outputcallbacks

DEBUG_OUTCTL_OVERRIDE_MASK |

DEBUG_OUTCTL_NOT_LOGGED,

"dv /i /t /v",

DEBUG_EXECUTE_DEFAULT ) != S_OK)

{

dprintf("Executing dv /i /t /v failled\n");

return;

}

dprintf("***** Extracting locals & formal params from frame 2 *****");

dprintf("\n%s\n", g_OutputCb.GetOutputBuffer().c_str());

}

19. Re-build the project. Copy the .DLL from release folder to a folder where Windbg looks for extension DLLs.

On x86 machine default location is  "<Drive letter>\Program Files\Debugging Tools for Windows (x86)\winext"

20. The extension is ready for the use or test.

21. Start windbg and open a full user mode dump. Type .load myextension and hit enter to load the extension DLL into Windbg process space

Step21

22. Run .chain command to verify if your extension is loaded by WinDbg . you will see output similar to below output, if your extension is loaded.

23. Type !dvf3 to run the extension function for extracting and displaying variable names, types from the frame 2 .

Step23

Additional references:

http://msdn.microsoft.com/en-us/library/cc265826.aspx - describes about how to Interact with debug engine, I/O operations with debug engine, Memory access, Using symbols, source files.

http://www.codeplex.com/ODbgExt  - Microsoft Open Debugger Extension for Windbg

Happy developing debug engine extensions!

Posted By: Srini Gajjela & Enamul Khaleque (DSD-SQLDeveloper group @ Microsoft)

Posted by sqlblog | 0 Comments

How to connect to file-based data sources (Microsoft Access , Microsoft Excel and Text files ) from a 64 bit application

 

The Issue:

A 64-bit process can load only 64-bit components in it's process boundary. Same is true for a 32-bit process also. So, if your application is 64 bit, you will need a 64-bit provider or driver to connect to  Microsoft Access (mdb, accdb) or Microsoft Excel 2010 (xls, xlsx, and xlsb) or  text files. Bad news is that there is no 64-bit provider or driver available "yet" to connect to these file-based data sources. Good news is that a 64-bit Provider is heading your way which is currently in beta phase.

 

The Kludge:

The common workaround is to connect to a 32-bit SQL Server instance that has a Linked Server to the Access/Excel/Text file. This is a hack, can be difficult to get set-up, and can have stability and performance issues, and realistically, we at Microsoft would rather not support this setup or issues arising from it.

 

The Good news:

 A 64-bit driver is headed your way. This is great news for users in a 64-bit world. Soon you'll be able to connect to these file-based data sources from your 64-bit application, rather than wrestle with obscure settings to force them to connect via a Linked Server.

 

The next version of Microsoft Office, Office 2010, will be available in a 64-bit version. This version will include a 64-bit version of "2010 Office System Driver Connectivity Components" which will include all the needed 64-bit ODBC driver and OLEDB providers to connect to these file-based data sources.

 

You will not have to buy or install Office 2010 to obtain and use the new 64-bit components. Like the current version of the provider, it will be available as a free download.

 

You can download the beta version from here:

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

 

Connection string for 64-bit OLEDB Provider:

  • For Microsoft Office Access : Set the Provider string to “Microsoft.ACE.OLEDB.12.0"
  • For Microsoft Office Excel   : Add “Excel 12.0” to the Extended Properties of the OLEDB connection string.

 

Connection string for 64-bit ODBC Driver:

  • For Microsoft Office Access: Set the Connection String to “Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file”
  • For Microsoft Office Excel: Set the Connection String to “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=path to xls/xlsx/xlsm/xlsb file”

 

The gotchas:

  • You cannot install the 32-bit version and the 64-bit version of the "2010 Office System Driver Connectivity Components" on the same computer.
  • You cannot install the 64-bit version of the "2010 Office System Driver Connectivity Components" on a computer that already has the 32-bit Office 2007 ACE Provider. However, the 32-bit Office 2007 provider can coexist side-by-side with the 32-bit version of the "2010 Office System Driver Connectivity Components".

 

Authors:  Enamul Khaleque & Srini Gajjela [DSD-SQLDeveloper group at Microsoft]

 

SQL Server 2005 setup fails when MSXML Core Services 6.0 Service Pack 2 has already been installed

 

There is a known issue with SQL Server setup when MSXML6 component update has been installed on the system. The problem described in

KB 968749 http://support.microsoft.com/kb/968749 has raised a lot of concerns by customers. The concerns are related to the fact that the solution is manual and not usable in a Large Enterprise environments. In order to automate MSXML6 component un-install we have created the automatic solution for this issue. However the solution needs to be implemented on a case by case basis.

 

If you are experiencing the issue described in KB 968749 and need an automated solution for this, please contact SQL CSS.

Please see details on how to open the incident here http://support.microsoft.com/

The incident for this specific issue is going to be free of charge.

 

We apologize for any inconvenience.

 

SQL CSS.

 

Follow us on twitter (http://twitter.com/MicrosoftSQLCSS)

Posted by sqlblog | 0 Comments

How to configure SQL server to listen on different ports on different IP addresses?

Technorati Tags:

The following post describes how you can configure your SQL Server to listen on a different port(s) on different IP addresses that are available on your system. This procedure applies to both SQL server 2005 and SQL server 2008.

Case 1: SQL Server is installed in an environment where the IP addresses had not changed since the time it is originally installed.

1) Open SQL Server configuration manager.

2) Ensure that TCP/IP protocol is enabled

image

By default, all the IP addresses listen the same port or ports configured in SQL Server configuration manager. The SQL Server error log will have an entry like the following:

2009-07-15 17:40:06.39 Server      Server is listening on [ 'any' <ipv4> 2675].

3) In the TCP/IP properties set ‘Listen All’ to ‘No’

image

4) Go to the IP addresses tab for the instance, set Enabled to Yes and TCP port number field for the specific IP address to the desired

port. The screen will look as follows:

image

5) Restart the SQL Server. Now you should see an entry similar to the following in the SQL error log:

2009-07-15 18:03:10.58 Server      Server is listening on [ x.y.z.w <ipv4> 2000].
2009-07-15 18:03:10.59 Server      Server is listening on [ x.y.z.v <ipv4> 2001].

As you can see from the above each of the IP addresses is listening on different port.

Case 2: SQL Server is installed in an environment where the IP addresses change dynamically, but the number of IPs active on the system are the same (For example there are two IPs active on the system, but because of lease expiry or when moving to a new subnet, the system hosting SQL Server got either one or both of its IPs changed). In this case, get the output of ipconfig /all on the system, and edit one or all the IP addresses as needed with the new IP addresses that are active on the system using a similar procedure discussed in Case 1.

Case 3: You add an additional IP address on the system:

In that scenario, you will not be able to use the procedure documented in Case 1  or Case 2 above as the Configuration Manager’s IP address list will just only have as many entries as the number of IPs that SQL Server found when it is installed

In this scenario, you can take the following steps to update the registry values SQL server looks at to listen on different ports on different IP addresses.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk. . For more information about how to back up, restore, and modify the registry, click the following article number to view the article in the Microsoft Knowledge Base:

322756 How to back up and restore the registry in Windows

1) Navigate to the following registry key on the SQL server machine:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\!INSTANCEID!\MSSQLServer\SuperSocketNetLib\Tcp\

Note: !INSTANCEID! is a place holder for your SQL server instance.

2) Right click on IP1, export the registry key as SQLIP template.reg

3) Edit the  key name and IP address key .reg file that you exported in step 2 with notepad with the new IP address. (You can get the IP address list on the system by executing ipconfig /all > ipconfig.txt command from the command prompt).

The contents would look as follows:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL10\MSSQLServer\SuperSocketNetLib\Tcp\IP3]—> Change this name to a new value for example IP4
"Enabled"=dword:00000000
"Active"=dword:00000001
"TcpPort"="2001"
"TcpDynamicPorts"=""
"DisplayName"="Specific IP Address"
"IpAddress"="a.b.c.d" –> Update this with new IP address value 

4) After editing the file save it with a different name – for example new IP4.reg

5) Double click the .reg file from step 3 to import the key as a new entry under [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQL10\MSSQLServer\SuperSocketNetLib\Tcp] registry entry.

6) Repeat steps (3),(4) and (5) for any other new IP addresses that you currently want to configure on the system.

Note: After adding the above registry keys, the new IP addresses should now show up in SQL server configuration manager.

7) Optional: Clean up any IPs that are no longer active by deleting the associated <IP_n> registry keys.

8) In SQL Server configuration manager, IP addresses tab, ensure that only the addresses that are listed in the ipconfig output on the system have Enabled property set to Yes and the other set to No

image

Note: If IP All is set to No and if the IP addresses tab has some IP addresses that have ‘Enabled’ set to ‘Yes’ but the actual IP is no longer active on the system, SQL Server service fails to start with an error message like the following logged to SQL Server error log:

2009-07-16 15:43:07.87 Server      Server is listening on [ 127.0.0.1 <ipv4> 2753].
2009-07-16 15:43:07.89 Server      Error: 26024, Severity: 16, State: 1.
2009-07-16 15:43:07.89 Server      Server failed to listen on x.y.z,w <ipv4> 2000. Error: 0x2741. To proceed, notify your system administrator.
2009-07-16 15:43:07.95 Server      Error: 17182, Severity: 16, State: 1.
2009-07-16 15:43:07.95 Server      TDSSNIClient initialization failed with error 0x2741, status code 0xa. Reason: Unable to initialize the TCP/IP listener. The requested address is not valid in its context.

2009-07-16 15:43:07.95 Server      Error: 17182, Severity: 16, State: 1.
2009-07-16 15:43:07.95 Server      TDSSNIClient initialization failed with error 0x2741, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. The requested address is not valid in its context.

2009-07-16 15:43:07.95 Server      Error: 17826, Severity: 18, State: 3.
2009-07-16 15:43:07.95 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2009-07-16 15:43:07.95 Server      Error: 17120, Severity: 16, State: 1.
2009-07-16 15:43:07.95 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Case 4: SQL Server is installed in a clustered environment.

On cluster, you cannot configure SQL Server to listen on a specific IP addresses. You must chose IPALL. The IP addresses on which the cluster instance will be listening on is determined by cluster resources (configurable through Cluster Administrator, by adding IP Address resources under SQL Network Name resource).

Additional links:

 

 

 

 

Ramu Konidena

Microsoft SQL Server Support Technical Lead

Posted by sqlblog | 0 Comments
Filed under:

Dealing with very large SQL Compact database files

Working with very large SQL Compact files, you may run into two issues:

 

1.     Manipulating a large SQL Compact database in VS 2008 IDE requires a hotfix

 

http://support.microsoft.com/kb/968436

 

Error message when you use the Visual Studio 2008 IDE to manipulate a SQL Server Compact 3.5 Database file which is larger than 128 Megabytes: "The database file that is larger than the configured maximum database size"

 

2.     Upgrading a very large database from SQL Compact 3.1 to 3.5 requires another hotfix

http://support.microsoft.com/kb/971027

FIX: Error message when you upgrade a very large database to SQL Server Compact 3.5: "The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only"

Hotfix 3.5.5692.12 fixes a problem where large SQL CE database upgrade fails with an error:

The database file is larger than the configured maximum database size. This setting takes effect on the first concurrent database connection only. [ Required Max Database Size (in MB; 0 if unknown) = <size>]

Depending upon the file-size and available resources on the machine, the upgrade process for a very large database may consume significant memory.  This is expected behavior.   You may want to stop other applications to make room for the Upgrade process to complete.

Performance: Tips for faster download of a large SQL Compact 3.5 database via Replication

SQL Compact 3.x Replication initial subscription is resource-intensive and may take significant time on devices.  Read the introduction to the SQL Compact Blog for this problem.

 

Introduction (from SQL Compact Blog)

 

“The initial subscription is going to bring down lot of data from server to client in merge replication.  It means that the requirement for bandwidth, memory, processing power is going to be high for the creation of subscription.  However, for devices all these requirements are not easy and even if they are available, it takes very long time to get the subscription created on the client database.”

 

Symptoms:

SQL Compact 3.5 Replication initial download for certain large publication takes a significant amount of time on mobile devices.  The corresponding publication with earlier versions (SQL CE 2.0 and SQL 2000) did not take that much time.

 

Performance Tips:

Here is a list of workarounds which have a potential to reduce the download time for large initial sync. Please keep in mind that choosing a single option may not resolve the issue completely.   However, a combination of several options may bring the download time to an acceptable level.


-
Remove unnecessary indexes from the server side (articles), especially indexes on Varchar columns

This helps to a certain extent; however the overall download time may still be high.


- Apply hotfix to turn off
UpdateStatistics:

 

The Client Agent log during download indicates that a portion of time was spent in executing update statistics.  After applying a new hotfix release, the application may invoke a new method call to turn off update statistics.

 

PostSyncCleanup to turn off UpdateStatistics during initial download of SQL Compact Replication

 

KB http://support.microsoft.com/kb/963060

FIX: An error message is logged, and the synchronization may take a long time to finish when you synchronize a merge replication that contains a SQL Server Compact 3.5 subscriber



- Turn off unnecessary
logging (Client Agent Log, other types of device logging) on production devices.


- Move to a
high-end device, where one can use higher buffer pool sizes and can also place the database .sdf file in main memory as appropriate.

-
Pre-create the .sdf on desktop and then download to devices. 

This relates to managing and deploying the solution to n number of devices in the field.   You may use the move-copy detection feature of SQL Compact on devices.   For more details on how it works, please see the SQL Compact team’s blog entry “"Merge Subscribe Once, Copy and Deploy Multiple Times" at http://blogs.msdn.com/sqlservercompact/archive/2007/06/26/merge-subscribe-once-copy-and-deploy-multiple-times.aspx

- Increase buffer pool size:
We got some real good benefit in terms of running times by
increasing buffer pool size to higher values.  By default this value is 640K on a device.  The running times for a very large single-table publication with Max Buffer Size  of 640K, 1024K, 2048K, and 4096K were as follows:

1) 640K  -  86 m 34 secs
2) 1024K - 73 m 56 secs
3) 2048K - 55 m 13 secs
4) 4096K - 38 m 30 secs


You can specify ‘Max Buffer Size’ in the connect string, as follows:
repl.subscriberconnectionstring = "provider=MICROSOFT.SQLServer.OLEDB.CE.3.5;data source=35Repl.sdf;SSCE:Max Buffer Size=4096";

Keep in mind that this setting consumes more program memory on the device.  However, this should not be an issue if you have a good 20 MB of program memory available. If the initial sync data is huge, you may set the buffer pool size to a conveniently higher value only for initial sync.

 

There are real improvements in running times with bigger buffer pool sizes.    However, certain devices can't increase buffer pool size beyond 2-3 MB, so, a very large buffer-size is not always applicable.

 

Fix for rowguid index processing delays:

 

In some cases, the delay is due to rowguid index processing, please look for the hotfix KB 970414 when it is available.

 

 

SQL Compact Performance: PostSyncCleanup to turn off UpdateStatistics during initial download of SQL Compact Replication

SQL Compact Replication initial subscription may take significant time on devices.   The UpdateStatistics operation adds additional overhead to the initial download. 

 

Indicators:

The Client Agent log may show the start and end times of UpdateStatistics operation.  For example:

SQLCECA: 04/09/2009-12:21:26 UpdateStatistics Start app=<appName>.exe

SQLCECA: 04/09/2009-12:27:59 UpdateStatistics End hr=0 app=<appName>.exe

 

Workaround:

 

Recent hotfixes for SQL Compact 3.5 and 3.1 (KB article URLs given below) expose a new API for SQL Compact applications to turn off UpdateStatistics during initial download of Replication.

 

Disabling UpdateStatistics can be done only on the merge replication, and not for RDA operations. One should use,

 

SqlCeReplication repl = new SqlCeReplication();

repl.PostSyncCleanup = 2;

 

to make sure that update stats is not called after merge replication sync. 

 

To use the new method in your SQL Compact app, in addition to applying the hotfix to device, you will also need to deploy the updated System.Data.SqlServerCe.dll  to the desktop folder where VS can find it, else an error will occur:

 

'PostSyncCleanup' is not a member of 'System.Data.SqlServerCe.SqlCeReplication'.

 

You can follow these steps to resolve the above error:

 

Extract SYSTEM~1.004 file from the device cab, rename it to “ System.Data.SqlServerCe.dll ” and replace at the appropriate path.

 

When working with SQL Server Compact 3.5 (Visual Studio 2008):

“C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Devices\System.Data.SqlServerCe.dll”

 

If your project refers to “System.Data.SqlServerCe.dll” in another location then replace the DLL at that location as well. 

 

Once the modified app runs on the device, if the hotfix bits were not deployed you may see the following error (as expected):

 

Method not found: set_ PostSyncCleanup

System.Data.SqlServerCe.SqlCeReplication”

 

Uninstalling SQL Compact 3.5 SP1 RTM and installing the hotfix .cab files should resolve the above error.

 

References:

 

http://support.microsoft.com/kb/963060

 

http://support.microsoft.com/kb/960142

 

Posted by:  Syed

Class not registered error after restoring an Analysis Services database that uses the SQL Server 2005 SQL Native Client.

After restoring an Analysis Services database that uses the SQLNCLI.1 SQL Native Client (SNAC) on a server which has never had SQL Server 2005 installed, an error similar to the following occurs during processing operations:
Internal error: The operation terminated unsuccessfully.
Internal error: The operation terminated unsuccessfully.
OLE DB error: OLE DB or ODBC error: Class not registered.
Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'Adventure Works DW'.
Errors in the OLAP storage engine: An error occurred while processing the 'Internet_Sales_2001' partition of the 'Internet Sales' measure group for the 'Adventure Works' cube from the Adventure Works DW database.
Server: The operation has been cancelled.
 
During execution of queries that access data contained in ROLAP storage, an error message similar to the following is returned:
Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Adventure Works DW', Name of 'Internet Sales Order Details' was being processed.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Adventure Works DW', Name of 'Adventure Works DW'.

OLE DB error: OLE DB or ODBC error: Class not registered.

Server: The operation has been cancelled.

 

Opening the Property Sheet for the Data Source from SQL Server Management Studio (SSMS) or the Data Source Designer in Business Intelligence Development Studio (BIDS) and attempting to edit the connection string returns the following error from Connection Manager:

The specified provider is not supported. Please choose different provider in connection manager

 

Checking the connection string from either SSMS or BIDS, it is similar to the following:

Provider=SQLNCLI.1;Data Source=MyServer;Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorksDW

 

Changing the connection string to use the native OLE DB provider, similar to the following:

Provider=SQLOLEDB.1;Password=Olapolap1;Persist Security Info=True;User ID=sa;Initial Catalog=AdventureWorksDW;Data Source=MyServer

 

Likewise, modifying the connection string to use the SQL Server 2008 SQL Server SNAC, similar to the following.

Provider=SQLNCLI10.1;Integrated Security="";Persist Security Info=False;User ID=sa;Initial Catalog=AdventureWorksDW;Data Source=MyServer;Initial File Name="";Server SPN=""

 

The issue occurs because the SQLNCLI.1 provider does not ship with SQL Server 2008 and the provider name for SNAC connections changed in SQL Server 2008. The SNAC Version Provider names for SQL Server 2005 and SQL Server 2008 are: 

SQL Server 2005           SQLNCLI.1

SQL Server 2008           SQLNCLI10.1

 

To workaround this issue, any of the following options can be used:

  1. Edit the Data Source(s) for the database and switch from the SQLNCLI.1 provider to the SQLOLEDB.1 provider
  2. Edit the Data Source(s) for the database and switch from the SQLNCLI.1 provider to the SQLNCLI10.1 Native Client provider.
  3. Download and install the SQL Server 2005 SQL Native Client from the following link: http://www.microsoft.com/downloads/details.aspx?FamilyID=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&DisplayLang=en

 

John Desch

Microsoft SQL Server Escalation Engineer

After Installation of .NET Framework 3.5 SP1 (or .Net framework 2.0 SP2) , SSIS packages using ODBC 3rd party drivers may fail

The Issue:

In the last few months, we have seen quite a few cases where after installing .net framework 3.5 SP1 ( KB http://support.microsoft.com/kb/951847), SSIS packages using certain ODBC 3rd party drivers and importing character data may fail with the error below -

Error Message:

[DataReader Source [<number>]] Error: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "DataReader Source" (<number>)" failed because error code 0x80131937 occurred, and the error row disposition on
"output column "ColumnName" (<number)" specifies failure on error. An error occurred on the
specified object of the specified component. There may be error messages posted
before this with more information about the failure.

The error messages may vary depending on if you are using DataReader Source in Visual Studio 2005 or ADO .Net Source in Visual Studio 2008 –

[ADO NET Source [<number>]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "ADO NET Source" (<number>)" failed because error code 0x80131937 occurred, and the error row disposition on "output column "ColumnName" (<number>)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

Error 0xc0209029: Data Flow Task: The "component "<ComponentName>" (<number>)" failed because error code 0x80131937 occurred...

[ADO NET Source [<number>] Error: The component "ADO NET Source" (<number>) was unable to process the data. Pipeline component has returned HRESULT error code 0xC0209029 from a method call.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "ADO NET Source" (<number>) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

NOTE: Not all ODBC drivers are affected by this problem. This problem affects primarily SSIS packages which import character data using certain 3rd party ODBC drivers, through the use of ADO.Net connection manager and System.Data.ODBC managed provider.

In Visual Studio 2005: SSIS DataReader Source --> ADO.Net connection manager --> .Net Providers \ ODBC Data Provider (System.Data.Odbc) --> Vendor ODBC driver --> Character data

In Visual Studio 2008: ADO .Net Source --> ADO.Net connection manager --> .Net Providers \ ODBC Data Provider (System.Data.Odbc) --> Vendor ODBC driver --> Character data

Cause:

The problem started with .Net Framework 2.0 SP2 after a change was made in the way OdbcDataReader checks for Nulls in Sequential Access mode, calling SQLGetData with a BufferLength argument of 0 for variable width data types.  Certain ODBC drivers are not compatible with this pattern. For more information on this function and its arguments, see: SQLGetData Function http://msdn.microsoft.com/en-us/library/ms715441.aspx

.Net framework 2.0 SP2 is also included .Net framework 3.5 SP1 components.

Microsoft is still investigating the issue and understands the difficult impact that this change has on customers. Determining the full impact of the issue (for example, what are all the data types that can cause the issue etc) is still a work in progress.

You can track the progress from the connect site below.

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=416455

In the meantime, we suggest that you use one of the workarounds mentioned below.

Workaround:

Workaround#1: (recommended)

Modify the SSIS package to redirect the DataReader (or ADO .Net Source in Visual Studio 2008) Error Output to a text file, thereby allowing the package to succeed even though no rows are written out to the error output file.

A. Go to Data Flow Task.

B. On DataReader Source Properties-> ‘Input and Output Properties’ Tab -> DataReader OutPut -> Output columns

azim1

C. Select the columns (only with character data type) and change the ‘ErrorRowDisposition’ and TruncationRowDisposition’ properties to RD_RidectRow

azim2

D. Redirect the DataReader Error Output to a text file

E. Repeat A-D for all Data Flow Tasks that fail with the above error.

azim3

Workaround#2:

If you need a quick fix (because this is a production environment and you cannot afford to modify SSIS packages at this time) – you may need to uninstall the components of .Net Framework 3.5 SP1 or .Net framework 2.0 SP2, depending on what framework version you installed that caused the issue.

a. If your issue is caused by installing only .Net framework 2.0 SP2 (for example, from a Microsoft download site http://www.microsoft.com/downloads/details.aspx?familyid=5B2C0358-915B-4EB5-9B1D-10E506DA9D0F&displaylang=en), then you can uninstall .Net framework 2.0 SP2.

b. If your issue is caused by installing .Net framework 3.5 SP1 (KB http://support.microsoft.com/kb/951847), Remove the .Net Framework 3.5 SP1 components and bring the system back to the state it was in before the update.  This process involves more than simply uninstalling .Net Framework 3.5 SP1, as discussed in the following blogs:

http://blogs.msdn.com/dougste/archive/2008/08/19/uninstalling-net-framework-3-5-sp1.aspx

http://blogs.msdn.com/astebner/archive/2008/08/01/8803442.aspx

Posted By: Azim Uddin & Enamul Khaleque

Posted by sqlblog | 2 Comments

Plan guides (plan freezing) in SQL Server 2005/2008

 

SQL Server 2005

 

The plan guides were first introduced in SQL Server 2005.You may find detailed information about the plan guides in SQL Server 2005 here:

 

General information

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

 

 Description of sp_create_plan_guide

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

 

This feature can be useful when you are trying to enforce the optimizer using a specific execution plan without changing the original query.

In SQL Server 2005, however,  you have to specify the statement exactly as it was submitted to the SQL Server. Sometimes it can be difficult

since you have to capture a SQL Profiler trace and copy the SQL statement from there. Also there are certain rules that have to be applied.

For example, the SQL parser does ignore spaces and escape characters inside the query text, but does not allow it at the end, so it may add

complexity to capturing the actual statement.

 

In SQL Server 2005 query statistics will appear in DMVs including the T-SQL statement. In order to make it easier you may create a plan guide

based on query statistics DMVs directly.

 

Here is the script that demonstrates this.

 

NOTE: Before you execute the examples below please make sure you have the  AdventureWorks database installed and the compatibility level for it  set to 90.

clip_image002[15]

If you don’t have the AdventureWorks database you can download it from:

http://www.codeplex.com/SqlServerSamples

 

 

 

use AdventureWorks

go

 

--- Cleaning cache for this sample

dbcc freeproccache

go

 

--- Running query first time to get plan generated for freezing

set statistics xml on

exec sp_executesql  

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

go

 

--- Based on query pattern creating a plan guide - freezing plan

declare @sql_text nvarchar(max),

        @sql_xml_plan nvarchar(max)

 

select

@sql_text=

    substring(sqt.text, (qs.statement_start_offset/2)+1,((CASE
    qs
.statement_end_offset WHEN -1 THEN DATALENGTH(sqt.text) ELSE  
    qs
.statement_end_offset END - qs.statement_start_offset)/2) + 1),

@sql_xml_plan =     

    convert(nvarchar(max),sqp.query_plan)

from sys.dm_exec_query_stats qs

       cross apply sys.dm_exec_sql_text(qs.sql_handle) sqt

       cross apply sys.dm_exec_query_plan(qs.plan_handle) sqp

where text like '%Employee%'

 

if @sql_text<>''

begin

       select @sql_text, @sql_xml_plan

       set @sql_xml_plan = 'OPTION(USE PLAN '''+@sql_xml_plan+''')'

       exec sp_create_plan_guide @name =N'MyPlan_Guide_1'

           , @stmt = @sql_text

           , @type = N'SQL'

           , @module_or_batch = NULL

           , @params = NULL

           , @hints = @sql_xml_plan

end

 

 

You may check the plan guide is created by querying sys.plan_guides catalog view

 

select * from sys.plan_guides

 

 

Now execute the query again.

 

--- This time we will see USEPLAN=1 and plan guide name in XML plan output

set statistics xml on

exec sp_executesql  

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

 

Click on ShowPlanXML hyperlink

clip_image002[17]

 

clip_image002[19]

SQL Server Management Studio 2005 will show you XML. Look at <StmtSimple> and <QueryPlan> tags.  

As you can see SQL Server picked the created plan guide and USEPLAN option.

 

WARNING! You should be careful using the plan guides in SQL Server 2005. In case if metadata and/or

data distribution has been changed the optimizer will not be able to use the plan guide anymore and the

query will fail with the following error:

 

 

Msg 8698, Level 16, State 0, Line 1

Query processor could not produce query plan because USE PLAN hint contains plan that could not be

verified to be legal for query. Remove or replace USE PLAN hint. For best likelihood of successful

plan forcing, verify that the plan provided in the USE PLAN hint is one generated automatically by

SQL Server for the same query.

 

 

To demonstrate this, disable the existent index that is used in the plan guide above

 

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE

GO

 

and try running the query again.

 

set statistics xml on

exec sp_executesql  

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

 

 

To clean up your server after this demonstration:

 

--- Rebuild disabled index to enable it

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD

GO

 

--- Drop plan guide

EXEC sp_control_plan_guide N'DROP',N'MyPlan_Guide_1';

 

 

SQL Server 2008

 

In SQL Server 2008 plan guides feature has been improved.

In addition to sp_create_plan_guide you can also use the sp_create_plan_guide_from_handle stored procedure

 

Understanding plan guides

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

 

sp_create_plan_guide_from_handle

http://technet.microsoft.com/en-us/library/bb964726.aspx

 

So now you can create a plan guide based on the actual plan handle without pulling the T-SQL statement text

 

Here is the example:

 

use AdventureWorks

go

 

--- Cleaning cache for this sample

dbcc freeproccache

go

 

--- Running query first time to get plan generated for freezing

set statistics xml on

exec sp_executesql  

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

go

 

 

--- Based on query pattern creating a plan guide - freezing plan

declare @plan_handle varbinary(1000)

 

select @plan_handle = plan_handle

from sys.dm_exec_query_stats qs

       cross apply sys.dm_exec_sql_text(qs.sql_handle) sqt

where text like '%Employee%'

 

select @plan_handle

 

exec sp_create_plan_guide_from_handle 'MyPlan_Guide_1', @plan_handle=@plan_handle

 

 

As you can see creating the plan guide is easier now,  and you may also easily copy and paste the plan handle from the

DMV output and manually pass it to sp_create_plan_guide_from_handle

 

Run the query again

 

set statistics xml on

exec sp_executesql  

N'SELECT COUNT(*) FROM [HumanResources].[Employee] e INNER JOIN [Person].[Contact] c ON c.[ContactID] = e.[ContactID]'

set statistics xml off

go

 

 

Click on ShowPlanXML hyperlink

clip_image002[17]

 

In SQL Server 2008 SQL Server Management studio will take you directly to the graphic execution plan automatically

 

Then right click on the execution plan page and choose Properties

clip_image002[21]

 

To clean up your server after this demonstration:

 

--- Drop plan guide

EXEC sp_control_plan_guide N'DROP',N'MyPlan_Guide_1';

 

 

Another difference in SQL Server 2008 from SQL Server 2005 is with the optimizer behavior  if the metadata has been changed.

If this is the case, then the plan guide can not be used anymore.  The SQL Server 2008 optimizer will silently skip the plan guide

and continue with query execution with whatever execution plan is the best. This makes the use of plan guides more robust and

less dangerous than in SQL Server 2005.

 

To monitor the plan guides behavior you may use two new SQL Profiler events in SQL Server 2008

clip_image002[23]

 

Posted by: Sergey Pustovit – SQL Server Escalation Services

 

Reporting Services Staaaarrrrrtttt Up

I hear the complaint a lot that after a time period of no calls to Reporting Services, the first call to the Reporting Services instance is very slow.  This is totally expected because the application pool generally gets spun down and we need to restart everything.

If you wire up some basic web service calls, you can see that (at least in my testing) the RS2005 web service takes approximately 30 seconds to start up.  The only database work done here is the bare minimum necessary to retrieve the encryption keys and configuration settings stored in the catalog database.  There is no access to actual datasources or reports.

First, I wrote a  basic web method that just returns an empty dataset:

Public Function ReturnBlankDataSet() As Data.DataSet

     Dim m_ds As New Data.DataSet
     Return m_ds

End Function

Then, I wrote some code that had the ability to make a call to a web service and timed how long it took to return from the web method call.  I ran the test against each web service five times.  In between each test, I did an IIS reset to ensure a cold start. 

My test harness code looked like this:

        Dim sr As New IO.StreamWriter("c:\ReportingServicesMetrics.txt")

        'let's loop through 5 times
        For i As Integer = 0 To 4
            Dim ts As DateTime
            Dim te As DateTime


            'let's reset IIS to make sure everything has to start from scratch
            sr.WriteLine("Doing an IISReset - Loop #" & (i + 1).ToString)
            Dim proc As New Diagnostics.Process
            proc.StartInfo.FileName = "iisreset"
            proc.Start()
            proc.WaitForExit()
            sr.WriteLine("IIS has been reset")

            'RS
            sr.WriteLine("Getting ready to instantiate the RS web service")
            ts = Now()
            Dim rs As New RS2005.ReportingService2005
            Dim creds As New Net.NetworkCredential
            creds = Net.CredentialCache.DefaultCredentials
            rs.Credentials = creds
            rs.Url = "http://servername/reportserver2005/reportservice2005.asmx"
            rs.ListChildren("/", False)
            sr.WriteLine("Instantiated the RS web service")
            te = Now()
            sr.WriteLine("RS took " & te.Subtract(ts).TotalMilliseconds.ToString)

            'dummy web service
            sr.WriteLine("Getting ready to instantiate the dummy web service")
            ts = Now()
            Dim dws As New DummyWebService.Service
            dws.ReturnBlankDataSet()
            sr.WriteLine("Instantiated the dummy web service")
            te = Now()
            sr.WriteLine("The dummy web service took " & te.Subtract(ts).TotalMilliseconds.ToString)

        Next
        sr.Close()


I did a FileMon on the startup for each process.  The dummy web service did about 130 file reads, while Reporting Services did almost 17000 reads.  This is because Reporting Services must load up the traditional ASP.NET assemblies, plus all of the specialized Reporting Services assemblies.  It also loads up all the localization files for all the various supported languages.


Reporting Services does a good bit of logging for both ReportServer, Report Manager, and the Report Server Windows Service.  I did not do test with logging turned off because we ship with a default level of logging, you could save some start up time by turning off all logging.  However, it is  not recommended to turn off logging  because of the value traditionally derived from standard logging.

Other things that cause additional overhead when Reporting Services starts up:

  1. Every time the SRS 2005 web service loads, it also has to read and decrypt the rsreportserver.config file
  2. Since there are no connections in the connection pool, we have to physically open up a socket connection between the two servers, plus log into the database instance
  3. The web service has to make RPC calls into the Windows Service to get the symmetric encryption key

This explanation is to try and provide an overview of some of the things that are going on during Reporting Services initial start up.  Remember, most of these things probably do not happen in traditional web applications. Again, all of this is completely expected behavior.

As discussed earlier, if this behavior causes some business issues, you could consider modifying the recycle options on your IIS process.  You can either increase the recycle time (causing them to be recycled less frequently) or schedule the recycle to occur at a non-peak time.  You could then combine this second option with a "ping" process that hits the process shortly after the recycle.  This will "wake" the ReportServer processes so that your initial customer doesn’t see the initialization time.  If you combine these options with turning off the idle worker process shutdown, you can significantly minimize the instances where a user would run into the startup delay. 

The only downside I can see to not idling the worker process is that the process could consume more resources than necessary.  Recycling from time to time is recommended because it cleans up the worker process if you have any leaked memory or fragmentation but it is not necessary from a technical perspective.

Unfortunately, none of this information is documented in any KB articles.  However, some of it is addressed in www.microsoft.com/technet/prodtechnol/sql/2005/pspsqlrs.mspx.  Beyond the counters in the performance document, you could also track the performance of your Reporting Services instance using some execution log reports (http://msdn2.microsoft.com/en-us/library/ms161561.aspx).

Posted By: Evan Basalik

Working around the 4.2 billion tuple calculation limit in Analysis Services 2005 is possible in some cases...

An Analysis Services 2005 query against a calculation on a cell may report the error:  "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples."  This may be because there are simply more underlying cells in the query than the supported maximum, but in some cases, the query can be rewritten to avoid the error by breaking out parts of a clause into other clauses.  Specifically, an IIF() statement apparently considers the total number of cells for calculation from both action clauses when counting the number of underlying cells.  But two IIF statements run within the same query are not counted together when the server code checks for this limit.  Each appear to be evaluated seperately, and so long as neither exceeds the 4.2B tuple limit, the query will pass the check and not fail in this way.

 

Here is an example of a calculation that produced this error when run for a high level cell in a relatively large database:

 

IIF(

MDX Condition=true,

MDX Expression 1,

MDX Expression 2

)

 

Replacing the query with the following achieved the same results, but avoided the error:

 

IIF (

MDX Condition = true,

NULL,

MDX Expression 2

)

+

IIF(

MDX Condition = true,

MDX Expression 1,

NULL

)

 

This approach allows Analysis Services to take advantage of internal optimizations since it can eliminate all cells for the first condition with NULL results, regardless of the context of the calculation.  The server is able to use "block mode" evaluation in the formula engine that does not require each result to be evaluated on a cell by cell basis.  This optimization may also improve performance in some cases for the same reason.

 

Posted By: Jon Burchel

 

General network errors (GNEs), Timeout expired, SQL Server not found or Access Denied…

Many times, the key to solving these errors is a network packet trace.  The problem is that these errors are often thrown intermittently.  This can obviously make it problematic to capture a network trace. 

 

Network Monitor 3 (AKA Netmon3) solves this problem quite nicely.  Netmon3 has the built-in ability to capture rolling traces.  This allows you to start the trace and then walk away and be sure that you will have captured the error.

 

Here are my steps for configuring Netmon3 for a rolling trace:

 

Please see the steps below in order to configure Network Monitor:

 

1.    Download Network Monitor 3

a.    (http://www.microsoft.com/downloads/details.aspx?FamilyID=18b1d59d-f4d8-4213-8d17-2f6dde7d7aac&DisplayLang=en)

 

2.    Install Network Monitor on both the client and server experiencing the problem.  If you are not able to install it on the server you can install it on another machine that is connected to the same hub as the server or to the admin port of the switch that the server is on.  If you go this route you must synchronize the time between the client, server, and this third machine.

 

3.    Synchronize the clocks on the Client and SQL machines

a.    At one of the machines bring up a command prompt and execute the following:

 

                                         i.    Net time <\\machinenamewewanttosynchwith> /set /yes

 

4.    Turn off TCP Chimney if any of the machines are Windows 2003

a.    bring up a command prompt and execute the following:

 

                                         i.    Netsh int ip set chimney DISABLED

 

5.    Using the NMCap command-line utility, start the chained trace (chained = create a new capture file after reaching the “Temporary capture file size” rather than overwriting the current capture)

a.    [Netmon 3 installation folder]\NMCap /network * /capture /file test.chn:100M  (creates 100 MB chained files)

 

                                         i.    NOTE:  YOU MUST USE THE .CHN EXTENSION TO CAPTURE CHAINED TRACES

 

                                        ii.    NOTE:  The target folder must exist for NMCap to create the trace file    

 

b.    More information can be found:

                                         i.    http://blogs.technet.com/netmon/archive/2006/10/24/nmcap-the-easy-way-to-automate-capturing.aspx

                                        ii.    Or by running the command-line switch /examples

                                       iii.    Or by running the command-line switch /?

 

6.    Get the IP addresses of the client and the server

a.    ipconfig /all on the client > client.txt

b.    ipconfig /all on the server > server.txt

 

7.    Reproduce the problem, please note the exact time the problem reproduces.  Also, please note the exact error message.

 

8.    Stop the trace by hitting Ctrl-c in the command-line window

 

Please note that Netmon3 does not run on Windows 2000.  Also, step #6 is probably the single most important step (besides actually starting the trace).  If you don’t know the timestamp of the error, it is extremely difficult to find the offending packets in the trace.

 

Happy captures!

 

Posted By: Evan Basalik

More Posts Next page »
 
Page view tracker