Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-28 : 08:41:00
|
| John writes "I support an application which uses a SQL 6.5 (SP5a)database on NTS 4.0 (SP6a) that needs to be migrated to SQL 2000 (SP2) on W2KS (SP2), approximately 850MB.The database has 9 database devices and must be configured identically in SQL 2000 using 9 filegroups in order for future upgrades of the application to install successfully; tables need to be located on specific filegroups.A drawback of the SQL 2000 Migration Wizard is that it places all the data in the primary filegroup, even if you let the Wizard create the database or restore into existing one with multiple filegroups.The vendor has a utility to unload/load but it has flaws that they can't seem to resolve.It then occurred to me if I use the Wizard and migrated into an existing database configured with 9 filegroups, could I simply move the tables to their correct filegroups?The only way I've found to do this is using SQL Enterprise Manager, open a table in Design View, right-mouse click, choose Propeties, and select the appropriate filegroup in the "Table Filegroup" drop-down list box.There are 333 tables in the database, thus I used SQL Profiler in attempt to write SQL script of this operation, but it doesn't appear to be easily script-able ... at least to me.Hence, I ask if you know a better way to perform this task?Thank you in advance for any assistance you can provide." |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-28 : 17:06:54
|
| ...So when you script the tables in 6.5, the resulting script does NOT include the "ON {FILEGROUP}" designation? |
 |
|
|
mozart
Starting Member
6 Posts |
Posted - 2002-05-28 : 18:16:48
|
quote:
...So when you script the tables in 6.5, the resulting script does NOT include the "ON {FILEGROUP}" designation?
Everything is configured as it should be in SQL 6.5.The problem is migrating the data from SQL 6.5 to SQL 2000; the vendor's utility, which locates the tables in the correct filegroups, has others problems that they have yet to resolve.SQL 2000's Upgrade Wizard will migrate the data without incident, expect all tables are placed in the primary (default) filegroup regardless if you let the Wizard create the target database or use an existing one.The Wizard, when creating the database, will successfully read SQL 6.5's database device layout and, by default, will create one filegroup with 9 files locating all the tables in the first file. Modifying the default configuration to have 9 individual filegroups produces the same result just as when using a an existing database with 9 individual filegroups. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-28 : 20:05:43
|
| If you migrate everything over to the primary filegroup, you can then move individual tables to different filegroups by dropping and re-creating a table's clustered index on the appropriate filegroup. Books Online has the details on this.Realize that devices are obsolete and the methodology behind them does not exist in SQL 2000. You can simulate the 9 device files you had earlier by creating a single filegroup consisting of 9 files, but they do not behave the same way as 9 devices. Nine separate filegroups would NOT work the same way, as a table cannot be created on more than one filegroup.You should read up on filegroups to ensure you understand the differences if you truly need to maintain precisely the same behavior you had in 6.5. Truthfully though, the differences are great enough that it is probably better to simply approach the migration so that you set up the tables in the way that's best for SQL2K, and not try to carry over the exact setup from 6.5. |
|
|
|
mozart
Starting Member
6 Posts |
Posted - 2002-05-28 : 22:35:37
|
| robvolk,Thank you for your response.I should have stated in my initial post that its the vendor direction to create 9 filegroups in SQL2K. They say if its not done in this manner, the application will still run but updates/upgrades could never be applied.I will review your suggestion in Books Online. |
|
|
|
|
|
|
|
|
Moving tables from one filegroup to another