Sync Tables with FileStream Column

MarioX

We have problems to sync databases.

The sync-wizard give usw following message

[5538] Partial updates are not supported on columns that have a FILESTREAM as a source.


Any Idea how to sync tables with FileStream column?

peter.peart

Hi there,

I think this relates to an existing issue under reference number SDC-909, however I can't confirm that without seeing the synch script generated, or, even better, having backups of the DB's in question.

If you would like for me to have a look at those, you can send the script to support(at)redgate.com with the subject line of F0031571, and if you can send backups just send me an e-mail to that address again with the same subject, and I will create an FTP for you to upload the backups to.

Pete

MarioX

Thanks for the answer.
Where can I find infos about SDC-909?

With the Import / Export Tool from Microsoft its possible to sync Tables with FileStream Column. Unfortunately the tool doesn't show the SQL script ;-)




Table Design

USE [IRLDatabasePrepared]
GO

/****** Object: Table [dbo].[FileStore] Script Date: 02/18/2010 23:38:20 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[FileStore](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Filename] [nvarchar](256) NOT NULL,
[Size] [nvarchar](256) NOT NULL,
[Date] [datetime] NOT NULL,
[MimeType] [nvarchar](256) NULL,
[Title] [nvarchar](256) NULL,
[IRLComment] [text] NULL,
[FileRefCount] [int] NOT NULL,
[FileRefs] [xml] NULL,
[RowGuid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Blob] [varbinary](max) FILESTREAM NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1],
UNIQUE NONCLUSTERED
(
[RowGuid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[FileStore] ADD CONSTRAINT [DF_FileStore_FileRefCount] DEFAULT ((0)) FOR [FileRefCount]
GO

ALTER TABLE [dbo].[FileStore] ADD DEFAULT (newid()) FOR [RowGuid]
GO

MarioX

ok, script to large for a forum post

Here you can find the generated script from DataCompare
http://aterp.de/SyncTableWithFileStreamColumn.sql



And here you can find a test database, includes
- a test DB with one row
- a empty DB
- DataCompare Sync File

http://aterp.de/TestDatabases.rar

peter.peart

Hi Mario,

Many thanks for your patience with this one. I have managed to replicate this here with your sample and I believe it is the same bug listed under SDC-909.

I have updated the bug with your details, and requested one of the developers have a look at it.

Pete

MarioX

Many thanks for your time.


Short info:
Its possible to sync filestream columns with the sync tool from MS (included in the SQL Server Tools).
Its a little bit tricky and not very comfortable but it works.

Chris Auckland

I'm writing to let you know that we now have a patch version of SQL Data Compare 8 that addresses the issue of writing BLOB data to FILESTREAM.

The issue was that SQL Data Compare would perform numerous partial updates when writing BLOB data; this was implemented to improve performance and memory usage. The current version of SQL Server does not support partial updates to FILESTREAM and therefore the script would fail. The fix in the patch version, now means that the update to the FILESTREAM BLOB happens in one go.

The caveat is that you might find this impacts on the performance and/or memory usage, but since it didn't work at all before, it should be an improvement.

For a download link to the patch, see below:
http://www.red-gate.com/messageboard/vi ... hp?t=10917

I hope this helps.