In the world of databases, the growth of data is increasing year after year. These new data items are not the traditional integers and text that we find in spreadsheets and reports; rather, the data is made up of digitally born medical images, music and video data files, and the like. With all these different kinds of data, it is becoming increasingly difficult to perform useful tasks, such as metadata searches or basic database management. SQL Server 2008 has a solution to this problem.
From a developer standpoint, dealing with heterogeneous data has always been a challenge. In SQL Server, we have two basic options to address the issue.
One option is for the developer to store the files in the file system and maintain a pointer to the file within the database. This solution works in cases where the file could be larger than 2GB and where fast streaming performance of the file is required. For those fairly new to databases, reading directly from the file system offers better streaming performance than reading the same data through the database. This is due to the fact that the database engine must manage locks on the table, even if the user is just reading the data.
Before you go off and design your new applications following this approach, it is important to note the downside of trying to maintain consistency between the files on the file system and the metadata about those files stored in the database. First, any changes that are made to the files directly through the file system are not detected by the database and will need to be addressed in your database application. Second, the administrators will have the heavy burden of trying to determine the appropriate security to apply to the file given the context of the data user. These are just some of the issues in a tedious, development-intensive solution that up until SQL Server 2005 was really the only answer to the problem.
SQL Server 2005 introduced the VARBINARY(MAX) datatype, which allows any arbitrary large object up to 2GB in size to be stored directly in the database. This mitigated a lot of user scenarios that required the use of the file system and gave developers a less error-prone database storage option for their large objects. Still, even with being able to store these large objects within the database, developers gave up on the streaming performance of the file system and faced a hard limit of 2GB for the storage of the data.
If it were a perfect world, wouldn’t it be great if we could leverage the streaming performance of the file system in addition to the transactional consistencies of the database in one big, happy solution? Well, although this world is far from perfect, the new FILESTREAM attribute to VARBINARY(MAX) may just be the one feature that causes you to see the world with a different perspective. With FILESTREAM, you can have it all and even cake for dessert, without gaining an ounce on the waistline. Actually, no one can make promises about your waistline—not even Weight Watchers—but with FILESTREAM, you will be able to at least store large objects in the file system and have these files integrated within the database.
Integration is the key to the value of FILESTREAM. With integration, when database backups are performed, the files are included in the backups. Since these files are stored in the file system, the size limit of storage within the database is the size of the volume on the disk. Thus, there is no longer a 2GB limit. Also, the security of these files is managed via SQL Server security features, so if database users have access to the table, they can access the contents of the file, without the Windows administrator needing to give them specific privileges. To top off the list of these benefits, developers can leverage the Win32 file APIs against the data stored within the database to essentially give them the performance of the file system within the database.
The only significant downside to FILESTREAM is the requirement that the drive volume that has the files must be in NTFS format.
FILESTREAM is best used when the objects that are being stored are larger than 1MB and when fast read access is important. Also, FILESTREAM will be beneficial when your database applications perform their application logic within the middle tier. For smaller objects (less than 1MB), storing VARBINARY(MAX) large objects within the database may give you better streaming performance.
Enabling Filestream Functionality
Before filestream functionality can be used, it must be enabled using the sp_filestream_configure_system stored procedure. This stored procedure has two parameters: enable_level and share_name. The enable_level parameter supports one of four values, as defined in Table.
To get started, enable filestream functionality for both T-SQL and file system access, as follows:
sp_filestream_configure @enable_level = 2
If you used a value of 3 instead, you would be able to provide a name for a fileshare located on the Windows server hosting your SQL Server instance. This share could then be used by remote clients who want to access the filestream data.
When you enable filestream functionality on the server, one of the things that happens is the creation of a fileshare. If you issue a NET SHARE statement in your command window, you can see the newly created fileshare, called MSSQLSERVER, created by SQL Server for use with filestreams. The result of this statement is as follows:
From a quick look at the resource that is being shared, you can see that the MSSQLSERVER share isn’t your traditional folder in the file system. What is happening here is SQL Server has a special driver used by the operating system that allows SQL Server to expose and manage a fileshare. Since SQL Server owns and manages this fileshare, it allows for applications to leverage the powerful streaming support of Win32 APIs while working in the context of a SQL Server transaction.
A Filestream Example
To help illustrate the power of SQL Server’s filestream support, let’s consider a scenario involving an online pet shop database that stores images in a database. In this example, you will create a database application called PetPhotoManager that will connect to a SQL Server instance and upload and view pictures stored in a table. First, you will create a database called OnlinePetStore, which contains a table called Pets that stores pictures of all the animals on the web site. Then you will write an application that will allow you to upload pictures to SQL Server, as well as read pictures and display them on aWinForm. These pictures will be stored as VARBINARY(MAX) and leverage the FILESTREAM attribute.
Creating the Database
Using FILESTREAM requires the designation of a special filegroup in the database. The filestream filegroup cannot be the primary filegroup, and you cannot specify the SIZE, MAXSIZE, or FILEGROWTHparameters of this filegroup, since it’s really just a file directory. The following database-creation code defines a filegroup called FileStreamPhotos, and the statement CONTAINS FILESTREAM follows the FILEGROUP definition. This extra statement designates this filegroup to be one that contains filestream data.CREATE DATABASE OnlinePetStore
This database-creation script indicates that the folder used to store filestream data will be C:dataphotos. In order for the database engine to accept this, the C:data folder must exist, and the c:dataphoto folder must not exist prior to execution of this script.
After you create the database, you are ready to define the simple Pets table that will be used to store the pet photographs:USE OnlinePetStore
Notice the first two bold statements. The first one is a column definition for a UNIQUEIDENTIFIER that has a designator for a GUID-based unique ID column called ROWGUIDCOL. FILESTREAM requires that you specify a ROWGUIDCOL; failure to do so means FILESTREAM won’t work.
The next bolded line demonstrates how to define a filestream column. The example has a column called pet_photo whose datatype is defined as VARBINARY(MAX) with the FILESTREAM attribute. Filestream
columns can be only of type VARBINARY(MAX) in SQL Server 2008.
With the database objects created, you can now create the application that will insert and select filestream data.
Creating the Application
Before diving into the code, there are a few disclaimers to make. This example is written to demonstrate how one goes about accessing and managing filestream data. To keep the code brief, we intentionally skipped necessary things, like optimizing the code by creating helper classes and implementing more elaborate error-handling routines. And to keep things interesting, the code is written with a security bug. After we present the code, we will talk about this bug, even though it’s not specific to SQL Server filestream data.
The PetPhotoManager application will be run from the command line and accept two options:
The core part of this application revolves around two functions: UploadPhoto() and ViewPhoto().
The following is the PetPhotoManager C# application.
To use this application, upload a photo with a statement like this:
PetPhotoManager /upload Zeus C:picturesZeus.jpg
To retrieve the photo, issue a statement like this:
PetPhotoManager /view Zeus
You’ll see aWinForm window containing a picture of the pet, as in the example in Figure.
PetPhotoManager view pet window (showing the author’s St Bernard, Zeus, recently
shaved for the summer)
Let’s start the analysis by looking at the UploadPhoto function. After making the connection to the database, you create a GUID and insert this GUID and a pet’s name into the Pets table.Guid NewGuid = Guid.NewGuid();
Having a GUID for each row is a requirement for SQL filestream data. Once you have this entry in the table, you want to query this entry and obtain two important pieces of information:
The SELECT statement to obtain this information is as follows:strCommand = "SELECT pet_photo.PathName()," +
After you have the path and transaction context, you can use the OpenSqlFilestream() function:SafeFileHandle handle = OpenSqlFilestream(
The OpenSqlFilestream() function allows for both read and write access to the file. The handle that is returned can then be used directly in Win32 APIs. You can see the true power of SQL filestreams by the simple file copy that follows the OpenSqlFilestream statement:FileStream writeBlob = new FileStream(handle, FileAccess.Write);
Here, you have two FileStream objects, named readPhoto and writeBlob, which are the source and destination, respectively. The source FileStream object is created with the handle obtained via the SQL filestream, and the destination FileStream object is created by passing the local filename directly into the API.
To view the photo, the process is similar to uploading the photo, in that you need to obtain the pathname and transaction context from the Pets table for the given pet name requested. The SELECT statement is as follows:SELECT pet_photo.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT()
Once you have this information, you can pass it into an OpenSqlFilestream() statement and request read access, as follows:SafeFileHandle handle = OpenSqlFilestream(
Now with a read handle, you can create a FileStream object based on the handle obtained from SQL.
FileStream readBlob = new FileStream(handle, FileAccess.Read);
To display the actual photo, you simply created aWindows Form, included a Picture Box control, and bound the image using the .FromStream() function, as follows:PictureBox pb = new PictureBox();
Although this example is fairly simple, you can see the power of being able to leverage the streaming performance of Win32 API calls against data stored in SQL Server.
Before we conclude this discussion, we need to address the security bug that is present in the sample code. By now, you should have at least browsed the entire code listing for the PetPhotoManager
application. If you haven’t done so, go back and see if you can spot a common security vulnerability.
One of the most common and easiest exploits to SQL Server is the SQL-injection attack. In the example, you take the user input stored in strPet_name and in the UploadPhoto() function, insert this pet name directly into the SQL statement, as follows:strCommand = "INSERT INTO Pets VALUES(CAST('" +
To exploit this, all you need to do is modify the parameters you are passing into the PetPhotoManager application.
Let’s have this harmless photo-viewer application create a SQL login and give it sysadmin access:PetPhotoManager.exe /upload "hack',0);CREATE LOGIN Hacker WITH
When SQL Server executes the INSERT statement, to SQL it looks like the following:INSERT INTO Pets VALUES
The key to note here is that the call is wrapped up nicely by appending the rest of the name followed by the closing apostrophe and ,0), so the INSERT statement is happy. Now all you need to do is append whatever code you desire. In this case, it is to create a login called Hacker and grant sysadmin access to the new login. To wrap things up, you just need to put in two dashes (--) at the end, indicating whatever else is just a comment. In this case, the rest of the ',0) that the real application appended is now a comment.
There are a few lessons here. Never run under elevated privileges. If the application wasn’t running as a sysadmin, you wouldn’t have this particular problem. However, the exploit is still here.
To mitigate the SQL-injection attack, you could cleanse the data before allowing the variable to be used within the statement. This would include removing characters like the comment dashes and limiting the number of characters of the variable. A more secure solution would be to wrap the INSERT statement inside a stored procedure. Your code to call the stored procedure would then look something like this:SqlCommand command = new SqlCommand("createPhotoEntry",connection);
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.