Step 3: Define the Remote Database Schema

The next step is to take the existing consolidated database (SQL Server) schema and migrate that over to our remote database.

One way of doing this is to once again click on the “Utilities” tab, and further down the list you will notice the option “Migrate Database”.

For this exercise will be using the default SQL Server Database called Northwind. If you have never used MS SQL server, this database gets created when you install MS SQL Server.

Double click the “Migrate Database” option and step through the required steps to migrate the schema.

The first thing we need to specify is the server we want to migrate the information to, and in this case we will specify the remote database (c:\demo\remote.db) that we created earlier on.

Once we have done that we need to specify or create the “Remote Server” where we are planning to migrate the schema from. This is the connection information to the SQL Server.

In this case we will use an  ODBC DSN.

Click the “Create Remote Server Now…” button and give it a name, this can be anything. (I will call it SQL Server)

Secondly we need to specify which type of RDBMS we will be connecting to... and in this case it is MS SQL Server, choose SQL Server  and click Next

Now we need to specify the name of the Datasource ( dsn=SQL Server) click Next

Accept the default “Read only” setting and click Next. Depending on your security you may need to specify your login. By default all MS SQL Server databases have a default user of ‘sa’ with no password.

To be on the safe side, select the external login and type in a login name of “sa”.

Click the “Test Connection” button. If everything is configured properly you will receive a “Successful” message, if not check your connection information.

All done?... click on Finish

What you will notice is the Remote Server connection pointing to our SQL Server. This is going to allow the Migration Wizard to import all the relevant database objects from the consolidated SQL Server database that we need in the remote to setup synchronization

Select  the remote server and click on Next

What you will see now, is that the Migration Wizard has connected to the Northwind Database and from here we can see all the tables in the Northwind SQL Server Database.

Note you can also see the Mobilink System tables that we created earlier on.

Choose the tables that you want to synchronize, and for this walkthrough  we will choose the Customers and Employees table to migrate across to our remote database. Select the tables and click Next

The next screen shows the SQL Anywhere owner of the table, click next and now we can choose exactly what we want to migrate from the SQL Server (Consolidated) database to the SQL Anywhere Server (Remote) database.

For this exercise we will choose everything except for the data (Untick the “Migrate Data” option), because we are going to use Mobilink server to synchronize the data. Click Finish

You will now see the migration wizard connecting to SQL Anywhere and migrating across the table, column and other database object information such as indexes etc.

If we refresh our remote database by clicking on the refresh icon   in the toolbar at the top, and then expand and look at the “Tables” folder in our remote SQL Anywhere (ASA) database, we will see the two tables that we have just migrated across.

If we look at the Customer table, we will notice that the schema matches exactly that of the table in the SQL Server database, it has the same datatype, same size and attributes.

If we look at the Employees table we will see that the Migration Wizard has also migrated the “Foreign Keys”, “Referencing Tables” and “Indexes”, basically all the information we am going to need to synchronize our SQL Anywhere database with SQL Server.

Looking at the Data Tab, you will notice that there is no data yet, because we chose not to migrate the data.