2

I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise edition. I know Standard editions do not support partition functions. Upgrading the version of SQL Server I'm migrating to Enterprise isn't an option.

I'm looking for a way to remove the partition function without causing any negative effects to the database currently so I can migrate it to the 2008 Standard Edition.

Any help would be greatly appreciated. Thanks!

Improve this question
5
  • 3
    Could you tell us why you used partitioning in your current set up? (Maintenance ease only, some sort of performance thing, something else?) Maybe more details on the partitioning scheme could be useful to people familiar with SQL Server to give you better advice.
    – Mat
    Commented Nov 1, 2012 at 15:52
  • Honestly I'm not sure why Partioning was setup. This was done years prior to me being here. From what I can tell it was done to partition out ticketing information and due to the mass amounts of information we have stored over the years. We will be decommissioning the server running SQL2005 ENT in the coming months and moving everything to our clustered environment. This is the one database I haven't been able to move.
    – user1791762
    Commented Nov 1, 2012 at 16:04
  • You need to search for other features as well, not only partitioning. You need to see if in your current setup you use snapshots, online index operations.. Regarding your question, it depends on how much data you're talking about. If you're having 100 mil records per year in this system, I'd rather stay with partitioning, if it helps :-). What size is your data (in millions of records or GB)?
    – Marian
    Commented Nov 1, 2012 at 17:40
  • @Marian - We can't stay with partioning. The server the database lives on currently will be decommissioned in the coming months. This database needs to be migrated to our clustered environment which is running SQL Server 2008 Standard. I need to know what my options are as far as moving this database from the Enterprise 2005 version to the Standard 2008 version. If I could stay with Partioning I would. A quick glance at the table shows around 3 million records starting in 2006 which is about 500,000 per year.
    – user1791762
    Commented Nov 1, 2012 at 18:11
  • 1
    As Mat said, we need more details about how partitioning is being used. Show us table definitions, partition functions, partition schemes, info about filegroups if relevant, etc. It's impossible to give you any kind of answer without this information.
    – Jon Seigel
    Commented Nov 1, 2012 at 22:28

1 Answer 1

Reset to default
1

Well, if you're only concerned with how to get around this partitioning issue you have, and are sure you won't face performance issues after you move to a single partition, then you could try to collapse all of your partitions into 1, and there's lots of ways to do.

If you feel your system can handle it, you could dump all the data into another table and give it a max ID number to dump into. Then when the bulk of the data is copied into there, you could note the ID, find any new records that came in. If it's a huge amount of records, dump those new records in the new table as well. After the 2nd dump you should be almost caught up. At that point you do not allow new data to come in for a few seconds/minutes while you run pre-scripted out commands to:

-move the remaining data into the new table that doesn't have partitioning enabled after making sure the schemas match.

-rename the old table. Make sure you don't have blocking issues.

-rename the new table to the old table.

You might want to avoid adding indexes at the beginning, and do those at the very end.

If doing this on the live system isn't an option, you could move the data to another system and do all these steps there.

Perhaps you could use this opportunity to archive your old data. You could consider creating a view and have your old data in a different archive read only database and have it referenced with a view.

How much data is it anyways, with and without indexes?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.