Thanks to the fact that all tables must have clustered indexes, it took me quite a bit longer to migrate the data of the database than I had anticipated. To help others, and also to remind myself, I am posting the steps I took below.
First, here is the error I got when I tried to use the Data Export method in SQL Server 2008 R2:
Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. (SQL Server Import and Export Wizard)
Since the database was already created I did not want to go through all 60 some tables and change the indexes. The following steps are what I had to do to get the data into the SQL Azure database.
1) Create a SQL Azure Server/Database.
a. I'm not going to go into details on this, for more info see: http://blog.sqlauthority.com/2011/04/27/sql-server-introduction-to-sql-azure-creating-database-and-connecting-database/2) Generate Database Script.
a. Open SQL Server 2008 R2 and open your on-premise database. Right click on your 2008 database and select Tasks->Generate Scripts
b. On the Introduction Screen, select Next.
c. On the Choose Objects screen select “Select specified objects” and then select the objects you want to migrate to SQL Azure.
d. On the Set Scripting Options screen, press the Advanced Button.
e. Under the General Options, select “Script for the database engine type” and select “SQL Azure Database”
f. Under the Table/View Options, select “Script Indexes” and change the value to “True”
g. Select OK.
h. On the Set Scripting Options screen, change the output to be “Save to New Query window”.
i. Press Next.
j. On the summary screen press Next.
k. Make sure all object were successful. Press Finish.3) Create empty tables in SQL Azure.
a. In SQL Server 2008 R2 Management Studio, select the query that was created in step 2.
b. Right click in the query window and select Connection->Change connection.
c. The SQL Server connection window will appear. Connect to your SQL Azure server. Your login is the <username>@<SQL Azure Server Fully Qualified DNS Name>
d. Execute the query
4) Turn off Foreign Keys and Indexes.
a. In order to avoid the error mentioned above and to allow data to be entered in any order, the foreign keys and indexes must be removed.
b. There are stored procedures that can be run to achieve this.
c. In SQL Server Management Studio, connect to you SQL Azure database.
d. Open a new Query and paste in the following code to create the stored procedures: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
e. Execute the query.
f. Open the Stored Procedures objects and verify that the 2 stored procedures exists
g. In a new Query window on the SQL Azure database, run the following statements:
i. EXEC SetForeignKeyEnabledStatus 0ii. EXEC SetIndexEnabledStatus 0
5) Export Data to SQL Azure
a. Open your on-premise SQL Server 2008 R2 database. Right click and select Tasks->Export Data
b. On the Choose a Data Source screen, select the on-premise database and then select Next:
c. On the choose destination, select “.Net Framework Data Provider for SqlServer” from the Destination drop down box.
d. Under the Security section, change Encrypt to “True”
e. Enter your password
f. Change TrustServerCertificate to “True”
g. Enter your username as <username>@<sql azure server fully qualified DNS name>
h. Under Source, set the Data Source to the <SQL Azure Sever Fully Qualified DNS Name>
i. Enter the database in the Initial Catalog. The available databases should be populated in the drop down box.
j. Select Next
k. On the Specify Table Copy or Query, Select Copy Data from one or more tables or views and press Next.
l. On the Select Source tables and views screen, select the tables and views to copy. Press Next.
m. On the Save and Run package select Run Immediately. Press Finish.
n. Once the process has run, make sure no error occurred.
i. I did get the following error the first time I ran the process:
Warning 0x80049304: Data Flow Task 1: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.To fix, I closed all connections to my SQL Azure database, except the export wizard.
6) Turn indexes and foreign keys back on.
a. Run the following stored procedures to turn the indexes and add the foreign keys back:
i. EXEC SetIndexEnabledStatus 1
ii. EXEC SetForeignKeyEnabledStatus 17) Add User/Login
a. While not part of adding data, the last step was needed to make sure both developers had access.
b. Open a new Query window in the SQL Server Management Studio, pointing to the SQL Azure master database.
c. Copy the following code, changing the username and password values.
CREATE LOGIN <username>WITH PASSWORD = '<password>'
GO
d. Open a new Query window in the SQL Server Management Studio, pointing to the SQL Azure database.CREATE USER <username>
FOR LOGIN <username>
WITH DEFAULT_SCHEMA = dbo
GO
-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<username>'
GO
Posts
3 comments:
Thank you so much for this posting! Following these steps lead to my success importing data into Azure's SQL Database. I had looked in many locations, however, your steps were the most precise, clearest, and solved the issue of importing where indexes and foreign keys existed. Thanks for taking the time to make this available.
Muchas gracias por esto, muchas gracias :). ya no sabia que hacer.
This is a great find! Another tip to avoid issue is to manually set the tables on Azure that you are exporting to has a primary key assign.
Post a Comment