Moving to a filegroup

I need to move some tables to a new filegroup
I'm actually working only in the primary group
Which is the best way to do this?
Thanks
RicardoRicardo
If you have clustered index defined on your table, so you can by CREATE
CLUSTERED INDEX ON (Filegroup) to place the table on another filegroup
For more deatils please refer to the BOL.
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
create table t1(col1 int) on new_customers
create table t2(col1 int) on sales
"Ricardo" <anonymous@.discussions.microsoft.com> wrote in message
news:441D13F7-DF9C-4E8C-8D82-9DDDA0D2813A@.microsoft.com...
> I need to move some tables to a new filegroup
> I'm actually working only in the primary group
> Which is the best way to do this?
> Thanks
> Ricardo|||Hi Ricardo.
Your would be able to Create to new Filegroup and to Create a New DataFile a
nd to establish this the new FileGroup. Then you should modify the tables t
hat you want to move the new filegroup.
You can utilize the Query Analyzer and to execute the "Alter Table" statemen
t for each table.
Hermilson.
****************************************
********************
I need to move some tables to a new filegroup
I'm actually working only in the primary group
Which is the best way to do this?
Thanks
Ricardo

Moving to a filegroup

I need to move some tables to a new filegrou
I'm actually working only in the primary grou
Which is the best way to do this
Thank
RicardoRicardo
If you have clustered index defined on your table, so you can by CREATE
CLUSTERED INDEX ON (Filegroup) to place the table on another filegroup
For more deatils please refer to the BOL.
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
ALTER DATABASE mywind
ADD FILE
(NAME='mywind_data_2',
FILENAME='d:\mw.dat2')
TO FILEGROUP sales
create table t1(col1 int) on new_customers
create table t2(col1 int) on sales
"Ricardo" <anonymous@.discussions.microsoft.com> wrote in message
news:441D13F7-DF9C-4E8C-8D82-9DDDA0D2813A@.microsoft.com...
> I need to move some tables to a new filegroup
> I'm actually working only in the primary group
> Which is the best way to do this?
> Thanks
> Ricardo|||Hi Ricardo
Your would be able to Create to new Filegroup and to Create a New DataFile and to establish this the new FileGroup. Then you should modify the tables that you want to move the new filegroup
You can utilize the Query Analyzer and to execute the "Alter Table" statement for each table
Hermilson
***********************************************************
I need to move some tables to a new filegrou
I'm actually working only in the primary grou
Which is the best way to do this
Thank
Ricard

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B9F7F302-964A-4825-9246-6143A8681900.scuk
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
--
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI on PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.
Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/B...3A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>
|||Thanks all.
I have one more question. How do I move the log file from the default location
to another one.
I would like to add a new file to the log file and remove the first file to the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.
|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its
> own.
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option in TSQL
to add a column at the desired location. The newly added NTEXT column should be
right before the existing NTEXT column, so that after dropping the existing NTEXT
column, its position is taken up by this new column. This is the only way to guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series of steps
it does to mimic that.
|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com
sql

moving tables and index to different filegroup

I need to create a script to move all tables and indexes to a different
filegroup after all tables and indexes are created on the primary. The
reason being we don't have license to the code and we all always get
updates to the schema in vanilla format.
It seems there is no easy way to do it since there is no ALTER
TABLE command to move table to a different filegroup.
The logic I want to use is:
(a) Find the cluster index on each table (almost all of the tables have CI o
n PKY)
and move it to different filegroup.
(b) for non clustered indexes, find them by querying sysindexes, syscolumns
and sysobjects
to recreate the index columns. Then drop the index and recreate it.
(c) Similarly all NTEXT columns will be identified and moved to separate fil
egroup of its
own.
Is there anything I should pay attention to.
How do I identify cluster index by querying index table.
Thanks.Hi,
Clustered index the INDID in sysindex table will be 1.
Note:
Ensure that you take full database backup before you do this activity.
Thanks
Hari
SQL Server MVP
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||This article talks about using SQL-DMO:
http://www.sqljunkies.com/How%20To/...43A8681900.scuk
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:3fmojvF8l53lU1@.individual.net...
>I need to create a script to move all tables and indexes to a different
> filegroup after all tables and indexes are created on the primary. The
> reason being we don't have license to the code and we all always get
> updates to the schema in vanilla format.
> It seems there is no easy way to do it since there is no ALTER
> TABLE command to move table to a different filegroup.
> The logic I want to use is:
> (a) Find the cluster index on each table (almost all of the tables have CI
> on PKY)
> and move it to different filegroup.
> (b) for non clustered indexes, find them by querying sysindexes,
> syscolumns and sysobjects
> to recreate the index columns. Then drop the index and recreate it.
> (c) Similarly all NTEXT columns will be identified and moved to separate
> filegroup of its own.
> Is there anything I should pay attention to.
> How do I identify cluster index by querying index table.
> Thanks.
>|||Thanks all.
I have one more question. How do I move the log file from the default locati
on
to another one.
I would like to add a new file to the log file and remove the first file to
the log created
at the time of database creation. This has to be done in a TSQL script.
TIA.|||Data Cruncher wrote:
> Thanks all.
> I have one more question. How do I move the log file from the default
> location to another one.
> I would like to add a new file to the log file and remove the first
> file to the log created at the time of database creation. This has to
> be done in a TSQL script.
> TIA.
You could probably detach the database, move the log file, and then
reattach specifying the new log file location.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> (c) Similarly all NTEXT columns will be identified and moved to separate filegroup of its[
vbcol=seagreen]
> own.[/vbcol]
it seems to do this in a script is a big pain. ALTER TABLE ALTER can not be
used
with a NTEXT column. I was thinking of this approach.
(a) Add a new NTEXT column with TEXTIMAGE_ON in the desired filegroup.
(b) Update table
set new_NTEXT_column = existing_NTEXT_COLUMN
(c) drop the existing_NTEXT_COLUMN
(d) rename the newly added NTEXT column to the old one which was dropped.
But the problem is (a) itself. It seems SQL Server does not have an option i
n TSQL
to add a column at the desired location. The newly added NTEXT column should
be
right before the existing NTEXT column, so that after dropping the existing
NTEXT
column, its position is taken up by this new column. This is the only way to
guarantee
that no application breaks.
ALTER TABLE ADD , adds a new column right at the end of the table. EM allows
adding a column at the desired location by hiding the complexity of a series
of steps
it does to mimic that.|||I wrote a T-SQL script that moves a table to another filegroup, and you can
download it from
http://education.sqlfarms.com/ShowPost.aspx?PostID=59
Note that you can specify whether only data pages, and/or other indexes and
constraints should be moved automatically by the script. It's fairly long,
however it was very well-tested.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com

Moving SQL Server 2000 from NT4.0 to Windows 2003

Hello everyone,
Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
between SP, OS and system tables(SQL Server 2000).
Thank you.
Scott YuScott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
> NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
> between SP, OS and system tables(SQL Server 2000).
Attached is an excellent post from Keith Kratochvil on moving SQL Server
2000 between servers. You should be OK on the various revision levels you
have there... I'd be more concerned about moving to downlevel (i.e. SP3 to
SP2) than your plan.
Steve
~~~~~~~~~~~~~~~~
The better approach would be to use BACKUP and RESTORE or sp_detach_db and
sp_attach_db to restore/attach your database(s) to the new server. You can
find lots of information within Books Online. Here are some great
resources:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
~~~~~~~~~~~~~~~~|||Hey Steve,
Thanks for your advice. Yeah, I already read Keith Kratochvil article on
moving SQL Servers, but the article assumes you are moving to a new machine
with the same SP and OS. This is not the case with me. It doesn't mention
about difference in service packs or the fact that Windows 2003 only allows
SQL Server 2000 SP3 or higher and Active Directory in 2003.

> I'd be more concerned about moving to downlevel (i.e. SP3 to SP2) than your plan.[
/vbcol]
----
-
Can't do that. Windows 2003 will not allow SQL Server SP less than 3. I
have to install SP3 on 2003 so I have to go uplevel.
So, you think moving(by detaching or using a bak file) the system
tables(Master, MSDB, tempdb and Model) will be okay. I'm going to name the
new server the same name as the old as well in a test domain, which is cut
off from the production domain.
Is there any order in which I need to restore or reattach the system table i
n?
Thanks Steve for the info and the links. I'll read them all this week.
Scott Yu
"Steve Thompson" wrote:
[vbcol=seagreen]
> Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> Attached is an excellent post from Keith Kratochvil on moving SQL Server
> 2000 between servers. You should be OK on the various revision levels you
> have there... I'd be more concerned about moving to downlevel (i.e. SP3 to
> SP2) than your plan.
> Steve
> ~~~~~~~~~~~~~~~~
> The better approach would be to use BACKUP and RESTORE or sp_detach_db and
> sp_attach_db to restore/attach your database(s) to the new server. You ca
n
> find lots of information within Books Online. Here are some great
> resources:
> INF: Disaster Recovery Articles for Microsoft SQL Server
> http://www.support.microsoft.com/?id=307775
> INF: Moving SQL Server databases to a new location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.support.microsoft.com/?id=314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/?id=221465
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://www.support.microsoft.com/?id=274188
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
> Database Is Moved
> http://www.support.microsoft.com/?id=298897
>
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> ~~~~~~~~~~~~~~~~
>
>|||Hi Scott,
Unless you did something unusual in Master (and Tempdb), you should not have
to move these 2 system databases.
Detach (or BACKUP) your existing user databases, then copy/re-attach the
user databases (or RESTORE) to the new server. Restore your copy of msdb to
retain the SQL Server agent scheduled tasks. Then, follow the procedures to
migrate the user accounts
http://support.microsoft.com/defaul...kb;en-us;246133
Come back with any questions. Planning something like this is a key aspect
of success!
Steve
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:9337CA09-B264-4944-84E1-460AD0C0CE39@.microsoft.com...
> Hey Steve,
> Thanks for your advice. Yeah, I already read Keith Kratochvil article on
> moving SQL Servers, but the article assumes you are moving to a new
machine
> with the same SP and OS. This is not the case with me. It doesn't
mention
> about difference in service packs or the fact that Windows 2003 only
allows
> SQL Server 2000 SP3 or higher and Active Directory in 2003.
>
your plan.[vbcol=seagreen]
> ----
--
> Can't do that. Windows 2003 will not allow SQL Server SP less than 3. I
> have to install SP3 on 2003 so I have to go uplevel.
> So, you think moving(by detaching or using a bak file) the system
> tables(Master, MSDB, tempdb and Model) will be okay. I'm going to name
the
> new server the same name as the old as well in a test domain, which is cut
> off from the production domain.
> Is there any order in which I need to restore or reattach the system table
in?[vbcol=seagreen]
> Thanks Steve for the info and the links. I'll read them all this week.
> Scott Yu
>
> "Steve Thompson" wrote:
>
on[vbcol=seagreen]
difference[vbcol=seagreen]
you[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
can[vbcol=seagreen]
Incomplete[vbcol=seagreen]
When[vbcol=seagreen]

Moving SQL Server 2000 from NT4.0 to Windows 2003

Hello everyone,
Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
between SP, OS and system tables(SQL Server 2000).
Thank you.
Scott Yu
Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
> NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
> between SP, OS and system tables(SQL Server 2000).
Attached is an excellent post from Keith Kratochvil on moving SQL Server
2000 between servers. You should be OK on the various revision levels you
have there... I'd be more concerned about moving to downlevel (i.e. SP3 to
SP2) than your plan.
Steve
~~~~~~~~~~~~~~~~
The better approach would be to use BACKUP and RESTORE or sp_detach_db and
sp_attach_db to restore/attach your database(s) to the new server. You can
find lots of information within Books Online. Here are some great
resources:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
~~~~~~~~~~~~~~~~
|||Hey Steve,
Thanks for your advice. Yeah, I already read Keith Kratochvil article on
moving SQL Servers, but the article assumes you are moving to a new machine
with the same SP and OS. This is not the case with me. It doesn't mention
about difference in service packs or the fact that Windows 2003 only allows
SQL Server 2000 SP3 or higher and Active Directory in 2003.

> I'd be more concerned about moving to downlevel (i.e. SP3 to SP2) than your plan.
Can't do that. Windows 2003 will not allow SQL Server SP less than 3. I
have to install SP3 on 2003 so I have to go uplevel.
So, you think moving(by detaching or using a bak file) the system
tables(Master, MSDB, tempdb and Model) will be okay. I'm going to name the
new server the same name as the old as well in a test domain, which is cut
off from the production domain.
Is there any order in which I need to restore or reattach the system table in?
Thanks Steve for the info and the links. I'll read them all this week.
Scott Yu
"Steve Thompson" wrote:

> Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> Attached is an excellent post from Keith Kratochvil on moving SQL Server
> 2000 between servers. You should be OK on the various revision levels you
> have there... I'd be more concerned about moving to downlevel (i.e. SP3 to
> SP2) than your plan.
> Steve
> ~~~~~~~~~~~~~~~~
> The better approach would be to use BACKUP and RESTORE or sp_detach_db and
> sp_attach_db to restore/attach your database(s) to the new server. You can
> find lots of information within Books Online. Here are some great
> resources:
> INF: Disaster Recovery Articles for Microsoft SQL Server
> http://www.support.microsoft.com/?id=307775
> INF: Moving SQL Server databases to a new location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.support.microsoft.com/?id=314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/?id=221465
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://www.support.microsoft.com/?id=274188
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
> Database Is Moved
> http://www.support.microsoft.com/?id=298897
>
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> ~~~~~~~~~~~~~~~~
>
>
|||Hi Scott,
Unless you did something unusual in Master (and Tempdb), you should not have
to move these 2 system databases.
Detach (or BACKUP) your existing user databases, then copy/re-attach the
user databases (or RESTORE) to the new server. Restore your copy of msdb to
retain the SQL Server agent scheduled tasks. Then, follow the procedures to
migrate the user accounts
http://support.microsoft.com/default...b;en-us;246133
Come back with any questions. Planning something like this is a key aspect
of success!
Steve
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:9337CA09-B264-4944-84E1-460AD0C0CE39@.microsoft.com...
> Hey Steve,
> Thanks for your advice. Yeah, I already read Keith Kratochvil article on
> moving SQL Servers, but the article assumes you are moving to a new
machine
> with the same SP and OS. This is not the case with me. It doesn't
mention
> about difference in service packs or the fact that Windows 2003 only
allows[vbcol=seagreen]
> SQL Server 2000 SP3 or higher and Active Directory in 2003.
your plan.
> ----
--
> Can't do that. Windows 2003 will not allow SQL Server SP less than 3. I
> have to install SP3 on 2003 so I have to go uplevel.
> So, you think moving(by detaching or using a bak file) the system
> tables(Master, MSDB, tempdb and Model) will be okay. I'm going to name
the
> new server the same name as the old as well in a test domain, which is cut
> off from the production domain.
> Is there any order in which I need to restore or reattach the system table
in?[vbcol=seagreen]
> Thanks Steve for the info and the links. I'll read them all this week.
> Scott Yu
>
> "Steve Thompson" wrote:
on[vbcol=seagreen]
difference[vbcol=seagreen]
you[vbcol=seagreen]
to[vbcol=seagreen]
and[vbcol=seagreen]
can[vbcol=seagreen]
Incomplete[vbcol=seagreen]
When[vbcol=seagreen]

Moving SQL Server 2000 from NT4.0 to Windows 2003

Hello everyone,
Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
between SP, OS and system tables(SQL Server 2000).
Thank you.
Scott YuScott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
> NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
> between SP, OS and system tables(SQL Server 2000).
Attached is an excellent post from Keith Kratochvil on moving SQL Server
2000 between servers. You should be OK on the various revision levels you
have there... I'd be more concerned about moving to downlevel (i.e. SP3 to
SP2) than your plan.
Steve
~~~~~~~~~~~~~~~~
The better approach would be to use BACKUP and RESTORE or sp_detach_db and
sp_attach_db to restore/attach your database(s) to the new server. You can
find lots of information within Books Online. Here are some great
resources:
INF: Disaster Recovery Articles for Microsoft SQL Server
http://www.support.microsoft.com/?id=307775
INF: Moving SQL Server databases to a new location with Detach/Attach
http://www.support.microsoft.com/?id=224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://www.support.microsoft.com/?id=314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/?id=221465
PRB: User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://www.support.microsoft.com/?id=274188
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
~~~~~~~~~~~~~~~~|||Hey Steve,
Thanks for your advice. Yeah, I already read Keith Kratochvil article on
moving SQL Servers, but the article assumes you are moving to a new machine
with the same SP and OS. This is not the case with me. It doesn't mention
about difference in service packs or the fact that Windows 2003 only allows
SQL Server 2000 SP3 or higher and Active Directory in 2003.
> I'd be more concerned about moving to downlevel (i.e. SP3 to SP2) than your plan.
----
Can't do that. Windows 2003 will not allow SQL Server SP less than 3. I
have to install SP3 on 2003 so I have to go uplevel.
So, you think moving(by detaching or using a bak file) the system
tables(Master, MSDB, tempdb and Model) will be okay. I'm going to name the
new server the same name as the old as well in a test domain, which is cut
off from the production domain.
Is there any order in which I need to restore or reattach the system table in?
Thanks Steve for the info and the links. I'll read them all this week.
Scott Yu
"Steve Thompson" wrote:
> Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> > Can anyone give me hints on moving the whole SQL Server 2000 with SP2 on
> > NT4.0 to Windows 2003 running SP3? My primary concerns are the difference
> > between SP, OS and system tables(SQL Server 2000).
> Attached is an excellent post from Keith Kratochvil on moving SQL Server
> 2000 between servers. You should be OK on the various revision levels you
> have there... I'd be more concerned about moving to downlevel (i.e. SP3 to
> SP2) than your plan.
> Steve
> ~~~~~~~~~~~~~~~~
> The better approach would be to use BACKUP and RESTORE or sp_detach_db and
> sp_attach_db to restore/attach your database(s) to the new server. You can
> find lots of information within Books Online. Here are some great
> resources:
> INF: Disaster Recovery Articles for Microsoft SQL Server
> http://www.support.microsoft.com/?id=307775
> INF: Moving SQL Server databases to a new location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://www.support.microsoft.com/?id=314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/?id=221465
> PRB: User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
> http://www.support.microsoft.com/?id=274188
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
> Database Is Moved
> http://www.support.microsoft.com/?id=298897
>
> Utility to map users to the correct login
> http://www.dbmaint.com/SyncSqlLogins.asp
> ~~~~~~~~~~~~~~~~
>
>|||Hi Scott,
Unless you did something unusual in Master (and Tempdb), you should not have
to move these 2 system databases.
Detach (or BACKUP) your existing user databases, then copy/re-attach the
user databases (or RESTORE) to the new server. Restore your copy of msdb to
retain the SQL Server agent scheduled tasks. Then, follow the procedures to
migrate the user accounts
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
Come back with any questions. Planning something like this is a key aspect
of success!
Steve
"Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
news:9337CA09-B264-4944-84E1-460AD0C0CE39@.microsoft.com...
> Hey Steve,
> Thanks for your advice. Yeah, I already read Keith Kratochvil article on
> moving SQL Servers, but the article assumes you are moving to a new
machine
> with the same SP and OS. This is not the case with me. It doesn't
mention
> about difference in service packs or the fact that Windows 2003 only
allows
> SQL Server 2000 SP3 or higher and Active Directory in 2003.
> > I'd be more concerned about moving to downlevel (i.e. SP3 to SP2) than
your plan.
> ----
--
> Can't do that. Windows 2003 will not allow SQL Server SP less than 3. I
> have to install SP3 on 2003 so I have to go uplevel.
> So, you think moving(by detaching or using a bak file) the system
> tables(Master, MSDB, tempdb and Model) will be okay. I'm going to name
the
> new server the same name as the old as well in a test domain, which is cut
> off from the production domain.
> Is there any order in which I need to restore or reattach the system table
in?
> Thanks Steve for the info and the links. I'll read them all this week.
> Scott Yu
>
> "Steve Thompson" wrote:
> > Scott Yu" <ScottYu@.discussions.microsoft.com> wrote in message
> > news:9829CD45-5809-42F5-AE3A-437541B2903C@.microsoft.com...
> > > Can anyone give me hints on moving the whole SQL Server 2000 with SP2
on
> > > NT4.0 to Windows 2003 running SP3? My primary concerns are the
difference
> > > between SP, OS and system tables(SQL Server 2000).
> >
> > Attached is an excellent post from Keith Kratochvil on moving SQL Server
> > 2000 between servers. You should be OK on the various revision levels
you
> > have there... I'd be more concerned about moving to downlevel (i.e. SP3
to
> > SP2) than your plan.
> >
> > Steve
> > ~~~~~~~~~~~~~~~~
> > The better approach would be to use BACKUP and RESTORE or sp_detach_db
and
> > sp_attach_db to restore/attach your database(s) to the new server. You
can
> > find lots of information within Books Online. Here are some great
> > resources:
> >
> > INF: Disaster Recovery Articles for Microsoft SQL Server
> > http://www.support.microsoft.com/?id=307775
> >
> > INF: Moving SQL Server databases to a new location with Detach/Attach
> > http://www.support.microsoft.com/?id=224071
> >
> > HOW TO: Move Databases Between Computers That Are Running SQL Server
> > http://www.support.microsoft.com/?id=314546
> >
> > INF: Using the WITH MOVE Option with the RESTORE Statement
> > http://support.microsoft.com/?id=221465
> >
> > PRB: User Logon and/or Permission Errors After Restoring Dump
> > http://www.support.microsoft.com/?id=168001
> >
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
> > http://www.support.microsoft.com/?id=274188
> >
> > How to Resolve Permission Issues When a Database Is Moved Between SQL
> > Servers
> > http://www.support.microsoft.com/?id=240872
> >
> > SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases
When
> > Database Is Moved
> > http://www.support.microsoft.com/?id=298897
> >
> >
> >
> > Utility to map users to the correct login
> > http://www.dbmaint.com/SyncSqlLogins.asp
> >
> > ~~~~~~~~~~~~~~~~
> >
> >
> >

Moving Primary Transaction log

After installing SQL server 2000 STD, SP3 how can I move primary transaction
log to another drive?
ThanksBACKUP and RESTORE WITH MOVE
or the quicker method:
sp_detach_db
copy the file
sp_attach_db
You will be able to find documentation on each of these methods within Books
Online (within the SQL Server program group).
--
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:7C5C9BAF-E835-413B-8F57-4581170BF33A@.microsoft.com...
> After installing SQL server 2000 STD, SP3 how can I move primary
transaction
> log to another drive?
> Thanks
>

Moving Primary Transaction log

After installing SQL server 2000 STD, SP3 how can I move primary transaction
log to another drive?
Thanks
BACKUP and RESTORE WITH MOVE
or the quicker method:
sp_detach_db
copy the file
sp_attach_db
You will be able to find documentation on each of these methods within Books
Online (within the SQL Server program group).
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:7C5C9BAF-E835-413B-8F57-4581170BF33A@.microsoft.com...
> After installing SQL server 2000 STD, SP3 how can I move primary
transaction
> log to another drive?
> Thanks
>

Moving Primary Transaction log

After installing SQL server 2000 STD, SP3 how can I move primary transaction
log to another drive?
ThanksBACKUP and RESTORE WITH MOVE
or the quicker method:
sp_detach_db
copy the file
sp_attach_db
You will be able to find documentation on each of these methods within Books
Online (within the SQL Server program group).
Keith
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:7C5C9BAF-E835-413B-8F57-4581170BF33A@.microsoft.com...
> After installing SQL server 2000 STD, SP3 how can I move primary
transaction
> log to another drive?
> Thanks
>

moving primary log file

hi,
i'm running out of space on my local drive. the db that i am working on has
one log file. i'd like to move it to a network drive. is that allowed?
i've gotten an error message to the contrary when i tried this w/ a test db.
also, how do a i move a primary log file for a database?
thank you
matthew
I would try sp_detach_db/ sp_attach_db.
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in
message news:3FD18944-59E5-48EE-A306-A35C93329FD9@.microsoft.com...
> hi,
> i'm running out of space on my local drive. the db that i am working on
has
> one log file. i'd like to move it to a network drive. is that allowed?
> i've gotten an error message to the contrary when i tried this w/ a test
db.
> also, how do a i move a primary log file for a database?
> thank you
> matthew
|||Hi
You can't put it onto a "normal" network drive, but if you have a SAN that
is ok. You may want to detach the database (sp_detach_db) and attach just the
data file to create a new (smaller) log file see sp_attach_single_file_db in
books online. To keep the existing one you can use sp_attach_db specifying
the new location of the log, or backup/restore the database using the MOVE
clause (or changing the location in the Advanced tab in EM).
John
"matthew c. harad" wrote:

> hi,
> i'm running out of space on my local drive. the db that i am working on has
> one log file. i'd like to move it to a network drive. is that allowed?
> i've gotten an error message to the contrary when i tried this w/ a test db.
> also, how do a i move a primary log file for a database?
> thank you
> matthew

moving primary log file

hi,
i'm running out of space on my local drive. the db that i am working on has
one log file. i'd like to move it to a network drive. is that allowed?
i've gotten an error message to the contrary when i tried this w/ a test db.
also, how do a i move a primary log file for a database?
thank you
matthewI would try sp_detach_db/ sp_attach_db.
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in
message news:3FD18944-59E5-48EE-A306-A35C93329FD9@.microsoft.com...
> hi,
> i'm running out of space on my local drive. the db that i am working on
has
> one log file. i'd like to move it to a network drive. is that allowed?
> i've gotten an error message to the contrary when i tried this w/ a test
db.
> also, how do a i move a primary log file for a database?
> thank you
> matthew|||Hi
You can't put it onto a "normal" network drive, but if you have a SAN that
is ok. You may want to detach the database (sp_detach_db) and attach just th
e
data file to create a new (smaller) log file see sp_attach_single_file_db in
books online. To keep the existing one you can use sp_attach_db specifying
the new location of the log, or backup/restore the database using the MOVE
clause (or changing the location in the Advanced tab in EM).
John
"matthew c. harad" wrote:

> hi,
> i'm running out of space on my local drive. the db that i am working on h
as
> one log file. i'd like to move it to a network drive. is that allowed?
> i've gotten an error message to the contrary when i tried this w/ a test d
b.
> also, how do a i move a primary log file for a database?
> thank you
> matthew

moving primary log file

hi,
i'm running out of space on my local drive. the db that i am working on has
one log file. i'd like to move it to a network drive. is that allowed?
i've gotten an error message to the contrary when i tried this w/ a test db.
also, how do a i move a primary log file for a database?
thank you
matthewI would try sp_detach_db/ sp_attach_db.
"matthew c. harad" <matthewcharad@.discussions.microsoft.com> wrote in
message news:3FD18944-59E5-48EE-A306-A35C93329FD9@.microsoft.com...
> hi,
> i'm running out of space on my local drive. the db that i am working on
has
> one log file. i'd like to move it to a network drive. is that allowed?
> i've gotten an error message to the contrary when i tried this w/ a test
db.
> also, how do a i move a primary log file for a database?
> thank you
> matthew|||Hi
You can't put it onto a "normal" network drive, but if you have a SAN that
is ok. You may want to detach the database (sp_detach_db) and attach just the
data file to create a new (smaller) log file see sp_attach_single_file_db in
books online. To keep the existing one you can use sp_attach_db specifying
the new location of the log, or backup/restore the database using the MOVE
clause (or changing the location in the Advanced tab in EM).
John
"matthew c. harad" wrote:
> hi,
> i'm running out of space on my local drive. the db that i am working on has
> one log file. i'd like to move it to a network drive. is that allowed?
> i've gotten an error message to the contrary when i tried this w/ a test db.
> also, how do a i move a primary log file for a database?
> thank you
> matthew

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group> )
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself t
o
another.
Any suggestions would be appreciated.
--
Todd C(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group> )
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-cluster
ed
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP|||Inserting/querrying/updating...not sure where the load is coming from at thi
s
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the blac
k
art of database performance tuning for a while...
Any good books or resources out there?
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because
the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issue
s.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
Todd C
(Re)Create a clustered index on the table of concern and specify the
filegroup then.
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C
|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>
|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>
|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
Aaron Bertrand
SQL Server MVP
|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:

> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...[vbcol=seagreen]
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C

Moving objects from Primary File Group using T-SQL

Hello All;
I am trying to spread database objects by moving certain tables and indexes
to file groups that I have created just for this purpose. Right now,
everything is in the PRIMARY file group, and I have created a secondary Data
filegroup and a third file group for indexes.
I see in BOL the syntax for moving an index (actaully dropping and
re-creating) but don't see how to move just a single table. I can't seem to
get the syntax correct.
ALTER TABLE <My Table>
DROP CONSTRAINT <My Constraint>
WITH (ONLINE = ON, MOVE TO <My File Group>)
That works, but only if I have something to drop. I don't want to drop the
PK Constraints, I want to move them to one file group and the table itself to
another.
Any suggestions would be appreciated.
--
Todd C(Re)Create a clustered index on the table of concern and specify the
filegroup then.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> Hello All;
> I am trying to spread database objects by moving certain tables and
> indexes
> to file groups that I have created just for this purpose. Right now,
> everything is in the PRIMARY file group, and I have created a secondary
> Data
> filegroup and a third file group for indexes.
> I see in BOL the syntax for moving an index (actaully dropping and
> re-creating) but don't see how to move just a single table. I can't seem
> to
> get the syntax correct.
> ALTER TABLE <My Table>
> DROP CONSTRAINT <My Constraint>
> WITH (ONLINE = ON, MOVE TO <My File Group>)
> That works, but only if I have something to drop. I don't want to drop the
> PK Constraints, I want to move them to one file group and the table itself
> to
> another.
> Any suggestions would be appreciated.
> --
> Todd C|||Todd, note that this moves the data, the clustered index and all
non-clustered indexes to the new filegroup. By definition, you cannot put
the clustered index on one filegroup, the data on another, and non-clustered
indexes on yet others...
--
Aaron Bertrand
SQL Server MVP
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> (Re)Create a clustered index on the table of concern and specify the
> filegroup then.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>> Hello All;
>> I am trying to spread database objects by moving certain tables and
>> indexes
>> to file groups that I have created just for this purpose. Right now,
>> everything is in the PRIMARY file group, and I have created a secondary
>> Data
>> filegroup and a third file group for indexes.
>> I see in BOL the syntax for moving an index (actaully dropping and
>> re-creating) but don't see how to move just a single table. I can't seem
>> to
>> get the syntax correct.
>> ALTER TABLE <My Table>
>> DROP CONSTRAINT <My Constraint>
>> WITH (ONLINE = ON, MOVE TO <My File Group>)
>> That works, but only if I have something to drop. I don't want to drop
>> the
>> PK Constraints, I want to move them to one file group and the table
>> itself to
>> another.
>> Any suggestions would be appreciated.
>> --
>> Todd C
>|||Hello Aaron;
So a table's data and indexes must all be in the same filegroup?
Leaving Table Partitioning out of the discussuion, what is the best way to
increase performance on tables that are heavily used and heavily indexed?
Our databases have been relatively small up til now and I foresee that in
the near future I am going to need to look at tuning and performance issues.
What is the best approach?
Thanks in advance.
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:
> Todd, note that this moves the data, the clustered index and all
> non-clustered indexes to the new filegroup. By definition, you cannot put
> the clustered index on one filegroup, the data on another, and non-clustered
> indexes on yet others...
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
> > (Re)Create a clustered index on the table of concern and specify the
> > filegroup then.
> >
> > --
> > TheSQLGuru
> > President
> > Indicium Resources, Inc.
> >
> > "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
> > news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
> >> Hello All;
> >> I am trying to spread database objects by moving certain tables and
> >> indexes
> >> to file groups that I have created just for this purpose. Right now,
> >> everything is in the PRIMARY file group, and I have created a secondary
> >> Data
> >> filegroup and a third file group for indexes.
> >>
> >> I see in BOL the syntax for moving an index (actaully dropping and
> >> re-creating) but don't see how to move just a single table. I can't seem
> >> to
> >> get the syntax correct.
> >>
> >> ALTER TABLE <My Table>
> >> DROP CONSTRAINT <My Constraint>
> >> WITH (ONLINE = ON, MOVE TO <My File Group>)
> >>
> >> That works, but only if I have something to drop. I don't want to drop
> >> the
> >> PK Constraints, I want to move them to one file group and the table
> >> itself to
> >> another.
> >>
> >> Any suggestions would be appreciated.
> >> --
> >> Todd C
> >
> >
>
>|||> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
Are the performance problems on inserting, querying, something else? The
"best" approach is pretty subjective, and depends on a lot of factors,
including budget.
--
Aaron Bertrand
SQL Server MVP|||Inserting/querrying/updating...not sure where the load is coming from at this
point. Currently running a tuning profile to analyze later.I just know that
this one database is the heaviest used (but not the biggest) on this one
server, and the one data drive is experiencing some heavy load.
Two new drives have been added (RAID, dedicated channels, etc) for me to
start spreading out the load. OK, So it looks like I'll be studying the black
art of database performance tuning for a while...
Any good books or resources out there?
--
Todd C
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Leaving Table Partitioning out of the discussuion, what is the best way to
> > increase performance on tables that are heavily used and heavily indexed?
> Are the performance problems on inserting, querying, something else? The
> "best" approach is pretty subjective, and depends on a lot of factors,
> including budget.
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||> So a table's data and indexes must all be in the same filegroup?
No. I don't think that was what Aaron's meant.
A table and it's *clustered index* need to be on the same file group because the clustered index
*is* the table. But you can have non-clustered indexes on other filegroup(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Todd C" <ToddC@.discussions.microsoft.com> wrote in message
news:A83833FE-CCDE-47C4-AC4F-D0404CD12B8F@.microsoft.com...
> Hello Aaron;
> So a table's data and indexes must all be in the same filegroup?
> Leaving Table Partitioning out of the discussuion, what is the best way to
> increase performance on tables that are heavily used and heavily indexed?
> Our databases have been relatively small up til now and I foresee that in
> the near future I am going to need to look at tuning and performance issues.
> What is the best approach?
> Thanks in advance.
> --
> Todd C
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> Todd, note that this moves the data, the clustered index and all
>> non-clustered indexes to the new filegroup. By definition, you cannot put
>> the clustered index on one filegroup, the data on another, and non-clustered
>> indexes on yet others...
>> --
>> Aaron Bertrand
>> SQL Server MVP
>>
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:%2343LVMe3HHA.1212@.TK2MSFTNGP05.phx.gbl...
>> > (Re)Create a clustered index on the table of concern and specify the
>> > filegroup then.
>> >
>> > --
>> > TheSQLGuru
>> > President
>> > Indicium Resources, Inc.
>> >
>> > "Todd C" <ToddC@.discussions.microsoft.com> wrote in message
>> > news:EBE06366-4135-4789-94AC-AF5F0886901E@.microsoft.com...
>> >> Hello All;
>> >> I am trying to spread database objects by moving certain tables and
>> >> indexes
>> >> to file groups that I have created just for this purpose. Right now,
>> >> everything is in the PRIMARY file group, and I have created a secondary
>> >> Data
>> >> filegroup and a third file group for indexes.
>> >>
>> >> I see in BOL the syntax for moving an index (actaully dropping and
>> >> re-creating) but don't see how to move just a single table. I can't seem
>> >> to
>> >> get the syntax correct.
>> >>
>> >> ALTER TABLE <My Table>
>> >> DROP CONSTRAINT <My Constraint>
>> >> WITH (ONLINE = ON, MOVE TO <My File Group>)
>> >>
>> >> That works, but only if I have something to drop. I don't want to drop
>> >> the
>> >> PK Constraints, I want to move them to one file group and the table
>> >> itself to
>> >> another.
>> >>
>> >> Any suggestions would be appreciated.
>> >> --
>> >> Todd C
>> >
>> >
>>|||Hello Tibor:
OK, I think I got it now. That makes more sense
Thanks
Todd C