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.
- In the list of services, right-click SQL Server Services, and then click Open.
- Locate instance of SQL Server Right-click and click Properties.
- Click the FILESTREAM tab.
- Select the Enable FILESTREAM for Transact-SQL access check box.
- If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
- If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
- Click Apply.
- In Query Editor, enter the following Transact-SQL code:
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.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr