FILESTREAM Configuration and Setup Changes in SQL Server 2008 February CTP
FILESTREAM is disabled by default in SQL2008. Before you can start to use FILESTREAM, you must enable FILESTREAM on the instance of the SQL Server Database Engine. Enabling/configuring FILESTREAM is a bit different from configuring other SQL features because it involves making machine wide changes and not only SQL instance changes. Also, FILESTREAM configuration has been changed for the February CTP to properly separate the Windows Built-in administrator and SQL administrator roles. In the November CTP, enabling/disabling FILESTREAM required Built-in Admin privilege. Enabling FILESTREAM is now made up of 2 admin layers:
1- The Windows configuration layer handles Windows related setup changes required for FILESTREAM. This requires built in administrator privilege. FILESTREAM settings in the SQL Configuration Manager (SQL-CM) represent this admin layer.
2- The SQL configuration layer handles the setup changes required within the boundaries of the SQL instance. The SQL Admin can now use sp_configure/RECONFIGURE to handle this FILESTREAM configuration.
These 2 layers are by design conceptually independent. If the SQL Admin decides to enable FILESTREAM for a particular SQL Server 2008 instance on the system, sp_configure/reconfigure will succeed regardless of the Windows Admin settings. However, FILESTREAM related operations (like creating a database with FILESTREAM filegroups or accessing data in FILESTREAM columns) will fail if the Windows admin configuration was not set to allow this access. To allow these operations to succeed, proper adjusting of the FILESTREAM settings in SQL Configuration Manager is required. Now, FILESTREAM operations will start succeeding. Similarly, if for example the SQL-CM FILESTREAM settings have enabled FILESTREAM, FILESTREAM related operations will fail if the FILESTREAM access level in sp_configure/reconfigure is set to disabled. To allow these operations to succeed, the user needs to call sp_configure/reconfigure and set the access level to a non disabled value. The FILETREAM operations will now succeed.
FILESTREAM has also been integrated for failover scenarios. However, setting up FILESTREAM instance failover remains not integrated in the failover UI. To allow easy failover instance setup for FILESTREAM scenarios in the future, enabling FILESTREAM has been integrated in SQL Setup UI (standalone non-failover instances only). Currently, calling sp_configure is required after SQL setup is complete. This will not be required in the upcoming Refresh CTP.
Here are step by step details on
- how the SQL Admin should configure FILESTREAM (sp_configure),
- how the Windows built in Admin should configure FILESMTREAM (during setup for standalone instances or in SQL Config Manager), and
- how to configure FILESTREAM for failover clusters.
To Enable FILESTREAM During SQL Server 2008 Installation
To enable FILESTREAM for Transact-SQL access, select “enable FILESTREAM for Transact-SQL access”. This control must be checked before the other control options will be available.
To enable Win32 streaming access, select “enable FILESTREAM for file I/O streaming access”. Use the “Windows share name” control to enter the name of the Windows share in which the FILESTREAM data will be stored. Select the “Allow remote clients to have streaming access to FILESTREAM data” control in case you plan to allow remote clients to access this FILESTREAM data on this server.
To Enable FILESTREAM using SQL Configuration Manager
To enable/configure FILESTREAM settings from the UI:
1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2008, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. 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.
8. 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.
9. Click Apply.
To do the same using a vb script, see the following blog entry for the script published on SQL engine community to do that.
Also, this blog entry describes a known issue in SQL Config Manager UI when configuring FILESTREAM.
FILESTREAM Configuration Option in Management Studio
Using T-SQL Script:
Use the FILESTREAM configuration option to change the FILESTREAM access level for this instance of SQL Server. For example:
· In SQL Server Management Studio, click New Query to display the Query Editor.
· In Query Editor, enter the following Transact-SQL code:
EXEC sp_configure ‘filestream_access_level’, ‘[level_value]’
RECONFIGURE
· Click Execute.
Here, [level_value] can be:
0 Disables FILESTREAM support for this instance.
1 Enables FILESTREAM for Transact-SQL access.
2 Enables FILESTREAM for Transact-SQL and Win32 streaming access.
Using the UI:
Right click on the SQL instance and select properties, Advanced and change the FILESTREAM access level.
Before this option has any effect, the Windows administration settings for FILESTREAM must be enabled. You enable these settings when you install SQL Server or by using SQL Server Configuration Manager.
To Set Up FILESTREAM on a Failover Cluster
To set up FILESTREAM on a failover cluster, set up the primary node for the failover cluster. After the setup finishes, enable FILESTREAM on the primary node by using SQL Server Configuration Manager. This enables the settings that require Windows Admin privileges. If remote access is required, select Allow remote clients to have streaming access to FILESTREAM data. This will create a file-share cluster resource. Now, set up a passive node.
After the setup finishes, enable FILESTREAM on the passive node by using SQL Server Configuration Manager. The name that you specify for Windows Share Name must be the same across all nodes in the cluster.
1. Add more passive nodes if needed.
2. After all the nodes are added, complete the process by executing the sp_configure stored procedure on each instance of SQL Server.
3. To add and enable additional nodes to the cluster at any time, you can repeat steps above.