Archive

Archive for December, 2009

SQL Server: ODBC, OLEDB and SQL Native Client Providers

December 22, 2009 Leave a comment

 

We have different providers to use (Microsoft OLE DB Provider for SQL Server or SQL Native Client) while creating SQL Server linked servers, or connecting to other sources like Excel, Access DB etc. This post gives brief overview of each of these providers and the difference between them.

ODBC is the programming API for connecting to any database management systems independent of vendor, operating system and programming languages. This was the first driver software designed for interacting with different Database Management Systems.

OLE DB is the Microsoft specific Data Access API for connecting to various Database Management Systems and extends the functionality of ODBC. This API was built using Microsoft COM technology and is part of the MDAC (Microsoft Data Access Components) which is installed with the Operating System or can be installed separately. MDAC includes the support for OLE DB, ODBC and ADO.

Till the SQL Server 2000 the SQL OLE DB and SQL ODBC connectivity was provided by MDAC package. But from SQL Server 2005 we have two different APIS

1. The Original MDAC which comes with OS

2. New API for SQL OLE DB, SQL ODBC and ADO which is called the SQL Native Client.

SQL Native Client combines the SQL OLE DB and SQL ODBC in MDAC into one DLL (SQLNCLI.DLL) which is shipped with the SQL Server and not part of the Operating System. So there is no dependency on the Operating System. As this DLL is shipped with the SQL Server every new release of the product might have a new version of SQL Native Client.

Now if you see the list of providers in SQL Server 2008 linked server creation wizard below; You can figure out that the providers listed with the prefix Microsoft are from the MDAC and SQL Native Client 10.0 is the SQL Server 2008 provider for SQL OLE DB and ODBC.

 

image

 

To create a linked server to SQL Server 2000 database we should use MDAC provider (Microsoft OLE DB Provider for SQL Server)  and to SQL Server 2005 database we can use SQL Native Client provider or MDAC provider if you are not plannng to leverage the following new features offered by SQL Native Client.

 

  • MARS (Multiple Active Result Sets in a single connection)
  • XML Data Types
  • CLR User Defined Types
  • Asynchronous Data Access
  • Database Mirroring
  • Snapshot Isolation

Is there a Problem to existing Applications that use MDAC SQL OLE DB or ODBC connectivity on SQL Server 2005?

For the applications that use SQL ODBC and SQL OLE DB from MDAC there is no impact. But if you want to utilize the new features in SQL Server 2005 like UDTs or XML Types you need to use the SQL Native Client otherwise your applications should work fine.

 

For more information on the MDAC architecture you can go here.

πŸ™‚ ranjith πŸ™‚

Automating Web Deployment using Windows Installer XML (WIX)

December 17, 2009 13 comments

Deployment of web sites is usually done by copying the compiled ASP.NET web site files into the target virtual directory using Copy Web Site or Publish web site features in Visual Studio and manually creating and configuring the Web Site in IIS.

Though this method is simple, it involves lot of manual effort in verifying the Pre Requisites, Creating/Modifying or Configuring the Web sites in IIS. We can automate this whole process by building a simple Windows Installer Package using WIX.

WIX installer package

  • Provides the features like Install, Un-Install, Repair and Remove to your Web Site similar to any other product install on your machine
  • You can check for all the Pre Requisites like OS Version, IIS version, and .NET Framework etc.. before proceeding with the deployment and inform the user about what happened with a nice UI
  • You can create a new web site, modify the existing web site,  Create Application Pools and configures IIS
  • During the un-install you can remove everything that is created (Web Site, Physical Directories) on Install and leave the target server in clean state
  • You can rollback the install changes in case of a failure

 

Create a Sample Web Site:

Let’s create a simple website and add a Web Deployment Project to it. We will build the installer package to deploy this web site on to the target server.

 

clip_image002 

Fig 1: Sample web site and its Web Deployment project

 

Right click on Web Deployment project and open the Property pages to set up the output location for our compiled web site files. Leave the default values for this demo which is set to the project output folder. This location we will be the source for our installer package to pick up the required files while building the installer package.

 

clip_image004 

Fig 2: Web Deployment Project property pages

 

Build the whole solution to see the output files in deployment project output location

clip_image006

Fig 3: Files in Web Deployment project output folder

 

Authoring Installer for our Sample Web Site:

Install the WIX 3.0 Visual Studio plug in from http://sourceforge.net/projects/wix/files/. And for basic understanding on the Directory, Component, Feature and other elements in WIX source files use the WIX documentation file in the installed location of the plug in or go to http://www.tramontana.co.hu/wix/.

Once the plug in is installed add the new WIX project to our sample web site solution by going to add new project –> Select WIX.

After you add it the solution looks like this

clip_image008

Fig 4: The web site and the set up project together in one solution

 

The Product.wxs is the WIX Source File which we will modify shortly to define our package components. Before that add references to WixIISExtension.dll and WixUtilExtension.dll in WIX binaries location to our WIX Project.

Now open the Product.wxs and add the following xml namespaces to get the intelliscenece for WIX IIS and other elements.

 

<Wix xmlns="http://schemas.microsoft.com/wix/2006/wi"
     xmlns:iis="http://schemas.microsoft.com/wix/IIsExtension"
     xmlns:util="http://schemas.microsoft.com/wix/UtilExtension">

 

The default directory structure defined in Product.wxs maps to β€œC:\Program Files\ApplicationName\” which specifies the target install location for our package i.e. the location on target server which will have all the output files from our MyWebSite_deploy project (See Fig 3).

Under the INSTALLLOCATION directory add the following to define our first component

<!-- root level files –>
<Component Id="MySite_root_Files" Guid="E06FD7E9-8360-4e78-B10F-3F53E88FE1FB">
 <File Id="MySite_Default_aspx" Source="$(var.SolutionDir)\MyWebSite_deploy\$(var.Configuration) \Default.aspx"/>
 <File Id="MySite_Web_Config" Source="$(var.SolutionDir)\MyWebSite_deploy\$(var.Configuration)\Web.Config"/>
</Component>

 

The component MySite_root_Files defines all the files that are directly needs to be copied under the INSTALLLOCATION. The <File/> element specifies the actual file that needs to be copied and the source attribute specifies the complete source path of the file.

Source="$(var.SolutionDir)\MyWebSite_deploy\$(var.Configuration)\Default.aspx"

 

$(var.SolutionDir) is a WIX pre-processor which gives the Solution folder path to the WIX compiler

$(var.Configuration) is another pre-processor which specifies the Active Configuration of the solution (i.e. Debug | Release)

Along with the files Default.aspx and Web.Config we also have bin folder in project output directory which needs to be created under the install location. So create the folder mapping \INSTALLLOCATION\bin by adding the directory element under the INSTALLLOCATION directory. And define the component and file or Directory element for each of the files and directories under the bin folder as we have done for INSTALLLOCATION directory.

 

<!-- bin directory –>
 <Directory Id="MySite_bin_Directory" Name="bin">
   <Component Id="MySite_bin_Files" Guid="2ECC2543-856E-4ca7-8DB3-D1657245A41E">
     <File Id="MYSite_MySite_deploy_dll" Source="$(var.SolutionDir)\MyWebSite_deploy\$(var.Configuration)
             \bin\MyWebSite_deploy.dll">
      </File>
    </Component>
  </Directory>

 

The same way we can add any number of directories and files mapping from source to the target location.

Setting up IIS web site:

So far we have seen how to move files from source to the target location by using the Directory, File and Component elements. But how can we configure IIS?

WIX has an API or an Extension (WIXIISExtension.dll) to interact with IIS. Remember that we have already added reference to this to our WIX Project. Add another component under the INSTALLLOCATION directory to define the configuration to create a web site in IIS.

 

<ComponentId="MyWebSite_IISConfigure" Guid="5146762F-0E78-47d2-A105-6E18E2993619" KeyPath="yes">
  <util:UserId="MyWebSite_AppPoolUser" Name="domain\username" Password="pwd"/>

  <!--define application pool-->
  <iis:WebAppPool Id="MyWebSite_AppPool" Name="MyWebSiteApplication"
                  Identity="other" User="MyWebSite_AppPoolUser"
                  RecycleMinutes="120" />

  <!--define web site-->
  <iis:WebSite Id="MyWebSite_Website" Description="MyWebSite"
               AutoStart="yes" StartOnInstall="yes" ConfigureIfExists="yes"
               Directory="INSTALLLOCATION" ConnectionTimeout="360" >

    <iis:WebAddress Id="MYSite_Bindings" IP="xx.xx.x.xxx" Port="80" Header="MyWebSite" />
    <iis:WebApplication Id="MY_WebApp" Name="MY Web Site" WebAppPool="MyWebSite_AppPool"
                        ScriptTimeout="360" />
    <iis:WebDirProperties Id="MyWebSite_Properties" AnonymousAccess="yes" WindowsAuthentication="no"
           DefaultDocuments="Default.aspx" />
  </iis:WebSite>
  </Component>

 

Most of the elements and their attributes in this component are self descriptive.

<Util:User/> define the domain user which can be referenced anywhere in the source file using the Id MyWebSite_AppPoolUser.  

<iis:WebAppPool/> creates the application pool with the name MyWebSiteApplication. The attribute Identity = β€œOther” specifies that this application pool uses Custom account for identity. And the user attribute specifies the ID of the domain\username created anywhere in the source file using <Util:User/>

<iis:WebSite/> and its child elements <iis:WebAddress/>, <iis:WebApplication/> and <iis:WebDirProperties/> define the complete web site in IIS. The Directory attribute of Web Site element is set to INSTALLLOCATION i.e. C:\Program Files\MyWebSite\ which is our target location to copy the compiled ASP.NET files to run our Web Site.

The bindings IP, PORT and Host Header for our web site are specified by <iis:WebAddress/> element, and the mapping between the application pool MyWebSite_AppPool and the site is defined by <iis:WebApplication/> . The Default Dcoument and the Authentication are specified by <iis:WebDirProperties/>.

So we have defined all the components (MySite_root_Files, MySite_bin_Directory, and MyWebSite_IISConfigure) that need to be installed on to the target server by our installer. But we know that every installer needs at least one feature which is a set of components that define one complete install feature i.e. our Web Site in this case. We have to define it using the feature element.

 

  <Feature Id="ProductFeature" Title="My WebSite" Level="1">
     <!-- add the components comprise of this feature -->
     <ComponentRef Id="MySite_root_Files"/>
     <ComponentRef Id="MySite_bin_Files"/>
     <ComponentRef Id="MyWebSite_IISConfigure"/>
  </Feature>

 

That is it. We have completed authoring the installer package for our Web Site. Upon building the entire solution again our Set up project reads the compiled ASP.NET files from our Web Deployment Project out put folder and embeds them into a Windows Installer package which is created in the out put directory of our setup project.

 

clip_image002[4]

Fig 5: Installer package in Setup project output location

 

We just need to copy this installer package to the target server and double click and wait for the job to be done.

clip_image004[4]

Fig 6: while installing our setup file

 

Once the install is complete, open the IIS Manager to see that our web site running.

 

Summary:

The web deployment using WIX is simple, flexible, and gives a overall great web deployment experience.

 

Hope it helps

πŸ™‚ ranjith πŸ™‚