Rosario April 2008 CTP Investigation (Part 8) - Database
Continued from http://dotnet.org.za/willy/archive/2008/06/18/rosario-april-2008-ctp-investigation-part-7-tester.aspx.
The DB environment is not one of my favourites as anyone who knows me will quickly realise, however, I am taking the opportunity to not only investigate the Team Edition for Database Professional, but the Rosario enhancements as well.
Let’s see if the business and plumbing layers, which I am typically involved in, have any advantages over the database environment ... based on the Team Edition off course J
The journey we will participate in includes:
· Creation of an isolated Database Development Environment
· Establishment of a baseline for the isolated development environment
· Performing iterative database development in an isolated environment
· Incremental Deployment
Creation of an isolated Database Development Environment
Create database project
· This is the first time I am going through the motions of creating a database project using the IDE wizards ... exciting J. Select New Project à Database Projects à Microsoft SQL Server ...
· We select the import database schema option:
· We create a new connection, pointing it at our server and the AdventureWorks database, accepting most of the defaults and eventually selecting Next to commence with the import.
· At this stage my VM went into a mad spin, but eventually returned with the following confirmation:
· We have a database ... sandboxed copy of the ‘production’ AdventureWorks database.
Deploy the project to the isolated development environment
· We define the connection details, i.e. placing the AdvWorksSandbox database back on our server:
· Deployment is easy ... select Build à Deploy AdvWorksSandbox
Create a data generation plan
· Select the Data Generation Plan option ... right-click solution then notice the following menu:
... then select Data Generation Plan template and define a meaningful name:
· We deselect all tables, by either doing it the long and trusted way ... deselect each table ... or by right clicking on the table and selecting “Exclude all tables from the data generation”. Then we select the tables HumanResources.Employee and Person.StateProvince, whereby Person.Contact and Person.ContactRegion is automatically selected for us:
· Select Person.StateProvince. In the details pane select StateProvinceCode and set the generator field to Data Bound Generator.
· Set the connection property to the original AdventureWorks database and define the select query as
SELECT [StateProvinceCode] FROM [Person].[StateProvince]
· Repeat the same procedure with table Person. Contact, field AdditionalContactInfo, defining the query: SELECT * FROM [Person].[Contact] and the generator output field as shown:
· Next we attack the HumanResources.Employee table. Select Title and set the generator field to RegularExpression. In the properties window set the expression to (Developer|Tester):
· Then we finish off the table with a few other fields:
o The Birthdate field is set to generator datetime, with a Max and Min property set.
o The MaritalStatus field is again a regular expression with the property set to (M|S):
o The Gender is also a regular expression, with the property (M|F).
o The Hire date has a MAX of today and a minimum of 1/1/2000:
· In the Person.CountryCode table we perform the following surgery:
o Rows to insert = 10|
· In the HumanResources.Employee table we perform the following surgery:
o Rows to insert = 25:
· In the Person.StateProvince table we perform the following final surgery:
o Related table field = [Person].[CountryCode]
o Ratio to Related Table = 2.1
· After this extensive table brain surgery, we select SAVE. Pfffffffffffffffff...
Populate the isolated database environment
· Before we can proceed, we need to disable one of the triggers. Proceed as follows:
o Data menu à T-SQL Editor à New Query Connection à Connect to our isolated database ... that’s AdvWorksSandbox
o In the T-SQL window capture and execute the following statement:
DISABLE TRIGGER dEmployee on [HumanResources].[Employee];
GO
· Why? Who knows ... the trigger is defined as follows:
CREATE TRIGGER [HumanResources].[dEmployee]
ON [HumanResources].[Employee] INSTEAD OF DELETE
NOT FOR REPLICATION
AS BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN
RAISERROR
(N'Employees cannot be deleted. They can only be marked as not current.', -- Message
10, -- Severity.
1); -- State.
-- Rollback any active or uncommittable transactions
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END;
END;
GO
· Next we select the Data Generator and Preview Data Generation. Make sure you have focus on the Generation Plan window, else you will look for this option like I did in between two cups of strong coffee:
· Generate and note with amazement ... whereby I did have a brief moment of heart failure, thinking I set the wrong connection ... clobbering the wrong database ... for a moment:
Establishment of a baseline for the isolated development environment
Setup project for unit test
· In the Database Schema View, we select “Create Unit Tests” under the Functions node.
· You would typically create a unit test for each function, stored procedure and trigger in your database ... but this is an evaluation and we therefore only focus on one function in the database as shown.
· Define the database connections, the deployment and the database state for the test and select OK.
· The wizard creates the test project and the default T-SQL test code.
Define unit test(s)
· We modify the code slightly as shown ...
· Ending with a rowCountCondition of 1.
Build and run unit tet(s)
· Using the Test View we select the relevant test and execute.
· If all goes well ... cross your fingers ... you should we the welcome green tick.
Performing iterative database development in an isolated environment
The final wizardry is the iterative development and the comparing of database schemas ... very useful when your team insists that ‘nothing’ has changed.
Rename refactor an object
· In the Database Schema View we select a relevant table, a column ... then right-click, refactor and rename:
· Define a new name for the column and select OK.
· We can preview all the changes before committing:
· This is a demo, a change is not required and I had enough issues ... I will therefore chicken out and select cancel. Or perhaps not ... let’s select Apply.
Compare project schema with production database
· So, nothing has changed? Really?
· Let’s select Schema Compare on the Data menu.
· The New Schema Comparison companion appears. We define the source schema, our unchanged database, and the target as the AdventureWorks original database. Hit the OK button gently ...
· You have to be patient ... but then the magic appears and you can point to the screen and say ... ‘really, no changes? What about ...’
Static Code Analysis
· Now this one is really cool:
· Gives us code analysis of our database and T-SQL code.
Conclusion
My personal rating for the Team DB improvements in Rosario at this stage is: : 
/ 5 smiles. The one unhappy smiley is for the battles I had with the deployment of the database and the lack of pre-installed SQL management tools on the CTP, which made me nervous and at times frustrated in places. The other frustration is the TSQL “RAISEERROR” function, which is actually “RAISERROR” ... guess what, I had two e’s, which seems correct to me, debugging the code around the function ... only to go “Oh” as our ‘Monk’ would say.
Note to self: Take strong cup of coffee to any TSQL battle in future.
HINT: Have a look at a great video on the database edition by Richard here.
Next ... Conclusion The series will also be available in a combined PDF document for easier reading.