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 |
|
mbyrd
Starting Member
5 Posts |
 Posted - 2009-06-18 : 08:36:11
|
| I need some advice on how to partition several (but I can do one at a time) large (> 70m rows, > 50gb) tables in a 24x7 environment with minimal down time. Almost all of our tables have a Identity PrimaryKey so I can copy over most of the data before shutting down the production environment to a new partitioned table, apply indexes and constraints, shut down production, copy remaining data over (with identity insert on), rename original table, rename new (partitioned) table to original table name, then bring production back up. Is there a better (faster and safer) way to do this? Obviously I would have this scripted out (and tested in a dev environment) prior to doing so. Our reasons for wanting to partition existing tables on our OLTP server is that we have experienced considerable performance gains on another server database (not 24x7) with partitioning large tables. Any advice would be appreciated. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-06-18 : 11:49:45
|
| the only way is to take a planned downtime... >>>Almost all of our tables have a Identity PrimaryKey so I can copy over most of the data before shutting down the production environment to a new partitioned table, apply indexes and constraints, shut down production, copy remaining data over (with identity insert on), rename original table, rename new (partitioned) table to original table name, then bring production back up. In your process above, you are assuming the existing data doesnt change and only new rows are added?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
|
|
|
|
|
How to partition large tables in 24x7 environment