Post reply

Splitting data of MDF file

  • November 13, 2015 at 4:37 am

    #302296

    Hi Experts,

    Need your inputs on below request. Looking for suggestions/scripts on achieving it.

    We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.

    Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can we go about splitting the data from 1 MDF files into multiple data files?

    How about moving indexes ? Any suggestions and guidelines will be a great help.

    SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) - Enterprise Edition (64-bit)

    Thanks in Advance.

    - Sam

  • November 13, 2015 at 5:00 am

    #1839585

    Sam

    Probably not all that easy. Please see this blog.

    John

  • November 13, 2015 at 9:36 am

    #1839647

    Thanks for the sincere response.

    1. Firstly, what all are the possible ways to fix this ?

    2. Secondly, is there any demo scripts available on sample database to get started ?

    -Sam

  • November 13, 2015 at 11:03 pm

    #1839788

    Can anybody help?

  • November 14, 2015 at 9:43 am

    #1839813

    vsamantha35 (11/13/2015)

    Can anybody help?

    To be honest, my opinion is that the drive assignments are flawed especially in light of the fact that you already know that a 10TB drive can be setup.

    That, notwithstanding, this isn't going to be easy and, if you haven't already done so, you need to read the article at the link that John Mitchell posted above.

    If I were compelled to do something like this, my first step would be to identify the largest tables in the database, what they're used for, how much they're expected to grow, and take care of those first. Yes, a filegroup with multiple files or multiple filegroups is going to be necessary. It depends on the type of table, size, growth expectations, etc, etc.

    While doing this, I'd also be looking carefully at my DR situation. For example, if all hell breaks loose and your only remaining recovery option is to restore the database, how long will it take you to "get back in business"? There are tables like audit tables and closed invoice tables that just don't need to be restored at the same time as the rest of the system AND those tables are typically the largest tables in the database. Consider putting those on separate "drives" and in separate files so that you can restore what's critical to getting "back in business" and then restoring the rest.

    It's also a prime opportunity to evaluate and reduce index maintenance and backup times. For example, audit and closed invoice tables are usually temporal in nature and once the rows are written to an audit table or an invoice is closed, the rows are NEVER updated. There's no reason why those rows ever have to be maintained again and no reason why they ever need to be backed up more than once or twice. They can be partitioned by month and the older partitions that contain data that will never again be changed can be reindexed a final time (being careful to NOT create a bunch of free-space in the partition... it does take a trick or two to pull that off) with a FILL FACTOR of 100, set to READ_ONLY, backed up a final time (perhaps a second final time so that you have two copies, just in case), and never have to suffer either index maintenance or backups ever again.

    And, yeah, I can vouch for all of that working. We had a 360GB table that was taking 6-10 hours to backup (the rest of the database was only 1GB) because the backups were going to NAS rather than to the SAN (and still is... it's actually a good idea because you don't want to store the backups on the same device as the MDF/LDF files). I reduced that to 6-10 minutes using the methods above because I partitioned the table with one filegroup/file per month, set all the older immutable file groups to "Read_Only" and now only have to backup the current month. That also means that a panic "get back in business" restore would also take something less than 10 minutes and that's a real "Martha Stewart moment" because that database IS the phone system.

    I still think that someone has made a rather uniformed decision to split your database logical drives into 1TB segments instead of a single logical drive of 10TB but it may be a blessing in disguise because it forces the opportunity to make other improvements, like I suggest above, that should have been done long ago. If you're lucky, you might find that some of those improvements already exist and you just need to be careful not to break them during the move.

    Again... this isn't a cake walk even if you forgo the recommended improvements. If you need it in a hurry and based on your questions, you don't have the time to figure it out on your own. You may want to consider hiring a good consultant that you can confirm has done this type of thing before...

    ... or the folks that laid out the drives in 1TB allotments could realize the mess they've made and go back to a single logical drive of 10TB and be done in a day. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 15, 2015 at 11:35 am

    #1839852

    Thanks Jeff for your inputs. Indeed makes a lot of sense!

  • November 16, 2015 at 12:38 pm

    #1840096

    Try this:

    1. Create Several file groups and files on the new server

    USE [master]

    GO

    /****** Object: Database [My_DB] Script Date: 11/16/2015 14:35:10 ******/

    CREATE DATABASE [My_DB] ON PRIMARY

    ( NAME = N'My_DB', FILENAME = N'X:\SQLData\My_DB.mdf' , SIZE = 3072KB , MAXSIZE = 1024000KB , FILEGROWTH = 1024KB ),

    FILEGROUP [Drive_W]

    ( NAME = N'My_DB_4', FILENAME = N'C:\My_DB_4.ndf' , SIZE = 3072KB , MAXSIZE = 1024000KB , FILEGROWTH = 1024KB ),

    FILEGROUP [Drive_Y]

    ( NAME = N'My_DB_2', FILENAME = N'V:\My_DB_2.ndf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

    FILEGROUP [Drive_Z]

    ( NAME = N'My_DB_3', FILENAME = N'D:\My_DB_3.ndf' , SIZE = 3072KB , MAXSIZE = 1024000KB , FILEGROWTH = 1024KB )

    LOG ON

    ( NAME = N'My_DB_log', FILENAME = N'W:\My_DB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

    GO

    EXEC dbo.sp_dbcmptlevel @dbname=N'My_DB', @new_cmptlevel=90

    GO

    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

    begin

    EXEC [My_DB].[dbo].[sp_fulltext_database] @action = 'disable'

    end

    GO

    2. Create tables on each drive

    CREATE TABLE [dbo].[acr_myTable](

    [Id] [bigint] NOT NULL,

    [label] [nvarchar](max) NOT NULL

    ) ON [Drive_Z]

    --....... DRIVE_C, DRIVE_D and etc.

    3. Use Export Wizard to export data..

    4. Check you data allocation by scripting your database

    Good Luck!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply