Migrating SQL Databases - MS Access

In this section, you will learn how to copy an existing on-premise database to SQL Azure. Using the methods described here, there will be no synchronization or connection between the local database and the database in Azure. (Synchronizing the databases is discussed in the next section.) To copy the database into Azure, you use the following sequence of steps:

  1. Generate a script file to be run in Azure that will create the empty tables.
  2. Modify the script file to be compliant with Azure requirements.
  3. Generate the empty tables.
  4. Use the SQL Server Import And Export Wizard to copy the data into Azure.

Once again, the sample files for this chapter include the instnwnd.sql script to create the Northwind sample database in your local SQL Server, ready for use in moving to SQL Azure.

Creating a Set of Tables

The first step is to use Management Studio with your on-premise database to generate a script for creating your database tables, as shown in Figure.

Generating a script for creating the tables.

Generating a script for creating the tables.

Figure shows the process of selecting your tables. Proceed through the various windows to generate a script in the query window.

Select only the tables.

Select only the tables.

As previously pointed out, SQL Azure is not identical to SQL Server, and as of this writing, Management Studio does not generate scripts that are compliant with the requirements of SQL Azure. This means that you will need to implement a number of find and replace operations to ensure that you have a compliant script.

The SQL Server Import And Export Wizard and UNICODE data types

When used with the transfer mechanism for communicating data to SQL Azure, the SQL Server Import And Export Wizard only allows data to be transferred to UNICODE data types. It will allow you to transfer from a non-UNICODE data type such as a VARCHAR to a UNICODE NVARCHAR. This means that you might need to perform a global find in your own script file to replace any of the following:

  • VARCHAR to NVARCHAR
  • CHAR to NCHAR
  • TEXT to NVARCHAR(MAX)

Note that this is only required because in the method outlined in this section, when using the wizard to transfer the data to Azure, SQL Azure does support these data types. As part of the create table syntax, your script file will contain syntax similar to the following:

You will need to change these to read as follows:

The easiest way to make this change is with a succession of find and replace operations to replace portions of the script; for example, replacing PAD_INDEX = OFF with an empty string. You will find it helpful to make copies of the script file before trying out the changes. This way, you can take one example of the create table syntax and practice making the replacements in a dummy database so that you can ensure that the script will execute. We have included NorthwindInitialScript.SQL and NorthwindFixedScript.SQL in the sample databases.

Once your script file is prepared and tested, you can use Management Studio to execute the script in your database, taking care to execute it in the correct database. Create a new database in SQL Azure and call it NorthwindAzure; you can do this either by using the browser tools or by using Management Studio and executing the following T-SQL while you are connected to the Master database in SQL Azure:

Now execute the script NorthwindFixedScript.SQL in the database NorthwindAzure to create a set of empty tables, relationships, and other database objects Transferring Data with the SQL Server Import and Export WizardNow that you have created your empty database structures in SQL Azure, use Management Studio to access your local on-premise database. Right-click the database and select the Export Data option from the Tasks shortcut menu, as shown in Figure.

Exporting data from the on-premise database.

Exporting data from the on-premise database.

Follow the steps in the SQL Server Import And Export Wizard until you reach the page shown in Figure.

In the Destination field toward the top of the wizard page, Select the .Net Framework Data Provider For SqlServer option.

In the Destination field toward the top of the wizard page, Select the .Net Framework Data Provider For SqlServer option

After selecting the correct data provider for your SQL Azure database, you need to enter the Password, User ID, Data Source, and Initial Catalog . On the next page, select the Copy Data From One Or More Tables Or Views option. Choose the tables that you want to copy (do not select the Views; it is also possible to use Views to transfer the data, but we do not require this), as shown in Figure .

Selecting the tables to transfer automatically completes the destination when the names are matched.

Selecting the tables to transfer automatically completes the destination when the names are matched.

On the next page, select Run Immediately. If everything has worked properly, the wizard will look similar to Figure. If there are errors, the wizard usually provides a good description of the nature of the problem.

Details are displayed after completing the transfer, including any error messages.

Details are displayed after completing the transfer, including any error messages.

For a detailed description of how to link your Access database application to the new SQL Azure database, refer to Chapter, “Linking Access Tables.”

SQL Import/Export features when transferring to SQL Azure

Normally, when you are transferring data by using the SQL Server ImportExport between two SQL Server databases, you need to consider the following :

  • For any tables with Identity properties, select the Enable Identity Insert option.
  • For any tables with TIMESTAMPS, click the Field Data Type, and then select Ignore.

If you were to use the profiler to monitor how the SQL Server Import And Export Wizard performs the data transfer, you would see insert bulk statements with a CHECK_ CONSTRAINTS option. This means that referential integrity can still prevent data from being transferred, so a great deal of care can be required. A fantastic feature of SQL Azure is that when you use Import/Export to transfer data to SQL Azure, you do not need to explicitly set these options, and the data transfer avoids any problems associated with referential integrity and the order in which tables are populated with data.

Backing up and Copying a Database

Within SQL Azure, you can copy a database, both within a single server and between SQL Azure servers; this can be used to either provide copies for development or to provide a snapshot backup of the database. The following code shows how to create a backup copy of the Northwind database in SQL Azure. It includes commands that can be used to monitor the progress of the operation:

Importing data from SQL Azure

If you want to transfer a SQL Azure database to a new, local on-premise database, you can do this by using Import. You perform an Import in Management Studio while connected to your local onpremise database, and not from the Azure database (if you right-click the Azure database, you will not see any Import/Export tasks). It is best to perform this operation into an empty local SQL Server database; this process will then create unkeyed and unrelated tables of all the data.

If you try to reverse the process that was used to push the data into SQL Azure by first creating tables and constraints in the empty on-premise SQL Database, you will run into the problems described earlier when using the SQL Server Import And Export Wizard to migrate between two local SQL Server databases. The process of exporting to SQL Azure and importing from SQL Azure are not identical.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics