Geekly Articles each Day
📜 ⬆️ ⬇️

Complete Guide: Tools and Methods for Data Migration to Windows Azure SQL Database

image

This document provides guidelines for migrating data definitions (schemas) and data to a Windows Azure SQL database. These recommendations are intended primarily for a one-time transfer from SQL Server to the SQL database. For information about sharing data and backing up a SQL database, see the SQL Data Sync Overview article.

Factors to Consider When Migrating


Microsoft Windows Azure provides several options for storing data. You can select one or more options for use in projects.
')
Windows Azure SQL Database is a SQL Server technology provided as a service on a Windows Azure platform. Cloud SQL databases provide many benefits, including fast provisioning, efficient scalability, high availability and reduced management costs. The SQL database supports the same development tools and techniques that are used for local SQL Server applications. Therefore, most developers can easily create cloud solutions.

The long-term goal of using SQL Server and the SQL database is to achieve symmetry and parity of components and capabilities. However, at present, when migrating databases to SQL database and developing solutions for SQL database, it is necessary to take into account peculiarities of the architecture and implementation methods.

First, you need to explore the differences between the SQL database and SQL Server, and also set a migration schedule.

Migration schedule


The Windows Azure platform supports three basic data storage methods. Windows Azure Storage contains tables, blobs and queues. When developing a Windows Azure solution, you need to ensure maximum performance by choosing the best way to store data.
Data storage methods
Purpose
Maximum size
Windows Azure SQL Database
Relational Database Management System
150 GB
Windows Azure Storage (Windows Azure Storage)
Blobs
Reliable storage for blobs such as video or audio
200 GB or 1 TB
Table
Reliable storage for structured data
100 TB
Turn
Reliable repository for interprocess messages
100 TB
Local storage
Temporary storage for each instance
250 GB to 2 TB

Local storage is intended for temporary storage of an application instance running locally. Only the local instance has access to local storage. In the case of restarting an instance on other equipment, for example, during outages related to a failure or maintenance of equipment, data in the local storage is not transferred to the instance. It is recommended that you use a Windows Azure storage account or a Windows Azure SQL database to ensure data integrity, data exchange between instances, or data access outside of Windows Azure.

The SQL database allows you to process data using queries, transactions, and stored procedures that run on the server side and return only the results to the application. If the application requires the processing of large data sets, it is recommended to use a SQL database. For an application that stores and retrieves large data sets, but does not require processing, it is better to choose a Windows Azure table storage.

Currently, the size of the SQL database is limited to 150 GB; at the same time, SQL database is much more expensive than Windows Azure storage. Therefore, we recommend moving blobs to Windows Azure storage. This will avoid the limitations on the size of the database and reduce operating costs.

For more information, see Data Storage Offerings on the Windows Azure platform .

Compare Windows Azure SQL Database and SQL Server


SQL Server and the SQL database have the same interface, allowing you to process a tabular data stream (TDS) for accessing a Transact-SQL database. This allows applications to use a SQL database in the same way as SQL Server.

Unlike SQL Server, the SQL database separates logical administration from physical. The user can still manage databases, accounts, users, and roles. However, management and configuration of physical hardware, such as hard drives, servers, and storage, is provided by Microsoft. The physical administration of the SQL database is done by Microsoft. Therefore, there are differences in SQL administration and SQL Server administration, training, Transact-SQL support, programming model used, and functionality.

Below is an overview of the main differences.

Database size


Currently it is possible to use two editions of the SQL database:


It is necessary to determine the size of the existing database and check its compliance with the limitations adopted for the SQL database. If the size of your database exceeds the maximum allowable amount of SQL database, you need to divide it into small segments or move most of the data to the Windows Azure BLOB storage. For more information about database segmentation, see Federation: Building Scalable, Elastic, and Multi-tenant Database Solutions with Windows Azure SQL Database. (Federation: Creating Scalable, Flexible, Multi-Tenant Database Solutions Using Windows Azure SQL Database) .

Authentication


The SQL database supports SQL authentication only. It is necessary to determine whether a change to the authentication scheme used by the application is required. For more information about security restrictions, see the Security Guidelines and Limitations article.

SQL Server Database Version


The SQL database is based on SQL Server 2008 (level 100). To migrate SQL Server 2000 or SQL Server 2005 databases to a SQL database, you must ensure that they are compatible with SQL Server 2008. The best option is to migrate from SQL Server 2008 to a SQL database. You can perform a local upgrade to SQL Server 2008 before migrating to the SQL database. When migrating from earlier versions of SQL Server, it is recommended that you study the following materials: Upgrading to SQL Server 2008 R2 (Upgrade to SQL Server 2008 R2) and Microsoft SQL Server 2008 Upgrade Advisor (Microsoft SQL Server 2008 Upgrade Advisor) .

Scheme


SQL Database does not support heap tables. ALL tables must have a clustered index. Only in this case data can be added to them. For more information about the requirement for a clustered index, see the article Inside Windows Azure SQL Database (Windows Azure SQL Database) .

Transact-SQL support


Windows Azure SQL Database supports a subset of Transact-SQL. Before deploying a database in a SQL database, you must modify the script so that only supported Transact-SQL statements are executed. For more information, see Supported Transact-SQL Statements (Supported Transact-SQL Statements) , Partially Supported Transact-SQL Statements (Partially Supported Transact-SQL Statements), and Unsupported Transact-SQL Statements (Unsupported Transact-SQL Statements) .

Operator Use


In a SQL database, the USE statement does not switch between databases. To change the database, you must connect to it directly.

Cost of


The cost of a SQL database subscription depends on the number of databases and their release. An additional fee is charged for the amount of data transferred to or from the data processing center (DPC). You have a choice: run the application code on the local servers and connect to the SQL database in the data center, or run the application code in the Windows Azure environment hosted in the same data center as the SQL database. Running application code in Windows Azure avoids the extra cost of paying for data transfer. In any case, you should remember about delays in the transmission of data over the Internet, which cannot be eliminated in any of these models. For more information, see the Pricing Overview article.

Functional limitations


Currently, the SQL database does not support some of the SQL Server features that include: SQL Agent, full-text search, Service Broker, backup and restore, CLR, and SQL Server Integration Services. For a more detailed list, see the SQL Server Feature Limitations article.

Connection Processing


When using a cloud database, such as a SQL database, a connection to the Internet or other complex networks is required. Therefore, you must be prepared to handle unexpected connection breaks.

SQL Database is a large-scale multi-tenant database management service hosted on shared resources. To ensure the convenience of all SQL database clients, the connection to the service can be closed if a number of conditions occur.

The following is a list of possible causes of disconnections.

Network latency


The delay leads to an increase in data transfer time to the SQL database. The best way to reduce the impact of delays is to transfer data using multiple parallel streams. However, the efficiency of parallelism is limited by the network bandwidth.

SQL Database allows you to create a database in various data centers. Depending on the location of the user and the capabilities of the network connections, the latency indicators in the network between the user's location and each data center will be different. To reduce delays, choose a data center in close proximity to customers. For information on measuring network latency, see Testing Client Latency to Windows Azure SQL Database (Testing Client Delay in Data Transfer to Windows Azure SQL Database) .

Placing the application code in a Windows Azure environment will improve the performance of the application, since in this case the network latency associated with the application's requests to the SQL database is reduced.
Reducing the circular path helps reduce the number of network problems.

Database failover


The SQL database replicates multiple backups of data to multiple physical servers, ensuring availability of information and business continuity. When disconnected due to a hardware failure or upgrade, the SQL database provides automatic failover to support maximum application availability. Currently, some failover actions result in a sudden session termination.

Load distribution


SQL Database Load Balancing provides the optimal use of physical servers and services in data centers. If CPU utilization, I / O latency, or the number of worker roles for a computer exceed thresholds, the SQL database can interrupt operations and disable sessions.

Regulation of the number of requests


To ensure that all subscribers receive the appropriate share of common resources and to exclude the possibility of monopolization of resources by some subscribers at the expense of others, under certain conditions, the SQL database may close or reject subscriber connections. The SQL database load control service continuously monitors performance thresholds. This allows you to assess the state of the system and regulate the number of user requests whose actions affect the performance of the system. The service tracks the following performance thresholds.


For more information, see the Windows Azure SQL Database Connection Management (Windows Azure SQL Database Connection Management) and the Windows Azure SQL Database Database Performance and Elasticity Guide (Windows Azure SQL Database) .

The best way to handle breaking a connection is to reestablish the connection and execute commands or queries that failed. For more information, see Transient Fault Handling Framework (Unstable Fault Handling Infrastructure) .

Database Optimization for Importing Data


To improve migration performance, you can do the following in databases.


Transferring large amounts of information to a SQL database


SQL Server Integration Services (SSIS) and BCP are good tools for migrating large amounts of data.

When loading into the SQL database, it is recommended to split the data into several parallel threads. This will improve boot performance.

By default, all lines in the data file are imported as one package. To distribute rows to multiple packages, it is recommended to specify the size of the package if it is known. If a package transaction fails, the insert rolls back only from the current package. Failure does not affect the status of packages that were previously imported using confirmed transactions. To determine the optimal package size, it is recommended to conduct preliminary testing using different settings of package sizes for specific scenarios and environments.

The choice of migration tools


You can migrate a database to a SQL database using various tools. As a rule, the database transfer process consists of schema migration and data migration. The following are tools that support one of these processes, or both. To create your own custom data-sending application, you can use the bulk copy API.

Migration from SQL Server


Facilities
Scheme
SQL Database Compatibility Check
Data
Data transfer efficiency
Note
Data Layer Application Package (DAC Package)
Yes
Yes
Not
Not available
· An entity containing all database objects, but not containing data.

· Full SQL database support.
Import and Export DAC BACPAC Data Tier Application
Yes
Yes
Yes
Good
· Export and import data and data tier applications using the DAC platform.

· A service is available to support only the cloud.

· The SQL DAC project is available on the CodePlex website.
SSMS Script Wizard
Yes
Partial
Yes
The bad
· Scripting parameters for the SQL database are set explicitly.

· Good for small databases.
Bcp
Not
Not available
Yes
Good
· Efficient data transfer to existing table.

· Each bcp command transfers one database.
SQL Database Migration Wizard
Yes
Yes
Yes
Good
· Extensive functionality, such as evaluating trace files.

· Open source codeplex website.

· Not supported by Microsoft.
SQL Server Integration Services
Not
Not available
Yes
Good
· High level of flexibility.
SQL Server Import and Export Wizard
Not
Not available
Yes
Good
· Simple user interface in SSIS; Also available in SSMS.

Migration from other RDMS


SQL Database Migration Assistant can be used to migrate Access, MySQL, Oracle, Sybase databases to SQL database.

A Microsoft product, codenamed Data Transfer, allows you to transfer data in CSV or Excel format to a SQL database.

Migration between SQL databases


You can use copy and synchronize SQL data to migrate data from one SQL database to another.

SQL Database supports database copy function. In this case, a database is created in the SQL database, which is a transactionally consistent copy of the existing database. To copy the database, you need to connect to the main database in the SQL Server database where the new database will be created, and run the CREATE DATABASE (Create Database) command:

CREATE DATABASE destination_database_name AS COPY OF

[source_server_name.] source_database_name

A new database can be on the same or a different server. The user executing this instruction must have the dbmanager role on the target server (to create the database) and the dbowner role in the source database. For more information, see the article Copying Databases in Windows Azure SQL Database (Copying databases in a Windows Azure SQL database) .

SQL Database Synchronization Service allows you to plan and regularly perform synchronization between the SQL database and SQL Server, as well as between different SQL databases. For more information, see the SQL Data Sync Overview article.

Migration Tools


Data Tier Application DAC Package


Data-tier applications (Data-tier Applications, DAC) were first introduced in SQL Server 2008 R2 and were used with support for development tools in Visual Studio 2010. They are designed to package the schema, code, and database configuration for deployment on another server. After preparing the DAC applications for deployment, they are embedded in the DAC package (BACPAC), which is a compressed file with DAC definitions in XML format. The database schema can be exported from SQL Server Management Studio to a DAC package, and then deployed to a SQL database.

Note. The DACPAC format is different from the BACPAC format. The BACPAC format is an extension of the DACPAC format and includes, along with the standard content of the DACPAC file, a metadata file and table data encoded with JavaScript Object Notation (JSON). The BACPAC format is discussed in the Import and Export DAC section.

Before deployment, a data-tier application package can be modified using Visual Studio 2010. In the data-tier application project, you can specify scripts to run before and after deployment. These are Transact-SQL scripts that are designed to perform any action, including inserting data into scripts that are run after deployment. However, inserting large amounts of data using a data-level application package is not recommended.

Installation and use
The DAC package is included with SQL Server 20008 R2. Migrating a SQL Server database schema to a SQL database is done in two main steps.

Extract the DAC package from the SQL Server database.
To create a DAC package based on an existing database, you can use the data-level application extraction wizard . The DAC package contains objects selected from the database and related instance-level objects, such as credentials of database users.

The screenshot shows the opening wizard.

clip_image002

The wizard allows you to perform the following basic actions.

  1. Set DAC properties, including data-level application name, version, description, and package file location.
  2. Checking the compatibility of database objects with the data-level application.
  3. Formation package.

DAC can only be retrieved from a database in a SQL database or SQL Server 2005 with Service Pack 4 (SP4) or later. If there are objects in the database that are not supported in the DAC or contain users, the DAC package cannot be retrieved. For more information about the types of objects supported in the DAC, see DAC Support for SQL Server Objects and Versions .

Deploy the DAC package in a SQL database.
You can use the data-level application deployment wizard to deploy a DAC package. First you need to connect to the SQL database server from SQL Server Management Studio. If the database does not exist, the wizard will create it. The wizard will deploy the DAC package in the DBMS kernel instance associated with the node that was selected in the object explorer hierarchy. In the example in the following screenshot, the wizard deploys the package on a SQL Server server named maqqarly23.database.windows.net .

clip_image003

Important! Before deploying a DAC package in a production environment, we recommend checking its contents, especially if this package was developed in another organization. For more information, see the Validate a DAC Package article.

The following steps describe the basic steps performed by the data-level application deployment wizard.

  1. DAC package selection.
  2. Check package contents.
  3. Configure database deployment properties with a SQL database.
  4. Deploy package.


You can refuse to use the wizard. Instead, you can use PowerShell with the dacstore .install () method to transfer the schema to the SQL database.

Resources

BACPAC Data Tier Applications


The data tier application is a standalone unit for the development, deployment, and management of data tier objects. DAC allows data-level application developers and database administrators to package Microsoft SQL Server objects, including database objects and instance objects, into a single entity called a DAC package (DACPAC file). The BACPAC format is an extension of the DACPAC format and includes, along with the standard content of the DACPAC file, a metadata file and table data encoded with JavaScript Object Notation (JSON). You can package a SQL Server database into a BACPAC file and use it to migrate a database to a SQL database.

Note. DACPAC and BACPAC share certain similarities, but are intended for use in completely different scenarios. The DACPAC package is focused on writing and deploying a circuit. It is used primarily for deployment in the development, testing and production environment. The BACPAC package is focused on writing schema and data. It is the logical equivalent of a database backup and cannot be used to update existing databases. BACPAC is used to move a database from one server to another (or to a SQL database), and also to archive an existing database in an open format.

Currently, the SQL database import and export service is available as an open CTP version. With it, you can directly import or export BACPAC files between a SQL database and a Windows Azure blob storage. The SQL Database Import and Export service provides several public REST endpoints for sending requests.

The Windows Azure Platform Management Portal has an interface to invoke the SQL database import and export service.

clip_image005

Currently, SQL Server Management Studio does not support the export database feature to a BACPAC file. You can use the DAC API to import and export data.

The SQL DAC Project Examples show how to use the data-level application platform API to migrate databases from SQL Server to SQL database. The package contains two command line utilities and their source code.

DAC client import and export tools are used to export and import BACPAC files.

The DAC Import and Export Client Client is used to invoke the SQL Database Import and Export Service, which allows you to import and export BACPAC files between the Windows Azure BLOLB storage and the SQL database.


You can also copy the BACPAC file to Windows Azure blob storage using a Microsoft product, code-named Data Transfer . For more information, see the Microsoft product section, code-named Data Transfer.

Note. Currently, the ability to import and export data to a SQL database using the data-tier application platform is available only as CodePlex examples. These tools are only supported by the community.

Installation and use
This section discusses the use of client tools in the SQL DAC Examples project to migrate a database from SQL Server to a SQL database.

The SQL DAC Project of Examples can be downloaded from the CodePlex website. To run the sample on a computer, you need to install a data-level application platform .

Before you can use the database migration tools, you must create a target SQL database. When using these funds, migration occurs in two stages.

Export SQL Server Database
Suppose there is a database running SQL Server 2008 R2 with integrated secure access. The database can be exported to a BACPAC file by calling the sample EXE with the following arguments:

DacCli.exe -s serverName -d databaseName -f C: \ filePath \ exportFileName.bacpac -x -e

Import Package to SQL Database
The exported file can be imported into the SQL database using the following arguments:

DacCli.exe -s serverName.database.windows.net -d databaseName -f C: \ filePath \ exportFileName.bacpac -i -u userName -p password

Resources

Script Wizard


The Script Wizard allows you to create Transact-SQL scripts for the SQL Server database and related objects in the selected database. Using these scripts, you can transfer the schema and data to the SQL database.

Installation and use
The Script Wizard is included with SQL Server 2008 R2. The wizard can be run from SQL Server Management Studio 2008 R2. The following screenshot shows the launch of the wizard.

clip_image006

The following steps describe the main steps performed by the wizard.

  1. Select objects for export.
  2. Setting the script parameters. You can save the script to a file, clipboard, new request window, or publish it to a website.
  3. Setting advanced script options.
    By default, the script is created for an isolated instance of SQL Server. To change the configuration, you need to click the Advanced button in the Set Scripting Options dialog box, and then set the Script for the database engine type property to SQL Database .

    clip_image007



    You can assign one of the following values to the Types of data to script property: Types of data to script : Schema only ( Data only ), Data only ( Data only ), Schema and data .

The created script can be modified before its execution in the SQL database.

Resources

Bcp utility


The bcp utility is a command-line tool for high-level bulk data sending to SQL Server or SQL database. This program is not a migration tool. It does not retrieve or create a schema. First, you must transfer the schema to the SQL database using one of the schema migration tools.

Note. The bcp utility can be used to back up and restore data in an SQL database.

Note. The SQL Database Migration Wizard uses bcp.

Installation and use

The bcp program is included with SQL Server. The version that comes with SQL Server 2008 R2 is fully supported by the SQL database.

When using the bcp program, migration occurs in two stages.

Export data to data file

To export data from a SQL Server database, run the following statement on the command line:

bcp tableName out C: \ filePath \ exportFileName.dat –S serverName –T –n -q

The out parameter means copying data from SQL Server. The -n option is designed to perform a bulk copy operation using its own database data types. The -q option is used to execute the SET QUOTED_IDENTIFIERS ON statement when interacting between bcp and an instance of SQL Server.

Import data file to SQL database

To import data into the SQL database, you must first create a schema in the target database, and then run the bcp program on the command line:

Bcp tableName in c: \ filePath \ exportFileName.dat –n –U userName @ serverName –S tcp: serverName.database.windows.net –P password –b batchSize

The –b parameter specifies the number of rows in each packet of imported data. Each package is imported and before confirmation is recorded in the log as a separate import operation of the entire package. To reduce the number of disconnections to the SQL database during the migration, it is recommended to optimize the size of the package.

The following are practical guidelines for using bcp to transfer large amounts of data.

Use the –N option to transfer data in basic mode. In this case, there is no need to convert the data type.

Use the –b parameter to specify the batch size. By default, all lines in the data file are imported as one package. If a transaction fails, only inserts from the current batch are rolled back.

Use the –h "TABLOCK, ORDER (...)" parameter. The –h “TABLOCK” parameter indicates that for the duration of the bulk load operation a table level lock is required for a bulk update. Otherwise, blocking is performed at the row level. Using this parameter allows to reduce the number of conflicts when locking in the table. The –h “ORDER (...)” parameter defines the sorting order of the data in the file. , .

–F –L . .

Resources

SQL


SQL — SQL Server 2005/2008 SQL. .

SQL . , , , SQL . SQL , . SQL bcp. , , . bcp .

Note. SQL — , .



SQL http://sqlazuremw.codeplex.com . SQLAzureMW.exe. .

clip_image008

, .

  1. , .
  2. , .
  3. , .
  4. . .
  5. . SQL.
  6. .

Resources

SQL Server Integration Services


SQL Server (Server Integration Services, SSIS) . , . . SSIS SQL. SQL Server 2008 R2 SQL Windows Azure.

SSIS . , SQL Server.


SQL SSIS SQL Server 2008 R2 ADO.NET. ADO.NET SQL. ADO.NET SQL. SQL Windows Azure OLEDB .

ADO.NET SQL.

clip_image009

, . , , .

ADO.NET Use Bulk Insert when possible ( ). .

— . « » SSIS. ADO .Net, Use Bulk Insert when possible.

Resources

SQL Server


SQL Server — SQL Server Integration Service . , , , . SSIS .

:


SQL Server . — DAC.

Note. 64- 64- SQL Server (DTSWizard.exe). , Access Excel, 32- . 32- . 32- , Client Tools ( ) Business Intelligence Development Studio.


SQL Server 2008 R2 , SQL Server SQL. .

Start () All Programs ( ), Microsoft SQL Server 2008 , Import and Export Data ( ).

Business Intelligence Development Studio, Solution Explorer ( ) SSIS Packages ( SSIS), SSIS Import and Export Wizard ( SSIS).

Project () Business Intelligence Development Studio SSIS Import and Export Wizard .

SQL Server Management Studio Database Engine ( ), Databases ( ), Tasks (), Import Data ( ) Export data ( ).

DTSWizard.exe, C:\Program Files\Microsoft SQL Server\100\DTS\Binn.

.
, . , . SQL .NET Framework Data Provider for SQLServer ( .NET Framework SQLServer).

clip_image010

. . .

, SQL Server, SQL Server Business Intelligence (BI) Development Studio.

Note. BI Development Studio, Integration Services.

Resources

Microsoft Database Transfer


Microsoft Database Transfer — SQL BLOB- Windows Azure. BLOB- Windows Azure. , CSV Microsoft Excel (XLSX), SQL. , SQL, .

Using
- https://web.datatransfer.azure.com/ . , , .

SQL .


Resources


SQL Server


SQL Server Migration Assistant (SSMA) — , Oracle, Sybase, MySQL Microsoft Access SQL SQL Server. SSMA , , SQL, .


SSMA . , SQL Server. :


SSMS Windows. SSMA , . .

SSMA Access .

  1. . SQL Migrate To ( ).
  2. Access.
  3. Access .
  4. SQL.
  5. . Access SQL, Access SQL. Access : Access , , SQL.
  6. .
  7. SQL.
  8. Access.

Resources

Source: https://habr.com/ru/post/155921/

More articles:


All Articles