Process to change collation on a database












2















I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.



So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:



Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
foreach-object
{ Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }


Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).



Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.










share|improve this question





























    2















    I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.



    So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:



    Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
    foreach-object
    { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }


    Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).



    Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.










    share|improve this question



























      2












      2








      2








      I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.



      So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:



      Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
      foreach-object
      { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }


      Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).



      Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.










      share|improve this question
















      I've been asked to change the collation on a database that has been in production for a few years as it was not set correctly at the time of deployment.



      So my research shows that in order change collation on all of the columns you have to drop constraints and what-not, and it looked to get ugly quickly. So I thought about just scripting out all objects, find/replace all references to the old collation, create the new database, and then use dbatools to copy the data:



      Get-DbaDbTable -SqlInstance SourceServer -Database SourceDb |
      foreach-object
      { Copy-DbaDbTableData -DestinationDatabase TargetDb -DestinationTable $_.Name }


      Is this a valid approach to changing collation on everything? At first blush it seems to have worked out rather swimmingly (i.e., got through the migration of data with no errors).



      Going from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP437_CI_AI if that matters.







      sql-server sql-server-2014 collation dbatools






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 1 hour ago







      Tony Hinkle

















      asked 1 hour ago









      Tony HinkleTony Hinkle

      2,8801523




      2,8801523






















          1 Answer
          1






          active

          oldest

          votes


















          4














          Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation



          Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:



          Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?



          Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.



          Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:



          Collation error in Jira when the database and server/default collation do not match in SQL Server



          Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:




          The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.




          whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).



          My advice: don't change anything! You even said:




          a database that has been in production for a few years




          If it has been in production for years without running into problems, then there are likely no problems :-)






          share|improve this answer





















          • 1





            Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

            – Tony Hinkle
            1 hour ago






          • 2





            @TonyHinkle please see my updated notes (bottom half of the answer)

            – Solomon Rutzky
            1 hour ago








          • 1





            --what collation do you dream in? :)

            – Tony Hinkle
            37 mins ago








          • 1





            Solomon dreams in every collation simultaneously.

            – Max Vernon
            29 mins ago











          Your Answer








          StackExchange.ready(function() {
          var channelOptions = {
          tags: "".split(" "),
          id: "182"
          };
          initTagRenderer("".split(" "), "".split(" "), channelOptions);

          StackExchange.using("externalEditor", function() {
          // Have to fire editor after snippets, if snippets enabled
          if (StackExchange.settings.snippets.snippetsEnabled) {
          StackExchange.using("snippets", function() {
          createEditor();
          });
          }
          else {
          createEditor();
          }
          });

          function createEditor() {
          StackExchange.prepareEditor({
          heartbeatType: 'answer',
          autoActivateHeartbeat: false,
          convertImagesToLinks: false,
          noModals: true,
          showLowRepImageUploadWarning: true,
          reputationToPostImages: null,
          bindNavPrevention: true,
          postfix: "",
          imageUploader: {
          brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
          contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
          allowUrls: true
          },
          onDemand: true,
          discardSelector: ".discard-answer"
          ,immediatelyShowMarkdownHelp:true
          });


          }
          });














          draft saved

          draft discarded


















          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231087%2fprocess-to-change-collation-on-a-database%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown

























          1 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          4














          Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation



          Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:



          Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?



          Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.



          Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:



          Collation error in Jira when the database and server/default collation do not match in SQL Server



          Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:




          The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.




          whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).



          My advice: don't change anything! You even said:




          a database that has been in production for a few years




          If it has been in production for years without running into problems, then there are likely no problems :-)






          share|improve this answer





















          • 1





            Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

            – Tony Hinkle
            1 hour ago






          • 2





            @TonyHinkle please see my updated notes (bottom half of the answer)

            – Solomon Rutzky
            1 hour ago








          • 1





            --what collation do you dream in? :)

            – Tony Hinkle
            37 mins ago








          • 1





            Solomon dreams in every collation simultaneously.

            – Max Vernon
            29 mins ago
















          4














          Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation



          Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:



          Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?



          Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.



          Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:



          Collation error in Jira when the database and server/default collation do not match in SQL Server



          Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:




          The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.




          whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).



          My advice: don't change anything! You even said:




          a database that has been in production for a few years




          If it has been in production for years without running into problems, then there are likely no problems :-)






          share|improve this answer





















          • 1





            Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

            – Tony Hinkle
            1 hour ago






          • 2





            @TonyHinkle please see my updated notes (bottom half of the answer)

            – Solomon Rutzky
            1 hour ago








          • 1





            --what collation do you dream in? :)

            – Tony Hinkle
            37 mins ago








          • 1





            Solomon dreams in every collation simultaneously.

            – Max Vernon
            29 mins ago














          4












          4








          4







          Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation



          Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:



          Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?



          Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.



          Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:



          Collation error in Jira when the database and server/default collation do not match in SQL Server



          Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:




          The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.




          whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).



          My advice: don't change anything! You even said:




          a database that has been in production for a few years




          If it has been in production for years without running into problems, then there are likely no problems :-)






          share|improve this answer















          Well, it could be very simple, or it could have lots of complications. There are quite a few restrictions in place that will prevent changing a database's default collation



          Please see the following post of mine that goes into great detail regarding what each level of collation affects, and what you might run into and need to look out for, and what to do about those things:



          Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?



          Your approach could be completely valid. If it seems to work, then possibly it does. Just go through each of the points in my post to make sure that you have looked in all of the places that need to be checked. For example, are you just changing the collation of a single database? If so, what is the instance-level collation? It is one of the two that you have mentioned? There could be some odd behavior if the database-level and instance-level collations don't match, but not necessarily.



          Just out of curiosity: why are you wanting to use SQL_Latin1_General_CP437_CI_AI. Seems like a potentially odd choice. Does the app that uses that DB really need code page 437? Is this, by any chance, for JIRA? I only ask because that is one of the two "supported" collations for JIRA, and it's not all that often that someone is wanting to use that particular collation. If this is the case, then you likely don't need to be making this change. Please see my recommendation to Atlassian here regarding this issue:



          Collation error in Jira when the database and server/default collation do not match in SQL Server



          Long support ticket short: Atlassian did themselves and their customers a huge disservice by coming up with "supported collations" in the first place. Thus far, no proof has been given for why these two were chosen, and why others are not "supported". I am 99.99999% sure that this is merely an issue of their developers not understanding collations (or SQL Server collations), which is nothing against their developers as Jira is kinda awesome, it's just that most folks don't understand this stuff. My recommendation to them is that they support the following:




          The database in which Jira is installed must have a case-insensitive collation that is the same as the instance-level collation.




          whether or not the collation is accent-insensitive is up to you and how you want sorting and comparisons to be done. In the end, just make sure that the instance and DB have the same collation. (if you were about to do all of this work only to change the DB to a collation that they "support" yet is different than the instance-level collation, then you would have broken Jira by trying to comply with their (most likely unnecessary) stated requirement ;-).



          My advice: don't change anything! You even said:




          a database that has been in production for a few years




          If it has been in production for years without running into problems, then there are likely no problems :-)







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited 58 mins ago

























          answered 1 hour ago









          Solomon RutzkySolomon Rutzky

          48.8k581177




          48.8k581177








          • 1





            Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

            – Tony Hinkle
            1 hour ago






          • 2





            @TonyHinkle please see my updated notes (bottom half of the answer)

            – Solomon Rutzky
            1 hour ago








          • 1





            --what collation do you dream in? :)

            – Tony Hinkle
            37 mins ago








          • 1





            Solomon dreams in every collation simultaneously.

            – Max Vernon
            29 mins ago














          • 1





            Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

            – Tony Hinkle
            1 hour ago






          • 2





            @TonyHinkle please see my updated notes (bottom half of the answer)

            – Solomon Rutzky
            1 hour ago








          • 1





            --what collation do you dream in? :)

            – Tony Hinkle
            37 mins ago








          • 1





            Solomon dreams in every collation simultaneously.

            – Max Vernon
            29 mins ago








          1




          1





          Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

          – Tony Hinkle
          1 hour ago





          Yes, it's Jira. I agree we probably don't need to change it, but I don't know enough about Jira or codepages to be convincing. Very grateful for you input--thanks!

          – Tony Hinkle
          1 hour ago




          2




          2





          @TonyHinkle please see my updated notes (bottom half of the answer)

          – Solomon Rutzky
          1 hour ago







          @TonyHinkle please see my updated notes (bottom half of the answer)

          – Solomon Rutzky
          1 hour ago






          1




          1





          --what collation do you dream in? :)

          – Tony Hinkle
          37 mins ago







          --what collation do you dream in? :)

          – Tony Hinkle
          37 mins ago






          1




          1





          Solomon dreams in every collation simultaneously.

          – Max Vernon
          29 mins ago





          Solomon dreams in every collation simultaneously.

          – Max Vernon
          29 mins ago


















          draft saved

          draft discarded




















































          Thanks for contributing an answer to Database Administrators Stack Exchange!


          • Please be sure to answer the question. Provide details and share your research!

          But avoid



          • Asking for help, clarification, or responding to other answers.

          • Making statements based on opinion; back them up with references or personal experience.


          To learn more, see our tips on writing great answers.




          draft saved


          draft discarded














          StackExchange.ready(
          function () {
          StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f231087%2fprocess-to-change-collation-on-a-database%23new-answer', 'question_page');
          }
          );

          Post as a guest















          Required, but never shown





















































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown

































          Required, but never shown














          Required, but never shown












          Required, but never shown







          Required, but never shown







          Popular posts from this blog

          How to label and detect the document text images

          Vallis Paradisi

          Tabula Rosettana