SQL Version Control

Practice Safe SQL Server Version Control (Part #2)

Part 2 will discuss the Solutions :

Nob Hill Software, after suffering from this lack of satisfactory solution to the problem, decided to come up with a solution that will once and for give a full answer to managing database change. The goal was to come up with a solution that’s:

1. Quick to implement with a minimal learning curve

2. Impact on the monitored databases needs to be minimal

3. It must be always possible to roll back any monitored database to any point in time, fully or partially.

4. In addition to w

orking as a stand-alone application, It must integrate with the existing version control systems that clients already have set up at their site

5. Monitors absolutely everything about the database – any single entity being modified in any way – tables, views, permissions – everything must be monitored and saved when changed.

6. Shifting responsibility from the developers to the software itself. The developers and DBAs need to be free to do what they’re hired to do, knowing that a background ‘agent’ fully versions their work, and it’s always there when needed.

7. Provide accurate information on every change, such as its exact timestamp, the user who made the change, and more

8. Not just save schema, but also offer many features that will make it easy to browse database entities at different points in time, generate reports on changes, drill down easily to find any particular change, generate alerts when certain entities are modified, and in short make full use of the fact that it’s not just another repository that’s being created – but rather a custom repository built to maintain database changes

9. Save everything about the server, and that means going ‘outside’ databases to save things like server logins, TSQL Jobs, SSIS packages, and more

10. Being able to also version data, since some tables, for example ‘configuration’ tables, have values that are practically part of a schema, and their change needs to be versioned

11. The tool must be affordable

After much planning, Nob Hill Software came out with a product that answers to all of the above requirements. It’s called ‘Randolph’, and it does it all. It’s a background service that keeps scanning the databases it monitors for any schema changes, and updates its own repository when any are found, and potentially pushes out changes to external version control systems (currently supporting SourceSafe, Subversion and Microsoft Team Foundation Server). The main idea is that the developers and DBAs can totally free themselves from this issue, and focus at what they’re doing best: developing and maintaining their software projects. When Randolph is set up, Any change whatsoever in their databases is being logged automatically, and from that point and on databases can always be fully rolled back - a complete rollback or selective entities – to their state in any point in time. Plus, a whole new set of features is included to make change management from a typical uneasy concern to a rather pleasant experience.

תגים של Technorati:‏ sql server,version control,sqlserver tool

SQL Version Control

Practice Safe SQL Server Version Control ( Part #1), (part #2)

While it’s obvious for any developer that some sort of version control\change management solution is needed on every database related development project, very few shops properly version their databases. This seems like a strange situation, at first. The database schema, which includes tables, views, procedures etc., is just as vital as the application code. Why is it being neglected so often?
Some readings of articles and discussion groups online shows that many people are in fact aware of this situation, but the main problems seems to be the lack of available tools in the market. While the version control\change management market is flooded with solutions - be it open source like Subversion, or a commercial product like Microsoft Team Foundation Server – there are very few solutions out there for versioning databases. Faced with this reality, most software shops adopt one of the following (if at all):

1. Simply relying on database backups

As strange as it sounds, database backups is what most companies out there have as far as versioning their databases. True, It is possible to restore the database, but its time consuming, resource intensive (both human and machine) it doesn’t catch changes done in between backups, and if one is looking for one specific change only it’s not always easy to find.

2. Periodically script the database and check the resulting files in the repository.

This is the most basic solutions for those who decide to tackle this problem. With its main upside being its simplicity, and the downside being that it requires some effort and a certain measure of responsibility on the part of the person in charge of implementing it; finding specific change is not always simple (since it requires many searches on plain text files) and rolling back is still tricky in some cases – for example, when dealing with complex table changes

3. Using custom TSQL Script to automate scripting and checking into a Version Control System

This is an upgrade on the previous solution. Many suggested automation scripts are available out there. They detect a change and script it out to a file. While this saves some human labor and resulting in smaller scripts, the problems mentioned above are still there. And automating a push to the existing versioning system is not always easy (depending on which system is used)

4. Using Database Comparison Software to do versioning

There are many popular database comparison and migration tools out there. Some of them are quite good at what they do, but the problem is, this is not what they were built to do. Having a full version control solution implemented using a database comparison tools is far from intuitive. Not only it requires a lot of thinking and planning in order to set up, once they are up they usually require a lot of human intervention to bridge the gap between simple database comparisons to full change management solution.

5. Implementing one of the database version control products on the market today

Recently, few vendors started coming out with custom tools for SQL Server versioning. But these tools still suffer from at least the following problems:
• They are not easy to set up
• They are rather light on features
• They have a rather heavy impact on the servers they monitor
• They don’t integrate with existing version control systems
• They are extremely expensive. Many of them charge by each database they monitor, which could mean an expense of thousands of dollars.

End of part 1, part 2 will discuss the solutions.