June 8, 2011 at 3:02 pm
I've got a vendor application that's getting upgraded soon. DB is moving from SQL2000 to 2008 via backup/restore. Their new db model has 32 new physical files going to 16 existing filegroups. Basically they are adding a file_3.ndf and file_4.ndf to filegroups where currently I only have file_1.ndf and file_2.ndf. When all done I'll have file_1 and 3 on one drive letter and file_2 and 4 on another.
My question is, if I restore the current SQL2000 db on the new server and then add the new files, data will be lopsided, all in the existing files, only populating the new files as data is added. Do I need to create the database empty with all the new files and then restore? Will that even spread the data evenly across the files? Am I overanalyzing?
Thanks.
June 8, 2011 at 3:06 pm
Randy Doub (6/8/2011)
I've got a vendor application that's getting upgraded soon. DB is moving from SQL2000 to 2008 via backup/restore. Their new db model has 32 new physical files going to 16 existing filegroups. Basically they are adding a file_3.ndf and file_4.ndf to filegroups where currently I only have file_1.ndf and file_2.ndf. When all done I'll have file_1 and 3 on one drive letter and file_2 and 4 on another.My question is, if I restore the current SQL2000 db on the new server and then add the new files, data will be lopsided, all in the existing files, only populating the new files as data is added. Do I need to create the database empty with all the new files and then restore? Will that even spread the data evenly across the files? Am I overanalyzing?
Thanks.
The restore will create the files exactly as they exist in the source database with the data distributed exactly as it is in the source.
June 8, 2011 at 4:01 pm
After you have added the new files, do an index rebuild. That should distribute the data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 8, 2011 at 4:34 pm
just restore the database then add the files.
SQL uses files in a filegroup in a proportional fill basis, i.e it writes more data to the emptier files, thus attempting to keep all files equally full (or empty if you wish).
You sure you won't get better performance by moving filegroups to different drives? that seems like a lot of filegroups and files, are we talking a huge database?
---------------------------------------------------------------------
June 9, 2011 at 6:48 am
Lot of files is right. 73 spread across 6 drive letters. The db is about 120GB now. The amount of data added each night depends on the different interfaces purchased. We are a health care system and currently only interface financial data. Clinical data will be coming soon after the upgrade, so it will be getting much larger. And nothing is ever deleted.
The drive array was built to the vendor's spec, so better to follow and not have config arguments if some processes run slow and vendor support gets involved.
After you have added the new files, do an index rebuild. That should distribute the data.
I thought of this after I first posted. This db has a very large filegroup on it's own drive that the vendor does not place any objects in. It's purpose is to allow one to rebuild indexes using 'drop existing'. I do this with all my million row+ tables. I drop the non-clustered & constraints, reindex to the 'regorgs' filegroup, reindex back to the original location, then put the non-clustered and constraints back. All remaining tables just get dbcc reindexed. It's a 9-5 application so I can do this on a weekend. Takes about 3 hours once a month. So yes, I should start evenly distributing data across all files, including the new ones, after the first reindex.
Thanks everyone.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply