Systems Engineering and RDBMS

Archive for the ‘.Net Development’ Category

SSMS and .Net Framework 4.5.1

Posted by decipherinfosys on March 31, 2016

Ran into an issue yesterday.  Installed the .Net Framework 4.5.1 on my laptop and post that, SQL Server Management Studio failed to connect to any of the instances.  The error received was:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 – The message received was unexpected or badly formatted.)

You can uninstall .Net Framework 4.5.1 and install .Net Framework 4.5.2 in order to fix this.  Or if you need to keep the .Net Framework 4.5.1, there is a workaround available which is listed on Microsoft Connect page here.

Posted in .Net Development, SQL Server | Leave a Comment »

Installing/Enabling .Net 3.5 SP1 on Windows 2008 R2 for SQL Server 2008 R2/SQL Server 2012 RC0 Install

Posted by decipherinfosys on February 24, 2012

A junior team member ran into this issue so thought it was worth a post.  While installing SQL Server 2012 RC0 on a new VM of Windows Server 2008 R2, it complained about installing or enabling .Net Framework 3.5 SP1 which is a pre-requisite for the SQL Server install.  Here are the steps to validate whether it is installed and how to enable it and if it is not installed, how to go about installing it.

  1. Under Administrative Tools, Select Server Manager and click on Features.
  2. All the installed Features are displayed in the right side pane where you can validate if .Net Framework 3.5 SP1 (3.5.1) is installed or not.
  3. If it is not installed, then click on “Add Features” and expand “.Net Framework 3.5.1 Features”.
  4. Check the check box for “.Net Framework 3.5.1 Features” and click on Next and then Install.

5.  Once the installation is complete, click on close and you are done.

Now, you can move on with your SQL Server 2012 RC0 install on Windows Server 2008 R2.  Have fun.

Posted in .Net Development, SQL Server, Windows | Leave a Comment »

Pooled Connections

Posted by decipherinfosys on May 31, 2010

What is connection pooling and why is it necessary? A connection resource on the database side is essentially the database server process, the session and the associated memory.  In order to utilize the database resources effectively and to provide end user concurrency, applications use various resource pooling solutions.   In a typical web based application, connection pooling is typically done by a middle tier like an application server which gets x number of connections to a database and stores them in a pool (like an array).  Those are then marked as not being in use.  When a request comes in from the end user that goes through the application server, it would get a connection from the pool and use it as well as mark it as “in use”.  Once the response is given to the request and the session has been fulfilled, it goes back to the pool so that it can be used by another request.

The benefits of connection pooling are obvious – no connect/disconnect of the connections which is an overhead and using pooled connections, one can service the needs to many users.  In this post, we will look into pooled connections from a DBA’s perspective i.e. how a DBA can find out whether the connections hitting their instance are pooled or not.

As per MSDN, the memory usage per connection is roughly: (3 * network_packet_size + 94) KB.  So, for 4096 byte network packet size which is the default, we are talking about 106KB per connection.   So, you can see how it can add up pretty fast.  So, from a DB side, how can we track whether our applications are using pooled connections?  We can either trace the sessions (sql trace/profiler provides all the pertinent information) – using the same SQL that we had used in our Profiler basics post, we can change the filter condition to see which event classes and which event name and data columns & subclassnames are to be used:

select
v2.name as Event_Class
, v1.name as event_name
, v4.name as data_column
, case v4.is_filterable when 0 then 'No' else 'Yes' end as Is_Column_Filterable
, v5.subclass_name
, v5.subclass_value
from
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
inner join sys.trace_event_bindings as v3
on v1.trace_event_id = v3.trace_event_id
inner join sys.trace_columns as v4
on v3.trace_column_id = v4.trace_column_id
inner join sys.trace_subclass_values as v5
on v3.trace_event_id = v5.trace_event_id
and v3.trace_column_id = v5.trace_column_id
where v5.subclass_name like '%pooled%'
order by v2.name, v1.name

Output:

Event_Class    event_name    data_column    Is_Column_Filterable    subclass_name    subclass_value
Security Audit    Audit Login    EventSubClass    Yes    Nonpooled    1
Security Audit    Audit Login    EventSubClass    Yes    Pooled    2
Security Audit    Audit Login Failed    EventSubClass    Yes    Nonpooled    1
Security Audit    Audit Login Failed    EventSubClass    Yes    Pooled    2
Security Audit    Audit Logout    EventSubClass    Yes    Nonpooled    1
Security Audit    Audit Logout    EventSubClass    Yes    Pooled    2

Using these, one can trace it out either by using SQL Trace or an active Profiler session.  However, a better solution would be to make use of the EVENTDATA() function along with a logon trigger since that would put less load on the server and also provide us an easy way to do trend analysis – we had discussed their usage once before when answering a question from a reader on how to prevent connections from certain IP addressess – you can read that post here.   As you know, in order to capture the XML data about LOGON events, we will make use of the EVENTDATA() function.  And as per BOL, the LOGON event returns this event data schema – and it has the IsPooled information that we are interested in:

<EVENT_INSTANCE>
<EventType>event_type</EventType>
<PostTime>post_time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login_name</LoginName>
<LoginType>login_type</LoginType>
<SID>sid</SID>
<ClientHost>client_host</ClientHost>
<IsPooled>is_pooled</IsPooled>
</EVENT_INSTANCE>

Unlike the Audit logon trigger that we had created in that post, over here we would be interested in doing some trend analysis since we need to capture the data over a period of time to see what is going on and whether we have pooled connections or not.  So, let’s create a table for analysis:

CREATE TABLE dbo.AUDIT_POOLED_CONNECTION
(
AUDIT_POOLED_CONNECTION_ID        INT    IDENTITY    NOT NULL
,    EVENT_TYPE                        NVARCHAR(128)    NOT NULL
,    POST_TIME                        DATETIME        NOT NULL
,    SPID                            INT                NOT NULL
,    SERVER_NAME                        NVARCHAR(128)    NOT NULL
,    LOGIN_NAME                        NVARCHAR(128)    NOT NULL
,    CLIENT_HOST                        NVARCHAR(30)    NOT NULL
,    IS_POOLED                        BIT                NOT NULL
,    CREATE_DATE_TIME                DATETIME        NOT NULL DEFAULT GETDATE()
,    CREATE_USER_ID                    INT                NOT NULL DEFAULT 2 --ADMIN USER
,CONSTRAINT PK_AUDIT_POOLED_CONNECTION PRIMARY KEY (AUDIT_POOLED_CONNECTION_ID)
)
GO

And here is the simple logon trigger code to dump the data into this table:

create trigger AuditIsPooled_Demo
/* server means instance level*/
on all server
with execute as 'sa'
/* We specify the logon event at this stage
– Capture all the pertinent information for trend analysis
*/
for logon
as
begin
/*Insert the data that we are interested in for Pooled connections analysis*/

Insert into dbo.Audit_Pooled_Connection (Event_Type, Post_Time, SPID, Server_Name, Login_Name, Client_Host, Is_Pooled)
SELECT
EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(128)’) as Event_Type
, EVENTDATA().value(‘(/EVENT_INSTANCE/PostTime)[1]’, ‘datetime’) as Post_Time
, EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]’, ‘int’) as SPID
, EVENTDATA().value(‘(/EVENT_INSTANCE/ServerName)[1]’, ‘nvarchar(128)’) as Server_Name
, EVENTDATA().value(‘(/EVENT_INSTANCE/LoginName)[1]’, ‘nvarchar(128)’) as Login_Name
, EVENTDATA().value(‘(/EVENT_INSTANCE/ClientHost)[1]’, ‘nvarchar(30)’) as Client_Host
, EVENTDATA().value(‘(/EVENT_INSTANCE/IsPooled)[1]’, ‘bit’) as Is_Pooled

end
go

Now, upon several executions, we can see the output in that table (abridged):

AUDIT_POOLED_CONNECTION_ID    EVENT_TYPE    POST_TIME    SPID    SERVER_NAME    LOGIN_NAME    CLIENT_HOST    IS_POOLED    CREATE_DATE_TIME    CREATE_USER_ID
118    LOGON    2010-05-31 12:00:04.023    56    SVDB-03    sysdba    192.168.104.130    1    2010-05-31 12:00:04.037    2
119    LOGON    2010-05-31 12:00:04.037    58    SVDB-03    sysdba    192.168.104.130    0    2010-05-31 12:00:04.037    2

So, now we have seen how a DBA can monitor pooled connections from the instance side.  And for more understanding of the connection pools, their configuration and controlling connection pool fragmentation, look at the resources section below.

We will look into connection pooling in Oracle from a DBA perspective perspective in one of our future posts.  Oracle has also introduced DRCP (Database Resident Connection Pooling) in Oracle 11g which is also something we will look into that post.

Resources:

  1. Logon Triggers and EventData() function – here, here and here.
  2. MSDN article on connection pooling for the .Net Framework Data Provider for SQL Server – here.
  3. Another good article on ADO.Net connection pooling – here.
  4. Article at Pythian – excellent article discussing connection pooling – here.
  5. MSDN post on SQL Server connection Pooling (ADO.Net) – here.

Posted in .Net Development, SQL Server | Leave a Comment »

Some more free training materials from MSFT

Posted by decipherinfosys on June 19, 2009

Windows 7 Introduction – here.

This is an older one but a very good one for those looking for an introduction to SSIS 2008 – here.

Windows 2008 Fundamentals – here.

Silverlight Fundamentals – here.

Windows Server 2008 R2, new features – here.

Posted in .Net Development, SQL Server, Technology, Windows | Leave a Comment »

Data Access Tracing and SQL 2008 Developer Training Kit

Posted by decipherinfosys on May 31, 2009

Here are two links for Developers working on SQL Server based systems and using .Net:

  • Whitepaper from MSFT on Data Access Tracing – here, and
  • SQL Server 2008 Developer’s Training Kit – here.

Posted in .Net Development, SQL Server | Leave a Comment »

Data Access Tracing

Posted by decipherinfosys on February 18, 2009

We have covered ODBC tracing before on our blog.  While troubleshooting an issue for a client, came across this wonderful MSDN article from MSFT on how to do data access tracing for applications using SQL Server 2008.

Posted in .Net Development, SQL Server, Windows | Leave a Comment »

Cloud Computing: Azure Services Platform

Posted by decipherinfosys on February 8, 2009

Here is an excellent whitepaper from David Chappell that goes over the basics of the Azure Services Platform and what it has to offer to the developers and corporations to build the next generation applications:

http://download.microsoft.com/download/e/4/3/e43bb484-3b52-4fa8-a9f9-ec60a32954bc/Azure_Services_Platform.pdf

Posted in .Net Development, Cloud Computing, SQL Server, Technology, Windows | Leave a Comment »

Visual Studio 2010 and .Net 4.0

Posted by decipherinfosys on October 10, 2008

MSFT gave the users a first peek on Visual Studio 2010 and the .Net 4.0 framework and also a peek at the Visual Studio Team System (VSTS) 2010 – code named Rosario. You can read more on the overview in this post at MSDN:

http://msdn.microsoft.com/en-us/vstudio/products/cc948977.aspx

Posted in .Net Development, Technology, Visual Studio | Leave a Comment »

Checking the .Net Framework Version on a Machine

Posted by decipherinfosys on July 30, 2008

This came up in a discussion at a client site while working with them on integrating their application with their client’s application.  How would you determine what version of the .Net Framework is on your machine or the server?

There are a couple of ways to do that.  Registry is one of them.  Do a start/run/regedit and enter.  Then navigate to:

HKLM\Software\Microsoft\Net Framework\NDP

and you will see a child key that will show the .Net framework version that is installed on that machine.  If you have more than one like I do on my machine, then you will see multiple key enteries like this:

And there you can see the other details like the Install Path and the exact version.

Posted in .Net Development | 1 Comment »

Different Flavors of LINQ

Posted by decipherinfosys on May 12, 2008

LINQ stands for Language Integrated Query and it is a set of .Net framework extensions that allow us to query various data sources using a .Net language. There are a couple of different flavors of LINQ:

a) LINQ to SQL: This is for applications that use objects mapped to the database objects.

b) LINQ to Entities: This is for applications that need more flexibility in mapping objects to a RDBMS supported by the ADO.Net data providers.

c) LINQ to XML: This provides an in memory XML API.

d) LINQ to object: This allows us to do queries against the in memory objects.

e) LINQ to Sharepoint: This allows us to query MS Sharepoint lists.

f) LINQ to DataSet: This allows us to query DataSets.

You can get more information on LINQ using these resources:

MSDN Site, LINQ – Basic Introduction, HookedOnLINQ, LINQ to MS Sharepoint, LINQ Books – Pro LINQ, LINQ in Action.

Posted in .Net Development, SQL Server, Windows | Leave a Comment »