Programming4us
         
 
 
SQL Server

Migrating Databases and Data to SQL Azure (part 1) - Generate and Publish Scripts Wizard

12/25/2010 9:36:16 AM
So you want to move one or more of your applications and their databases to the cloud. It's a noble idea. More than likely, you're in the same category as countless others who are looking into moving applications into the cloud: you don't want to start from scratch. You'd rather migrate an existing application to the cloud, but you aren't sure about the steps necessary to do so, or the technologies available to help in the process. This section discusses three tools from Microsoft and come with SQL Server:
  • Generate and Publish Scripts Wizard

  • SQL Server Integration Services

  • Bcp utility

In addition to these three tools, we will also briefly mention a free utility found on CodePlex called the SQL Azure Migration Wizard which provides a wizard-driven interface to walk you through migrating your database and data to SQL Azure.

The examples in this article use SQL Server 2008 R2 Community Technology Preview (CTP), which at the time of this writing is available from Microsoft's MSDN site. These examples also work with SQL Server 2008, although some the screens may be a bit different.

You may wonder why the SQL Server Import and Export Wizard isn't listed here. The answer is that the SQL Server Import and Export Wizard isn't supported for SQL Azure yet. Microsoft is working on it. No timeframe has been given as to when the Import/Export Wizard will support SQL Azure, but support is definitely in the works.

The database you use in these examples is TechBio, which you can download from the Apress web site for this book. This sample database is a mini version of the TechBio database that is behind the TechBio application found in the download for this book.

1. Generate and Publish Scripts Wizard

The Generate and Publish Scripts Wizard is used to create T-SQL scripts for SQL Server databases and/or related objects within the selected database. You have probably used this wizard, so this section doesn't walk through it step by step; instead, the section briefly highlights a few steps in the wizard and points out the options necessary to effectively work with SQL Azure.

SQL Server 2008 R2 comes with the ability to script an on-premises database for the SQL Azure environment. Because many haven't moved to SQL Server 2008 R2, the examples in this section use the version prior to R2, which is the original release of SQL Server 2008.

One of the differences between SQL Server 2008 R2 and SQL Server 2008 (pertaining to object scripting) is a setting in the Advanced Scripting Options dialog as you go through the wizard. This dialog includes two properties you can set regarding the version of SQL Server for which you're scripting database objects: Script for Server Version and "Script for the database engine type." The Script for Server Version option lists the version of SQL Server that the Generate and Publish Scripts wizard supports, which ranges from SQL Server 2000 to SQL Server 2008 R2.

The "Script for the database engine type" property has two options you can choose from: "Stand-alone instance" and "SQL Azure database." The "SQL Azure database" option only works with the SQL Server 2008 R2 Server version. For example, if you set the Script for Server version to SQL Server 2008 (non R2) and then set the "Script for the database engine type" property to "SQL Azure database," the Script for Server version property value automatically changes to SQL Server 2008 R2.

The Generate and Publish Scripts Wizard does a really nice job of appropriately scripting objects for SQL Azure. The wizard checks for unsupported syntax and data types, and checks for primary keys on each table. Thus, the following example sets SQL for Server Version to SQL Server 2008 (non R2) for several reasons. First, many people aren't using SQL Server 2008 R2 and therefore don't have the option to script for SQL Azure. Second, this exercise shows you what steps are needed to get a script ready to run in SQL Azure.

1.1. Starting the Wizard

To start the Generate and Publish Scripts Wizard in SQL Server Management Studio (SSMS), open Object Explorer and expand the Databases node. Select a database, right-click it, and then select Generate Scripts from the context menu.

On the wizard's Introduction page for SQL Server 2008 R2, you're informed that you must follow four steps to complete this wizard:

  1. Select database objects.

  2. Specify scripting or publishing objects.

  3. Review selections.

  4. Generate scripts.

The following sections work through these steps.

1.2. Choosing Target Objects

To select your target database objects, follow these steps:

  1. On the Introduction page of the Generate and Publish Scripts Wizard, click Next.

  2. On the Choose Objects page (see Figure 5-1), select the "Select specific database objects" option, because for the purposes of this example, you simply want to select a few objects to migrate.

    Figure 1. Choosing objects to migrate into script form
  3. In the list of objects in Figure 1, expand the Tables and Stored Procedures nodes, and select the following objects:

    • Tables: Docs, UserDoc, and Users

    • Stored procedures: proc_CreateProfile, proc_GetDocument, and proc_UpdateDocFile

  4. Click Next on the Choose Objects page.

  5. On the Set Scripting Objects page, select the "Save to new query window" option shown in Figure 2, and then click the Advanced button.

Figure 2. Scripting options
Other -----------------
- SQL Azure : Security - Access Control
- SQL Server 2008 : Full-Text Searches (part 3) - Stop Lists
- SQL Server 2008 : Full-Text Searches (part 2)
- SQL Server 2008 : Full-Text Searches (part 1) - Search Phrase
- SQL Azure : Securing Your Data (part 3) - Certificates
- SQL Azure : Securing Your Data (part 2) - Hashing
- SQL Azure : Securing Your Data (part 1) - Encryption
- SQL Azure : Security - Overview
- Setting Up a Full-Text Index (part 4) - Using the Full-Text Indexing Wizard to Build Full-Text Indexes and Catalogs
- Setting Up a Full-Text Index (part 3) - Diagnostics
- Setting Up a Full-Text Index (part 2) - Full-Text Indexing of BLOBs and XML
- Setting Up a Full-Text Index (part 1) - Using T-SQL Commands to Build Full-Text Indexes and Catalogs
- Implementing SQL Server 2008 Full-Text Catalogs
- How SQL Server FTS Works
- SQL Azure : Connecting to a SQL Azure Database (part 2) - Connecting from the Entity Framework
- SQL Azure : Connecting to a SQL Azure Database (part 1) - Connecting Using ADO.NET
- SQL Azure : Creating Databases, Logins, and Users (part 2)
- SQL Azure : Creating Databases, Logins, and Users (part 1)
- SQL Azure : Azure Server Administration (part 3) - Databases
- SQL Azure : Azure Server Administration (part 2) - Firewall Settings
 
 
Most View
- Windows Phone 7 : Working with the Calendar - Deleting Appointments
- Programming Windows Azure : Table Operations - Using Partitioning
- jQuery 1.3 : Improving a basic form (part 7)
- Microsoft XNA Game Studio 3.0 : Adding Bread to Your Game (part 2) - Improving Programs Using Methods, Handling Collisions
- Windows 7 : Configuring the Fax Service
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- Windows Server 2003 : Configuring IAS for Use with VLANs
- SQL server 2012 : T-SQL Enhancements - The GROUPING SETS Operator (part 2) - Mixing and Matching, Handling NULL Values
- SQL Server Integration Services : Using bcp (part 2) - Fundamentals of Exporting and Importing Data
- SQL Server 2008 : Transparent Data Encryption
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS