Full Migration 2008R2 -> 2016












0















We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances.
We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard default instance and two named instances. If I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability. What I need to know is:




  1. How to migrate the user records with their passwords

  2. How to migrate the Linked Servers with their passwords


I have read posts similar to SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me but find no info' specific to Users and/or Linked Servers










share|improve this question





























    0















    We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances.
    We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard default instance and two named instances. If I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability. What I need to know is:




    1. How to migrate the user records with their passwords

    2. How to migrate the Linked Servers with their passwords


    I have read posts similar to SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me but find no info' specific to Users and/or Linked Servers










    share|improve this question



























      0












      0








      0








      We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances.
      We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard default instance and two named instances. If I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability. What I need to know is:




      1. How to migrate the user records with their passwords

      2. How to migrate the Linked Servers with their passwords


      I have read posts similar to SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me but find no info' specific to Users and/or Linked Servers










      share|improve this question
















      We currently have Windows Server 2008 with MS/SQL 2008R2: the default instance and two named instances.
      We are spinning up a Windows 2016 Server onto which we will install MS/SQL Server 2016 Standard default instance and two named instances. If I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability. What I need to know is:




      1. How to migrate the user records with their passwords

      2. How to migrate the Linked Servers with their passwords


      I have read posts similar to SQL Server Upgrade 2008 -> 2016 / 100 to 130 compatibility slow queries, new CE killing me but find no info' specific to Users and/or Linked Servers







      sql-server sql-server-2008-r2 sql-server-2016 migration






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 5 mins ago









      Paul White

      51.3k14278450




      51.3k14278450










      asked Jan 4 at 19:49









      Swamp_ThingSwamp_Thing

      1




      1






















          2 Answers
          2






          active

          oldest

          votes


















          1















          f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.




          No. Dont do detach/attach. Instead use backup/restore.



          If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.



          I have detailed about pre and post steps for migration.




          How to migrate the user records with their passwords




          Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.




          How to migrate the Linked Servers with their passwords




          use Copy-DbaLinkedServer






          share|improve this answer































            1














            Please do not copy datafiles from one instance to another. If there are any problems, it likely will not be supported by Microsoft.



            There are several ways to safely migrate from one Server to another, though my preference is using the Data Migration Assistant. For now, lets talk about your steps:



            1) Always Collect all user DDL scripts including:




            • SQL Logins via Microsoft's sp_help_revlogin

            • SQL Users. Try this one: Database Users and their Roles


            There are many scripts out there, this version is pretty dependable and runs in the context of one database



            /*
            This script will script the role members for all roles on the database.
            This is useful for scripting permissions in a development environment before refreshing development with a copy of production. This will allow us to easily ensure development permissions are not lost during a prod to dev restoration.
            */

            /*********************************************/
            /********* DB CONTEXT STATEMENT *********/
            /*********************************************/
            SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
            1 AS [-- RESULT ORDER HOLDER --]
            UNION
            SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
            1 AS [-- RESULT ORDER HOLDER --]

            UNION

            SELECT '' AS [-- SQL STATEMENTS --],
            2 AS [-- RESULT ORDER HOLDER --]

            UNION


            /*********************************************/
            /********* DB ROLE PERMISSIONS *********/
            /*********************************************/
            SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
            3 AS [-- RESULT ORDER HOLDER --]
            UNION
            SELECT 'EXEC sp_addrolemember @rolename ='
            + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
            3 AS [-- RESULT ORDER HOLDER --]
            FROM sys.database_role_members AS rm
            WHERE USER_NAME(rm.member_principal_id) IN (
            --get user names on the database
            SELECT [name]
            FROM sys.database_principals
            WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
            and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
            )
            --ORDER BY rm.role_principal_id ASC


            UNION

            SELECT '' AS [-- SQL STATEMENTS --],
            4 AS [-- RESULT ORDER HOLDER --]

            UNION

            /*********************************************/
            /********* OBJECT LEVEL PERMISSIONS *********/
            /*********************************************/
            SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
            5 AS [-- RESULT ORDER HOLDER --]
            UNION
            SELECT CASE
            WHEN perm.state <> 'W' THEN perm.state_desc
            ELSE 'GRANT'
            END
            + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
            + CASE
            WHEN cl.column_id IS NULL THEN SPACE(0)
            ELSE '(' + QUOTENAME(cl.name) + ')'
            END
            + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
            + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
            END
            AS [-- SQL STATEMENTS --],
            5 AS [-- RESULT ORDER HOLDER --]
            FROM
            sys.database_permissions AS perm
            INNER JOIN
            sys.objects AS obj
            ON perm.major_id = obj.[object_id]
            INNER JOIN
            sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
            LEFT JOIN
            sys.columns AS cl
            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
            --WHERE usr.name = @OldUser
            --ORDER BY perm.permission_name ASC, perm.state_desc ASC



            UNION

            SELECT '' AS [-- SQL STATEMENTS --],
            6 AS [-- RESULT ORDER HOLDER --]

            UNION

            /*********************************************/
            /********* DB LEVEL PERMISSIONS *********/
            /*********************************************/
            SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
            7 AS [-- RESULT ORDER HOLDER --]
            UNION
            SELECT CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
            + SPACE(1) + perm.permission_name --CONNECT, etc
            + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
            + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
            END
            AS [-- SQL STATEMENTS --],
            7 AS [-- RESULT ORDER HOLDER --]
            FROM sys.database_permissions AS perm
            INNER JOIN
            sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
            --WHERE usr.name = @OldUser

            WHERE [perm].[major_id] = 0
            AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
            AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

            ORDER BY [-- RESULT ORDER HOLDER --]



            • Any SQL Jobs

            • Replication

            • Any other external files necessary for the new instance


            2) Pre-Plan Storage Requirements




            • Sometimes Migration is the perfect time to following Microsoft's Top 10 Storage Best Practices including:

            • Separating tempdb from the rest of the instance and also your system datafiles from the user databases

            • Address any special databases into its own disks, settings for optimal performance.


            3) TEST, TEST, TEST!!



            Always Test your migration. Create a Backup and restore this onto your new server so you verify




            • a) the storage fits

            • b) the time it take to restore

            • c) pre-test connectivity to be free from Network, Firewall, or Permissions issues (including SPNs, DNS A-records, etc)


            4) Migrate (using one of three methods)



            A) Use a Local SQL Backup.




            • Create a local backup from your source instance to the new instance. Restores from local backup sets are usually much faster than network.


            B) Use a Backup Tool like Veritas




            • Basically, this is a network restore so expect it to be slower than local backups. Also, verify this works before going for a live migration.


            C) Use the Data Migration Assistant from Microsoft



            This is my personal favorite, since this tool is a all-in-one solution for the day of Migration.




            • Finds any breaking, deprecated, changed changes and behaviors prior to migrating to a newer version of SQL Server

            • Discover new features and recommendations! DMA is fully comprehensive in its recommendations and fully supports Azure!

            • Uses Windows Authentication, which simplifies the permissions necessary for migration.


            • BEST PART Seamlessly transfer SQL Logins and Users to the new instance so your application can connect after migration of data is complete!


            I have successfully migrated several large instances, including one with 100+ databases ranging from small to 1TB+, from 2008R2 to 2016. Without comparison, the DMA performed faster and more consistently than any other method. And unlike other tools, you can graphically move your databases to whatever customization you want. Very cool and highly recommended.






            share|improve this answer

























              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%2f226368%2ffull-migration-2008r2-2016%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              1















              f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.




              No. Dont do detach/attach. Instead use backup/restore.



              If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.



              I have detailed about pre and post steps for migration.




              How to migrate the user records with their passwords




              Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.




              How to migrate the Linked Servers with their passwords




              use Copy-DbaLinkedServer






              share|improve this answer




























                1















                f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.




                No. Dont do detach/attach. Instead use backup/restore.



                If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.



                I have detailed about pre and post steps for migration.




                How to migrate the user records with their passwords




                Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.




                How to migrate the Linked Servers with their passwords




                use Copy-DbaLinkedServer






                share|improve this answer


























                  1












                  1








                  1








                  f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.




                  No. Dont do detach/attach. Instead use backup/restore.



                  If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.



                  I have detailed about pre and post steps for migration.




                  How to migrate the user records with their passwords




                  Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.




                  How to migrate the Linked Servers with their passwords




                  use Copy-DbaLinkedServer






                  share|improve this answer














                  f I understand what I've read so far I can detach the databases from 2008R2, copy the files to the new 2016 environment and attach to them... enable query store and up the comparability.




                  No. Dont do detach/attach. Instead use backup/restore.



                  If you have a very tight migration window, setup logshipping or Mirroring and just do a cutover to new instance.



                  I have detailed about pre and post steps for migration.




                  How to migrate the user records with their passwords




                  Use dbatools to help you out in migration. For logins, just use Copy-DbaLogin.




                  How to migrate the Linked Servers with their passwords




                  use Copy-DbaLinkedServer







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Jan 4 at 22:02









                  KinKin

                  53.6k481189




                  53.6k481189

























                      1














                      Please do not copy datafiles from one instance to another. If there are any problems, it likely will not be supported by Microsoft.



                      There are several ways to safely migrate from one Server to another, though my preference is using the Data Migration Assistant. For now, lets talk about your steps:



                      1) Always Collect all user DDL scripts including:




                      • SQL Logins via Microsoft's sp_help_revlogin

                      • SQL Users. Try this one: Database Users and their Roles


                      There are many scripts out there, this version is pretty dependable and runs in the context of one database



                      /*
                      This script will script the role members for all roles on the database.
                      This is useful for scripting permissions in a development environment before refreshing development with a copy of production. This will allow us to easily ensure development permissions are not lost during a prod to dev restoration.
                      */

                      /*********************************************/
                      /********* DB CONTEXT STATEMENT *********/
                      /*********************************************/
                      SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
                      1 AS [-- RESULT ORDER HOLDER --]
                      UNION
                      SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
                      1 AS [-- RESULT ORDER HOLDER --]

                      UNION

                      SELECT '' AS [-- SQL STATEMENTS --],
                      2 AS [-- RESULT ORDER HOLDER --]

                      UNION


                      /*********************************************/
                      /********* DB ROLE PERMISSIONS *********/
                      /*********************************************/
                      SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
                      3 AS [-- RESULT ORDER HOLDER --]
                      UNION
                      SELECT 'EXEC sp_addrolemember @rolename ='
                      + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
                      3 AS [-- RESULT ORDER HOLDER --]
                      FROM sys.database_role_members AS rm
                      WHERE USER_NAME(rm.member_principal_id) IN (
                      --get user names on the database
                      SELECT [name]
                      FROM sys.database_principals
                      WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                      and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                      )
                      --ORDER BY rm.role_principal_id ASC


                      UNION

                      SELECT '' AS [-- SQL STATEMENTS --],
                      4 AS [-- RESULT ORDER HOLDER --]

                      UNION

                      /*********************************************/
                      /********* OBJECT LEVEL PERMISSIONS *********/
                      /*********************************************/
                      SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                      5 AS [-- RESULT ORDER HOLDER --]
                      UNION
                      SELECT CASE
                      WHEN perm.state <> 'W' THEN perm.state_desc
                      ELSE 'GRANT'
                      END
                      + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
                      + CASE
                      WHEN cl.column_id IS NULL THEN SPACE(0)
                      ELSE '(' + QUOTENAME(cl.name) + ')'
                      END
                      + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
                      + CASE
                      WHEN perm.state <> 'W' THEN SPACE(0)
                      ELSE SPACE(1) + 'WITH GRANT OPTION'
                      END
                      AS [-- SQL STATEMENTS --],
                      5 AS [-- RESULT ORDER HOLDER --]
                      FROM
                      sys.database_permissions AS perm
                      INNER JOIN
                      sys.objects AS obj
                      ON perm.major_id = obj.[object_id]
                      INNER JOIN
                      sys.database_principals AS usr
                      ON perm.grantee_principal_id = usr.principal_id
                      LEFT JOIN
                      sys.columns AS cl
                      ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
                      --WHERE usr.name = @OldUser
                      --ORDER BY perm.permission_name ASC, perm.state_desc ASC



                      UNION

                      SELECT '' AS [-- SQL STATEMENTS --],
                      6 AS [-- RESULT ORDER HOLDER --]

                      UNION

                      /*********************************************/
                      /********* DB LEVEL PERMISSIONS *********/
                      /*********************************************/
                      SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                      7 AS [-- RESULT ORDER HOLDER --]
                      UNION
                      SELECT CASE
                      WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
                      ELSE 'GRANT'
                      END
                      + SPACE(1) + perm.permission_name --CONNECT, etc
                      + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
                      + CASE
                      WHEN perm.state <> 'W' THEN SPACE(0)
                      ELSE SPACE(1) + 'WITH GRANT OPTION'
                      END
                      AS [-- SQL STATEMENTS --],
                      7 AS [-- RESULT ORDER HOLDER --]
                      FROM sys.database_permissions AS perm
                      INNER JOIN
                      sys.database_principals AS usr
                      ON perm.grantee_principal_id = usr.principal_id
                      --WHERE usr.name = @OldUser

                      WHERE [perm].[major_id] = 0
                      AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
                      AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                      ORDER BY [-- RESULT ORDER HOLDER --]



                      • Any SQL Jobs

                      • Replication

                      • Any other external files necessary for the new instance


                      2) Pre-Plan Storage Requirements




                      • Sometimes Migration is the perfect time to following Microsoft's Top 10 Storage Best Practices including:

                      • Separating tempdb from the rest of the instance and also your system datafiles from the user databases

                      • Address any special databases into its own disks, settings for optimal performance.


                      3) TEST, TEST, TEST!!



                      Always Test your migration. Create a Backup and restore this onto your new server so you verify




                      • a) the storage fits

                      • b) the time it take to restore

                      • c) pre-test connectivity to be free from Network, Firewall, or Permissions issues (including SPNs, DNS A-records, etc)


                      4) Migrate (using one of three methods)



                      A) Use a Local SQL Backup.




                      • Create a local backup from your source instance to the new instance. Restores from local backup sets are usually much faster than network.


                      B) Use a Backup Tool like Veritas




                      • Basically, this is a network restore so expect it to be slower than local backups. Also, verify this works before going for a live migration.


                      C) Use the Data Migration Assistant from Microsoft



                      This is my personal favorite, since this tool is a all-in-one solution for the day of Migration.




                      • Finds any breaking, deprecated, changed changes and behaviors prior to migrating to a newer version of SQL Server

                      • Discover new features and recommendations! DMA is fully comprehensive in its recommendations and fully supports Azure!

                      • Uses Windows Authentication, which simplifies the permissions necessary for migration.


                      • BEST PART Seamlessly transfer SQL Logins and Users to the new instance so your application can connect after migration of data is complete!


                      I have successfully migrated several large instances, including one with 100+ databases ranging from small to 1TB+, from 2008R2 to 2016. Without comparison, the DMA performed faster and more consistently than any other method. And unlike other tools, you can graphically move your databases to whatever customization you want. Very cool and highly recommended.






                      share|improve this answer






























                        1














                        Please do not copy datafiles from one instance to another. If there are any problems, it likely will not be supported by Microsoft.



                        There are several ways to safely migrate from one Server to another, though my preference is using the Data Migration Assistant. For now, lets talk about your steps:



                        1) Always Collect all user DDL scripts including:




                        • SQL Logins via Microsoft's sp_help_revlogin

                        • SQL Users. Try this one: Database Users and their Roles


                        There are many scripts out there, this version is pretty dependable and runs in the context of one database



                        /*
                        This script will script the role members for all roles on the database.
                        This is useful for scripting permissions in a development environment before refreshing development with a copy of production. This will allow us to easily ensure development permissions are not lost during a prod to dev restoration.
                        */

                        /*********************************************/
                        /********* DB CONTEXT STATEMENT *********/
                        /*********************************************/
                        SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
                        1 AS [-- RESULT ORDER HOLDER --]
                        UNION
                        SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
                        1 AS [-- RESULT ORDER HOLDER --]

                        UNION

                        SELECT '' AS [-- SQL STATEMENTS --],
                        2 AS [-- RESULT ORDER HOLDER --]

                        UNION


                        /*********************************************/
                        /********* DB ROLE PERMISSIONS *********/
                        /*********************************************/
                        SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
                        3 AS [-- RESULT ORDER HOLDER --]
                        UNION
                        SELECT 'EXEC sp_addrolemember @rolename ='
                        + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
                        3 AS [-- RESULT ORDER HOLDER --]
                        FROM sys.database_role_members AS rm
                        WHERE USER_NAME(rm.member_principal_id) IN (
                        --get user names on the database
                        SELECT [name]
                        FROM sys.database_principals
                        WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                        and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                        )
                        --ORDER BY rm.role_principal_id ASC


                        UNION

                        SELECT '' AS [-- SQL STATEMENTS --],
                        4 AS [-- RESULT ORDER HOLDER --]

                        UNION

                        /*********************************************/
                        /********* OBJECT LEVEL PERMISSIONS *********/
                        /*********************************************/
                        SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                        5 AS [-- RESULT ORDER HOLDER --]
                        UNION
                        SELECT CASE
                        WHEN perm.state <> 'W' THEN perm.state_desc
                        ELSE 'GRANT'
                        END
                        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
                        + CASE
                        WHEN cl.column_id IS NULL THEN SPACE(0)
                        ELSE '(' + QUOTENAME(cl.name) + ')'
                        END
                        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
                        + CASE
                        WHEN perm.state <> 'W' THEN SPACE(0)
                        ELSE SPACE(1) + 'WITH GRANT OPTION'
                        END
                        AS [-- SQL STATEMENTS --],
                        5 AS [-- RESULT ORDER HOLDER --]
                        FROM
                        sys.database_permissions AS perm
                        INNER JOIN
                        sys.objects AS obj
                        ON perm.major_id = obj.[object_id]
                        INNER JOIN
                        sys.database_principals AS usr
                        ON perm.grantee_principal_id = usr.principal_id
                        LEFT JOIN
                        sys.columns AS cl
                        ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
                        --WHERE usr.name = @OldUser
                        --ORDER BY perm.permission_name ASC, perm.state_desc ASC



                        UNION

                        SELECT '' AS [-- SQL STATEMENTS --],
                        6 AS [-- RESULT ORDER HOLDER --]

                        UNION

                        /*********************************************/
                        /********* DB LEVEL PERMISSIONS *********/
                        /*********************************************/
                        SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                        7 AS [-- RESULT ORDER HOLDER --]
                        UNION
                        SELECT CASE
                        WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
                        ELSE 'GRANT'
                        END
                        + SPACE(1) + perm.permission_name --CONNECT, etc
                        + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
                        + CASE
                        WHEN perm.state <> 'W' THEN SPACE(0)
                        ELSE SPACE(1) + 'WITH GRANT OPTION'
                        END
                        AS [-- SQL STATEMENTS --],
                        7 AS [-- RESULT ORDER HOLDER --]
                        FROM sys.database_permissions AS perm
                        INNER JOIN
                        sys.database_principals AS usr
                        ON perm.grantee_principal_id = usr.principal_id
                        --WHERE usr.name = @OldUser

                        WHERE [perm].[major_id] = 0
                        AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
                        AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                        ORDER BY [-- RESULT ORDER HOLDER --]



                        • Any SQL Jobs

                        • Replication

                        • Any other external files necessary for the new instance


                        2) Pre-Plan Storage Requirements




                        • Sometimes Migration is the perfect time to following Microsoft's Top 10 Storage Best Practices including:

                        • Separating tempdb from the rest of the instance and also your system datafiles from the user databases

                        • Address any special databases into its own disks, settings for optimal performance.


                        3) TEST, TEST, TEST!!



                        Always Test your migration. Create a Backup and restore this onto your new server so you verify




                        • a) the storage fits

                        • b) the time it take to restore

                        • c) pre-test connectivity to be free from Network, Firewall, or Permissions issues (including SPNs, DNS A-records, etc)


                        4) Migrate (using one of three methods)



                        A) Use a Local SQL Backup.




                        • Create a local backup from your source instance to the new instance. Restores from local backup sets are usually much faster than network.


                        B) Use a Backup Tool like Veritas




                        • Basically, this is a network restore so expect it to be slower than local backups. Also, verify this works before going for a live migration.


                        C) Use the Data Migration Assistant from Microsoft



                        This is my personal favorite, since this tool is a all-in-one solution for the day of Migration.




                        • Finds any breaking, deprecated, changed changes and behaviors prior to migrating to a newer version of SQL Server

                        • Discover new features and recommendations! DMA is fully comprehensive in its recommendations and fully supports Azure!

                        • Uses Windows Authentication, which simplifies the permissions necessary for migration.


                        • BEST PART Seamlessly transfer SQL Logins and Users to the new instance so your application can connect after migration of data is complete!


                        I have successfully migrated several large instances, including one with 100+ databases ranging from small to 1TB+, from 2008R2 to 2016. Without comparison, the DMA performed faster and more consistently than any other method. And unlike other tools, you can graphically move your databases to whatever customization you want. Very cool and highly recommended.






                        share|improve this answer




























                          1












                          1








                          1







                          Please do not copy datafiles from one instance to another. If there are any problems, it likely will not be supported by Microsoft.



                          There are several ways to safely migrate from one Server to another, though my preference is using the Data Migration Assistant. For now, lets talk about your steps:



                          1) Always Collect all user DDL scripts including:




                          • SQL Logins via Microsoft's sp_help_revlogin

                          • SQL Users. Try this one: Database Users and their Roles


                          There are many scripts out there, this version is pretty dependable and runs in the context of one database



                          /*
                          This script will script the role members for all roles on the database.
                          This is useful for scripting permissions in a development environment before refreshing development with a copy of production. This will allow us to easily ensure development permissions are not lost during a prod to dev restoration.
                          */

                          /*********************************************/
                          /********* DB CONTEXT STATEMENT *********/
                          /*********************************************/
                          SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
                          1 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
                          1 AS [-- RESULT ORDER HOLDER --]

                          UNION

                          SELECT '' AS [-- SQL STATEMENTS --],
                          2 AS [-- RESULT ORDER HOLDER --]

                          UNION


                          /*********************************************/
                          /********* DB ROLE PERMISSIONS *********/
                          /*********************************************/
                          SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
                          3 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT 'EXEC sp_addrolemember @rolename ='
                          + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
                          3 AS [-- RESULT ORDER HOLDER --]
                          FROM sys.database_role_members AS rm
                          WHERE USER_NAME(rm.member_principal_id) IN (
                          --get user names on the database
                          SELECT [name]
                          FROM sys.database_principals
                          WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                          and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                          )
                          --ORDER BY rm.role_principal_id ASC


                          UNION

                          SELECT '' AS [-- SQL STATEMENTS --],
                          4 AS [-- RESULT ORDER HOLDER --]

                          UNION

                          /*********************************************/
                          /********* OBJECT LEVEL PERMISSIONS *********/
                          /*********************************************/
                          SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                          5 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT CASE
                          WHEN perm.state <> 'W' THEN perm.state_desc
                          ELSE 'GRANT'
                          END
                          + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
                          + CASE
                          WHEN cl.column_id IS NULL THEN SPACE(0)
                          ELSE '(' + QUOTENAME(cl.name) + ')'
                          END
                          + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
                          + CASE
                          WHEN perm.state <> 'W' THEN SPACE(0)
                          ELSE SPACE(1) + 'WITH GRANT OPTION'
                          END
                          AS [-- SQL STATEMENTS --],
                          5 AS [-- RESULT ORDER HOLDER --]
                          FROM
                          sys.database_permissions AS perm
                          INNER JOIN
                          sys.objects AS obj
                          ON perm.major_id = obj.[object_id]
                          INNER JOIN
                          sys.database_principals AS usr
                          ON perm.grantee_principal_id = usr.principal_id
                          LEFT JOIN
                          sys.columns AS cl
                          ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
                          --WHERE usr.name = @OldUser
                          --ORDER BY perm.permission_name ASC, perm.state_desc ASC



                          UNION

                          SELECT '' AS [-- SQL STATEMENTS --],
                          6 AS [-- RESULT ORDER HOLDER --]

                          UNION

                          /*********************************************/
                          /********* DB LEVEL PERMISSIONS *********/
                          /*********************************************/
                          SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                          7 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT CASE
                          WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
                          ELSE 'GRANT'
                          END
                          + SPACE(1) + perm.permission_name --CONNECT, etc
                          + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
                          + CASE
                          WHEN perm.state <> 'W' THEN SPACE(0)
                          ELSE SPACE(1) + 'WITH GRANT OPTION'
                          END
                          AS [-- SQL STATEMENTS --],
                          7 AS [-- RESULT ORDER HOLDER --]
                          FROM sys.database_permissions AS perm
                          INNER JOIN
                          sys.database_principals AS usr
                          ON perm.grantee_principal_id = usr.principal_id
                          --WHERE usr.name = @OldUser

                          WHERE [perm].[major_id] = 0
                          AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
                          AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                          ORDER BY [-- RESULT ORDER HOLDER --]



                          • Any SQL Jobs

                          • Replication

                          • Any other external files necessary for the new instance


                          2) Pre-Plan Storage Requirements




                          • Sometimes Migration is the perfect time to following Microsoft's Top 10 Storage Best Practices including:

                          • Separating tempdb from the rest of the instance and also your system datafiles from the user databases

                          • Address any special databases into its own disks, settings for optimal performance.


                          3) TEST, TEST, TEST!!



                          Always Test your migration. Create a Backup and restore this onto your new server so you verify




                          • a) the storage fits

                          • b) the time it take to restore

                          • c) pre-test connectivity to be free from Network, Firewall, or Permissions issues (including SPNs, DNS A-records, etc)


                          4) Migrate (using one of three methods)



                          A) Use a Local SQL Backup.




                          • Create a local backup from your source instance to the new instance. Restores from local backup sets are usually much faster than network.


                          B) Use a Backup Tool like Veritas




                          • Basically, this is a network restore so expect it to be slower than local backups. Also, verify this works before going for a live migration.


                          C) Use the Data Migration Assistant from Microsoft



                          This is my personal favorite, since this tool is a all-in-one solution for the day of Migration.




                          • Finds any breaking, deprecated, changed changes and behaviors prior to migrating to a newer version of SQL Server

                          • Discover new features and recommendations! DMA is fully comprehensive in its recommendations and fully supports Azure!

                          • Uses Windows Authentication, which simplifies the permissions necessary for migration.


                          • BEST PART Seamlessly transfer SQL Logins and Users to the new instance so your application can connect after migration of data is complete!


                          I have successfully migrated several large instances, including one with 100+ databases ranging from small to 1TB+, from 2008R2 to 2016. Without comparison, the DMA performed faster and more consistently than any other method. And unlike other tools, you can graphically move your databases to whatever customization you want. Very cool and highly recommended.






                          share|improve this answer















                          Please do not copy datafiles from one instance to another. If there are any problems, it likely will not be supported by Microsoft.



                          There are several ways to safely migrate from one Server to another, though my preference is using the Data Migration Assistant. For now, lets talk about your steps:



                          1) Always Collect all user DDL scripts including:




                          • SQL Logins via Microsoft's sp_help_revlogin

                          • SQL Users. Try this one: Database Users and their Roles


                          There are many scripts out there, this version is pretty dependable and runs in the context of one database



                          /*
                          This script will script the role members for all roles on the database.
                          This is useful for scripting permissions in a development environment before refreshing development with a copy of production. This will allow us to easily ensure development permissions are not lost during a prod to dev restoration.
                          */

                          /*********************************************/
                          /********* DB CONTEXT STATEMENT *********/
                          /*********************************************/
                          SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
                          1 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
                          1 AS [-- RESULT ORDER HOLDER --]

                          UNION

                          SELECT '' AS [-- SQL STATEMENTS --],
                          2 AS [-- RESULT ORDER HOLDER --]

                          UNION


                          /*********************************************/
                          /********* DB ROLE PERMISSIONS *********/
                          /*********************************************/
                          SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
                          3 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT 'EXEC sp_addrolemember @rolename ='
                          + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
                          3 AS [-- RESULT ORDER HOLDER --]
                          FROM sys.database_role_members AS rm
                          WHERE USER_NAME(rm.member_principal_id) IN (
                          --get user names on the database
                          SELECT [name]
                          FROM sys.database_principals
                          WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                          and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                          )
                          --ORDER BY rm.role_principal_id ASC


                          UNION

                          SELECT '' AS [-- SQL STATEMENTS --],
                          4 AS [-- RESULT ORDER HOLDER --]

                          UNION

                          /*********************************************/
                          /********* OBJECT LEVEL PERMISSIONS *********/
                          /*********************************************/
                          SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                          5 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT CASE
                          WHEN perm.state <> 'W' THEN perm.state_desc
                          ELSE 'GRANT'
                          END
                          + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects
                          + CASE
                          WHEN cl.column_id IS NULL THEN SPACE(0)
                          ELSE '(' + QUOTENAME(cl.name) + ')'
                          END
                          + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
                          + CASE
                          WHEN perm.state <> 'W' THEN SPACE(0)
                          ELSE SPACE(1) + 'WITH GRANT OPTION'
                          END
                          AS [-- SQL STATEMENTS --],
                          5 AS [-- RESULT ORDER HOLDER --]
                          FROM
                          sys.database_permissions AS perm
                          INNER JOIN
                          sys.objects AS obj
                          ON perm.major_id = obj.[object_id]
                          INNER JOIN
                          sys.database_principals AS usr
                          ON perm.grantee_principal_id = usr.principal_id
                          LEFT JOIN
                          sys.columns AS cl
                          ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
                          --WHERE usr.name = @OldUser
                          --ORDER BY perm.permission_name ASC, perm.state_desc ASC



                          UNION

                          SELECT '' AS [-- SQL STATEMENTS --],
                          6 AS [-- RESULT ORDER HOLDER --]

                          UNION

                          /*********************************************/
                          /********* DB LEVEL PERMISSIONS *********/
                          /*********************************************/
                          SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
                          7 AS [-- RESULT ORDER HOLDER --]
                          UNION
                          SELECT CASE
                          WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
                          ELSE 'GRANT'
                          END
                          + SPACE(1) + perm.permission_name --CONNECT, etc
                          + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
                          + CASE
                          WHEN perm.state <> 'W' THEN SPACE(0)
                          ELSE SPACE(1) + 'WITH GRANT OPTION'
                          END
                          AS [-- SQL STATEMENTS --],
                          7 AS [-- RESULT ORDER HOLDER --]
                          FROM sys.database_permissions AS perm
                          INNER JOIN
                          sys.database_principals AS usr
                          ON perm.grantee_principal_id = usr.principal_id
                          --WHERE usr.name = @OldUser

                          WHERE [perm].[major_id] = 0
                          AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
                          AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                          ORDER BY [-- RESULT ORDER HOLDER --]



                          • Any SQL Jobs

                          • Replication

                          • Any other external files necessary for the new instance


                          2) Pre-Plan Storage Requirements




                          • Sometimes Migration is the perfect time to following Microsoft's Top 10 Storage Best Practices including:

                          • Separating tempdb from the rest of the instance and also your system datafiles from the user databases

                          • Address any special databases into its own disks, settings for optimal performance.


                          3) TEST, TEST, TEST!!



                          Always Test your migration. Create a Backup and restore this onto your new server so you verify




                          • a) the storage fits

                          • b) the time it take to restore

                          • c) pre-test connectivity to be free from Network, Firewall, or Permissions issues (including SPNs, DNS A-records, etc)


                          4) Migrate (using one of three methods)



                          A) Use a Local SQL Backup.




                          • Create a local backup from your source instance to the new instance. Restores from local backup sets are usually much faster than network.


                          B) Use a Backup Tool like Veritas




                          • Basically, this is a network restore so expect it to be slower than local backups. Also, verify this works before going for a live migration.


                          C) Use the Data Migration Assistant from Microsoft



                          This is my personal favorite, since this tool is a all-in-one solution for the day of Migration.




                          • Finds any breaking, deprecated, changed changes and behaviors prior to migrating to a newer version of SQL Server

                          • Discover new features and recommendations! DMA is fully comprehensive in its recommendations and fully supports Azure!

                          • Uses Windows Authentication, which simplifies the permissions necessary for migration.


                          • BEST PART Seamlessly transfer SQL Logins and Users to the new instance so your application can connect after migration of data is complete!


                          I have successfully migrated several large instances, including one with 100+ databases ranging from small to 1TB+, from 2008R2 to 2016. Without comparison, the DMA performed faster and more consistently than any other method. And unlike other tools, you can graphically move your databases to whatever customization you want. Very cool and highly recommended.







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Jan 5 at 15:39

























                          answered Jan 5 at 15:32









                          clifton_hclifton_h

                          54737




                          54737






























                              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%2f226368%2ffull-migration-2008r2-2016%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

                              Liste der Baudenkmale in Friedland (Mecklenburg)

                              Single-Malt-Whisky

                              Czorneboh