Skip to content

connecting-to-sql-server-sybasetosql.md

Latest commit

 

History

History
123 lines (77 loc) · 12.6 KB

connecting-to-sql-server-sybasetosql.md

File metadata and controls

123 lines (77 loc) · 12.6 KB
title Connecting to SQL Server (SybaseToSQL) | Microsoft Docs
ms.custom
ms.date 01/19/2017
ms.prod sql
ms.reviewer
ms.technology ssma
ms.topic conceptual
helpviewer_keywords
Connecting to SQL Server
ms.assetid dd368a1a-45b0-40e9-b4d3-5cdb48c26606
author Shamikg
ms.author Shamikg

Connecting to SQL Server (SybaseToSQL)

To migrate Sybase Adaptive Server Enterprise (ASE) databases to [!INCLUDEssNoVersion], you must connect to any of the target instances of [!INCLUDEssNoVersion]. When you connect, SSMA obtains metadata about all the databases in the instance of [!INCLUDEssNoVersion] and displays database metadata in the [!INCLUDEssNoVersion] Metadata Explorer. SSMA stores information about which instance of [!INCLUDEssNoVersion] you are connected to, but does not store passwords.

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

Metadata about the instance of [!INCLUDEssNoVersion] is not automatically synchronized. Instead, if you want to update the metadata in [!INCLUDEssNoVersion] Metadata Explorer, you must manually update the [!INCLUDEssNoVersion] metadata, as described in the "Synchronizing [!INCLUDEssNoVersion] Metadata" section later in this topic.

Required SQL Server Permissions

The account that is used to connect to [!INCLUDEssNoVersion] requires different permissions depending on the actions that are performed by that account.

  • To convert ASE objects to [!INCLUDEtsql] syntax, to update metadata from [!INCLUDEssNoVersion], or to save converted syntax to scripts, the account must have permission to log in to the instance of [!INCLUDEssNoVersion].

  • To load database objects into [!INCLUDEssNoVersion], the minimum permission requirement is membership in the db_owner database role in the target database.

  • To migrate data to [!INCLUDEssNoVersion], the account should be a member of the sysadmin server role. This is required to run the [!INCLUDEssNoVersion] Agent data migration packages.

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

If the account that is used to connect to [!INCLUDEssNoVersion] is to perform all migration tasks, the account must be a member of the sysadmin server role.

Establishing a SQL Server Connection

Before you convert ASE database objects to [!INCLUDEssNoVersion] syntax, you must establish a connection to the instance of [!INCLUDEssNoVersion] where you want to migrate the ASE database or databases.

When you define the connection properties, you also specify the database where objects and data will be migrated. You can customize this mapping at the ASE schema level after you connect to [!INCLUDEssNoVersion]. For more information, see Mapping Sybase ASE Schemas to SQL Server Schemas (SybaseToSQL).

Important

Before you try to connect to [!INCLUDEssNoVersion], make sure that the instance of [!INCLUDEssNoVersion] is running and can accept connections.

To connect to SQL Server

  1. On the File menu, select Connect to SQL Server.

    If you previously connected to [!INCLUDEssNoVersion], the command name will be Reconnect to SQL Server.

  2. In the connection dialog box, enter or select the name of the instance of [!INCLUDEssNoVersion].

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

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

    • If you are 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 [!INCLUDEssNoVersion] is configured to accept connections on a non-default port, enter the port number that is used for [!INCLUDEssNoVersion] connections in the Server port box. For the default instance of [!INCLUDEssNoVersion], the default port number is 1433. For named instances, SSMA will try to obtain the port number from the [!INCLUDEssNoVersion] Browser Service.

  4. In the Database box, enter the name of the target database.

    This option is not available when reconnecting to [!INCLUDEssNoVersion].

  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 [!INCLUDEssNoVersion] login, select SQL Server Authentication and then provide login name and password.

  6. For 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 will validate the SQL Server SSL certificate. Validating the server certificate is a part of the SSL handshake and ensures that the server is the correct server to connect to. To ensure this, a certificate must be installed on the client side as well as on the server side.

  7. Click Connect.

Higher Version Compatability

  • You will be able to connect to [!INCLUDEssNoVersion] 2008 and [!INCLUDEssNoVersion] 2012 and [!INCLUDEssNoVersion] 2014 and [!INCLUDEssNoVersion] 2016 when the migration project created is [!INCLUDEssNoVersion] 2005.

  • You will be able to connect to [!INCLUDEssNoVersion] 2012 and [!INCLUDEssNoVersion] 2014 and [!INCLUDEssNoVersion] 2016 when the migration project created is [!INCLUDEssNoVersion] 2008 but you will not be able to connect to lower versions i.e. [!INCLUDEssNoVersion] 2005.

  • You will be able to connect to only [!INCLUDEssNoVersion] 2012 and [!INCLUDEssNoVersion] 2014 and [!INCLUDEssNoVersion] 2016 when the project created is SQL Server 2012.

  • Higher version compatibility is not valid for SQL Azure.
PROJECT TYPE Vs TARGET SERVER VERSION [!INCLUDEssNoVersion] 2005
(Version: 9.x)		 [!INCLUDEssNoVersion] 2008
(Version: 10.x)		 [!INCLUDEssNoVersion] 2012
(Version:11.x)		 [!INCLUDEssNoVersion] 2014
(Version:12.x)		 [!INCLUDEssNoVersion] 2016
(Version:13.x)		 SQL Azure
[!INCLUDEssNoVersion] 2005 Yes Yes Yes Yes Yes
[!INCLUDEssNoVersion] 2008 Yes Yes Yes Yes
[!INCLUDEssNoVersion] 2012 Yes Yes Yes
[!INCLUDEssNoVersion] 2014 Yes Yes
[!INCLUDEssNoVersion] 2016 Yes
SQL Azure Yes

Important

Conversion of the database objects is carried out as per the project type but not as per the version of the [!INCLUDEssNoVersion] you are connected to. In case of [!INCLUDEssNoVersion] 2005 project, Conversion is carried out as per [!INCLUDEssNoVersion] 2005 even though you are connected to a higher version of [!INCLUDEssNoVersion] ( [!INCLUDEssNoVersion] 2008 or [!INCLUDEssNoVersion] 2012 or [!INCLUDEssNoVersion] 2014 or [!INCLUDEssNoVersion] 2016)

Reconnecting to SQL Server

Your connection to [!INCLUDEssNoVersion] stays active until you close the project. When you reopen the project, you must reconnect to [!INCLUDEssNoVersion] if you want an active connection to the server. You can work offline until you update metadata, load database objects into [!INCLUDEssNoVersion], and migrate data.

The procedure for reconnecting to [!INCLUDEssNoVersion] is the same as the procedure for establishing a connection.

Synchronizing SQL Server Metadata

Metadata about the [!INCLUDEssNoVersion] databases is not automatically updated. The metadata in [!INCLUDEssNoVersion] Metadata Explorer is a snapshot of the metadata when you first connected to [!INCLUDEssNoVersion], 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 are connected to [!INCLUDEssNoVersion].

  2. In [!INCLUDEssNoVersion] 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.

Next Step

The next step in the migration depends on your project needs:

See Also

Migrating Sybase ASE Databases to SQL Server - Azure SQL DB (SybaseToSQL)