Muokkaa

Jaa

Connect to SQL Server (Db2ToSQL)

  • Artikkeli

To migrate Db2 databases to SQL Server, you must connect to the target SQL Server instance. When you connect, SQL Server Migration Assistant (SSMA) obtains metadata about all the databases in the instance of SQL Server and displays database metadata in the SQL Server Metadata Explorer. SSMA stores information about which instance of SQL Server you're connected to, but doesn't store passwords.

Your connection to SQL Server stays active until you close the project. When you reopen the project, you must reconnect to SQL Server if you want an active connection to the server. You can work offline until you load database objects into SQL Server and migrate data.

Metadata about the instance of SQL Server isn't automatically synchronized. Instead, to update the metadata in SQL Server Metadata Explorer, you must manually update the SQL Server metadata. For more information, see the "Synchronizing SQL Server Metadata" section later in this article.

Required SQL Server permissions

The account that is used to connect to SQL Server requires different permissions depending on the actions that the account performs:

  • To convert Db2 objects to Transact-SQL syntax, to update metadata from SQL Server, or to save converted syntax to scripts, the account must have permission to sign in to the instance of SQL Server.

  • To load database objects into SQL Server, the account must be a member of the db_ddladmin server role.

  • To migrate data to SQL Server, the account must be a member of the db_owner database role.

  • To run the code that is generated by SSMA, the account must have EXECUTE permissions for all user-defined functions in the ssma_db2 schema of the target database. These functions provide equivalent functionality of Db2 system functions, and are used by converted objects.

Establish a SQL Server connection

Before you convert Db2 database objects to SQL Server syntax, you must establish a connection to the instance of SQL Server where you want to migrate the Db2 database or databases.

When you define the connection properties, you also specify the database where objects and data are migrated. You can customize this mapping at the Db2 schema level after you connect to SQL Server. For more information, see Map Db2 Schemas to SQL Server Schemas.

Important

Before you try to connect to SQL Server, make sure that the instance of SQL Server is running and can accept connections.

To connect to the SQL Server:

  1. Navigate to File > Connect to SQL Server. If you previously connected to SQL Server, the command name is Reconnect to SQL Server.

  2. In the connection dialog box, enter or select the name of the instance of SQL Server.

    • If you're connecting to the default instance on the local computer, you can enter localhost or a dot (.).

    • If you're connecting to the default instance on another computer, enter the name of the computer.

    • If you're connecting to a named instance on another computer, enter the computer name followed by a backslash and then the instance name, such as MyServer\MyInstance.

  3. If your instance of SQL Server is configured to accept connections on a non-default port, enter the port number that is used for SQL Server connections in the Server port box. For the default instance of SQL Server, the default port number is 1433. For named instances, SSMA tries to obtain the port number from the SQL Server Browser Service.

  4. In the Database box, enter the name of the target database. This option isn't available when you reconnect to SQL Server.

  5. In the Authentication box, select the authentication type to use for the connection. To use the current Windows account, select Windows Authentication. To use a SQL Server login, select SQL Server Authentication, and then provide the login name and password.

  6. For a secure connection, two controls are added, the Encrypt Connection and TrustServerCertificate check boxes. Only when Encrypt Connection is checked, the TrustServerCertificate check box is visible. When Encrypt Connection is checked (true) and TrustServerCertificate is unchecked (false), it validates the SQL Server TLS/SSL certificate. Validating the server certificate is a part of the secure handshake and ensures that the server is the correct server to connect to; a certificate must be installed on the client side and on the server side.

  7. Select Connect.

Important

While you might connect to a higher version of SQL Server, compared to the version chosen when the migration project was created, conversion of the database objects is determined by the target version of the project and not the version of the SQL Server you're connected to.

Synchronize SQL Server metadata

Metadata about SQL Server databases isn't automatically updated. The metadata in SQL Server Metadata Explorer is a snapshot of the metadata when you first connected to SQL Server, or the last time that you manually updated metadata. You can manually update metadata for all databases, or for any single database or database object. To synchronize metadata:

  1. Make sure that you're connected to SQL Server.

  2. In SQL Server Metadata Explorer, select the check box next to the database or database schema that you want to update. For example, to update the metadata for all databases, select the box next to Databases.

  3. Right-click Databases, or the individual database or database schema, and then select Synchronize with Database.

Related content