Connecting to SQL Server(MySQL to SQL)
To migrate MySQL databases to SQL Server, you must connect to the target instance of SQL Server. When you connect, 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 of the instance of SQL Server you are connected to, but does not 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 is not 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 topic.
The account that is used to connect to SQL Server requires different permissions depending on the actions that the account performs:
To convert MySQL objects to Transact-SQL syntax, to update metadata from SQL Server, or to save converted syntax to scripts, the account must have permission to log on to the instance of SQL Server.
To load database objects into SQL Server, the minimum permission requirement is membership in the db_owner database role in the target database.
Before you convert MySQL database objects to SQL Server syntax, you must establish a connection to the instance of SQL Server where you want to migrate the MySQL 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 MySQL schema level after you connect to SQL Server. For more information, see Mapping MySQL Databases to SQL Server Schemas(MySQLToSQL)
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 SQL Server
On the File menu, select Connect to SQL Server (this option is enabled after the creation of a project).
If you have previously connected to SQL Server, the command name will be Reconnect to SQL Server.
In the connection dialog box, enter or select the name of the instance of SQL Server.
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.
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 will try to obtain the port number from the SQL Server Browser Service.
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.
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.
Higher version compatibility
It is allowed to connect/reconnect to higher versions of SQL Server.
You will be able to connect to SQL Server 2008 or SQL Server 2012 when the project created is SQL Server 2005.
You will be able to connect to SQL Server 2012 when the project created is SQL Server 2008 but it is not allowed to connect to lower versions i.e. SQL Server 2005.
You will be able to connect to only SQL Server 2012 when the project created is SQL Server 2012.
Higher version compatibility is not valid for “SQL Azure”.
PROJECT TYPE Vs TARGET SERVER VERSION
SQL Server 2005
SQL Server 2008
SQL Server 2012
SQL Server 2005
SQL Server 2008
SQL Server 2012
Conversion of the database objects is carried out as per the project type but not as per the version of the SQL Server connected to. In case of SQL Server 2005 project, Conversion is carried out as per SQL Server 2005 even though you are connected to a higher version of SQL Server (SQL Server 2008/ SQL Server 2012).
Metadata about SQL Server databases is not 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
Make sure that you are connected to SQL Server.
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.
Right-click Databases, or the individual database or database schema, and then select Synchronize with Database.
The next step in the migration depends on your project needs:
To customize the mapping between MySQL schemas and SQL Server databases and schemas, see Mapping MySQL Databases to SQL Server Schemas(MySQLToSQL)
To customize configuration options for the projects, see Setting Project Options (MySQL to SQL)
To customize the mapping of source and target data types, see Mapping MySQL and SQL Server Data Types (MySQL to SQL)
If you do not have to perform any of these tasks, you can convert the MySQL database object definitions into SQL Server object definitions. For more information, see Converting MySQL Databases (MySQL to SQL)