Monday, May 09, 2011

Migrating SQL Server 2008 R2 database to SQL Azure

I was tasked with taking a pre-existing database and migrating it to SQL Azure.  The reason I needed a SQL Azure database was because I am working on a project that has 2 developers, one located in Chicago and one (me) in Indy, and we needed to make sure we were using the same database at all times during development.  And no, we do not have a network.

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
clip_image001
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.
clip_image003
d. On the Set Scripting Options screen, press the Advanced Button.
clip_image005
e. Under the General Options, select “Script for the database engine type” and select “SQL Azure Database”
clip_image007
f. Under the Table/View Options, select “Script Indexes” and change the value to “True”
clip_image008
g. Select OK.
h. On the Set Scripting Options screen, change the output to be “Save to New Query window”.
clip_image010
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.
clip_image011
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>
clip_image012
d. Execute the query
clip_image013
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
clip_image014
g. In a new Query window on the SQL Azure database, run the following statements:
            i. EXEC SetForeignKeyEnabledStatus 0
                      ii. 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
clip_image015
b. On the Choose a Data Source screen, select the on-premise database and then select Next:
clip_image016
c. On the choose destination, select “.Net Framework Data Provider for SqlServer” from the Destination drop down box.
clip_image017
d. Under the Security section, change Encrypt to “True”
clip_image018
e. Enter your password
f. Change TrustServerCertificate to “True”
clip_image019
g. Enter your username as <username>@<sql azure server fully qualified DNS name>
clip_image020
h. Under Source, set the Data Source to the <SQL Azure Sever Fully Qualified DNS Name>
clip_image021
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.
clip_image022
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.
clip_image023
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 1
7) 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

Thursday, January 20, 2011

MPS Partners BizTalk presentations/whitepapers

My company took quite a look at BizTalk and the cloud (Azure).  My manager, Chris Kabat, and I put together a demo for Tech Ed 2010 that shows how BizTalk can be used to connect SAP with a web application using Azure:  http://www.msteched.com/2010/NorthAmerica/ASI305

Chris also did a presentation for Channel 9 on using Windows Azure:  http://channel9.msdn.com/Shows/Inside+Out/Building-on-Azure-MPS-Partners

Lastly, Chris and another co-worker, Naresh Koka, created a whitepaper on SAP and BizTalk that can be found here:  http://msdn.microsoft.com/en-us/library/ff458152(BTS.10).aspx 

Its been a busy year for us - though mainly for Chris :).