Hi there,

I have a bit of a strange / naive question to pose.

I am documenting a proposed database architecture, and as part of it an approach to backup / restore.

It is a 2008 system, and the database will be split into 3 filegroups / physical disks. 1 for data, 1 for logs and 1 for FILESTREAM data.

The FILESTREAM data will be very large in comparison to the rest of the data (hence its own filegroup), and I was thinking about whether to propose it be backed up separately to the other filegroups. The filestream filegroup applies to only 1 column of the table, with the rest of the table being stored on the main data filegroup.

However, it occurs to me that this may be illogical, as I can't see how the data could ever be restored in a synchronized manner. Presumably if the backups occurred at a slightly different moment in time, they may not be in sync (eg a new row could have appeared in one but not the other).

Does this problem apply to backing up any database split into different filegroups? Basically I can't see the point of only backing up a filegroup in isolation.

Cheers.

Ross

PS. Here is a sample table definition that I am playing with. Note that the ChildBinary column is a FILESTREAM and therefore on its own filegroup :

CREATE TABLE [dbo].[Child](
[ChildId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[ChildName] [nvarchar](50) NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[ChildBinary] [varbinary](max) FILESTREAM NOT NULL,
[ParentId] [int] NOT NULL,
CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
(
[ChildId] 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
(
[ChildId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]



to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

Hi Mitch,

Thanks for the advice. I should have made it clear that the table I
included is just an example. It is nothing to do with a real system.

I was using that table to test the Filestream data type, and needed a few
'normal' columns to profile the memory usage of SQLServer when accessing
Filestream data. I wanted to make sure that the Filestream data was not
buffered in anyway, as advertised. I am happy to report I saw no buffering
in my tests.

By the way, to use Filestream you must have one column that is a
uniqueidentifier type, is unique (by PK or 'unique') and attributed with
'rowguid'. In production I use newsequentialid() as the default on the
uniqueidentifier column to reduce fragmentation. The other thing to
consider is that when you are storing Filestream data, it is most likely you
are storing fairly big blobs, so the overhead of the guid is probably not
too bad.

Cheers.

Ross

----- Original Message -----
From: "Mitch Wheat"
To:
Sent: Monday, August 11, 2008 6:54 PM
Subject: RE: [SQLDownUnder] Filegroup backups / Filestream


> Hi Ross
>
> Unfortunately I don't have the answer for your question, but would also
> ask
> where you intend putting TempDB? (A separate RAID 0 array is a good
> choice).
> Also, does the description column really need to be varchar(max)? I
> usually
> define description columns to be smaller and then enlarge later if
> absolutely necessary.
>
> In addition, you are defining a clustered index on a GUID column which
> means
> you should be aware that this index will probably need to be rebuilt often
> if there are many inserts/deletes on this table. The extra NC unique index
> is probably not needed IMO, as the chances of generating 2 GUIDs that are
> the same is very, very low (provided you use the standard methods for
> creating them).
>
> Regards,
> Mitch
>
>
> -----Original Message--
> From: SQLDownUnderList@...
> [mailto:SQLDownUnderList@...] On Behalf Of
> rjempo@...
> Sent: Monday, 11 August 2008 2:59 PM
> To: SQLDownUnder@...
> Subject: [SQLDownUnder] Filegroup backups / Filestream
>
> Hi there,
>
> I have a bit of a strange / naive question to pose.
>
> I am documenting a proposed database architecture, and as part of it an
> approach to backup / restore.
>
> It is a 2008 system, and the database will be split into 3 filegroups /
> physical disks. 1 for data, 1 for logs and 1 for FILESTREAM data.
>
> The FILESTREAM data will be very large in comparison to the rest of the
> data
> (hence its own filegroup), and I was thinking about whether to propose it
> be
> backed up separately to the other filegroups. The filestream filegroup
> applies to only 1 column of the table, with the rest of the table being
> stored on the main data filegroup.
>
> However, it occurs to me that this may be illogical, as I can't see how
> the
> data could ever be restored in a synchronized manner. Presumably if the
> backups occurred at a slightly different moment in time, they may not be
> in
> sync (eg a new row could have appeared in one but not the other).
>
> Does this problem apply to backing up any database split into different
> filegroups? Basically I can't see the point of only backing up a
> filegroup
> in isolation.
>
> Cheers.
>
> Ross
>
> PS. Here is a sample table definition that I am playing with. Note that
> the ChildBinary column is a FILESTREAM and therefore on its own filegroup
> :
>
> CREATE TABLE [dbo].[Child](
> [ChildId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
> [ChildName] [nvarchar](50) NOT NULL,
> [Description] [nvarchar](max) NOT NULL,
> [ChildBinary] [varbinary](max) FILESTREAM NOT NULL,
> [ParentId] [int] NOT NULL,
> CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
> (
> [ChildId] 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
> (
> [ChildId] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
>
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net
>
>



to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

Ross

The other comment I would make is that you should add an additional file
group to be the default file group and leave the primary file group with no
data. From 2005 onwards you can bring the primary group online and then
selectively restore or bring online "selected" file groups. Obviously if
your primary file group is the default one you end up with most of your data
in the primary and as such have to restore most of your data before you have
options.

The command for changing the default file group is as follows

ALTER DATABASE PartDemo
MODIFY FILEGROUP PartDemo_FG1 DEFAULT;

Geoff Orr

-----Original Message-----
From: SQLDownUnderList@...
[mailto:SQLDownUnderList@...] On Behalf Of rjempo
Sent: Monday, 11 August 2008 8:35 PM
To: SQLDownUnder@...
Subject: Re: [SQLDownUnder] Filegroup backups / Filestream

Hi Mitch,

Thanks for the advice. I should have made it clear that the table I
included is just an example. It is nothing to do with a real system.

I was using that table to test the Filestream data type, and needed a few
'normal' columns to profile the memory usage of SQLServer when accessing
Filestream data. I wanted to make sure that the Filestream data was not
buffered in anyway, as advertised. I am happy to report I saw no buffering
in my tests.

By the way, to use Filestream you must have one column that is a
uniqueidentifier type, is unique (by PK or 'unique') and attributed with
'rowguid'. In production I use newsequentialid() as the default on the
uniqueidentifier column to reduce fragmentation. The other thing to
consider is that when you are storing Filestream data, it is most likely you

are storing fairly big blobs, so the overhead of the guid is probably not
too bad.

Cheers.

Ross

----- Original Message -----
From: "Mitch Wheat"
To:
Sent: Monday, August 11, 2008 6:54 PM
Subject: RE: [SQLDownUnder] Filegroup backups / Filestream


> Hi Ross
>
> Unfortunately I don't have the answer for your question, but would also
> ask
> where you intend putting TempDB? (A separate RAID 0 array is a good
> choice).
> Also, does the description column really need to be varchar(max)? I
> usually
> define description columns to be smaller and then enlarge later if
> absolutely necessary.
>
> In addition, you are defining a clustered index on a GUID column which
> means
> you should be aware that this index will probably need to be rebuilt often
> if there are many inserts/deletes on this table. The extra NC unique index
> is probably not needed IMO, as the chances of generating 2 GUIDs that are
> the same is very, very low (provided you use the standard methods for
> creating them).
>
> Regards,
> Mitch
>
>
> -----Original Message--
> From: SQLDownUnderList@...
> [mailto:SQLDownUnderList@...] On Behalf Of
> rjempo@...
> Sent: Monday, 11 August 2008 2:59 PM
> To: SQLDownUnder@...
> Subject: [SQLDownUnder] Filegroup backups / Filestream
>
> Hi there,
>
> I have a bit of a strange / naive question to pose.
>
> I am documenting a proposed database architecture, and as part of it an
> approach to backup / restore.
>
> It is a 2008 system, and the database will be split into 3 filegroups /
> physical disks. 1 for data, 1 for logs and 1 for FILESTREAM data.
>
> The FILESTREAM data will be very large in comparison to the rest of the
> data
> (hence its own filegroup), and I was thinking about whether to propose it
> be
> backed up separately to the other filegroups. The filestream filegroup
> applies to only 1 column of the table, with the rest of the table being
> stored on the main data filegroup.
>
> However, it occurs to me that this may be illogical, as I can't see how
> the
> data could ever be restored in a synchronized manner. Presumably if the
> backups occurred at a slightly different moment in time, they may not be
> in
> sync (eg a new row could have appeared in one but not the other).
>
> Does this problem apply to backing up any database split into different
> filegroups? Basically I can't see the point of only backing up a
> filegroup
> in isolation.
>
> Cheers.
>
> Ross
>
> PS. Here is a sample table definition that I am playing with. Note that
> the ChildBinary column is a FILESTREAM and therefore on its own filegroup
> :
>
> CREATE TABLE [dbo].[Child](
> [ChildId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
> [ChildName] [nvarchar](50) NOT NULL,
> [Description] [nvarchar](max) NOT NULL,
> [ChildBinary] [varbinary](max) FILESTREAM NOT NULL,
> [ParentId] [int] NOT NULL,
> CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED
> (
> [ChildId] 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
> (
> [ChildId] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
>
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net
>
>
> to unsubscribe to this list, please send a message back to the list with
> 'unsubscribe' as the subject. Powered by mailenable.com - List managed by
> http://www.readify.net
>
>



to unsubscribe to this list, please send a message back to the list with
'unsubscribe' as the subject. Powered by mailenable.com - List managed by
http://www.readify.net





to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net