Using Black Falcon Software’s SQL Server Source Control for Developers

Overview

Application source control is a veritable standard in all well-run Information Technology organizations. However, it is surprising how many small organizations seem to believe that individual developers merely saving backups of their projects on a remote server is more than enough for source control processes.

It is even more surprising how many quality IT organizations that do not have formal database teams ignore database source control thinking that the backed-up databases housing the scripts are more than enough for such a vital development process.

To some degree, the lack of such control on databases is understandable given that there are few products available for organizations to avail themselves of. In fact, the only substantial products available are for Microsoft’s SQL Server database, while many other database engines are involved in production processes throughout the international IT community.

For Microsoft’s SQL Server, both ApexSQL and Red-Gate offer excellent products for teams and as a result, are rather expensive for individual professionals to afford. However, Black Falcon Software now offers a far more affordable solution for professionals looking to incorporate database source control into their development processes that will allow them to avoid the pitfalls of having their database scripts deleted or modified by others accidentally while under development when source control is not available.

Such a situation can be a traumatic situation when one has developed quite a number of new tables, procedures, or other such objects in a test database only to have them suddenly disappear as a result of a colleague’s work on the same database. And this is a common occurrence since test databases in many instances tend to be regarded as reusable by those working with them.

To begin with, Black Falcon Software’s SQL Server Source Control for Developers is not designed for teams but instead for the individual professional who wants and\or needs to maintain ongoing versions of all the database object scripts that they are developing without the more elaborate requirements of a remote source control systems such as TFS or Subversion. Stored in local repositories on the desktop, developers can now implement easy-to-use database source control for SQL Server that are protected from accidental modifications by colleagues or worse, complete database refreshes when it is felt that such a refresh is required. Under either condition, recreating original scripts can be a taxing experience.

Black Falcon Software’s product has a very simple set of administrative source control processes.

Starting with the administration of the application, the developer is presented with the following options…

*** click on the images to enlarge them

Installation

In a word, there is no installation required for the application itself. However, the application does require Microsoft’s “Shared Management Objects” (SMO) for SQL Server, which must be installed prior to being able to use the version control features of the product. The installation packages (32bit & 64bit) for the SMO software are provided with the product downloadable zip-file.

Other than this, the only requirement is that the machine the product is to be run on has the .NET 4.5 Framework or higher installed.

Administration

The “Repository Management” option allows the developer to set up as many local repositories as he or she requires. These repositories are where all of the database scripts will be stored for versioning purposes.

The “Message Logs” option provides the developer with logging information for both application-wide processes (i.e.,: the creation of a repository) or repository-specific processes such as when the import of a database’s scripts is run.

The “Options” selection allows the developer to make some changes to certain application parameters such as the interface theme.

Starting with the “Repository Management” option, there are four sub-options available to manage the creation and maintenance of the repositories…

“Create\Edit\Delete Repository”

With this option, the developer is presented with the screen shown above.

Here, a new repository can be created with all the necessary tables freshly initialized, modified if already in existence, or deleted if no longer found to be necessary.

For small and medium sized databases, a single repository should be enough to store all of their object scripts. However, for large and very large databases where there will be many objects, a single repository for each such database should be considered so there is no concern that a repository may run out of space for all the storage requirements.

Each repository can be up to four gigabytes in size, which is quite large. However, just to be on the safe side, a specified, single repository for a large database will ensure that space is never a concern.

Notice that in this screen, there is no database linkage available at this time. This will come with the next two sub-options.

“Set Repository”

Once the initial repositories that will be required have been created, only one can be selected to be worked with at any one time. Thus, in this option, a list of all the existing repositories will be provided from which the one that is to be worked with can be selected.

Any repository can be either selected or reselected at any time, which is what this option provides for. This option merely tells the application that for any and all work after a selection is made, the selected repository is to be used.

This is a little different from using any of the popular remote source control systems where all corresponding repositories are always available for the databases being supported. However, this product is a desktop application, which comes with a few minor restrictions as a result.

The current database used for the repositories is Microsoft’s SQL Server CE (Compact Edition), which has the restriction of the four gigabyte size for each database. As a result, Black Falcon Software may consider sometime in the future that SQL Server CE may be replaced with SQLite, which has no such restriction. However, even so, the ability to create multiple repositories will be maintained as a feature of this product.

“Create\Edit\Delete Repository Database Connections”

Once a repository has been selected to work with the database, linkages can now be set up with the screen shown above.

It should be noted, as the sample screen demonstrates, that if a repository has not been selected prior to bringing up any screen that requires a selected repository, a “No Repository Selected” message will be displayed at the top of the respective screen. As a result, if this message is being displayed, the screen being displayed will not be able to process any data entries.

This situation will usually only occur when the application is being used for the first time and the user has not yet created a repository or has forgotten to select an existing one.

Assuming that a repository has been created and selected, the developer will now be able to enter connection-string information for as many databases and their objects are to be stored in the selected repository.

When a set of entries has been completed (and saved or not), the viability of the connection can be tested with the broadcast-emitter command button at the far right to the standard command buttons.

“Clear Repository”

With this option, a developer may delete any or all database objects scripts for a selected database. This allows a developer to keep their repositories from getting too cluttered with old versioned scripts when there is no longer a necessity to keep them in storage.

“Message Logs”

There are two message logs that the application uses; one is for the application itself, which is stored in the application database; and the other is found in each repository database that is created.

All application-wide event information (information & errors) are stored in the application’s message log table. All such events correspond to processes that have nothing to do with processes being run against a selected repository such as the creation of one.

All repository-specific event information is stored in each repository as each is selected and has processes executed against (i.e.: database object\script import).

The screens that are provided with each of the message log options are fairly similar as can be seen below…

Both screens provide options to clear each of the message log types of their data.

“Options”

This screen allows the developer to change a few application options to his or her preferences. This includes the use of the default theme (blue) or a standard black and white theme; whether strict validation standards are used for all entries and the size of the pages shown in the message logs.

Source Control

The “Source Control” menu allows the developer to import either a selection of database object scripts into the selected repository or export the scripts individually by version number.

“Import Database Objects”

The screen shown above allows the developer to import a selected set of database object scripts or all object scripts for a selected database. There are 12 types of objects scripts that can be imported for versioning purposes…

• Tables\Indices
• Views
• Stored Procedures
• Triggers
• User Defined Functions
• User Defined Data Types
• User Defined Tables
• Sequences
• Synonyms
• Aggregates
• XML Schema Collections
• Queues

The display area in the bottom portion of the screen provides line-item information on each script imported if any are found for the current object type. If none are found for an object type, that information will also be reflected.

Each object script selected found for import has its data and time compared to the latest stored version of the same script in the repository. If the imported script is found to have a later date\time than that of its corresponding script in the repository, it is imported and assigned a new version number. In a few cases where object types do not provide data\time information (such information is provided by the object type structure within the database), the corresponding scripts are then compared in a byte-to-byte fashion. If the script being imported is different from the last stored version in the repository, it then is also imported assigning it a new version number.

“Export Database Objects”

This screen allows the developer to select versioned database scripts and copy them to the clipboard for manipulation purposes.

All such selections are based upon the database and what database object types have been stored. When an object type has been selected, all of the stored versions are shown unless the “Latest Version” indicator has been checked whereby only the latest version of each script will be displayed.

Conclusion

Black Falcon Software is committed to making this product a mainstream offering for all SQL Server database developers. Whether a developer uses VB.NET, C#, or Java to provide database applications, those that use SQL server now have a more affordable option for versioning their database object scripts.

A 30-Day Trial download is available at the Black Falcon Software site… http://www.blackfalconsoftware.com.

An individual documentation download is also available for those who would like to read further on this product offering.

All suggestions, comments, and criticisms (the life blood of good software development) are welcome through the “Contact” form on the site…