Friday, July 7, 2017

First three weeks as New Development Manager.

On Friday, June 16th I have officially stepped into my new role as New Development Manager at Civica CMI. Today completes my first three full weeks in the role. I would be lying if I said it was not a rollercoaster of up and downs trying to navigate new role while at the same time providing leadership to people who are very confused by sudden changes. For the first time today I feel like I have finally can take a short breather before diving back into a hectic world of managing software development.

In the long list of responsibilities for my role included a need to hire several developers to replace vacant position as well as new ones. I'm very excited to say that as of today I have hired three new employees, two of them starting on June 17th and one on June 24th. Two weeks from today I will have final follow-up interview for last open role on my team. I am grateful that I had an opportunity in the previous job to be part of interview process which taught me a lot about finding right candidates and filtering out not qualified ones. Only time will tell whether or not I made correct decisions, but I am willing to stand by everything and accept responsibility. If I screwed up, just need to own up to it and seek to do better next time.

Later this year I expect that I will need to expand IT department to include DBA, BI Developer, and possibly ETL Developer. My goal is to leverage my involvement in SQL Saturdays to utilize right avenue for finding candidates. Civica CMI is sponsoring SQL Saturday Columbus which gives me a great avenue to reach qualified candidates in the local market. In regards to a cost, it comes in a fraction of what anyone has to spend on trying to hire people through recruiting agency. Additionally, why not hire candidates who are taking time on a weekend to better themselves and to be prepared for their roles.

For now, I am loving being in new position even though it came with a huge list of responsibilities and challenges. Time at work flys quick and I put in a lot of overtime which has not bother me one bit. It seems that in the short time of being in this role I only managed to spend company's money on giving out well-deserved raises and hiring of new personnel. Time will tell how changes that I am implementing will affect company's bottom line. Just need to keep a positive attitude and push forward.

Tuesday, May 9, 2017

T-SQL Tuesday #90 – Shipping Database Changes

Last month I finally figured out what is a TSQLTuesday after I have "accidentally" used a hashtag that I did not fully understand. This time around I want to contribute to the topic of a month: "Shipping Database Changes."

As a full-time developer, this particular subject is close and dear to my heart. When it comes to Shipping Database Changes, it is an absolute necessity for all developers. Since this is such a broad subject, I want to focus on specifics of how I manage it in my current work environment.

First a little bit of a background on my current role and the company I where I work. My official title is a Senior Software Developer for CivicaCMI, my primary responsibility is to developer new software. Even though I end up writing my share of .NET code at the end of the day I am a SQL Server developer who writes hundreds of lines of T-SQL a week to produce software that matches specification.

Most of my work is broken down into ticket items that request a specific feature or address a particular bug. I would say that 90% of update requests take only from several minutes to several hours to complete. As long as changes restricted to a single object, it is very easy to generate a script file. I have adapted a habit of creating change scripts as soon as I finish development of the item. This can be both good and bad. It is good that I already have a file that I can check-in to version control, but bad if QA finds a problem and I need to make multiple changes before it passes testing.

Another approach that I have to resort to is running Redgate Schema Compare software to identify all changes between development and test servers. Depending on developers working on the same database and number of unfinished changes it is possible to generate too many changes that do not need to promotion. Thus it is possible to spend extra time sifting through before creating correct change script. After having to do this process multiple times as a last resort. I have implemented a rule for each developer to generate their scripts. Otherwise, I have found myself spending almost entire day packaging their changes, and in the end, I still had to verify that work was complete.

After trying many things that did not seem to work, I realized that it is a combination of software and procedures that enable streamlined process for shipping database changes.

Software:

I have found through trial and error the following list of applications enabled me to achieve desired results.
  • Redgate Source Control - Very useful to keep track of all changes between builds. Moreover, very often I use it to rollback or troubleshoot my changes.
  • Redgate Schema Compare - Primary software I use to identify differences between development and test servers
  • SSMS 10.50 - Even though I use latest SSMS (14.0) for T-SQL development, whenever I need to generate DROP and CREATE script only SSMS 10.50 produces by default IF EXISTS DROP scripts for all objects.
Now before I go any further I would like to put a disclaimer about, DROP/CREATE of SQL objects. Since the application is connecting with specific SQL User, I do not have to worry about the issue with dropping permission in production. Additionally, because users are out of the application during the upgrade, I do not need to do anything special for handling multiple version of the same object running at the same time.

Procedures/Rules:

Now, these are not in any particular order.
  • Your own scripts - Each developer is responsible for generating their own scripts. If time permits I still verify it but I do not spend extended amount of time going over it.
  • Script files - First of all I require that only one SQL Object is modified in a file. This way if there is an issue with that file during deployment, the new version can be checked in to fix the problem.
  • File names - I have implemented the following standard.
    • 10_schema_objectname - this is used only for scripting changes to tables and other base object types.
    • 20_schame_objectname - this is used for all views because files applied in alphabetical order it is guaranteed that all base tables are in the database before any change to views.
    • 30_schema_objectname - this files include all data changes, like updates to lookup tables and other static data that is not usually changed from within application
    • 40_schema_objectname - this is done for all SP, FN and other SQL Server objects. If there is a dependency on another SP or FN, it is required to change a dependent object to a higher number like 41 or 42. In some cases of multiple nested dependencies, it is possible to go higher in numbers. Nonetheless, scripts arranged in a manner that ensures all dependent objects created in right order.
  • DROP/CREATE - All views, SP and FN are scripted as IF EXISTS DROP GO CREATE. This guarantees that if a script run on a client with version mismatch it does not fail. Alternative for other people who have to worry about losing permission in production system they can script IF NOT EXISTS CREATE GO ALTER

When it comes to actual deployment to Test and production servers, it is handled by application update program that runs scripts on the target server one by one in alphabetical order. Since we have clients running different versions, scripts always have to be applied in order, for example, if the customer is on version 1.5 before the could get 2.5 they need 2.0. This ensures that database changes are applied in correct order, and I don't have to worry about something breaking.

One last problem that I have to deal with on a regular basis is Version-drift. This is caused when I manually patch a client for a fix without going through the proper build process. In those cases, I just have to manually merge changes into development to guarantee that it will make it out to other clients. Once in a while, it becomes quite complicated to keep track of different clients running different versions and how to ensure that if they need a fix, it is not something that could be resolved through update versus manual code changes.

Conclusion

Even though this is not a full-proof plan, it seems to work for me and organization where I work. Over last 12 month of my employment, I have been part of multiple major and minor build of the software. I realize that it is possible that for some developers none of the things I have described are of any use while for others they can easily relate to them and possibly even adopt some into their process.

Thanks for reading. Don't forget to leave comments, questions or concerns. I always welcome constructive criticism.

Tuesday, April 11, 2017

THROW versus RAISERROR

Error handling is just as important as verifying that your code runs and produces correct results. For developers, error handling is last chance to prepare code to fail gracefully instead of just blowing up and having to pick up pieces later.

When it comes to SQL Server, there are few options for error handling. Basics of error handling are wrapping the code that is likely to fail inside the TRY block and then dealing with failures inside the CATCH block.

Let's look at a most basic scenario below.
 
SELECT 1 / 0

SELECT 1
The first statement generates the error division by 0, but the second statement still executed as nothing is controlling what happens when errors encountered. In some cases, this is perfectly fine, but in others, we want to stop the execution of our script in the event of encountered errors. We might even want to roll back the transaction to prevent partial data change.

Now if we take the same code and place it inside TRY block we get different behavior. In this case, the first statement generates the error but the second statement is not run.
 
BEGIN TRY
SELECT 1 / 0

SELECT 1
END TRY
BEGIN CATCH

END CATCH

Now that we covered the basics let's look at controlling a flow of the code through the use of RAISERROR and THROW. In some cases, we need to fail code based on business rules which pass syntax test. For example, if our procedure is responsible for withdrawing money from an account, we might have a business rule that prevents going into negative. For the first example, we are just going to try to withdraw money.
 
CREATE PROCEDURE dbo.WithdrawMoney
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID = @AccountId
    END
In this example we can keep withdrawing money until arighmetic overflow error will be thrown for creating large negative number. Since we want to avoid negative balance we are going to add code to where clause.
 
CREATE PROCEDURE dbo.WithdrawMoney
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID              = @AccountId
                AND (Amount - @Amount) > 0
    END
Now, whenever you try to withdraw money that results in negative amount update will "fail." This failure is only reflected by the number of rows affected. In this instance, it is zero. Now we need something in front end application that reads the number of rows affected by stored procedure to understand what happened.

Another way of handling this would be to run a check first and to generate the error that is sent back to front end. So we write two stored procedures one using RAISERROR and another one using THROW and expect the same result.
 
CREATE PROCEDURE dbo.WithdrawMoney_RAISERROR
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        IF EXISTS
            (
                SELECT  1
                FROM    dbo.Accounts
                WHERE   AccountID              = @AccountId
                        AND (Amount - @Amount) > 0
            )
            RAISERROR( 'Insufficient Amount in account to perform withdrawal', 16, 1 )

        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID = @AccountId
    END
GO
CREATE PROCEDURE dbo.WithdrawMoney_THROW
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        IF EXISTS
            (
                SELECT  1
                FROM    dbo.Accounts
                WHERE   AccountID              = @AccountId
                        AND (Amount - @Amount) > 0
            )
            THROW 51000, 'Insufficient Amount in account to perform withdrawal', 1

        UPDATE  dbo.Accounts
        SET Amount = Amount - @Amount
        WHERE   AccountID = @AccountId
    END
Upon executing the first procedure, we get the error message back to the front end, but after checking balance, we find that money withdrawn from the account, but in the case of the second procedure, the same error returned to the front end but money still there.

Now we begin to scratch our head trying to figure out why we lost the money even though we got errors in both cases. The truth behind is the fact that RAISERROR does not stop the execution of code if it is outside of TRY CATCH block. To get same behavior out of RAISERROR, we would need to rewrite procedure to look something like following example.
 
CREATE PROCEDURE dbo.WithdrawMoney_RAISERROR
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        BEGIN TRY
            IF EXISTS
                (
                    SELECT  1
                    FROM    dbo.Accounts
                    WHERE   AccountID              = @AccountId
                            AND (Amount - @Amount) > 0
                )
                RAISERROR( 'Insufficient Amount in account to perform withdrawal', 16, 1 )


            UPDATE  dbo.Accounts
            SET Amount = Amount - @Amount
            WHERE   AccountID = @AccountId
        END TRY
        BEGIN CATCH
            DECLARE @ErrorMessage  VARCHAR(4000)
                  , @ErrorSeverity INT
                  , @ErrorState    INT

            SELECT  @ErrorMessage  = ERROR_MESSAGE()
                  , @ErrorSeverity = ERROR_SEVERITY()
                  , @ErrorState    = ERROR_STATE()

            RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState )
        END CATCH
    END
Now we added another complexity to the procedure because if we wrap RAISERROR in TRY CATCH block, we suppress the error and the front end left clueless as to what happened inside stored procedure. Therefore we need to "rethrow" it by catching the information about it and doing another RAISERROR. If we choose to do the same with THROW, it is a whole lot simpler. Just look at the next example.
 
CREATE PROCEDURE dbo.WithdrawMoney_THROW
    @AccountId INT
  , @Amount    DECIMAL(18, 2)
AS
    BEGIN
        BEGIN TRY
            IF EXISTS
                (
                    SELECT  1
                    FROM    dbo.Accounts
                    WHERE   AccountID              = @AccountId
                            AND (Amount - @Amount) > 0
                )
                THROW 51000, 'Insufficient Amount in account to perform withdrawal', 1


            UPDATE  dbo.Accounts
            SET Amount = Amount - @Amount
            WHERE   AccountID = @AccountId
        END TRY
        BEGIN CATCH
            THROW
        END CATCH
    END
In this case, we simply add THROW to CATCH block which acts as "RETHROW." Unfortunately for me, I write most of the code for SQL Server 2008 R2 which did not have THROW command. It is only available starting with SQL Server 2012. Therefore I have to use RAISERROR and always remember to wrap it inside TRY CATCH block to ensure that it always stops execution when I want it to do so.

Be sure to leave a comment and thank you so much for reading.