SQL Server Processing
The SQL Processing Wizard allows you to update your Microsoft SQL servers to reflect the domain migration changes that were made using Migration Manager. The SQL update should be performed after Migration Manager has been used to migrate accounts to the new domain.
|
|
Caution: SQL processing wizard does not process an SQL server if one or more of its databases is in Offline, Read Only, or Single User mode. Such behavior is merely a precaution to prevent inconsistencies in permissions. |
The SQL Processing Wizard retrieves the object migration information from the migration project and replaces the old accounts it locates on the processed SQL server (the source logins) with the corresponding new accounts (the target logins).
The wizard automatically detects the SQL Server version and performs the updates in accordance with the server’s structure.
|
|
Important: For information on supported SQL Server versions, refer to Processed Platforms – SQL Servers topic of the System Requirements and Access Rights document. |
The wizard has the ability to merge logins. That is, if a target login name or security identifier (SID) is already used on the SQL server, or several source logins have the same target login, then the resulting target login will have its own privileges and the privileges of all the source logins as well.
The wizard cannot split database users; that is, a source database user can be migrated only to one target database user. Once a source database user has been migrated, the SQL Processing Wizard cannot then migrate that source database user to another target database user because the source database user is already absent—it has been migrated to the first target database user.
If you decide to roll back a migration, the SQL Processing Wizard can also be used to revert the changes to the SQL server.
|
|
NOTE: If accounts are merged during the update process, the wizard will not be able to separate them during the rollback. In this case, it is recommended that you restore the server from a backup if required. |
SQL Objects Processed
SQL Processing Wizard replaces all occurrences of the selected migrated accounts with the corresponding target accounts.
Microsoft SQL Server 2012 SP1 and Microsoft SQL Server 2014
The following objects are updated on Microsoft SQL Server 2012 Service Pack 1 or Microsoft SQL Server 2014 servers:
- Security Logins
- Database Users
- Object Owners
- Object Permissions
- Database Owners
- Replication Publications:
- Login Names in Publication Access Lists
- FTP Logins for Snapshot Locations
- Destination Owners
- Database Maintenance Plan Owners
- Legacy Database Maintenance Plan Owners
- Job Owners for SQL Server Agents
- Statement Permissions
- Role Owners
- Endpoints Owners:
- Database Mirroring Owners
- Service Broker Owners
- SOAP Owners
- TSQL Owners
- Schema Owners
- Keys Owners (symmetric, asymmetric)
- Certificates Owners
- Service Broker:
- Message Types Owners
- Contracts Owners
- Services Owners
- Routes Owners
- Remote Service Binding Owners
- Full Text Catalog Owners
- Assemblies Owners
Microsoft SQL Server 2008 and Microsoft SQL Server 2008 Express
The following objects are updated on Microsoft SQL Server 2008 servers:
- Security Logins
- Database Users
- Object Owners
- Object Permissions
- Database Owners
- Replication Publications:
- Login Names in Publication Access Lists
- FTP Logins for Snapshot Locations
- Destination Owners
- Database Maintenance Plan Owners
- Legacy Database Maintenance Plan Owners
- Job Owners for SQL Server Agents
- Legacy Data Transformation Services:
- Local Package Owners
- Meta Data Services Package Owners
- Meta Data Authors
- Statement Permissions
- Role Owners
- Endpoints Owners:
- Database Mirroring Owners
- Service Broker Owners
- SOAP Owners
- TSQL Owners
- Schema Owners
- Keys Owners (symmetric, asymmetric)
- Certificates Owners
- Service Broker:
- Message Types Owners
- Contracts Owners
- Services Owners
- Routes Owners
- Remote Service Binding Owners
- Full Text Catalog Owners
- Assemblies Owners
Microsoft SQL Server 2005
The following objects are updated on Microsoft SQL Server 2005 servers:
- Security Logins
- Database Users
- Object Owners
- Object Permissions
- Database Owners
- Replication Publications:
- Login Names in Publication Access Lists
- FTP Logins for Snapshot Locations
- Destination Owners
- Database Maintenance Plan Owners
- Legacy Database Maintenance Plan Owners
- Job Owners for SQL Server Agents
- Legacy Data Transformation Services:
- Local Package Owners
- Meta Data Services Package Owners
- Meta Data Authors
- Aliases
- Statement Permissions
- Role Owners
- Endpoints Owners:
- Database Mirroring Owners
- Service Broker Owners
- SOAP Owners
- TSQL Owners
- Schema Owners
- Keys Owners (symmetric, asymmetric)
- Certificates Owners
- Service Broker:
- Message Types Owners
- Contracts Owners
- Services Owners
- Routes Owners
- Remote Service Binding Owners
- Full Text Catalog Owners
- Assemblies Owners
Microsoft SQL Server 2000
The following objects are updated on Microsoft SQL Server 2000 servers:
- Security Logins
- Database Users
- Object Owners
- User-Defined Data Types
- User-Defined Functions
- Database Owners
- Replication Publications:
- Login Names in Publication Access Lists
- FTP Logins for Snapshot Locations
- Destination Owners
- Database Maintenance Plan Owners
- Job Owners for SQL Server Agents and Accounts Under which the Job is Started
- Data Transformation Services:
- Local Package Owners
- Meta Data Services Package Owners
- Meta Data Authors
- Linked Servers:
- Local Logins
- Remote Users
- Default Remote Logins
- Remote Servers:
- Remote Logins for login mapping
- Aliases
- Statement Permissions
- Object Permissions
- Role Owners
Processing Details
|
|
NOTE: The target account always is preferred over the source account during the update. For instance, SQL Server does not allow you to merge aliases, so if the logins are merged and the target login already has an alias, it is left intact, and the source login’s alias is not used. If accounts are merged during the update process and if at least one of these accounts had a deny attribute, the target account will also have a deny attribute. The SQL Server Agent Proxy Account’s password is not updated during processing. For SQL Server to function correctly, you should set the password for the Agent Proxy Account after processing. |
Also, the wizard changes ownership for database objects such as tables, views, stored procedures, extended stored procedures, rules, defaults, user data types, and user-defined functions, and it processes statement permissions and object permissions of the database user.
|
|
Caution: If any of the source accounts are renamed after the migration but before the SQL Server update, some SQL objects might have old names, but they will preserve their privileges to certain actions. Renaming the migrated accounts before processing the SQL server is not recommended. |
Pre-requisites (SQL Server Processing)
The following requirements must be met for a successful SQL server update:
- The names of all the databases to be processed must conform to the standard Microsoft SQL naming requirements. For details, see the Microsoft SQL Server Books online article, Rules for Regular Identifiers.
- Processing errors will appear if the database to be processed is either of the following:
- In Single User mode and there is already a connection to the database
- In Read-only mode
- To preserve the consistency of the SQL server, the wizard will not update the server if any of the databases on the server are in Suspend or Offline mode.
The login used to process SQL Server versions 2000 and 2005 must be a member of the sysadmin role.
|
|
Caution: In the case of the error message ‘Operation failed. Failed to migrate User1 to User2’, it is recommended that you increase the key value [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Aelita\Enterprise Migration Manager\Current Version\SQL Processing Wizard]\LongCommandTimeout. |
Starting SQL Update
|
|
Caution: It is recommended that you run Resource Updating Manager before using SQL Processing Wizard. Otherwise, the wizard will not be able to update rights granted via membership in local groups. |
|
|
NOTE: It is recommended that you create a backup of the SQL server before starting SQL Processing Wizard. |
You can perform SQL processing in several ways. Select the one that best suits your situation:
- Create the SQL Processing task and run it from Migration Manager. To create an SQL processing task, go to the Resource Processing | Tasks node and click the SQL Processing button in the right pane.
- Create a setup package for the SQL Processing task, delegate rights to perform this task to another person, and send this package to that person. The delegated administrator will then install the package and perform the SQL processing as specified in the task configuration.
- Export an INI file with the appropriate settings for SQL processing, and then create and configure an SQL processing task to run in stand-alone mode using this INI file. Refer to the Delegating Resource Update topic for more details.
Regardless of the method you select, the SQL Processing Wizard will guide you through the updating process, as explained in the related topics.