Over the past few years one of the big drives and innovations in the SQL Server world, has been to bring database schema changes into a full ALM (Application Lifecycle Maintenance Methodology).  There are many competing products in this space, but all of them ( well, that I know of )  only solve database schema change problems.  Databases are all about the data and without data applications are pretty useless.  Most systems have some form of standing reference data, countries, currencies, name prefixes etc and Reference Data Manager is a (private) beta product from Redgate and its aim is to treat data as a first class citizen in the ALM cycle.

Mostly,  even if this data is even ‘managed’,  they are managed by large MERGE ( or MERGE-esque ) scripts that validate the whole of the dataset at deploy time.  The problem here is that these are large by nature and the whole intent is unclear, its all or nothing and as human eyes do generally like to look over a deployment script before it happens, make the process longer and more error prone.

Essentially Reference Data Manager, is Redgate bringing the already mature technology of Data Compare into Microsoft’s SSDT environment using Deployment Contributors.  This, Redgate working within SSDT, is interesting in itself and I do hope that Redgate will bring more of their products into SSDT in the future.  Also, and more pertinently,  this means that the data is now maintainable with SSDT right next to the schema and is an integral part of the deployment, not an extra ‘cog’ somewhere.

So, lets take a look, as with all Redgate tools, its pretty simple.  Ive defined a table “COUNTRY”, which is nothing special:

CREATE TABLE [dbo].[Country]
(
CountryId INT NOT NULL PRIMARY KEY,
CountryName varchar(255) not null,
Currency    char(3) not null 
)

Now, to add data I simply R-Click on “Country.Sql” in Solution Explorer and I see a new “Add Reference Data” option:

That adds a child file to Country.Sql called Country.refdata.sql,  this is the file in which you define your data.  Presently this is a simple SQL script with inserts:

insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (1,'UK','GBP');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (2,'USA','USA');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (3,'France','EUR');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (4,'Germany','EUR');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (5,'Austrlia','AUD');

In the fullness of time I would expect a grid UI here, but as I say it’s beta ATM.

So now when I deploy my solution, the Reference Data Manager kicks in and adds to the deployment script the relevant data changes.  As this is a new table this is obviously just inserts.

What about adding a new country and modify ‘USA’ to be ‘U.S.A.’ and correcting the error on currency from ‘USA’ to ‘USD’. I modify the refdata.sql file to be :

insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (1,'UK','GBP');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (2,'U.S.A.','USD');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (3,'France','EUR');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (4,'Germany','EUR');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (5,'Austrlia','AUD');
insert into [dbo].[Country](CountryID,CountryName,Currency)
Values (6,'New Zealand','NZD');

When I now deploy the solution, there is no schema change but the script will contain only the data changes.

As you can see,  it only publishes the required changes,  not retest using MERGE or whatever at deployment time.  Obviously this is a really trivial example but how many reference data tables does your system have ? and how many rows in each of those ? Is the intent a lot clearer ?

Personally I think that now by treating data as a first class citizen the Reference Data Manager will not only enable data to be source controlled but also simplyfy the deployment pipeline by being very explicit about the changes that are to be made.

If you would like to get involved then you can sign up for the beta here : http://www.red-gate.com/ssdt