Archive

Posts Tagged ‘SQL Azure Database Copy’

DacImportExportCli : Import / Export Data from SQL Azure to SQL Server (vice versa)

May 11, 2011 12 comments

SQL Azure Database Import / Export v 2.0 is a command line interface to create DacPac for importing / exporting data from SQL Azure to SQL Server and vice versa.

Its available for download from https://www.sqlazurelabs.com/DacImportExportCli.zip (Note: You need to register for SQL Azure Labs for downloading beta programs)

This tool is built upon SQL Server “Denali”

It supports the following versions of SQL Servers

1. SQL Azure

2. SQL Server “Denali”

3. SQL Server 2008 SP1 or above / 2008 R2

4. SQL Server 2005 SP4 and above

5. SQL Server 2000 (Only Export is supported, Import is not supported)

To install and run this, you need the following list of components

1. .Net Framework 4.0

2. SQLSysClrTypes.msi – https://www.sqlazurelabs.com/SQLSysClrTypes.msi

3. SharedManagementObjects.msi – https://www.sqlazurelabs.com/SharedManagementObjects.msi

4. DACFramework.msi – https://www.sqlazurelabs.com/DACFramework.msi

5. SQLDom.msi – https://www.sqlazurelabs.com/SqlDom.msi

6. TSQLLanguageService.msi – https://www.sqlazurelabs.com/TSqlLanguageService.msi

After installing the prerequisites, extract the DacImportExportCli.zip to any directory ex. C:\DacImportExportCLI

Parameters Supported

-s – Server name

-d – Database name

-e – Integrated Authentication (For on-premise SQL Server)

-u – SQL User Name

-p – Password for SQL User name

-x – Export Schema and Data to bacpac file

-i – Import Schema and Data from bacpac file

-f – bacpac file , can be used as input and out based on –x or -i

-edition (Used for SQL Azure, Supported Values “business” or “web”)

-size – specify the Size of database in Gb ex. 1 (Used for SQL Azure to specify database size limit)

To Export Data and Schema

DacImportExportCli.exe –s ServerA.database.windows.net –d Northwind_DB –u dbuser –p password –f C:\DacImportExportCli\Northwind_DB_Backup.bacpac –x

image

To Import Data and Schema

DacImportExportCli.exe –s ServerB.database.windows.net –d Northwind_DB –u dbuser –p password –f C:\DacImportExportCli\Northwind_DB_Backup.bacpac –i

DacImportExportCli.exe –s ServerB.database.windows.net –d Northwind_DB –u dbuser –p password –f C:\DacImportExportCli\Northwind_DB_Backup.bacpac –i –edition web –size 1

image

To Remove a Database created using DAC Import

DacImportExportCli.exe –s ServerB.database.windows.net –drop Northwind_DB –u dbuser –p password

image

If you manually dropped a database using “DROP DATABASE” command for the database created using bacpac, the bacpac registration will still be available in server, you so you need to use –drop option to clear the registration, before reimporting it again 

Advantages:

1. Helps automating backup and restore process for SQL Azure

2. Helps automatic synchronizing between SQL Azure and SQL Server (On Premise) easily

Disadvantages:

1. The exported bacpac file contains the schema and data, not the log files, so it’s a not a real backup like the regular SQL Server backup file

For more information about DAC read the below white paper http://msdn.microsoft.com/en-us/library/ff381683(SQL.100).aspx 

Notes:

Do not install this if you have already installed SQL Server “Denali” CTP1

Synchronize or Replicate Databases in SQL Azure across Servers

May 9, 2011 Leave a comment

SQL Azure Data Sync is an incubation project in SQL Azure Labs, which provides Hub – Member model based to synchronize databases from one SQL Azure server to another. You can replicate data from one Hub server to multiple member servers.

You can sign up for free beta account in http://www.sqlazurelabs.com/

In this post, we will see how to use SQL Azure Data Sync to synchronize Northwind_DB from Server A to Server B

Step 1 : Once signed up and setup your account, click on “SQL Azure Data Sync” tab

image

Step 2: Click “Add New” to create a new Synchronization group

image

Enter Sync Group Name and click Next

Step 3: Click on “Register New Server” to add Hub Server

image

Repeat Step 3 to add Member Server

Step 4: Select the Hub Server and Member Server to participate in Sync Group and then select the database as well

image

Once defined the servers and database, click “Next” to proceed

For one Sync Group job, you can have only one Hub server, but you can define multiple jobs to use the Member server as Hub Server in another job

Step 5: Select the tables you want to Sync and click “Finish”

image

Now you have successfully defined the Sync Group job

image

Now you can manually run the Sync for first time by clicking on “Sync Now” button

To automatically setup the Sync job to run on specific schedules, click on “Schedule Sync” option

image

You can setup the job, to run Daily at specific time or Monthly on specific day and time, or Hourly or Weekly on specified days at specified time, once selected your preferred schedule, click “Ok” to proceed

If you double click on the “Sync Demo Group”, you can view the details and log of the job

image

Click “View Log” to see detailed log

Starting new job. Job ID = 2ccec979-3848-48f5-b0f6-73a90a8ae1d2

Retrieved ScopeName as 625dec91-cd29-465c-98de-e1fe4be221b9

Retrieved Hubendpoint as SERVERA.database.windows.net,Northwind_DB,625dec91-cd29-465c-98de-e1fe4be221b9

Retrieving DbSyncScopeDescription from Hub

Checking to see if Scope 625dec91-cd29-465c-98de-e1fe4be221b9 exists in endpoint SERVERB.database.windows.net,Northwind_DB

    Scope doesnt exist. Provisioning server.

Synchronizing Endpoint SERVERB.database.windows.net,Northwind_DB ==> HUB. Conflict Endpoint wins
    Total Changes Transferred = 0, Total Changes Failed = 0.
    Sync time (in seconds): 5.9.

Synchronizing HUB ==> Endpoint SERVERB.database.windows.net,Northwind_DB. Conflict HUB wins
    Total Changes Transfered = 217, Total Changes Failed = 0.
    Sync time (in seconds): 6.2.

I hope you all find this information useful. Please don’t use this yet for production, since its an incubation project.

How to Backup Azure Database within SQL Azure

May 3, 2011 2 comments

Currently there is no built in Backup option to backup SQL Azure databases from Management Studio

image

You also can’t use BACKUP DATABASE command

image

There is a new option to create a copy of your database quickly from SQL Azure within the same server using

CREATE DATABASE <TargetDB> AS COPY OF <SourceDB>

Ex.

CREATE DATABASE NorthWind_DB_Copy AS COPY OF Northwind_DB;

By running this SQL Command in Master Database, you can create a Copy of NorthWind_DB to Northwind_DB_Copy

Using the following query you can get the status of Copy Status

– Query to fetch the state of the new database

SELECT

           DATABASE_ID, NAME, STATE, STATE_DESC

FROM SYS.DATABASES

WHERE NAME = ‘NorthWind_DB_Copy’

image

 

– Get the Status of Database Copying

SELECT * FROM SYS.DM_DATABASE_COPIES 

        WHERE DATABASE_ID = DB_ID(‘NorthWind_DB_Copy’)

image

 

Once the copying is complete, Database will be Online and available for access

SELECT

DATABASE_ID, NAME, STATE, STATE_DESC

FROM SYS.DATABASES

WHERE NAME = ‘NorthWind_DB_Copy’

image

Follow

Get every new post delivered to your Inbox.

Join 111 other followers