Migrating databases to SQL Azure.

Hello, everyone! . It will be pretty much of screenshots, so gently traffic:). As we all know. SQL Azure. is based on. Microsoft SQL Server. But despite this, most developers having some difficulties with the process of migrating an existing database in the cloud. In today's publication, I will tell you how this can be done with a pretty brute force, namely the BCP command utility.

So, to begin with, we naturally need a blank database in the cloud. To do this, we proceed to the portal developer in Section. Database. click on the project, which is associated with an active subscription, after which the selected server will create a new database.

In the resulting dialog, enter the name of the new framework and its maximum size ( fast enough for me to head to the 1 GB version). After an empty database is created we need to add an exception to the firewall rules, so that he missed incoming requests. To do this, go to the site in the configuration section and add a firewall exception ( for simplicity, I'm allowed to connect to the entire spectrum of possible incoming IP-addresses ).

Generating DDL- scripts.

As soon as we finish all the preparations on the site we need to generate the DDL- scripts to transfer (remember DDL stands for ... Data Definition Language. ... To generate first need to connect in SSMS to the selected database and in the context menu select. >Tasks Generate Scripts. then select the first three points as shown in the screenshot.

* Unfortunately at the moment SQL Azure does not support DDL triggers.

Next we need to disclose in the same sub- window. Tables. and to choose where all the necessary tables with clustered indexes (this requirement. SQL Azure. ). Once all the necessary tables are selected, proceed to the next step of the dialogue and there is press the button. Advanced.


By clicking opens another window with the advanced settings in which the option. >General Script for database. set to ... SQL Azure Database. ... >Table / View Options Script Indexes. in the value of. True.

The final step is to select the storage location of the resulting SQL-code and review. Summary. page. As soon as our scripts to create database objects will be ready we will need to change the connection in SSMS and connect to the cloud base is.

The new connection will fulfill pre-generated scripts, and upon completion we obtain a cloud database that contains all the necessary facilities.

Create procedures to support data migration.

As soon as our structure will be restored, we will create two auxiliary stored procedures to simplify the data migration. Hranimka. SetForeignKeyEnabledStatus. will be used to disable foreign key constraints during the data transfer. This allows us to load the data tables in any order without primary / foreign key constraints. This procedure is called as before ( to disable the checks) and after data transfer ( to return all konstreyntov ). Hranimka. SetIndexEnabledStatus. will be used to disable all non-clustered indexes during data transfer. This will speed up the process of loading data. This procedure will also be run before and after transfer. The code listing above procedures presented below:.

IF OBJECT_ID ('[dbo]. [SetForeignKeyEnabledStatus] ',' P ') IS NOT NULL. 
DROP PROCEDURE [dbo]. [SetForeignKeyEnabledStatus];. 
GO. 
CREATE PROCEDURE [dbo]. [SetForeignKeyEnabledStatus]. 
(. 
@ enabled bit. 
). 
AS. 
BEGIN. 
DECLARE. 
@ schema_name sysname,. 
@ table_name sysname,. 
@ fk_constraint_name sysname,. 
@ cmd_txt varchar ( 8000);. 
DECLARE fk_cursor CURSOR FOR. 
SELECT. 
sc. name AS schema_name,. 
so. name AS table_name,. 
so2. name AS fk_constraint_name. 
FROM. 
sys. objects so. 
inner join sys. schemas sc ON so. schema_id = sc. schema_id. 
inner join sys. foreign_key_columns fk ON so. object_id = fk. parent_object_id. 
inner join sys. objects so2 ON so2. object_id = fk. constraint_object_id. 
WHERE. 
so. type = 'U';. 
OPEN fk_cursor;. 
FETCH NEXT FROM fk_cursor INTO. 
@ schema_name,. 
@ table_name,. 
@ fk_constraint_name;. 
WHILE @ @ FETCH_STATUS = 0. 
BEGIN. 
IF @ enabled = 0. 
SET @ cmd_txt = 'ALTER TABLE [' @ schema_name ']. ['@ Table_name'] NOCHECK CONSTRAINT ['@ fk_constraint_name']; '. 
ELSE. 
SET @ cmd_txt = 'ALTER TABLE [' @ schema_name ']. ['@ Table_name'] WITH CHECK CHECK CONSTRAINT ['@ fk_constraint_name']; ';. 
PRINT @ cmd_txt;. 
EXECUTE (@ cmd_txt);. 
FETCH NEXT FROM fk_cursor INTO. 
@ schema_name,. 
@ table_name,. 
@ fk_constraint_name;. 
END;. 
CLOSE fk_cursor;. 
DEALLOCATE fk_cursor;. 
END;. 
GO. 
IF OBJECT_ID ('[dbo]. [SetIndexEnabledStatus] ',' P ') IS NOT NULL. 
DROP PROCEDURE [dbo]. [SetIndexEnabledStatus];. 
GO. 
CREATE PROCEDURE [dbo]. [SetIndexEnabledStatus]. 
(. 
@ enabled bit. 
). 
AS. 
BEGIN. 
DECLARE. 
@ schema_name sysname,. 
@ table_name sysname,. 
@ index_name sysname,. 
@ cmd_txt varchar ( 8000);. 
DECLARE idx_cursor CURSOR FOR. 
SELECT. 
sc. name AS schema_name,. 
so. name AS table_name,. 
si. name AS index_name. 
FROM. 
sys. objects so. 
inner join sys. schemas sc ON so. schema_id = sc. schema_id. 
inner join sys. indexes si ON so. object_id = si. object_id. 
WHERE. 
so. type = 'U'. 
>AND si. index_id 1;. 
OPEN idx_cursor;. 
FETCH NEXT FROM idx_cursor INTO. 
@ schema_name,. 
@ table_name,. 
@ index_name;. 
WHILE @ @ FETCH_STATUS = 0. 
BEGIN. 
IF @ enabled = 0. 
SET @ cmd_txt = 'ALTER INDEX [' @ index_name '] ON [' @ schema_name ']. ['@ Table_name'] DISABLE; '. 
ELSE. 
SET @ cmd_txt = 'ALTER INDEX [' @ index_name '] ON [' @ schema_name ']. ['@ Table_name'] REBUILD; '. 
PRINT @ cmd_txt;. 
EXECUTE (@ cmd_txt);. 
FETCH NEXT FROM idx_cursor INTO. 
@ schema_name,. 
@ table_name,. 
@ index_name;. 
END;. 
CLOSE idx_cursor;. 
DEALLOCATE idx_cursor;. 
END;. 
GO. 

These two stored procedures sorted out objects ( foreign keys and indexes ) from the system view. For each object dynamically formed a team to turn it on or off.

Use BCP to export / import data source database.

To export data, use the command utility ... Bulk copy program. ... Let's look at the format used by the command:.

@ ECHO OFF. 

SET SourceSqlServerName = [REPLACE_SERVER]. 
SET SourceSqlDbName = [MY_SOURCE_DB_NAME]. 


SET TableName = [dbo. MY_FIRST_TABLE]. 

ECHO ******************************. 
ECHO Migrating data for% TableName%. 
ECHO ******************************. 

bcp% SourceSqlDbName%. % TableName% out% TableName%. dat-n-S% SourceSqlServerName%-T. 
IF ERRORLEVEL 1 GOTO ABORT. 

As we can see, we need to register in the batch file server address, the name of the original database and in turn to overtake all the tables ( to save space, I exported a table [. dbo. MY_FIRST_TABLE]. ). At the end of the script will create the necessary dat- files for each prescription in the script table. All that's left - is to load our data into the target database, again using the BCP. Let's look at a script that helps in this simple problem:.

@ ECHO OFF. 

SET DestSqlServerName = [REPLACE_SQLAZURE_SERVER]. 
SET DestSqlDbName = [REPLACE_AZURE_DB_NAME]. 
SET DestSqlUserName = [REPLACE_USERNAME]. 
SET DestSqlUserPassword = [REPLACE_PASSWORD]. 


SET TableName = [dbo. MY_FIRST_TABLE]. 

ECHO ******************************. 
ECHO Migrating data for% TableName%. 
ECHO ******************************. 

bcp % YustSqlDbName. % TableName% in% TableName%. dat-n-S YustSqlServerName UYustSqlUserName % % %-E PYustSqlUserPassword. 
IF ERRORLEVEL 1 GOTO ABORT. 

As you can see the import process uses pre-prepared dat- files. Be very careful with the option -E. If we forget this option, then IDENTITY- fields are generated by the new values ​​instead of values ​​of the dat- file. In principle, this process of migrating a database in SQL Azure can be considered complete. And do not forget to run previously created auxiliary procedures. Before importing data, you must run hranimki like this:.

EXECUTE [dbo]. [SetForeignKeyEnabledStatus] 0. 
GO. 
EXECUTE [dbo]. [SetIndexEnabledStatus] 0. 
GO. 

After you import like this:.

EXECUTE [dbo]. [SetForeignKeyEnabledStatus] 1. 
GO. 
EXECUTE [dbo]. [SetIndexEnabledStatus] 1. 
GO. 

Thank you for your attention! .
Blogpost sources:. http://msdn. microsoft. com/en-us/gg282151. ,. http://msdn. microsoft. com/en-us/library/windowsazure/ee730904. aspx. ,. http://www. sqlazureperformance. com/2010/migrating-sql-server-database-to-sql-azure /.