1. Fix local database so that it can be migrated to SQL Azure
SQL Azure requires all the tables to have clustered index. Create clustered indexes & primary keys in local database by running below script whichever is appropriate.
CREATE CLUSTERED INDEX INDEX_NAME ON [dbo].[TABLE_NAME]([PRIMARY KEY]);
OR
ALTER TABLE dbo. TABLE_NAME ADD Id INT IDENTITY PRIMARY KEY CLUSTERED (Id);
OR
ALTER TABLE TABLE_NAME WITH NOCHECK
ADD CONSTRAINT PK_ TABLENAME _ PRIMARY KEY PRIMARY KEY CLUSTERED
(PRIMARY KEY);
2. Connect to SQL azure database from SQL server 2008 R2 Client
a. Server Name: ******.database.windows.net
b. User Id: *****@******.database.windows.net
c. Password: *******
3. Create schema in SQL Azure.
CREATE SCHEMA [SCHEMA_NAME] AUTHORIZATION [USER_NAME]
GO
4. Generate scripts from SQL server 2008 R2 client
Use Generate scripts task in SQL server 2008 R2 to generate scripts separately for each objects.
1. While generating scripts for table objects set “script indexes” to True
2. While generating scripts for table objects set “script Triggers” to True
3. Once script is generated for stored procedure objects –
· In all stored procedure Change any other schema names. To dbo. or your own schema name.
5. Executing Schema Objects scripts in the following order
· Table object Scripts
· View Object scripts
· User Defined Functions scripts
· Stored Procedures scripts
6. Generate In/Out BCP commands into a batch file
In this section we get the BCP commands in a batch file for getting data out of local database and commands for sending data to SQL azure.
SET NOCOUNT ON
DECLARE @DestServer nvarchar(max)
DECLARE @DestDatabase nvarchar(max)
DECLARE @DestLogin nvarchar(max)
DECLARE @DestPassword nvarchar(max)
DECLARE @SrcServer nvarchar(max)
DECLARE @SrcDatabase nvarchar(max)
DECLARE @SrcLogin nvarchar(max)
DECLARE @SrcPassword nvarchar(max)
-- SQL Azure Server
SET @DestServer = '*******.database.windows.net'
SET @DestDatabase = 'Database name'
SET @DestLogin = 'username@*******.database.windows.net'
SET @DestPassword = '*******'
-- SQL Server
SET @SrcServer = 'Local db server name'
SET @SrcDatabase = 'local DB name'
SET @SrcLogin = 'user id'
SET @SrcPassword = '*******'
PRINT 'echo %DATE% %TIME% > bcp.log'
PRINT ''
---------------------------------------------------
DECLARE @Schema nvarchar(max)
DECLARE @Table nvarchar(max)
DECLARE table_cursor CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables
WHERE Table_Type = 'BASE TABLE'
OPEN table_cursor;
-- Perform the first fetch.
FETCH NEXT FROM table_cursor INTO @Schema, @Table;
-- Check @@FETCH_STATUS to see if there are any
-- more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'REM Download ' + @Schema + '.' + @Table
PRINT 'echo bcp.exe ' + @SrcDatabase + '.' + @Schema + '.' +
@Table + ' out ' +
@Schema + '.' + @Table + '.bcp -q -h "TABLOCK" -N -CRAW -S' +
@SrcServer + ' >> bcp.log'
PRINT 'bcp.exe ' + @SrcDatabase + '.' + @Schema + '.' + @Table +
' out ' + @Schema + '.' + @Table +
'.bcp -q -h "TABLOCK" -N -CRAW -S' + @SrcServer +
' -U' + @SrcLogin + ' -P' + @SrcPassword + ' >> bcp.log'
PRINT ''
PRINT 'REM Upload ' + @Schema + '.' + @Table
PRINT 'echo bcp.exe ' + @DestDatabase + '.' + @Schema + '.' +
@Table + ' in ' + @Schema + '.' + @Table +
'.bcp -q -h "TABLOCK" -N -E -CRAW -S' + @DestServer +
' >> bcp.log'
PRINT 'bcp.exe ' + @DestDatabase + '.' + @Schema + '.' +
@Table + ' in ' + @Schema + '.' + @Table +
'.bcp -q -h "TABLOCK" -N -E -CRAW -S' + @DestServer +
' -U' + @DestLogin + ' -P' + @DestPassword + ' >> bcp.log'
PRINT ''
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor INTO @Schema, @Table;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
1. Run the above scripts on SQL server 2008 R2 client query window. Copy the generated code into . a batch file.
2. Open Command prompt with an Administrator mode go to the folder and run the batch file.
7. Summary
Hope the above steps can help you migrate local database server to Azure.
No comments:
Post a Comment