I am considering moving all my customers' DBs to SQL Azure. I am using a single FILESTREAM field to store files in blobs (not for performance on large blobs), but simply because I am using the Express Edition and so I use Filestream as a way not to hit the Express DB size limit).
I have this table, the DOCUMENT field contains the filestream data and the GUID field is needed for filestream implementation:
CREATE TABLE [dbo].[DOC_FILES](
[ID_DOC_FILE] [int] NOT NULL,
[DOCUMENT] [varbinary](max) FILESTREAM NULL,
[GUID] [uniqueidentifier] ROWGUIDCOL NOT NULL
CONSTRAINT [PK_DOC_FILES] PRIMARY KEY CLUSTERED
(
[ID_DOC_FILE] ASC
) ON [PRIMARY] FILESTREAM_ON [MyFileStreamFileGroup],
UNIQUE NONCLUSTERED
(
[GUID] ASC
) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [MyFileStreamFileGroup]
How to remove the filestream in the best way?
One technique I have in mind is simply:
1) renaming the DOCUMENT field to DOCUMENT_TEMP
2) creating a new DOCUMENT (not FILESTREAM) varbinary(max) field
3) copy and delete from DOCUMENT_TEMP to DOCUMENT one by one
4) altering the table to remove filestream [THIS IS NOT CLEAR TO ME]
Can you please suggest?
Best Answer
When you create the tables in Azure simply create them as varbinary(max) and use SSIS or the import/export wizard to move the data.
If you wanted to convert an existing database to using the native varbinary you would need to follow the basic concept you have above.