Copying Databases in SQL Azure

Link:http://msdn.microsoft.com/en-us/library/windowsazure/ff951624.aspx

Microsoft SQL Azure Database is the relational database service on the Windows Azure platform. Once you have created a database in SQL Azure, you can back up that database by copying it to a new database in SQL Azure. The new database that is created from the copy process is a fully functioning database, independent of the source database once the copy is complete. The new database will have the same edition and maximum size as the source database.

Overview

The database copy feature enables many of the back-up scenarios you may be familiar with for on-premises databases:

• Application data back up: Copy the application database to a new database in order to help protect it from user and application errors. Recover the application database state by renaming the new database with the application database name. Alternatively, recover a specific subset of data from the new database by use of Transact-SQL queries.
• Application development and testing: Copy the production database to a new database that can be used for development and testing.
• Application upgrade: Copy the application database to a new database before major application updates. If a mistake is made during the upgrade, recover the earlier database state by renaming the new database to the application database name.
• Application migration: the database copy feature creates a transactionally consistent copy of the database; you can then perform a data-tier application (DAC) export to create an export file that has both the definitions of the objects in the database and the data from the user tables. This export file can then be imported to another SQL Azure server, or to an instance of the SQL Server Database Engine. For more information, see How to: Export a Data-tier Application (SQL Azure).

Note

Databases created by the copy feature count toward the SQL Azure database limit of 150 databases for each SQL Azure server.

Copying a Database

Databases are copied asynchronously, so a connection to the SQL Azure server is not needed for the full duration of the process. You can copy a database by logging into the master database of the destination server and executing the Transact-SQL CREATE DATABASE statement with the AS COPY OF clause. Then, you can monitor the copy process by using the sys.dm_database_copies and sys.databases views on the destination server.

Same-Server Copying

When you copy a database to make a new database on the same SQL Azure server, the same logins can be used on both databases. The security principal you use to copy the database becomes the database owner (DBO) on the new database when it is created. The following figure illustrates same-server copying:

In this figure, Database1A is copied to a new database, Database1B, on the same SQL Azure server, Server1. The login that copied the database becomes the DBO of Database1B. All database users, their permissions, and their security identifiers (SIDs) from Database1A are copied to Database1B. Because the user SIDs are the same on both databases, the logins from Server1 maintain the same permissions on both databases.

After the copy is complete, Database1B becomes a fully functional, independent database. The logins, users, and permissions of Database1B can be managed independently of Database1A.

Cross-Server Copying

You can also copy a database between two different SQL Azure servers that are in the same sub-region or data center. Because the new database is created on a different SQL Azure server, it is associated with a different master database. All users in the new database maintain the permissions that they had in the source database. The security principal you use to copy the database becomes DBO on the new database when it is created and is assigned a new security identifier (SID). The following figure illustrates cross-server copying:

In this figure, Database1A is copied from Server1 to a new database, Database2A, on a different SQL Azure server, Server2. The login that copied the database becomes the DBO of Database2A. All database users and their permissions (but not their SIDs) from Database1A are copied to Database2A. The logins from Server1 cannot be used with the new database because they are associated with a different SQL Azure server and because the Database2A user SIDs are different from the Database1A user SIDs.

Note

A SQL Azure sub-region may consist of multiple physical clusters. Currently, you cannot copy a database between two different clusters - the copy will return the error, Msg 40532 - Cannot open server “<server name>" requested by the login. The login failed. To determine if a database copy is possible, use a ping command ("ping <server>") on both the source and destination servers to resolve their IP addresses. If the IP addresses are equal then the database copy is possible.

If a database copy is not possible, then do the following:

1. Create two new logical servers in the same sub-region (the servers should receive the same IP address).
2. Perform an offline database migration to move the database to one of these servers. You can use various tools to do this, such as the Import/Export Service. For example, see SQL Azure Import/Export Service or How to: Migrate a Database by Using the Generate Scripts Wizard (SQL Azure Database) for more information.

Now you can perform a database copy between these two servers.

After the copy is complete, the logins, users, and permissions of Database2A can be managed independently of Database1A.

Note   After the cross-server copy process is complete, use the DBO login and the ALTER USER statement to map users in the new database to logins on the new SQL Azure server. For example: ALTER USER userName WITH LOGIN='loginName'. For more information, see ALTER USER (SQL Azure Database).

Permissions

In order to copy a database in SQL Azure, your login requires the following permissions:

• On both servers: The login must have the same login name and password on both SQL Azure servers.
• On the destination server: The login must be a member of the server-level dbmanager role. Note: the server-level principal of your SQL Azure server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in SQL Azure, see Managing Databases and Logins in SQL Azure.
• On the source server: The login must be the DBO of the source database. Only the login that created the source database, the DBO, can copy that database to another database on the same server or on a different server.

When these permission requirements are met, your login can execute the ALTER DATABASE and DROP DATABASE statements against the new database.

Note

Changing the database owner by using the ALTER AUTHORIZATION ON DATABASE statement is not supported by SQL Azure. Like SQL Server, you can create additional users inside the database in SQL Azure and add them to the db_owner database-level role. These additional users can be linked to a login other than the login that created the database initially. However, these additional users cannot perform a database copy.

Timing

The database copy workload will impact the performance of the SQL Azure server(s) involved in the copy process, and may take an extended period of time to complete.

Note

If the database copying fails, restart the copy process when the demand on your source database is low and suspend other workloads on the source database until the copy process completes.

Starting the Copy Process

Start copying the source database with the SQL Azure Database CREATE DATABASE statement. You must be connected to the master database with a login that meets the permission requirements as described in Permissions. To initiate cross-server copying, you must be connected to the master database of the SQL Azure server where the new database will be created: the destination server.

CREATE DATABASE destination_database_name
    AS COPY OF [source_server_name.]source_database_name

Executing this statement initiates the database copy process. If source_server_name is not provided, the source database must be located on the same SQL Azure server that your login is connected to. Because this is an asynchronous process, the CREATE DATABASE statement will return before the database completes copying.

Monitoring a Copy

After the database copy begins, you can query the sys.databases and sys.dm_database_copies views on the master database of the destination server to retrieve more information about the copying progress. The following Transact-SQL example demonstrates how to obtain the copying progress for a new database named Database1B.

-- execute on the master database
-- retrieve state of the new database
select name, state, state_desc from sys.databases
where name = ’Database1B’

-- retrieve copying details
select * from sys.dm_database_copies
where database_id = DB_ID(’Database1B’)

You pay for the databases you have, for the days you have them. If an error occurs during the copy, drop the target database immediately. For more information on the billing details, see Accounts and Billing in SQL Azure. For a list of database copy errors, see Database Copy Errors.

Transactional Consistency

A new database created from the copy process is transactionally consistent with the source database at the point in time when the copy completes.

From a database backup perspective, copying a database in SQL Azure on a recurring schedule is similar to taking full backups of an on-premise SQL Server database. On each recurrence, after the copy completes successfully, you can drop the database that copied from the previous recurrence.

Stopping the Copy Process

If you want to stop the copy process before it completes, you can use the DROP DATABASE statement to drop the destination databases. Dropping the source database will also cause the copy process to be canceled. In order for any of these options to work, use a login that meets the permission requirements as described in Permissions.

After the Copying Completes

When the database copying completes, the new database will enter one of two states, as indicated by the state_desc column of the sys.databases view. If the copying was successful, the new database will enter the Online state. After the new database is Online, it can be managed and used independent of the source database.

If the copy is not successful, the new database will enter the Suspect state. When it is Suspect, the new database is unavailable and cannot be accessed or recovered. In this case, drop the destination database and try copying the source database again.

Note

You specify the edition and maximum size of the database when you create it. Once you have created a database in SQL Azure, you can back up that database by copying it to a new database in SQL Azure. When the copy is complete, the new database will have the same edition and maximum size as the source database. For more information on how to set and change the edition or maximum size of the database in SQL Azure, see Accounts and Billing in SQL Azure.

See Also

Tasks

How to: Back Up Your Database (SQL Azure)
How to: Copy Your Database to a Different Server (SQL Azure)

Reference

CREATE DATABASE (SQL Azure Database)

Concepts

Administration (SQL Azure Database)