SQL azure (9) migrate the local SQL Server database to SQL azure cloud Database SQL azure (3) create an SQL azure server SQL azure (4) Create an SQL az

This chapter describes how to migrate the content of the local SQL Server 2008 R2 database to the SQL azure cloud database.

Before starting this chapter, I suggest you familiarize yourself with the previous chapters:

-SQL azure (iii) Create an SQL azure Server

-SQL azure (4) Create an SQL azure Database

-SQL azure (5) use SQL Server Management studio to connect to SQL azure

 

For better hands-on testing, I hope you can install the following software in advance:

-SQL Server 2008 r2

-SQL Server Management Studio

 

1. Create a new SQL azure Database

1. Open your browser and navigate to https://windows.azure.com/. enter the live ID of the azureservice. Click "Database" on the left.

2. Because we have created the SQL azure server in the Hong Kong data center, we only need to create a new SQL azure database.

3. The new database information includes:

-Database Name: sqlazurefrom2008r2

-Version Web

-Maximum Size: 1 GB

4. Click OK.

5. Click "firewall rules" and select "allow other Windows azure services to access this server"

6. In the "firewall" rule, click "add" to allow a specific IP address to access the SQL azure server I created. This is what I created for convenience.

-Rule name: All IP

-IP Range start value: 0.0.0.0

-IP Range end value: 255.255.255.255

 

Ii. Generate DDL scripts

You can use the SQL Server 2008 R2 service that has been created locally to perform the following migration tasks. I have installed SQL Server 2008 R2 locally and created a database school, including data tables and stored procedures. The T-SQL script can be downloaded here.

1. Start SQL Server managemenet Studio

2. Connect to the local school database.

3. Right-click the school database and choose --> task --> Generate scripts.

4. If the "Introduction" dialog box appears, select "Next"

5. In the "choose objects" dialog box, select "select specific database objects" and select table, views, and store procedures.

(Note: SQL azure requires that all tables have clustered indexes. If the tables in the local SQL Server do not have clustered indexes, they cannot be imported to SQL azure.)

6. Select "select specific database objects" in "choose objects" (because all the tables of the local database school have clustered indexes, we select all the tables)

7. In the "set scripting options" dialog box, select "advanced ".

8. Modify options:

-General --> "script for the database engine type" is set to "SQL azure Database"

-Table/view options --> Script Index is set to "true"

9. In the set scripting Options dialog box, select "Save scripts to a specific location" and select "Save to new query window"

10. In the summary dialog box, select next

11. view the Save or publish scripts dialog box and click Finish.

 

3. Execute the exported T-SQL statement in SQL azure

1. In the previous script generation window, right-click connection --> change connection

2. In the pop-up connect to database engine dialog box, enter

-SQL azure server name

-Logon Username

-Password

3. Click "options" and specify the SQL azure database to be connected in Connection Properties.

4. After connecting to SQL Azure, press F5 (or query --> execute menu option) to execute the script.

We can see that the table and store procedure of the local SQL server have been successfully migrated to SQL azure.