by Jacob Sebastian and Sven Aelterman
FILESTREAM is implemented as an extension to the VARBINARY(MAX) data type and allows large object data to be stored in a special folder on the NTFS file system, while bringing that data under the transactional control of SQL Server. This book describes both the way it works and the implementation, administration and troubleshooting of it.
Storing large object data in the database offers a number of benefits, but introduces performance challenges. Conversely, storing large object data in the file system has overriding performance advantages, but fails to offer some of the basic data integrity, security and manageability features that are required for business data, and which SQL Server provides.
Up to now, most people have adopted file system storage by necessity, and often struggled to overcome the associated shortcomings. This is exactly where SQL Server’s new FILESTREAM feature fits in. Introduced in SQL Server 2008, it is implemented as an extension to the VARBINARY(MAX) data type and allows large object data to be stored in a special folder on the NTFS file system, while bringing that data under the transactional control of SQL Server.
This book guides you step-by-step through every phase of FILESTREAM implementation, from enabling the feature, to creating FILESTREAM tables, to manipulating FILESTREAM data through the streaming APIs. We also cover, in detail, administration and troubleshooting of FILESTREAM databases and tables.
No feature is used in isolation, so we devote several chapters to explaining how this new feature can be integrated into ASP.NET and Silverlight web applications, and into applications using Entity Framework. We also take a look at how well FILESTREAM plays with other SQL Server features such as SSIS and SSRS, pointing out various issues and pain-points along the way. We also devote a full chapter to the FileTable feature, a long-awaited new addition to FILESTREAM, with SQL Server 2012, which allows Windows applications to access FILESTREAM data exactly as is it were stored directly on the file system.
We hope this book will get you started, quickly, with FILESTREAM, and then help you master all essential aspects of programming and administering FILESTREAM-enabled databases.
Table of Contents
Chapter 1: Storing and Managing Unstructured Data
Chapter 2: Getting Started with FILESTREAM
Chapter 3: Accessing FILESTREAM Data from Client Applications
Chapter 4: FILESTREAM with Entity Framework and LINQ to SQL
Chapter 5: FILESTREAM with ASP.NET and Silverlight
Chapter 6: FILESTREAM with SSIS and SSRS
Chapter 7: FILESTREAM Database Administration
Chapter 8: Backup and Restore for FILESTREAM Databases
Chapter 9: Investigating FILESTREAM Databases
Chapter 10: Integrating FILESTREAM with other SQL Server Features
Chapter 11: FileTable
Chapter 12: Planning, Configuration and Best Practices
Jacob Sebastian is a Microsoft MVP (SQL Server) from Ahmedabad, India, who has been working with SQL Server for almost 15 years. He works as CTO with Excellence Infonet, Ahmedabad, which focuses on building highly scalable, mission critical applications for the healthcare industry.
Jacob started his database career in the early nineties with Dbase and then moved to Clipper, FoxPro and, finally, to SQL Server, starting with version 6.5. He has presented at various SQL Server conferences, including PASS Global Summit, European PASS Conference, 24 Hour PASS, Tech-ED India, Community Techdays, Microsoft Virtual Techdays, SQL Server Saturday and more.
Jacob wrote his first book in 2009, The Art of XSD – SQL Server XML Schema Collections which delved deeply into the XSD and XML Schema collections, introduced in SQL Server 2005. He also wrote the XML chapter for the book, SQL Server 2008 Bible.
When not working, Jacob spends time at beyondrelational.com.
Sven Aelterman is a lecturer in Information Systems at Troy University in Troy, Alabama. He teaches undergraduate courses in data warehousing, network infrastructure and security. He is also the technology specialist for Troy University’s Sorrell College of Business, and performs a variety of technology roles with a global scope.
He continues consulting work through Adduxis, where he assists customers with software development projects using the Microsoft .NET Framework and related technologies. Sven focuses on application design and architecture and secure coding practices. He received his MBA from Troy University and his bachelor’s degree from the Hogeschool Gent (University College, Ghent) in Belgium, his native country. Sven is married to Ebony and they have a son, Edward, and a daughter, Sofia.