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

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

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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