SQL Server Performance

Strategy to partition prod data using partitioned table with minimal downtime

Discussion in 'ALL SQL SERVER QUESTIONS' started by Thanh Nguyen, Sep 20, 2012.

  1. Thanh Nguyen New Member

    Hi Experts,

    I got a 24x7 prod database with size 2TB running on sql 2012 enterprise failover cluster. This database has couple hundreds of tables, but I only want to partition the data for a dozen of them only. The total size for table partitioning is aprox 1TB. One of the table is 800GB in size. What i want to do is to partition the data by customerID and store it in its own separate filegroup. Now comes to the hard part: How I'm gonna do it? My initial thougt is:
    - lock down the database (e.g. put db in single-user/restricted mode)
    - bcp out data for those selected tables
    - drop FK for those selected tables
    - drop selected tables
    - create new filegroups
    - create partition function/partition scheme
    - Re-create selected partitioned tables structure using newly created partition scheme
    - bcp in the data for selected partitioned tables
    - re-create FK

    Am I missing anything out? Is there a better way to proceed this? My main concern is downtime. I'd like to minimize it a much as possible since this is prod database

    Any advise would be appreciated.
  2. Shehap MVP, MCTS, MCITP SQL Server

    Yes, sure you can re-partition your table with a different schema partitioning with Non-appreciable impacts that can be transparent for end users with no longer need for any down time even for huge BLOB tables as follows:

    · You can create another table with the same schema design of the original table except non clustered indexes but on the schema partitioning configured along with the appropriate file groups

    · Then you can Import the data of the original table to the new table through any means such as DTS or SSIS package

    · Once you are planning to switch between the 2 tables, you have to be get confident of both tables are identical through a simple DWH to scan all mismatches between the 2 tables

    · Then you can create a simple DWH using Merge Commands of SQL Server 2008 to reflect all new inserted records , updated records or deleted records from the original non partitioned table to the new partitioned table

    To build this DWH, you can read more at my blog below

    You have to take care much of Storage aligned feature ( Both non-clustered indexes are aligned on the same partitioning schema of table ( clustered index)) as it might be loose the value of your indexes as perhaps might Query Analyzer becomes not able to select these indexes till removing the partitioning schema

    Kindly work out it and let me know your feedback
  3. Thanh Nguyen New Member

    Thanks for your advise, Shehap.

    Just like to clarify the steps that you mentioned:
    - create another table
    - switch between old and new table

    Did you mean something like below?
    - create new partitioned table dbo.Customer_new
    - rename old non-partitioned table from dbo.Customer to dbo.Customer_old
    - rename new partitioned table dbo.Customer_new to dbo.Customer
  4. Shehap MVP, MCTS, MCITP SQL Server

    Yes, right but after making sure of both tables are identical using that simple DWH solution mentioned above …

    If you need more help regarding this aspect, you can post me a sample of your table design and then I am going to feedback you by the appropriate T-SQL script for that DWH solution
  5. Thanh Nguyen New Member

    Thank you so much, Shehap. That's very kind of you.
    This project is still in planning phase. Let me sketch out all neccessary scripts and will have you take a look.
    Thanks again.

Share This Page