Today we have another “30 minute meal” recipe for Windows Azure. This time we will move a simple database into the cloud. For databases that meet our criteria, this is a “piece of cake”.
I must emphasize we are talking about simple databases: large or complex databases will certainly take more than 30 minutes; some take weeks! Below we are specific about the criteria for an easy migration.
Of course we often think of a database as just one tier of a complete solution, but this is not always the case. Sometimes a database on its own is a valued asset, such as reference data that many consume for different reasons. Even if you do need to migrate a complete solution, this recipe can be of use, because it is often the best approach to migrate the data first and then the application code.
To illustrate the steps of the migration, we will use the AdventureWorksLT database from Microsoft. If you wish to practice using the same data, you can obtain it here.
Recipe: Migrating a Simple SQL Server Database to SQL Azure in 30 Minutes
Databases on the Windows Azure platform are hosted by the SQL Azure Database service, available in sizes from 1-50GB at a cost of $9.99/GB/month plus bandwidth charges. SQL Azure is very similar to SQL Server and uses the same protocol (TDS) and programming model. You can work with SQL Azure databases using both the familiar SQL Server Management Studio tool (2008 R2 edition) and the SQL Azure section of the Windows Azure Management Portal.
Plan
An excellent tool for migrating SQL Server databases to SQL Azure is the SQL Azure Migration Wizard. We will use this tool to analyze, revise, and migrate the schema and data from an on-premise database to a cloud database. Nothing we will do will modify the source database in any way.
There are 5 steps we will perform:
1. Create a SQL Azure Database Server
2. Create a SQL Azure Database
3. Migrate the Database
4. Review SQL Azure Database in SSMS
5. Review SQL Azure Database in Portal
Databases come in all sizes and complexities. The majority of the world’s databases are small in size and simple in nature. Database products come in all sizes and complexities too. Check that you meet the criteria below before using this recipe.
Criteria
Migrations to SQL Azure are smoothest and fastest when the following are true:
• SQL Server Database. Your starting point database should be in SQL Server or SQL Server Express, preferably a recent version like 2008 or 2008 R2. If this is not the case, you can expect a longer migration time and you may have to work through feature differences. If you are migrating from a different database product or a very old version of SQL Server, it is recommended you “stage” your migration by moving your database over to SQL Server 2008 R2 first, and then up into the cloud on SQL Azure.
• Small Database. Database size matters in migrations for several reasons. First of all, there is an upper size limit for a SQL Azure Database (currently 50GB); if your database is larger, then you will need to partition into multiple databases. Second, large data takes a long time to transmit (days perhaps) and you are more likely to be encountering occasional communication failures during upload. If you measure your database size in megabytes, this 30-minute recipe is for you. If you are dealing with gigabytes of data, you can still use this recipe but expect to spend one or more days migrating and you may want to employ the services of an expert.
• Simple Database. A simple database is one that is mostly about the data. Complex databases are those that depend on database server features which may extend the migration time or pose barriers to migration. Use of features like constraints, granular security, or stored procedures are often fine but can sometimes pose complications. Features like Windows authentication, SQL Agent jobs, replication, full text search, XML indexing, distributed transactions, and transparent data encryption are not available yet in SQL Azure.
Ingredients:
1 SQL Server database meeting the above criteria
You Will Need:
• A Windows Azure subscription
• The SQL Azure Migration Wizard.
Directions
Step 1: Create a SQL Azure Database Server
In this step you will create a SQL Azure database server using the Windows Azure management portal.
Note: if you have already created a SQL Azure database server, proceed to Step 2.
1A. In a large bowl.. .er, I mean In a web browser, go to the Windows Azure portal.
1B. Navigate to the Database category on the lower left.
1C. Select a subscription on the upper left.
1D. Click the Create button in the Server toolbar. A dialog appears.
1E. Specify the data center locale where you want to create your database server and click Next.
1F. Now specify a name you would like to use for an Administrator, along with a strong password. Record the administrator name and password for safekeeping. Again click Next.
Note: it’s possible to change the password in the future, but not the admin name.
1G. Finally, you will be prompted to set firewall rules. In order to access the database we will need to add a firewall rule.
A. Check the Allow other Windows Azure services to access this server checkbox.
B.Click Add.
C. Enter a rule name.
D. Enter a starting and ending IP address. You can either specify your own IP (shown in the dialog), or some other range. If you want to allow access for all, you can specify 0.0.0.0 through 255.255.255.255.
E. Click OK and Finish to close the dialogs.
1H. A database server will soon appear in the main area of the Windows Azure portal. You will need to capture the server name, which is auto-generated and cryptic. In our example, it is b7e77waic7. Your name will be different.
So far we have created a database server (It is actually multiple servers, but this single virtual server name fits the SQL Server connection string model). The next step will be to create a database on that server.
Step 2: Create a SQL Azure Database
In this step you will create a database on the database server you created in Step 1.
2A. In the Windows Azure portal, select your database server.
2B. Click the Create button in the Database toolbar. A dialog appears.
2C. Specify (and record) a name for your database, and select an edition/size sufficient for your database. The Web edition offers 1/5GB sizes and the Business edition 10/20/30/40/50GB. Then click OK.
2D. Verify the database was created by finding it and selecting it in the outline at upper left. Notice that in addition to your database the database server also contains a master database.
Now that we have created our cloud database, we can begin the migration.
Step 3: Migrate the Database
In this step you will use the SQL Azure Migration Wizard to migrate your SQL Server database to the SQL Azure database created in Step 2.
3A. Launch the SQL Azure Migration Wizard and select the Analyze and Migrate / SQL Database option. Then click Next.
3B. In the Connect to Server dialog that appears, specify your source database server and authentication credentials as described below. Then click Connect.
• Server name: Here you must specify server-name or server-name\instance-name. If you are running full SQL Server on your local machine and using the default SQL Server instance, just leave the setting to its default of localhost. If you are running SQL Server Express on your local machine, you are most likely using the .\SQLEXPRESS instance.
• Authentication: Your source database may be set up for Windows authentication or SQL authentication. Select the appropriate option. For SQL authentication, also enter your username and password credentials.
• Database. Leave this at its default value of Master DB. This will cause the databases to be listed for you on the next screen.
3C. On the next screen (Select Source), select your database and click Next.
3D. On the Choose Objects dialog, leave the default of Script all database objects and click Next.
3E. On the Script Wizard Summary dialog, make no changes and click Next. When prompted Ready to generate SQL script?, click Yes.
3F. When the Results Summary dialog appears, review the report content fully to see if there are any problems. You may also use the Save button to save a copy of the report.
In the case of the AdventureworksLT sample database we are using to illustrate this recipe, there is a problem noted in the report for the ProductionDescriptionsSchemaCollection table: XML Schema Collections are currently not supported in SQL Azure. This means our source database is using an unsupported feature in this one table. In our case we are unconcerned and decide to proceed without that one table. If you encounter errors, you will need to evaluate how serious they are and what action you should take about them. Note that it is possible in Step 3D earlier to specify which database objects are and are not included in the migration.
3G. If you have chosen after reading the report to proceed with the migration, click Next. A Connect to Server dialog appears. Otherwise click Exit and you will need to work on resolving your issues.
3H. Specify the information below to connect to the SQL Azure database you created earlier in Steps 1 and 2. Then click Next.
• Server name: Specify the server name in the form SERVER.database.windows.net where SERVER is the generated database server name from Step 1H (b7e77waic7 in our example).
• Authentication: Specify Use a specific user ID and password
• User name: Specify an administrator username in the form USERNAME@SERVER where the administrator user name is the administration name you made up in Step 1F and the server name is the generated database server name from Step 1H (chefazured@b7e77waic7 in our example).
• Password: Specify the administrator password from Step 1F.
• Database: Select the Specify Database option and specify the name of the database you created in Step 2C (adventureworks in our example).
3I. On the Setup Target Server Connection dialog, confirm the database name is correct and click Next. When prompted to Execute script against destination server?, click Yes.
3J. Now sit back as the database is migrated. The running report will show you what is happening, including errors and remedial actions. Just how long this takes depends on the size of your database and the quality of your Internet connection.
3K. When the processing completed, you’ll see Processing finished on the bottom of the report. Review the report and decide if you think the migration was successful. You can save this report with the Save button if you wish. Then click Exit.
The last thing to do is confirm the database migration was successful. You can do this with SQL Server Management Studio (Step 4) or the Windows Azure Portal (Step 5).
Step 4: Review SQL Azure Database in SSMS (Optional)
In this step you will confirm your database was successful migrated to the cloud by accessing it with SQL Server Management Studio. You need the 2008 R2 edition of SSMS for this.
4A. Launch SQL Server Management Studio. The Connect to Server dialog appears.
4B. In the Connect to Server dialog, specify the connection information below:
• Server name: the generated server name from Step 1H.
• Authentication: SQL Server Authentication.
• Login: The login name from Step 1F.
• Password: the password from Step 1F.
• Remember password: check if you don’t want to have to re-enter this info.
• Options / Connect to Database: click the Options button and enter the database name from Step 2C under Connect to Database.
4C.Click Connect to connect to the database. If it fails, check the following:
• Did you create your database server?
• Did you create your database?
• Are you specifying the correct database server name in the right format?
• Are you specifying the correct admin username and password?
• Did you specify the database name on the Connection Properties tab?
• Are there any extraneous leading or trailing spaces in what you entered?
• Does your firewall prevent port 4133 (which SSMS required).
4D. Now inspect your database, start by expanding the SSMS outline at left and ensuring the tables you expect to be present are there.
4E. Perform some SELECT queries to ensure the data looks right. Do tables contain the number of rows you expect? Does table data look like you expect?
Step 5: Review SQL Azure Database in Portal (Optional)
This step is optional. If you would also like to see your data in the SQL Azure Portal, or if you were unable to use SSMS in Step 4, follow these steps:
5A. In the Windows Azure Management Portal, return to the Database area where you were working in Steps 1 and 2.
5B. Select your database on the outline and left and click the Manage button in the Database toolbar.
5C. In the sign-in dialog, specify the administrator username and password from Step 1F. Then click Log on.
5D. Click Tables at left and the database tables will be listed. Confirm the tables you expect are all there.
5E. Select a table you want to view. Click the Data icon to view its data, or the Design icon to view its design.
Concluding Remarks
Congratulations, you were magnificent! Vous avez été magnifiques! I am very proud of you.
I very much hope that your migration went well. If you ran into complications, if the souffle did not rise, do not depair. You must appreciate that not all database migrations are simple affairs. For help you can make avail of the excellent online guidance, support forums, community blogging, and perhaps consider obtaining help from an experienced consultant.
No comments:
Post a Comment