Earlier this year, Scott Ambler wrote that "Evolutionary approaches are trickling down from software to database development. Despite a skimpy array of tools, the incremental approach has much to offer." Perhaps his books Agile Database Techniques (2003) and Refactoring Databases: Evolutionary Database Design (2006) have influenced software makers: integrated "Agile DBA" tools are starting to appear. Case in point: Microsoft's Visual Studio Team System (VSTS) for Database Professionals, to be released at the end of this month, which will include integrated change management and testing for datebase schemas.
The iterative approach to database development and design requires test driven development (TDD) to work against the database. TDD is a tool-facilitated Agile practice, in which developers write a test before making a change in the system to fulfill that test. TDD then relies on tools that run the automated regression suite hourly or daily, checking the codebase for functionality that used to work, but which has been broken in the course of the day's development. When the process is well applied by developers, broken code is seldom found.
So, what help will VSTS for Database Professionals offer for TDD against the database? In What Microsoft Visual Studio 2005 Team Edition for Database Professionals Can Do for You, Tony Loton described four key activities enabled by this new release, in enough detail to illustrate the appropriateness of each new feature in a real-life situation, while keeping it simple enough for readers to follow:
Grant Frichly, writing on SQLservercentral.com, lauds the appearance of VSTS for Database Professionals, saying that it contains "some seriously slick unit testing that is automatically generated against your database code." He provides an example of creating and running a test. He says the following test conditions are provided for, out-of-the-box:
The iterative approach to database development and design requires test driven development (TDD) to work against the database. TDD is a tool-facilitated Agile practice, in which developers write a test before making a change in the system to fulfill that test. TDD then relies on tools that run the automated regression suite hourly or daily, checking the codebase for functionality that used to work, but which has been broken in the course of the day's development. When the process is well applied by developers, broken code is seldom found.
So, what help will VSTS for Database Professionals offer for TDD against the database? In What Microsoft Visual Studio 2005 Team Edition for Database Professionals Can Do for You, Tony Loton described four key activities enabled by this new release, in enough detail to illustrate the appropriateness of each new feature in a real-life situation, while keeping it simple enough for readers to follow:
- managing database objects as version controlled items within a solution.
- creating and running unit test where the executable "units" are stored procedures, which also requires...
- setting up a database schema and test data capable of unit testing.
- renaming database table and column names that have been generated by a schema creation tool.
Grant Frichly, writing on SQLservercentral.com, lauds the appearance of VSTS for Database Professionals, saying that it contains "some seriously slick unit testing that is automatically generated against your database code." He provides an example of creating and running a test. He says the following test conditions are provided for, out-of-the-box:
- Empty Result Set: Requires that the procedure tested return nothing
- Execution Time: Minimum time for execution of the given test
- Inconclusive: The default test. Simply a place holder to remind you to set your test conditions
- Not Empty Result Set: Requires that the procedure tested return something
- Row Count: Specify the number of rows anticipated by the test
- Scalar Value: For a given column and row, does the value match the test value
Automated unit testing of this type is specifically targeted to support Agile development methodologies, though it can be rewarding as a general approach too... If you're not looking into this as part of your own development, you should because it clearly has momentum within the industry.The "Agile DBA" role is still defining itself, as enterprises adopt and adapt the Agile approach for data. At Software Development East 2001, independent consultant Peter Schuh first introduced the role as: one who performs traditional tasks like database setup, performance tuning, physical data modeling, database development and developer support, but in an agile, evolutionary manner. Scott Ambler's vision is a little different:
[Schuh] narrowed in on a single team, whereas I examine the entire enterprise. The agile DBA works side by side with application developers, typically supporting a single large team or several small teams, as necessary. The agile DBA can often be responsible or co-responsible for several data sources. In large organizations, the agile DBA must also coordinate with enterprise architects and enterprise administrators to achieve mutual goals.For more on the subject of the Agile DBA and how MS VSTS will integrated change management and testing for datebase schemas, check out the podcast with Matt Nunn on Database Change Management with Visual Studio Team Edition for Database Professionals over at AgileJournal.com .