SQL Server 2012 : Working with FileTables

-
Hi Guys,

 I had a requirement to store files in the database. But had an issue with storage as the files were growing, so my project leader asked me to come up with a solution. Then I found about FileStream and FileTables. So thought of sharing my experience.

 1) Enable FileStream in your instance
Go to Configuration Manager, select the instance -> Properties -> FileStream Tab

2) Enable FileStream on your Server
Go to SQL Server Management Studio, select server -> Properties -> Advanced ->FileStream -> FileStream Access Level -> Full Access Enabled 




3) Create Database with FileStream

CREATE DATABASE Archive 
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'd:\FileStorage\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'd:\FileStorage\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'd:\FileStorage\archlog1.ldf')
GO


4) Specify FileStream Directory Name
Go to SQL Server Management Studio, select database -> Properties -> Options ->FileStream -> FileStream Directory Name  -> Provide Any Name



5) Create FileTable 

USE Archive

IF OBJECT_ID('dbo.TestFileTable', 'U') IS NOT NULL
  DROP TABLE dbo.TestFileTable
GO

CREATE TABLE dbo.TestFileTable AS FILETABLE
  WITH
  (
    FILETABLE_DIRECTORY = 'ArchiveFileTable', --This name must be the name given in 3rd Step
    FILETABLE_COLLATE_FILENAME =  database_default
  )
GO

6) Add Files to your FileTable

There are three ways you can do this.

  • Drag and drop files from the source folders to the new FileTable folder in Windows Explorer.
  • Use command line options such as MOVE, COPY, XCOPY, or ROBOCOPY from the command prompt or in a batch file or script.
  • Write a custom application in C# or Visual Basic.NET that uses methods from the System.IO namespace to move or copy the files.




7) Insert Files to your FileTable using TSQL

INSERT INTO [dbo].[TestFileTable]([name],[file_stream])SELECT'NewFile.txt', * FROM OPENROWSET(BULK N'd:\NewFile.txt', SINGLE_BLOB)AS FileData
GO


Comments

Post a Comment

Popular posts from this blog

Secure .NET Core Api Server and Client

-
Image
 Hi Guys, Recently I had a requirement to create a JWT token based WebApi server and a WebApi Client to consume it using .NET Core. So first I created two .NET Core Web Api projects as shown below. In the appsettings.json file of both the WebApi Server and the Client. I added the following values. "JwtConfig": { "Key": "dsadsagfaqrergsdsfdffdsfdsffdsfdsfdsfdsfdfdsfsfdsfd", //Some Secure Key longer the better "AudienceId": 7895, //Some Secure Audience Id "HostUrl": "https://somesecureurl.com" //Some Host Url } Now we will first look at implementation of the WebApi Server. 1) First we must create code to Generate the JWT token, this can be achieved by creating JWTTokenManager.cs file and adding following code public interface IJwtTokenManager { string Authenticate(string userName, string password); } public class JwtTokenManager : IJwtTokenManager { private readonly IConfiguration _c...
Read more

Security tips for hosting .NET Web Application on IIS

-
Image
  Hi guys, After having made multiple small/medium level web application and hosted them in IIS. I have learned certain actions we can take while hosting to make a web application secure. They are as below. 1) Prevent Clickjacking In order to prevent clickjacking add the below to configuration > system.webServer > httpProtocol > customHeaders in web.config.  <add name="X-Frame-Options" value="sameorigin" /> 2) To prevent clients knowing what options the server is serving (unless you want them to know) add the below  to configuration > system.webServer > httpProtocol > customHeaders in web.config. <add name="X-Content-Type-Options" value="nosniff" /> 3) To prevent CSS attacks add the below to configuration > system.webServer > httpProtocol > customHeaders in web.config. <add name="X-XSS-Protection" value="1" /> 4) Make sure you are using https (TLS) whenever you can. 5) Go to your we...
Read more

Simple API using Python, Flask and pyodbc

-
Image
  Hi Guys, I recently learnt the fundamentals of python, one of widely used languages right now. It is super fast in executing and I recommend everyone to give it a try. So I decided to the create a Simple API which will connect to a SQL Server database to perform CRUD operations. Hope you enjoy.  1) Make sure you have installed python     Go to command prompt type " python --version ", it will return the installed version of python in your computer as shown in the image. If it throws an error, don't worry you can go   to https://www.python.org/downloads/ to download the latest version of python to your pc 2) Install pyodbc to connect to SQL Server Database      Go to command prompt type " pip install pyodbc " 3) Install flask to create the api application      Go to command prompt type " pip install flask " 4) Open Visual Studio Code and create a new file "sqlapi.py" and paste the following code import  pyodbc  impor...
Read more