Sunday, March 29, 2015

ssh without using password

I have 3 laptops at home, a Linux Server and 2 Windows 8 Machine and a switch back and forth between them.  I have installed ssh-server on the Linux Server  and from windows I ssh into it and then work on it.

Today's blog entry is very simple. Since I didn't want to enter my password every time I login into the server.  These are the simple steps you can follow and get the basic understanding.

You need to have SSH service running on the Linux Server, you can quickly check that by running this command

service --status-all | grep "ssh"

 Then on the Windows Server download my fvt XTerminal on Windows, which is MobaXterm which you can download by clicking here.

1) The main thing is on the client you need to generate a set of ( Public + PrivateKey) using RSA encrypting algorithm (preferable).

ssh-keygen -t rsa

This will create 2 files (id_rsa,   which is the Private and PublicKey respectively.

2) Then you need you copy the public key on to the Linux Server. You can do 2 using the following command.

ssh-copy-id Username@Host

3) Once you have copied the file, you need to append the contents of the file into .ssh/authorized_keys file.

4) Now you can use the same private key from any of the Windows Boxes and Login into the Linux Server without the password.

On the Linux this the Authorized file contents.

When you generate the private and public key, you will get an interactive prompt to use password, you can use one if you like to more secure, incase the privateKey file gets compromised.

You can view the ssh logs if you encounter any error while logging in . Its under /var/llog/auth.log file.


Sunday, November 16, 2014

SQL Server table as a Queue with SQL Hints

Queues can be implemented in SQL Sever using Tables. Consider this scenario, we have 1 Producer and N number of consumers which pick up the job and Execute it. There are issues with this that needs to be take care of

There are 3 concurrency issues we have to keep on eye on in any asynchronous application in Sql Server, namely

1) Dirty Reads  ( Doesn't happen in READ COMMITTED isolation level)
2) Non repeatable Reads (Can happen in READ COMMITTED isolation level)
3) Phantom Reads  ( Can Happen in READ COMMITTED isolation level)

In this scenario, the issue is

1) Consumers can pickup the same job and start executing it, without knowing other consumer has picked up the job.

Create Table [Queue]
JobName NVARCHAR(256),
IsExecuting bit,
LastExecutionTime DATETIME2
INSERT INTO Queue(JobName,IsExecuting,LastExecutionTime)
Declare @JobId INT
Declare @Count INT
SET @Count = 0;

WHILE (@Count < 5)

   -- Pick up a job 
   SET @JobId = (Select TOP 1 JobId from [Queue] WITH (ROWLOCK,READPAST)  WHERE IsExecuting = 0 order by LastExecutionTime asc)
   -- Print the pickedup jobId 
   PRINT @JobId
   SET IsExecuting = 1
   WHERE JobId = @JobId
   -- SIMULATE THE JOB TIME (Move this out of transaction since you dont want to lock the table for that duration)
   WAITFOR DELAY '00:00:10'
   -- Update the JobId after executing it
   SET LastExecutionTime = DateAdd(Minute,1,GETDATE()), IsExecuting = 0
   WHERE JobId = @JobId

   PRINT Concat('COMPLETED ', @JobId)
   SET @Count = @Count + 1

Using the sql query hint  (ROWLOCK and READPAST), we can gurantee that the same row will not be picked up by the other transaction.

READPAST -- (As per MSDN Documentation)  "Specifies that the Database Engine not read rows that are locked by other transactions. When READPAST is specified, row-level locks are skipped. That is, the Database Engine skips past the rows instead of blocking the current transaction until the locks are released. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

Hope this is useful and will help to recude concurrency issues with queues.


Tuesday, July 29, 2014

Running Total

Often when you are creating reports you want to do the Running Total  eg. Sum = Current Sum + (Sum of all the preceding values).

There are many ways to do this. I just wanted to outline some of the standard techniques used to do this and the new feature in 2012 which makes it very easy and fast.

1) Using Non-Equi Joins

Select A.PhysicalFileId,A.BytesWritten,A.CreatedDate, 
Sum(B.BytesWritten)as RunningTotal from #X A
inner join #X B  on B.PhysicalFileId <= A.PhysicalFileId
group by A.PhysicalFileId, A.BytesWritten,A.CreatedDate
2) My personal favorite -- Correlated Query

Select A.PhysicalFileId, A.BytesWritten,A.CreatedDate , 
(Select Sum(B.BytesWritten) from #X B where B.PhysicalFileId <= A.PhysicalFileId) as RunningTotal
from #X  A
3) Available in SQL 2012 and beyond and fastest of all, using Window Functions

Select A.PhysicalFileId,A.BytesWritten,A.CreatedDate, Sum(A.BytesWritten) Over( 
Order by A.PhysicalFileId
) As RunningTotal from #X AS A

You can find the sql queries at ::!6/40d6e/3/0

Until next time!


Sunday, July 27, 2014

Pivot table and its uses

Recently I started working on a BI project, which is a new domain for me. I always wanted to work in a Business Intelligence project since that was one area I didn't cover. I have been learning lot of new concepts which are interesting. We generate lot of reports which are used by the management teams to make proper business decisions and for that we use Self Service Power BI. If you have done reporting you will know, Pivot table is one of the most fundamental way of generating Cross-Tabular Reports.

Pivot tables are basically used for 2 purpose

1) To generate cross tabular reporting i.e converting the rows into columns.
2) With Entity-Attribute table ( which is a Key-Value kind of table, like a dictionary)

As an example ::  I have 2 table with one to many relationship, which are used to record Api Success/Failure .

[dbo].[ApiLookUp]  (1) 
[dbo].[ApiHealthMonitoring]   (Many)

You can see the Table Schema and Queries here  ::!6/d5fbf/3

The best way in my opinion to write Pivot query is to follow these steps

1) Write the Base query i.e the table for which you want to convert rows into columns .
     This becomes your Base Query
2) Select the Aggregate Column from one of the columns of the table, created by the Base query, for the columns you want to pivot on.
3) Select the Columns as the output of the  Pivot Query

One thing worth mentioning about Pivot table is  that Columns in the Pivot table need to be hardcoded. So if the values of the Column on which you are pivoting then you need to either change the query frequently or use dynamic sql.

This same thing can be achieved very easily using the Excel - Power Pivot feature. It is very powerful and simple to use. All you need to do, is to import the tables from the Sql Server Database. Add a pivot table and select the appropriate columns. You can even generate interactive graphs and publish it to SharePoint using power query and power pivot. The deployment story for Excel reports is very powerful and easy to use by even non programmers.

You can also add slicers to the table which help in slicing/dicing of the data. Pretty powerful.

Until next time!


Sunday, April 6, 2014

Love for Powershell!

This is a quick blog entry, to appreciate my love for powershell. I have been running into Low disk space on my work computer, and I wanted to fix this issue.

Powershell to the rescue!

Simple powershell command and I was able to get the files which is occupying max disk space and then go from there.

Get-ChildItem -Path C:\ -Recurse | Where-Object {$_.PSIsContainer -eq $false} | 
sort-Object -Property Length -Descending |
Select-Object -First 10 | 
Format-List -Property FullName,Length,
@{name = "SizeInMB";expression = {$_.Length / 1MB}}

I have always been a commadline guy and I really enjoy writing small useful scripts!


Saturday, January 11, 2014

AutoResetEvent And ManualResetEvent

AutoResetEvent and ManualResetEvent are one of the low-level Thread signaling mechanism in C#. The main reason for writing this blog entry is to differentiate between these 2 WaitHandles.

AutoResetEvent is the like the devices you see in Airport parkings, where you punch you card, and then you can get in, but as soon as you get in, entry closes. The next person has to again punch the card.
You can see from the output of the program, there are 2 Threads and both are waiting on a Set Signal. Since only 1 set signal is received, only one thread can resume the operation while the second thread continues to wait.

ManualResetEvent are like a door. Once you open the door, the door is kept open till someone closes it. So in the code below, both theads are waiting on a Set signal to resume there operation. As soon as the Set Singnal is received, both the thread resume there operations.
        static void Main(string[] args)
            ManualResetEvent manualResetEvent = new ManualResetEvent(false);
            AutoResetEvent autoResetEvent = new AutoResetEvent(false);

            Thread t1 = new Thread(() =>
                Thread.Sleep(1000); for (int i = 0; i < 10; i++)
                    if (i == 5)
                        Console.WriteLine("Waiting to be signalled -- Thread 1");
                        Console.WriteLine("Signal Received, continuing -- Thread 1" );

            Thread t2 = new Thread(() =>
                Thread.Sleep(1000); for (int i = 101; i < 120; i++)
                    if (i == 110)
                        Console.WriteLine("Waiting to be signalled -- Thread 2");
                        Console.WriteLine("Signal Received, continuing -- Thread 2");

            Console.WriteLine("In the Main function");

Tuesday, December 10, 2013