January 16, 2012 at 2:08 am
Hello,
I've read about the value of archiving data and placing it in a read only filegroup. While I understand that this reduces contention overhead during accesses, I was curious as to how you would insert data into such a read only filegroup during the regular migration of "live" data to archive data (e.g. moving all completed orders over 90 days old into the archive database).
Obviously, by definition, a read only filegroup will not allow you to move data into it, so I'm wondering which is the best way to allow this.
I know that I can generate a script in SSIS when I change the filegroup from read only to non read only and vice versa and could use this script within a stored procedure to allow the temporary changing of the filegroup's attributes in order to allow the data migration, but is there a better or more elegant way to achieve this?
Any help appreciated.
Regards
Steve
January 16, 2012 at 2:14 am
You would change the filegroup to read-write, move the data there, set it back to read-only and take a backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2012 at 2:30 am
GilaMonster (1/16/2012)
You would change the filegroup to read-write, move the data there, set it back to read-only and take a backup.
Right, but which is the best way to change the read only attributes in order to achieve the above?
Is the method I surmised in my original questionm the best or correct way to do it?
January 16, 2012 at 2:56 am
Like this?
ALTER DATABASE [TestDB] MODIFY FILEGROUP [ReadOnly] READONLY
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [ReadOnly] READWRITE
GO
ALTER DATABASE [TestDB] MODIFY FILEGROUP [ReadOnly] READONLY
GO
January 16, 2012 at 3:00 am
Thanks for your help 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply