Viewing entries tagged
relational database

Comment

Implementing SQL Source Control

Every developer working with databases and backend magic needs DB source control. If you think you don’t, then you are crazy, or will become crazy soon after you realize it. But not every developer has the money to pay for the awesome RedGate SQL Source Control tool (~$1500 per developer, or ~$6000 for a team of 5). Sometimes, the ramen noodles expression must be interpreted literally. So how do you resolve/implement this problem? You may want to follow K. Scott Allen’s database version control blog series (links below).

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging

These posts are great for the conceptual background and practices when doing the change scripts and such. The problem is there is no much implementation in the series to help speed up the process. To complement the series, you can DOWNLOAD my implementation of SQL Source Control. It consist of roughly of 4 tables and 4 sprocs. If you want to implement it on your SQL Server Database, download this update script and run it against your database (make a backup first, just in case).

Here is the ERD of the version control entities:

To use it, just create your change script (maybe follow some of the advice from Scott Allen), and follow this template to add the final touches to the script. Essentially all you have to do is call the Database_Update sproc and inspect the DatabaseErrorLog table whenever you encounter a problem to find more details.

[sourcecode language="sql"] BEGIN TRY

BEGIN TRAN

DECLARE @DatabaseVersionLogId INT , --This is for later, you may needed if you want to report to a CI Server. @UpdateFromDatabaseVersion VARCHAR(32) = '1.2.3' ,--The database version you want to update. This is your starting point. @UpdateToDatabaseVersion VARCHAR(32) = '1.2.4' ,--The new version of the database after successfuly updated @UpdateScriptName VARCHAR(64) = N'''1.2.3 to 1.2.4''' ,--A friendly name for this script @UpdateScriptFileName VARCHAR(1024)= N'''123 to 124.sql''' ,--The file name containing this script. @CreateScriptName VARCHAR(64) = N'1.2.4' ,--A friendly name for ANOTHER script (not this one) that generates the new schema from scratch as opposed to from the @UpdateFromDatabaseVersion database @CreateScriptFileName VARCHAR(1024)= N'124.sql' ,--The file name containing the @CreateScriptName script @Tags VARCHAR(MAX) = N'1.2.4, 1.2.3, super release'--A CSV for tagging and searching your releases later on.

-- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION -- ADD YOUR CHANGE SCRIPT IN THIS SECTION

EXEC @return_value = [dbo].[Database_Update] @UpdateFromDatabaseVersion, @UpdateToDatabaseVersion, @UpdateScriptName, @UpdateScriptFileName, @CreateScriptName, @CreateScriptFileName, @Tags, @DatabaseVersionLogId = @DatabaseVersionLogId OUTPUT

SELECT @DatabaseVersionLogId AS N'@DatabaseVersionLogId'

PRINT ( 'All good. Update finished... now go open a beer' )

COMMIT

END TRY

BEGIN CATCH

PRINT ( 'Michael Jackson''s moonwalk. The script is a piece of shit. Fix it and try again. Your database is safe.' )

ROLLBACK

END CATCH [/sourcecode]

You can download the same script HERE

Enjoy and Happy coding!

Comment

Comment

Letter from a MS Access hater!

I hate MS AccessI am a very frustrated man right now. I happen to be working on a project to “fix” a MS Access database some genius business user created with a ton of crappy forms, redundant data, thousands of data anomalies, zero relationships, unimaginable naming conventions, and the craziest data structures I have ever seen in my life. Somehow these people have been using this Access database to run their business for YEARS now, and the only reason they needed someone to look at it, is because the geniuses that created it made a giant table with columns that represented a combination of Month and Year where they would add some data as time went by. Every month they will come to this table and add a couple of columns to continue adding crap for the next month and so on. Well, they ran out of columns in the Access table (up to 255) and now they are stuck with it.

I just wanted to shoot myself in the head when I saw the db for the first time. My first suggestion was “Let’s do it right. Let’s port this to SQL Server Express (FREE), normalize it, create a couple of views, and you can still use your forms with the correct data model” but off course, they do not want me to do that, even after I explained that trying to fix this database will take more time and money because of how flawed it is, they still wanted their old stuff, full of errors and inconsistencies. They want me to fix this piece of shit. Unbelievable!

The situation made me think again about how businesses that are not in the IT space, see their IT and software development efforts as a liability instead of an asset. Why is it so hard for some companies to embrace technology? Unless your business is that of fighting against IT (and I can’t think of a business model based on that premise), they should embrace technology as a tool to improve business processes and enable collaboration between your clients, partners and your workforce. Every time I come across a corporation closing the doors of innovation, creativity and the enablement of IT to support and improve business processes, I see a dying mammoth. Such companies are doomed for failure, no matter the size or industry they are in.

Anyways… let me get back to my grinding Access DB. Ufff… I just needed to let out some steam.

Comment