Installing Multiple Instances of Windows Service

To Installing Multiple Instances of Windows Service.

• Install InstallUtil.exe.
• Add a /name parameter to specify what the service will be named. The names have to be unique, so if you already have "Telligent Mail Gateway", you might need to name it "Telligent Mail Gateway - Site 2".
• Specify the filename of the Windows Service, which is usually like "....Service.exe”. With Mail Gateway.
  
  it would be like this:
  InstallUtil /name="Telligent Mail Gateway - Site 2" Service.Service.exe
  
  Run this from a command prompt within the add-ons directory. The name must be in quotes if it contains spaces and must come before the filename.

How to Uninstall:

Uninstalling the service is very similiar to installing. You will still use the InstallUtil.exe program and will need to specify the name of the service to uninstall, though this time you will need to add a /u switch to specify that you intend to uninstall it. For instance:

InstallUtil /u /name="Telligent Mail Gateway - Site 2" Service.Service.exe

The name must be specified since if you have multiple instances of the same executable installed, it will need to know which one. If no name is specified, it will remove the one with the default name as opposed to a custom named one.

Again, the uninstall and name parameters must come before the filename so that it know they are for that file.

SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

When you choose to Include the Actual Execution Plan in SSMS 2008 it suggest any missing indexes it thinks that are needed. Plus it also calculates the performance benefit from adding them.

And best of all this also works when you're connected to a SQL Server 2000 or 2005.

Updated Unused Index Query

To clean up and update unused index query. Query below returns a list of indexes ordered by ascending reads. If the instance has been up for a long time, it is probably safe to drop indexes with zero or close to zero reads. If the reads are low and the writes are high, this may help improve your transactions per second count. Otherwise, you are just cleaning up unused space. Use caution though. A missing index is worse than an unused index.

SELECT objectname=OBJECT_NAME(s.OBJECT_ID)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, p.rows
FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
ON i.index_id = s.index_id AND s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON p.index_id = s.index_id AND s.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
ORDER BY reads, rows DESC

Dynamic SQL using like

Dynamic SQL was the best choice by far in terms of performance for dynamic search problems,
check article below:
http://www.sommarskog.se/dyn-search-2008.html

It takes a long time from me to write a correct sysntax with LIKE criteria

First I wrote Syntax:

DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT [CategoryName]
FROM [StarterSite_productcatalog].[dbo].[Adventure Works Catalog_CatalogProducts]
where categoryname like ''%@param1%'''
DECLARE @param1 nvarchar(20)
DECLARE @paramlist nvarchar(20)
SET @param1='sl'
SET @paramlist = '@param1 varchar(20)'
EXEC sp_executesql @sql, @paramlist,@param1

I found correct syntax:

DECLARE @SQL nvarchar(4000)
SET @SQL='SELECT [CategoryName]
FROM [StarterSite_productcatalog].[dbo].[Adventure Works Catalog_CatalogProducts]
where categoryname like ''%''+@param1+''%'''
DECLARE @param1 nvarchar(20)
DECLARE @paramlist nvarchar(20)
SET @param1='sl'
SET @paramlist = '@param1 varchar(20)'
EXEC sp_executesql @sql, @paramlist,@param1

LINQ to SQL Serialization

Problem:

• LINQ to SQL classes do not support binary serialization. Although I can manually modify them to meet my needs, it is a very time-consuming job, and difficult to maintain if the table is changed in the future.
• LINQ to SQL classes cannot be serialized by XML serializer if there is a relationship between tables.

These articles give us work around:
http://www.west-wind.com/WebLog/posts/147218.aspx
http://www.codeproject.com/KB/linq/linqsqlserialization.aspx

LINQ and Dynamic Query Expressions and SQL Injection

When you want to create LINQ with dynamic expression you will use concatenate string like:
var query = db.Customers.Where("City = '"+country+"' and Orders.Count >="+ordersCount)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");

To prevent SQL injection you must use parameters:
var query = db.Customers.Where("City = @0 and Orders.Count >= @1", country, ordersCount)
.OrderBy("CompanyName")
.Select("new(CompanyName as Name, Phone)");

Enabling HTTP Compression (IIS 6.0)

1. In IIS Manager, double-click the local computer, right-click the Web Sites folder, and then click Properties.
2. Click theService tab, and in the HTTP compression section, select the Compress application files check box to enable compression for dynamic files.
3. Select the Compress static files check box to enable compression for static files.
4. In the Temporary directory box, type the path to a local directory or click Browse to locate a directory. Once a static file is compressed, it is cached in this temporary directory until it expires, or the content changes. The directory must be on the local drive of an NTFS–formatted partition. The directory cannot be compressed or shared, and the access control lists (ACLs) for the directory must include Full Control access to the identity of the application pool or to the IIS_WPG group.
5. Under Maximum temporary directory size, click a folder size option. If you specify a maximum size under Limited to (in megabytes) (the default setting is 95 MB), then when the limit is reached, IIS automatically cleans up the temporary directory by applying the "least recently used" rule.
6. Click Apply, and then click OK.
7. Created a web service extension for c:\windows\system32\inetsrv\gzip.dll
8. Used metabse explorer or Adsutil.vbs to:
   a) edit the compression levels (set to level 9 for both HcDynamicCompressionLevel and HcOnDemandCompLevel) for both deflate and gzip.
   b) ensure that gzip was used for static compression only (HcDoStaticCompression set to 1) c) set HcFileExtensions (static files) for both gzip and deflate to htm,html,txt,js
   d) set HcScriptFileExtensions for both gzip and deflate to asp,dll,exe,aspx,asbx,ashx,asmx,axd,js and css (I have css files that are dynamically generated)
   e) set HcCreateFlags to 0 for deflate and 1 for gzip
   f) Checked that, underneath parameters, HcDoDynamic, HcDoStatic and HcDoOnDemand are set to 1.
   g) Other settings: Buffer sizes are set to 8192, HcMinFileSizeForComp is 1.

Samples for Adsutil.vbs:

To enable HTTP Compression for Individual Sites and Site Elements:

1. Disable global static compression by executing the following command at a command prompt:
   adsutil set w3svc/filters/compression/parameters/HcDoStaticCompression false
2. Enable static compression at this directory by executing the following command at a command prompt:
   adsutil set w3svc/1/root/Home/StyleSheets/DoStaticCompression true
   

To disable static compression for only a single directory, first enable global static compression (if it is disabled) and then disable static compression at that directory. For example, to enable static compression for a directory at http://www.contoso.com/Home/StyleSheets, perform the following steps:

1. Disable global static compression by executing the following command at a command prompt:
   adsutil set w3svc/filters/compression/parameters/HcDoStaticCompression true
2. Enable static compression at this directory by executing the following command at a command prompt:
   adsutil set w3svc/1/root/Home/StyleSheets/DoStaticCompression false