Friday, 10 May 2013

SQL AutoIncrement without using an IDENTITY Column Plus its Concurrent and Thread Safe with MIN - MAX



Its been a while since I have last blogged so it here it goes again.

I plan to do a series of blog entries on SQL concurrency safe inserts / updates and selects. Also how to trace and avoid deadlocks.

This blog entry though discusses various methods of obtaining an autoincrementing id without creating a table and having an identiry column. Also this approach allows you to create a pool of ids. Or specify a minium and maximum id. Why would you need that you might ask? Well when it comes to concurrency and transactions/deadlocks this is very handy. This method can be used to avoid deadlocks. More about this later in this blog.

Now what do you need to do.

Well its quite simple actually and there are 2 approaches.

One way is to
DECLARE @SettingValue as int

WHILE 1 = 1
BEGIN
      UPDATE [MicroangeloAutoIncrement].[dbo].[ClientSettings]
         SET @SettingValue = [SettingValue] = [SettingValue] + 1
       WHERE [Setting] = 'Client'

      --TESTING Duplication Hence Verifying Unique ID by inserting id in a primary table
      INSERT INTO [MicroangeloAutoIncrement].[dbo].[ClientSettingsTest]
           ([ClientSettingsTest])
    VALUES
           (@SettingValue)

      PRINT @SettingValue
END

You can test this as described above by running the above query in 2 or more windows. This it the result in one window

(1 row(s) affected)
80102

(1 row(s) affected)

(1 row(s) affected)
80104

(1 row(s) affected)

(1 row(s) affected)
80106

Another way is listed below. I like this method because you can adapt it to control the minimum and maximum setting value.

DECLARE @Update as bit
DECLARE @CurrentSetting as int
DECLARE @NewSetting as int

WHILE 1=1
BEGIN

      SET @Update = 0  

      SELECT @CurrentSetting = [SettingValue]
      FROM [MicroangeloAutoIncrement].[dbo].[ClientSettings]
      WHERE [Setting] = 'Client'
     
      WHILE @Update = 0
      BEGIN
            UPDATE [MicroangeloAutoIncrement].[dbo].[ClientSettings]
               SET [SettingValue] = @CurrentSetting + 1
                     ,@Update = 1
                     ,@NewSetting = @CurrentSetting + 1
             WHERE [Setting] = 'Client'
             AND   [SettingValue] = @CurrentSetting  
                 
            IF @Update = 0 -- UPDATED BY ANOTHER THREAD
            BEGIN
                  SELECT @CurrentSetting = [SettingValue]
                  FROM [MicroangeloAutoIncrement].[dbo].[ClientSettings]
                  WHERE [Setting] = 'Client'
            END
      END
     
      INSERT INTO [MicroangeloAutoIncrement].[dbo].[ClientSettingsTest]
           ([ClientSettingsTest])
    VALUES
           (@NewSetting)
     
      PRINT @NewSetting

END  

You can test this as described above by running the above query in 2 or more windows. This it the result in one window

(1 row(s) affected)
167970

(1 row(s) affected)

(1 row(s) affected)
167972

(1 row(s) affected)

(1 row(s) affected)
167974

Ok back in the beginning of the blog post I said you might need a pool of identities to avoid deadlocks. When will you need to do that you might ask? Well let me give you an example.
Let say for instance you have a client who does multithreaded calls to your database via wcf /web exposed service. This exposed service runs in a database transaction and you may need to insert a record and update another record within a database transaction.

If different threads access the same primary id then you can get deadlocks. How can you fix this? 

Well you can assign multiple primary ids i.e. a pool of ids with autoincrementing on each thread and a minimum maximum ud. This gives a pool of ids for the single client and can be done by using and modify the autoincrement code above to fix the issue.

Here is an example of what I mean.

CREATE PROCEDURE [dbo].[sp_RetrieveWebAccountNumber]
(
      @WebClientName as varchar(100)
      ,@CurrentAccountID as int OUTPUT
)
AS
BEGIN
      DECLARE @ID as int
      DECLARE @MinAccountID as int
      DECLARE @MaxAccountID as int
      DECLARE @Update as bit

      DECLARE @NewAccountID as int

      SELECT @ID = [ID], @CurrentAccountID = [WEBAccountNumber], @MinAccountID = [WEBAccountNumberMin], @MaxAccountID = [WEBAccountNumberMax]
            FROM [AccountSettings]
            WHERE [WEBClientName] = @WEBClientName
           
      IF @ID IS NULL
      BEGIN
            SET @CurrentAccountID = -1
            RETURN
      END
     
      SET @NewAccountID = @CurrentAccountID + 1
      IF @NewAccountID > @MaxAccountID SET @NewAccountID = @MinAccountID
     
      SET @Update = 0  

      WHILE @Update = 0
      BEGIN
            UPDATE [AccountSettings]
                  SET [WEBAccountNumber] = @NewAccountID, @Update = 1
                  WHERE [ID] = @ID AND [WEBAccountNumber] = @CurrentAccountID
                 
            IF @Update = 0 -- UPDATED BY ANOTHER THREAD
            BEGIN
                  SELECT @ID = [ID], @CurrentAccountID = [WEBAccountNumber], @MinAccountID = [WEBAccountNumberMin], @MaxAccountID = [WEBAccountNumberMax]
                        FROM [AccountSettings]
                        WHERE [WEBClientName] = @WEBClientName

                  SET @NewAccountID = @CurrentAccountID + 1
                  IF @NewAccountID > @MaxAccountID SET @NewAccountID = @MinAccountID
            END
      END
     
      -- Return the result of the function
      RETURN
END

You will need to have a pool count greater than the maximum number of threads to avoid deadlocks. Essentialy each id is serviced by each thread. Hence no more deadlocks.
 
So there you have it a multi threaded autoincrement stored procedure with min and max. Plus its thread safe.

Enjoy

Sunday, 17 June 2012

Installing Windows Azure FTP Cloud using both ACTIVE and PASSIVE mode

On a recent project there was a need to create a ftpsite using Windows Azure as the cloud service. By default Windows Azure does not support ftp. This blog describes how to install FTP using the WebRole.cs and specifically the public override bool OnStart(). It is done here so that if the Azure Virtual Machine is recycled the setup will be replicated in the new recycled virtual machine.

The first thing you will need is the FTP msi for IIS 7 / IIS 7.5. This is downloadable from http://go.microsoft.com/fwlink/?LinkID=143197
This can be put in blob azure storage or on the website itself. Lets just put it in the website under msi for now.

The following support methods are written which will be called by the public override bool OnStart().

1:ExecInProcess (This function is used to execute a command on the Azure Server)
2:GetExternalIP (Used for FTP in Passive Mode - Returns the External IP address) This function uses the public available http://checkip.dyndns.org/
public string ExecInProcess(string strCmd, string strParameters)
{
               try
            {
                               Trace.TraceInformation(string.Format("EXEC CMD {0} {1}", strCmd, strParameters));
                               ProcessStartInfo psipkg = new ProcessStartInfo(strCmd, strParameters);
                               psipkg.UseShellExecute = true;
                               psipkg.RedirectStandardOutput = false;
                               psipkg.RedirectStandardInput = false;
                               psipkg.RedirectStandardError = false;
                               Process procpkg = Process.Start(psipkg);
                               while (!procpkg.HasExited)
                                             System.Threading.Thread.Sleep(100);
               }
                catch (Exception ex)
                {
                                Trace.TraceError("ERROR: ExecInProcess. Cmd: {0} Params: {1} \r\n Details: {2}", strCmd, strParameters, ex.ToString());
               }
                return string.Empty;
}
public static string GetExternalIP()
{
               Regex regReplace = new Regex(@"[^\d\.]*", RegexOptions.Multiline|RegexOptions.IgnorePatternWhitespace);
                String direction = "";
               WebRequest request = WebRequest.Create(@http://checkip.dyndns.org/);
               using (WebResponse response = request.GetResponse())
               {
                              using (StreamReader stream = new StreamReader(response.GetResponseStream()))
                               {
                                               direction = stream.ReadToEnd();
                                }
                }
                direction = regReplace.Replace(direction, "");
                return direction;
}
Also we need to plan by setting up the ports to be opened etc. We are only allowed a maximum of 20 ports.Right client the Windows Azure Role

In the Settings TAB we will specify the FTP Values needed for configuration. See screen shot below.


In the Endpoint TAB we will need to specify both port 80/21/20000-20019. See screen shot below.

 
Then lets begin coding the WebRole.cs OnStart Function. But first let me describe what we hope to accomplish in the OnStart Function.

1: We retrieve the settings from the above using the RoleEnvironment class.
2: We determine where the website is installed. Usually drive E but when recycled drive F. I have put in drive C for good measure but that is unlikely. The code here could be better but I will leave it for now.
3: I Install the FTP7.5.msi for IIS 7. I exec the msiexec.exe to do this, obviously in silent mode.
4: I add the new ftp user using net.exe.
5: I grant the user access.
6: I create the FTP site
7: I allow SSL communication for good measure.
8: I configure the firewall to Allow FTP communication on port 21.
9: I then configure users to allow the specified user ftp access.
10: If I am to use passive mode I specify the range of ports to use. Note the limit in azure is 20. Also I set the external facing ip address to the ftp server configuration needed to run in passive mode.
11: I once again grant access to users
12: I configure the firewall to give ftp service full access.
13: I setup the ftp ssl authorization.
14: I restart the ftp service to reflect the changes made.

Now for the code

public override bool OnStart()
{
string strFtpUser = RoleEnvironment.GetConfigurationSettingValue("FtpUser");
string strFtpUserPassword = RoleEnvironment.GetConfigurationSettingValue("FtpUserPassword");
string strLowPort = RoleEnvironment.GetConfigurationSettingValue("FtpPassiveLowPortRange");
string strHighPort = RoleEnvironment.GetConfigurationSettingValue("FtpPassiveHighPortRange");
string strFTPMode = RoleEnvironment.GetConfigurationSettingValue("FtpMode");//ACTIVE or PASSIVE
string strIP = GetIP();//THIS IS INACCURATE:::RoleEnvironment.CurrentRoleInstance.InstanceEndpoints.ElementAt(0).Value.IPEndpoint.Address.ToString();
string path = @"E:\FTP";
string msiPath = @"E:\sitesroot\0\MSI\FTPIIS.msi";
try
{
       Directory.CreateDirectory(path);
       Directory.CreateDirectory(msiPath);
}
catch (Exception)
{
       path = @"F:\FTP";
       string msiPath = @"F:\sitesroot\0\MSI\FTPIIS.msi";
       try
       {
             Directory.CreateDirectory(path);
             Directory.CreateDirectory(msiPath);
       }
       catch (Exception)
       {
                path = @"C:\FTP";
                string msiPath = @"C:\sitesroot\0\MSI\FTPIIS.msi";
                try
                {
                       Directory.CreateDirectory(path);
                       Directory.CreateDirectory(msiPath);
                 }
                 catch (Exception){ }
        }
}
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\msiexec.exe", @" /i " + msiPath + @" /passive /l* " + msiPath + @".log");
//ADD The User to the access control list
ExecInProcess(@"net.exe", @"user " + strFtpUser + " " + strFtpUserPassword + @" /Add");
ExecInProcess(@"icacls.exe", path + @" /grant " + strFtpUser + @":(OI)(CI)F");
//ADD The FTP Site
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe", @"add site /site.name:MicroangeloFTPServer /+bindings.[protocol='ftp',bindinginformation='*:21:'] /physicalpath:" + path );
//ADD The FTPS Service
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe" , @"set config -section:system.applicationHost/sites /siteDefaults.ftpServer.security.ssl.controlChannelPolicy:SslAllow /commit:apphost");
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe" , @"set config -section:system.applicationHost/sites /siteDefaults.ftpServer.security.ssl.dataChannelPolicy:SslAllow /commit:apphost");
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe" , @"set config -section:system.applicationHost/sites /siteDefaults.ftpServer.security.authentication.basicAuthentication.enabled:true");
//Configure Firewall
ExecInProcess(@"netsh.exe", @"advfirewall firewall add rule name=""AllowFTP"" protocol=TCP dir=in localport=21 action=allow" );
//Configure Users
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe" , @"set config MicroangeloFTPServer /section:system.ftpserver/security/authorization /+[accessType='Allow',permissions='Read,Write',roles='',users='" + strFtpUser + @"'] /commit:apphost");
if (strFTPMode.ToUpper().Contains("PASSIVE"))
{
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe", @"set config /section:system.ftpServer/firewallSupport /lowDataChannelPort:" + strLowPort + @" /highDataChannelPort:" + strHighPort);
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe", @"set config /section:system.applicationHost/sites /siteDefaults.ftpServer.firewallSupport.externalIp4Address:" + strIP);
}
//Configure Users
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe" , @"set config MicroangeloFTPServer /section:system.ftpserver/security/authorization /+[accessType='Allow',permissions='Read,Write',roles='',users='*'] /commit:apphost");
//Configure Firewall
ExecInProcess(@"netsh.exe", @"advfirewall set global StatefulFtp enable");
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe", @"set config -section:system.applicationHost/sites /siteDefaults.ftpServer.security.ssl.controlChannelPolicy:SslAllow /commit:apphost");
ExecInProcess(Environment.GetEnvironmentVariable("WINDIR") + @"\system32\inetsrv\appcmd.exe", @"set config -section:system.applicationHost/sites /siteDefaults.ftpServer.security.ssl.dataChannelPolicy:SslAllow /commit:apphost");
ExecInProcess(@"net.exe", @"stop ftpsvc");
ExecInProcess(@"net.exe", @"start ftpsvc");
return base.OnStart();
}


Now you have Azure FTP Cloud Solution both Active/Passive using Windows Azure as the hosting platform.

Enjoy

Friday, 24 February 2012

Comparing old and new Asynchronous MSMQ Calls using IAsyncResult and the new System.Threading.Tasks.TaskFactory in .Net 4.0.


In this blog entry I thought I would describe the old way of doing Asynchronous Calls and the new way of doing Asynchronous Calls using System.Threading.Tasks.TaskFactory

Recently I have been doing some asynchronous calls using IAsyncResult and MSMQ.
Before I show the code I need to explain how it was done the old fashion way.

In the olden days of .NET 1.0 to 3.5 sp1 you would start by calling a BeginReceive(TimeSpan, Cursor, Object, AsyncCallback) or BeginPeek(TimeSpan, Cursor, Object, AsyncCallback). But because BeginReceive is asynchronous, you can call it to receive a message from the queue without blocking the current thread of execution.
EndPeek is used to read the message that caused the PeekCompleted event to be raised.
Once an asynchronous operation completes, you can call BeginPeek or BeginReceive again in the event handler to keep receiving notifications. The event handler here is the AsyncCallback(void PeekCompleted(IAsyncResult asyncResult)

In that callback of PeekComplete the EndPeek will return the Message from the MSMQ.

Here is the code for .NET 3.5 sp1 or earlier

msgqueueCurrentCursor = queue.CreateCursor();
 
queue.BeginPeek(new TimeSpan(0, 0, 0, 1, 0), msgqueueCurrentCursor, PeekAction.Current, 
0, new AsyncCallback(PeekCompleted));
while (true)
{
     Thread.Sleep(1000);
}
 
 
// Provides an event handler for the PeekCompleted event.
private void PeekCompleted(IAsyncResult asyncResult)
{
   try
   {
      bool bPeeking = false;
 
      // End the asynchronous peek operation.
      Message msg = queue.EndPeek(asyncResult);
 
      lock (queueMessagesToTransmit)
      {
         queueMessagesToTransmit.Add(msg.LookupId, msg);
      }
      bPeeking = true;
      queue.BeginPeek(new TimeSpan(0, 0, 0, 1, 0), msgqueueCurrentCursor, 
PeekAction.Next, 0, new AsyncCallback(PeekCompleted));
   }
   catch (System.Messaging.MessageQueueException msgqueueException)
   {
      if (msgqueueException.MessageQueueErrorCode == 
System.Messaging.MessageQueueErrorCode.IOTimeout)
      {
         queue.BeginPeek(new TimeSpan(0, 0, 0, 1, 0), msgqueueCurrentCursor, 
PeekAction.Next, 0, new AsyncCallback(PeekCompleted));
      }
      else
      {
         Trace.WriteLine(TraceName + string.Format("Message Queue Exception {0}", 
msgqueueException.ToString()));
         return;
      }
   }
   catch (ThreadAbortException thrdExc)
   {
      System.Diagnostics.Trace.WriteLine(TraceName + thrdExc.ToString());
      return;
   }
}

Now with the new Task.Factory in System.Threading.Tasks there is no need for the callback. This is managed with the function Task.Factory.FromAsync as shown below.

public Task<TResult> FromAsync<TResult>(
        IAsyncResult asyncResult,
        Func<IAsyncResult, TResult> endMethod
)

In our case for a BeginReceive from the message queue we could very easily do this without the callback function

Task<Message> ts = Task.Factory.FromAsync<Message>(queue.BeginReceive(), queue.EndReceive);
ts.Wait();
Message msg = ts.Result;

Not there is no typo above the EndReceive is a function parameter.

Now you might be asking this is a blocking call. And the quick answer is yes sort of. The Task.Wait() is actually acts like a Thread.Sleep(). So if you created a thread to do this you wont need to worry about the thread and the code is rather simple as shown above. And other threads can process while you wait for the transfer a message inside the MSMQ.
As with all threads commonly used I have seen they loop with a Thread.Sleep(). This is now no longer needed. This actually gives maximum throughput without blocking therefore getting the best performance for reading from the MSMQ.

Now I include the code in its entirety. This code would be the thread called function running in the background while you do other processing. You would do a Thread.abort() to terminate it.

Here is the code .NET 4.0 or later

try
{
Task<Message> ts = Task.Factory.FromAsync<Message>(queue.BeginPeek(new TimeSpan(1, 0, 0, 1, 0), msgqueueCurrentCursor, PeekAction.Current, 0, null), queue.EndPeek);
ts.Wait();
Message msg = ts.Result;
Console.WriteLine(msg.Id.ToString());
while (true)
{
Task<Message> tsnext = Task.Factory.FromAsync<Message>(queue.BeginPeek(new TimeSpan(1, 0, 0, 1, 0), msgqueueCurrentCursor, PeekAction.Next, 0, null), queue.EndPeek);
      tsnext.Wait();
      Message msg = tsnext.Result;
      Console.WriteLine(msg.Id.ToString());
}
}
catch (ThreadAbortException thrdExc)
{
   System.Diagnostics.Trace.WriteLine(TraceName + thrdExc.ToString());
   return;
}

Please refer to this forum entry where I was unsure of how to accomplish this.


Here is where the new Task.TaskFactory is documented. http://msdn.microsoft.com/en-us/library/system.threading.tasks.taskfactory.aspx

P.S. On a side note I have done this for both sockets, sql and streams. If you need me to blog how to do that then post a comment and I will do my best.

As always happy asynchronous programming

Command Script Using Appcmd.exe to configure AutoStart WCF in IIS


In this blog entry I will talk about how to configure auto starting WCF / HTTP exposed services. I will provide a script below that can be executed to enable auto start for the website.

In IIS, you can configure an application pool and all or some of its applications to automatically start when the IIS service starts. This is available in (IIS) 7.5 which is included in Window 7 or Windows Server 2008 R2.

When you enable the auto-start feature for a service, the service is up and running as soon as the application that it belongs to is started and before the service receives the first WCF message from the client. Therefore, the service processes the first message quickly because it is already initialized. For example, suppose a service needs to connect to a database to read hundreds of rows of data to populate a .NET Framework caching data structure when it is first created. The initialization processing takes a long time before the service is ready to begin the actual operation. If you use auto-
start in this case, the service is initialized before it receives the first call.


Lets look at the script below which will create and autostart an application pool.

Firstly we start with creating a new application pool call MyAutoStartAppPool
 
%windir%\system32\inetsrv\APPCMD.exe add apppool /name: MyAutoStartAppPool
 
We then set the properties autoStart:true and startMode: AlwaysRunning
Also I set the .NET framework to v4.0 and Enable 32 bit processing. These can be skipped if not required.
 
%windir%\system32\inetsrv\APPCMD.exe set apppool MyAutoStartAppPool
 /managedRuntimeVersion:v4.0 /autoStart:true /startMode:AlwaysRunning
%windir%\system32\inetsrv\APPCMD.exe set apppool MyAutoStartAppPool
 /enable32BitAppOnWin64:true" 
 
We then set the identity of the app pool
 
%windir%\system32\inetsrv\appcmd.exe set config /section:applicationPools
 /[name='MyAutoStartAppPool'].processModel.identityType:SpecificUser
 /[name='MyAutoStartAppPool'].processModel.userName:user
 /[name='MyAutoStartAppPool'].processModel.password:password 
 
Finally we then set the website in this case “Default WebSite/” to the application pool created. Then we set the website serviceAutoStartEnabled: true serviceAutoStartMode: all and serviceAutoStartProvider: Service
 
%windir%\system32\inetsrv\appcmd.exe set app /app.name:"Default WebSite/"
 /applicationPool: MyAutoStartAppPool 
 
%windir%\system32\inetsrv\appcmd.exe set app /app.name:"Default WebSite/"
 /serviceAutoStartEnabled:True /serviceAutoStartMode:All /serviceAutoStartProvider:Service

Now any hosted WCF service or website is autostarted. I sometimes put processing code and populate memory caches in global.asax.cs in the Application_Start method.

This autostart feature makes the code startup quicker and stay started.