Removing spaces from some column

Quick one. Here's an example how you can remove spaces from column in database: TSQL:

update Products set ProductCode = REPLACE(ProductCode, CHAR(32),'') where id = @SomeID

Reseting autoimcrement seed in MSSQL

I was working on importing data into MSSQL database recently and I needed to reimport data to some table. Because the primary key of the table was setup as identity column after removing previously inserted rows I needed to reset Identity Seed. Here's the way how you can accomplish that: DBCC CHECKIDENT ('dbo.MyTable', RESEED, 1)

Webfarm web servers configuration - Part 3 of 3 - cache synchronization

Because my application is using the HttpContext.Current.Cache object to store items used commonly in my application I needed to make sure that when the cache item was updated on one of Web servers, the aprropriate cache item on second server would also get updated. To accomplish that I decided to create a action in controller responsible for removing item from cache. Then whenever an item from the cache is removed this action would be called on second server to remove cached data. First step is to store in web.config the list of web servers that should be notified when the cache item is removed. So I added following key in web.config file on Server A (10.0.1.1).

and on Server B (10.0.1.2):

This is my Cache Synchronizer class

/// 

    /// Manages cache synchronization on webservers that the application is set up on
    /// Requires following application key to be present in web.config file: WebFarmServersAddresses
    /// 
    public static class CacheSynchronizer
    {
        public static void SynchronizeCache(string cacheKey)
        {
            // Remove cache items on another servers
            var servers = ConfigUtils.GetAppSetting("WebFarmServersAddresses").Split(';');
            foreach (var serverAddress in servers)
            {
                // Call action on second server responsible for removing item from cache
                using (WebClient asyncWebClient = new WebClient())
                {
                    asyncWebClient.DownloadDataCompleted += new DownloadDataCompletedEventHandler(asyncWebClient_DownloadDataCompleted);
                    Uri urlToRequest = new Uri(string.Format("http://{0}/System/RemoveCacheItem?key={1}", serverAddress, cacheKey));
                    //System.Diagnostics.Debug.WriteLine("Syncrhnizing cache on - " + urlToRequest.ToString());                    
                    asyncWebClient.DownloadDataAsync(urlToRequest);
                }
            }
            
        }

        static void asyncWebClient_DownloadDataCompleted(object sender, DownloadDataCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                Elmah.ErrorSignal.FromCurrentContext().Raise(e.Error);
            }
        }
    }

Cache Synchronizer is called whenever an item is removed from cache:

cache.Remove(hash + cacheKey);
            #if !DEBUG
            // If the application is compiled in release mode we need to run Cache Synchronize in order to synchronize cache on another webservers
            if (updateAnotherServers)
            {
                CacheSynchronizer.SynchronizeCache(hash + cacheKey);
            }
            #endif

The last thing we need to do is to implement action in controller which is going to be called by cache synchronizer:

    public class SystemController : Controller
    {
        public void RemoveCacheItem(string key)
        {
            HttpContext.Cache.Remove(key);
        }
    }

That's it. The cache is going to be syncrhonized on another web servers in your web farm. This is the end of 3 part tutorial of setting asp.net mvc web application in web farm environment. Hope you enjoyed it.

Webfarm web servers configuration - Part 2 of 3 - forms authentication setup

Second of my three part series about configuring webfarm for web application will cover setting up configuring forms authentication in order for users to stay logged in while the server is served from any of web farm servers.

 

When the network load balance affinity has been changed to ‘none’ users of my application could get any of the web pages from any of web farm web servers.

Immediately I’ve noticed an issue with authentication.

When user logged in to Web server A and the next Web page has been served from Web server B – user has been redirected to the log in page. I need to notice here that I use Forms Authentication.

 

This is because the authentication ticket has been encrypted and decrypted on web servers using different encryption and decryption keys.

To resolve this you have to follow instructions from this link:

http://technet.microsoft.com/en-us/library/cc772287%28WS.10%29.aspx

1.       Open IIS Manager and navigate to the level you want to manage. For information about opening IIS Manager, see Open IIS Manager (IIS 7). For information about navigating to locations in the UI, see Navigation in IIS Manager (IIS 7).

2.       In Features View, right-click Machine Key, and then click Open Feature.

3.       On the Machine Key page, select an encryption method from the Encryption method drop-down list. The default encryption method is SHA1.

4.       Choose a decryption method from the Decryption method drop-down list. The default decryption method is Auto.

5.       Optionally, configure settings for validation and decryption keys.

6.       In the Actions pane, click Generate Keys, and then click Apply.

In the picture you can see the screen shot of example machine key configuration.

This configuration generates following entry in your web.config file:

<machineKey decryption="Auto" decryptionKey="B73B88BE243A7E931F9BBA991E62DD0E2B7E172AE47A6F8D"

                              validation="SHA1" validationKey="B0FFEFC743869AB4AF1ED142AAE2890187FE035EA734F76FEC8B21FFA05F6AC7894828F0190D53D6A5737BC1D3C19FA8A648DBEB395332D6DA13AB2E71E779E3" />

 

All you have to do is now to include this entry in <system.web> section in your web.config files on both Web servers.

 

Webfarm web servers configuration - Part 1 of 3 - session storage

Today I wanted to share with you my expierience in configuring ASP.NET MVC website running on 2 web servers.

Web servers are going to be configured on 2 servers running on Microsoft Windows Server 2008R2. The net balancing will make the website be served from either server A or server B.

 

The first of 3 parts will cover configuring the session to be available across two machines.

Because the user may be switched from webserver A to webserver B we need to make sure that the same session is available on both Webservers.

 

According to msdn source http://msdn.microsoft.com/en-us/library/ms178586.aspx there are few modes of session state:

·         In-Process Mode – (default) the session is stored within the web server worker process. So for 2 werbserers we’d have 2 different places that the session would be stored at – won’t work

·         State Server Mode – the session state in a process which is separate ASP.NET worker process. This mode ensures that the state is preserved if the Web application is restarted and also makes session state available to multiple Web servers in a Web farm

·         Sql Server Mode – similar to State Serever Mode, but the data stored in session is stored in sql database.

I’ve chosen the State Server Mode, because as I found in some forum it’s quicker than Sql Server State storage.

 

The configuration that needs to be add

 

To start with I’ve decided to configure it locally to give it a quick test.

After setting this in web.config  system.web section
<sessionState mode="StateServer"

                  stateConnectionString="tcpip=localhost:42424"

                  cookieless="false"

                  timeout="20"/>

In live environment you’ll have to replace localhost with the IP address of the server where the ASP.NET State Service is running.

I got following error:

Server Error in '/' Application.

Unable to make the session state request to the session state server. Please ensure that the ASP.NET State service is started and that the client and server ports are the same.  If the server is on a remote machine, please ensure that it accepts remote requests by checking the value of HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\aspnet_state\Parameters\AllowRemoteConnection.  If the server is on the local machine, and if the before mentioned registry value does not exist or is set to 0, then the state server connection string must use either 'localhost' or '127.0.0.1' as the server name.

This is because the State Server wasn’t enabled on my machine. To enable it you have to go to AdministrativeTools > Computer Management > Services and Applications >Services and start the ASP.NET State Service

Then following error came out:

Unable to serialize the session state. In 'StateServer' and 'SQLServer' mode, ASP.NET will serialize the session state objects, and as a result non-serializable objects or MarshalByRef objects are not permitted. The same restriction applies if similar serialization is done by the custom session state store in 'Custom' mode.

This is because all the objects stored in State Server session must be serializable and in my case these weren’t.

After making objects stored in session serializable (adding attribute to the class definitions) it worked for me.

 

So in web farm configuration you only need to configure ASP.NET State Service on one server and use it from both applications on both web servers.  

 

That’s all for the first part. In second part I’ll write about problem with keeping users signed in across two webservers.

Few notes

These are just few notes, hopefully I'll have a minute soon to explain why do I post it here. Problem 1: 2 linked servers with different logins causing following error: OLE DB provider "MSDASQL" for linked server returned message "[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user .... Solution:

EXEC sp_addlinkedsrvlogin 'server1', 'false', NULL, 'user', 'test'

Quick T-SQL tip: Insert image into db using SQL statement

The goal of this quick post is to show how to insret image into database cell using sql statement. Here is the table that we insert the row into.

The cell is type of image, but the actual data stored inside of it is binary. Here is the insert statement that you can use to insert row into that table:

INSERT INTO MsMailTemplateImages(ContentID, Image, MsMailTemplateID) 
SELECT 'greentree' as ContentID, BulkColumn, 1 as MsMailTemplateID FROM 
OPENROWSET(BULK N'C:\Downloads\environmental.gif', SINGLE_BLOB) as DATA