Microsoft SQL Server 2008 R2 Unleashed- P166:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1594 CHAPTER 42 What s New for Transact-SQL in SQL Server 2008 FIGURE Setting FILESTREAM options in SQL Server Configuration Manager. file system for storing FILESTREAM data. You have three options for how FILESTREAM functionality will be enabled Allowing only T-SQL access by checking only the Enable FILESTREAM for Transact-SQL Access option . Allowing both T-SQL and Win32 access to FILESTREAM data by checking the Enable FILESTREAM for File I O Streaming Access option and providing a Windows share name to be used to access the FILESTREAM data . This allows Win32 file system interfaces to provide streaming access to the data. Allowing remote clients to have access to the FILESTREAM data that is stored on this share by selecting the Allow Remote Clients to Have Streaming Access to FILESTREAM Data option . NOTE You need to be Windows Administrator on a local system and have sysadmin rights to enable FILESTREAM for SQL Server. After you enable FILESTREAM in SQL Server Configuration Manager a new share is created on the host system with the name specified. This share is intended only to allow very low-level streaming interaction between SQL Server and authorized clients. It is recommended that only the service account used by the SQL Server instance should have access to this share. Also because this change takes place at the OS level and not from within SQL Server you need to stop and restart the SQL Server instance for the change to take effect. Using FILESTREAM Storage 1595 After restarting the SQL Server instance to enable FILESTREAM at the Windows OS level you next need to enable FILESTREAM for the SQL Server Instance. You can do this either through SQL Server Management Studio or via T-SQL. To enable FILESTREAM for the SQL Server instance using SQL Server Management Studio right-click on the SQL Server instance in the Object Explorer select Properties select the Advanced page and set the Filestream Access Level property as shown in Figure . The available
