locked
Converting primary key id column from int to bigint in AG VLDB database RRS feed

  • Question

  • Hi 

    I have a VLDB database (3.2 TBs) that is an Availability group that is going to run out of ids soon on a couple of tables. I need to check but I am guessing at least one of these tables is over TB.  I need to convert the int to bigint and the column is not a "clustered" index but it is the primary key.  I still need to check to see if there are foreign keys on the id as well but assume there are. 

    The SQL Server is getting upgraded to 2016 in the next week or two. I've read most recommendations advocate for copying over the data into a new table with bigint as the primary key, etc. and re-applying the indexes and foreign key constraints. 

    Some questions: 

    Is there any scenario that would allow you to keep the database in the AG group? 

    Could you use the "Alter Table" online that is available starting in SQL Server 2016 to change the primary NON-Clustered key from int to bigint?

    If yes, would you need to drop the DB out of the availability group?  What are the ramifications to the db logging and how much would it cause the database mdf/ndfs and ldfs to grow? Can you keep the application online and users connected if you can use this method? Any other considerations? 

    Thanks,

    Sue


    Sue

    Wednesday, August 14, 2019 4:48 AM

All replies

  • Hi OutThere,

     

    >>Could you use the "Alter Table" online that is available starting in SQL Server 2016 to change the primary NON-Clustered key from int to bigint?

     

    Yes,  you could. And you don't need to drop the database out of the availability group. It will be synced to the secondary replica.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, August 15, 2019 7:57 AM
  • Sorry to take so long to get back to the question. I did test converting from int to bigint and I realized I had to drop my primary key but could keep the identity seed. I was wondering what happened while the conversion was taking place if the primary key is dropped. I haven't had time to try to test it. 

    Would the rows keep being inserted with the next available id while the conversion was ongoing? 

    Are there issues or behaviors on my queries for that table that I should be aware of because the primary key has been dropped? 

    Thanks,

    Sue


    Sue

    Sunday, September 29, 2019 8:12 PM