Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Setup / November 2005

Tip: Looking for answers? Try searching our database.

Migrating to new hardward

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RossT - 30 Nov 2005 20:28 GMT
We are upgrading our hardware on a SQL Server 2000. We want to put all of our
non-clustered indexes on a separate hard drive and we want to put tempdb on
it's own drive.

Right now all tables and indexes are in Primary. We are setting up the
filegroups for the main database. Do we have to DTS everything to the new
Primary and then move filegroups for the tables and indexes that we want on
different drives? Can we automate changing the filegroups for the
non-clustered indexes with a script or do we have to do this table by table
and index by index?

How do we move TempDB to a different drive? Do we have to install SQL first?
If not, what should we do?

Thanks in advance.
Geoff N. Hiten - 30 Nov 2005 21:01 GMT
One thing at a time.

Moving tempdb:
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

There is a specific section on moving tempdb.  BTW, the newest
recommendation is to have one file per processor in tempdb, even if they are
on the same spindle set.  This is only for tempdb.  I would do this first.

You can move a table from one filegroup to another by dropping and
recreating the clustered index and specifying a filegroup for the new table.
One alternative is to detach and attach as per the above KB article.  If you
have only a very small maintenance window, then you can do a backup and
restore with norecovery to the new locationso you can apply transaction logs
to "catch up" when you switch over.  Simply rename the old and the new
databases after finishing catching up and you are in business.

You will have to recreate each index on each table independently to move
them to the new filegroup.  You can use the INFORMATION_SCHEMA views to
generate lists and with a little string manipulation you can create the
scripts almost automatically.

As always, test everything on a server you can afford to break.

Signature

Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

> We are upgrading our hardware on a SQL Server 2000. We want to put all of
> our
[quoted text clipped - 16 lines]
>
> Thanks in advance.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.