Martyn Bullerwell's blog

Adventure Works Samples Update and FILESTREAM

A bit late (released at the beginning of the month), but a follow up to post I made earlier this year, there is a new release of the Adventure Works Databases.  These can now be found here.  They are simplified into just 4 downloads, which makes things a lot easier! Also the install is a lot more straight forward, although for the install to complete you must have full text search and FILESTREAM installed and enabled.   It is good to see that FILESTREAM is being used already, and Adventure Works can be used to test and demonstrate this new functionality.

What is FILESTREAM?

Prior to SQL Server 2008 if you needed to store images, videos, PDF's, or any other binary data we used a blob data type and it was in-line with relational data.  Now SQL Server 2008 can store blobs in its own private namespace on the file system, which simplifies the storage of blob data. Adventure Works examples have now used it to store instructions in word doc files. (See table Production.Documents)

Enable FILESTREAM

Start -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager.

EXEC sp_configure filestream_access_level, 2
RECONFIGURE

Using FILESTREAM Databases

• Create a new database is SSMS:

• Under the "Filegroups" Tab add a new FILESTREAM:

• Once set up click OK. This should now have added a new folder as follows under your SQL installation:

From here we can see that we have our new FileStreamTestData directory as well as documents folder, which has been added from the install of the SQL2008 AdventureWorks samples.  So now we want to test the storage of some files in SQL.  So lets try and store some Pictures for example.  To begin with we will need to set up a new table.  It is worth noting here that we must add a ROWGUIDCOL column, preferably the primary key, and the unique identifier.

CREATE TABLE [dbo].[Media] (
    [mediaId] [uniqueidentifier] NOT NULL ROWGUIDCOL PRIMARY KEY,
    [dateCreated] [datetime] NOT NULL DEFAULT(GETDATE()),
    [fileName] [nvarchar](256) NOT NULL,
    [location] [geometry] NULL,
    [file] [varbinary](max) FILESTREAM);
GO

This will give us a new table, that we can insert into.  So If we insert an dummy row and keep an eye on the folders created when creating this new filestream.  Execute the following statement:

INSERT INTO [FileStreamTest].[dbo].[Media]
    (
        [mediaId]
        ,[fileName]
        ,[file]
    )
    VALUES
        (NEWID(),
        'NewFile.jpg',
        convert(varbinary(max), 'Not a real Picture, but proves a point'))

You will now notice that a new file has been added to the file system.

The more rows we insert, the more files are added to this directory.  You will also notice that the date time stamps on the files are also reflected by when the items were added to the database.  If a row is updated with a new image, the new image is created,  however the old image remains.  This also applies when the row is deleted.  However, these directories are cleaned up periodically by the FILESTREAM garbage collector.

So, in short it seems that this will become the way forward for storing all sorts of files in your database that can be centrally stored, managed and backed up.  A Simple application could be easily written to save, update and retrieve images through SQL Server 2008, using file stream data.

So where is Microsoft Master Data Management (MDM)?

Well, back in June 2007, Microsoft acquired Stratature, who had a product named +EDM.  +EDM,  was  a master  data  hub with a web UI,  business  rules,  simple human workflow  and  notification,  entity  and  hierarchy management,  versioning,  transaction  logging, and an open subscription interface.  Microsoft has not continued a release of +EDM, but has continued support for existing customers.  In November a technology preview was released to those who applied and were accepted onto the program.  Well since then we have seen a CTP on Connect, which Jamie explored here.  This Blog was written back in March 2008, and since then Microsoft has acquired yet another company called Zoomix, who focuses on MDM data quality.  Information on this acquisition, and what Zoomix is can be found here.   The hope is that all of this will be integrated into Microsoft MDM.  

The official release plan was as follows:

• Technology Preview - February, 2008 to a select group of customers.  Near identical functionality to Stratature release (actually released earlier than that, in November 2007).
• TAP - Program commenced first half of 2008.  
• CTP - First CTP release available Q3, 2008.  

The codename for Microsoft MDM is "Bulldog", and it seems that Microsoft MDM will ship in Microsoft SharePoint - Office 14 Release which is currently due for release late 2009, early 2010, around the same time as Windows 7.   The codename for the subsequent release of the Microsoft MDM product is "Greenwich". Apparently major new features will be released in the "Greenwich" timeframe, including integration, analytical & transactional capabilities.  But for the mean time let’s see how “Bulldog” does.

I will continue to keep an eye on MDM, from here on in, although it might be a long wait until you all get your hands on it. 

PerformancePoint Server SP2

A good few months ago I wrote about SP1 for PerformancePoint and getting this working on SQL Server 2008.  There were a number of attempts, and with a great deal of hacking, I got an untested environment set up, however this was not very stable and never fully tested.  SP2 is due for release in December, and there are mentions of a Beta available on the web, although unavailable through MSDN, or Connect.  It seems that the two promises of SP2 are Hyper-V compatibility and SQL Server 2008 integration.   

A few good resources for this are as follows:

http://hmorgenstern.spaces.live.com/Blog/cns!28A6BE83102A0EB3!419.entry and http://performancepointing.blogspot.com/2008/11/performance-point-server-sp2-pre.html