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:

The following sections work through these steps.

To select your target database objects, follow these steps:

Figure 5.2. Scripting options

Clicking the Advanced button brings up the Advanced Scripting Options dialog shown in Figure 5-3. Follow these steps:

Complete the wizard with these steps:

Open the file you created, and let's take a quick look at the generated T-SQL. The following snippet from what you see shows the creation of three tables and three stored procedures, and a few INSERT statements that add rows of data to some of the tables. Except for the things you told the script-generation wizard to ignore, the following T-SQL looks like all other object creation T-SQL you typically deal with on a daily basis:

/****** Object:  Table [dbo].[Users]    Script Date: 03/31/2010 23:39:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
        [ID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](50) NULL,
     [NTUserName] [nvarchar](128) NULL,
     [Domain] [nvarchar](50) NOT NULL,
     [Intro] [nvarchar](100) NULL,
     [Title] [nvarchar](50) NOT NULL,
     [State] [nvarchar](10) NOT NULL,
     [Country] [nvarchar](100) NULL,
     [PWD] [varbinary](100) NULL,
     [rowguid] [uniqueidentifier] DEFAULT NEWSEQUENTIALID()
PRIMARY KEY CLUSTERED
(
     [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title],
    [State], [Country], [PWD])
VALUES (1, N'Herve Roggero', N'hroggero', N'PYNLOGIC',
N'Enterprise and Azure Architect; Speaker. Expert knowledge in C#. Prev. mgmt exp.',
N'Azure Architect', N'FL', N'US',
0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title],
    [State], [Country], [PWD])
VALUES (2, N'Jim Mullis', N'jmullis', N'PYNLOGIC',
N'Expert in software development. C++; Oracle; SQL Server DBA', N'', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title],

[State], [Country], [PWD])
VALUES (3, N'Scott Klein', N'sklein', N'',
N'Expert in software development. MVP SQL Server. Author. Speaker.',
N'Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)
SET IDENTITY_INSERT [dbo].[Users] OFF

/****** Object:  StoredProcedure [dbo].[proc_CreateProfile]
Script Date: 03/31/2010 23:39:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_CreateProfile]
    @uid [nvarchar](50),
    @pwd [nvarchar](50),
    @name [nvarchar](50),
    @title [nvarchar](50),
    @country [nvarchar](50),
    @state [nvarchar](20),
    @rowguid uniqueidentifier
WITH RECOMPILE, ENCRYPTION
AS
DECLARE @password varbinary(100)
SET @password = HASHBYTES('sha1', @pwd)

-- Make sure the UID is not already taken...
IF (Exists(SELECT TOP 1 * FROM Users WHERE NTUserName = @uid))
BEGIN
    RAISERROR(N'0x001 - User ID already in use', 16, 1)
END
ELSE
BEGIN
    INSERT INTO Users
         (Name, NTUserName, Domain, Intro, Title, State, Country, PWD, rowguid)
    VALUES
         (@name, @uid, '', '', @title, @state, @country, @password, @rowguid)
 END
GO

Notice that the script enables several options, such as ANSI_NULL and ANSI_PADDING. Then, the script creates the Users table. This table has an IDENTITY column as well as a rowguid column that uses the uniqueidentifier database. The rowguid column also has a default on it, which uses the NEWSEQUENTIALID() function to automatically generate new GUIDs. This table is created on the PRIMARY file group, followed by the setting of several table options via the WITH clause.

Further down in the script, several stored procedures are created, one of which is shown in the preceding code snippet. proc_CreateProfile is a standard stored procedure that accepts several input parameters and uses the WITH option to specify procedure options: in this case, RECOMPILE (to indicate that the database engine doesn't need to cache a plan for this procedure and to compile the procedure at runtime) and ENCRYPTION (indicating that SQL Server converts the text of this stored procedure to an obfuscated format).

Because you selected to script for SQL Server 2008, the script includes some syntax and statements that aren't supported in SQL Azure. Figure 5-5 shows some of the errors you see if you try to run the script as generated.

Figure 5.5. SQL Azure execution errors

Another problem is that SQL Azure doesn't support heap tables. A heap table is one without a clustered index. SQL Azure currently supports only clustered tables.

You need to make some changes for your script to run under SQL Azure. Here's what to do:

Appendix B discusses the need for these changes in detail. For now, here's a quick explanation:

A word about the final item in the list. The syntax for defining a clustered index looks like this:

CREATE TABLE [dbo].[UserDocs]
(
    [UserID] [int] NOT NULL,
    [DocID] [int] NOT NULL
PRIMARY KEY CLUSTERED
(
    [UserID], [DocID] ASC
)
)

One of the things the SQL Azure documentation suggests, and which is listed earlier, is to set the Convert UDDTs to Base Types property to True. This is because user-defined types aren't supported in SQL Azure.

After you make the changes just described to your SQL script, it should look like the following:

/****** Object:  Table [dbo].[Users]    Script Date: 03/31/2010 23:39:20 ******/
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
     [ID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](50) NULL,
     [NTUserName] [nvarchar](128) NULL,
     [Domain] [nvarchar](50) NOT NULL,
     [Intro] [nvarchar](100) NULL,
     [Title] [nvarchar](50) NOT NULL,
     [State] [nvarchar](10) NOT NULL,
     [Country] [nvarchar](100) NULL,
     [PWD] [varbinary](100) NULL,
     [rowguid] [uniqueidentifier] NULL
PRIMARY KEY CLUSTERED
(
     [ID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
GO
SET ANSI_PADDING OFF
GO

SET IDENTITY_INSERT [dbo].[Users] ON
INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title],
    [State], [Country], [PWD])

VALUES (1, N'Herve Roggero', N'hroggero', N'PYNLOGIC',
N'Enterprise and Azure Architect; Speaker. Expert knowledge in C#. Prev. mgmt exp.',
N'Azure Architect', N'FL', N'US',
0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title],
    [State], [Country], [PWD])
VALUES (2, N'Jim Mullis', N'jmullis', N'PYNLOGIC',
N'Expert in software development. C++; Oracle; SQL Server DBA', N'', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)

INSERT [dbo].[Users] ([ID], [Name], [NTUserName], [Domain], [Intro], [Title],
    [State], [Country], [PWD])
VALUES (3, N'Scott Klein', N'sklein', N'',
N'Expert in software development. MVP SQL Server. Author. Speaker.',
N'Architect', N'FL', N'US', 0xE8F97FBA9104D1EA5047948E6DFB67FACD9F5B73)
SET IDENTITY_INSERT [dbo].[Users] OFF

/****** Object:  StoredProcedure [dbo].[proc_CreateProfile]
Script Date: 03/31/2010 23:39:21 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[proc_CreateProfile]
    @uid [nvarchar](50),
    @pwd [nvarchar](50),
    @name [nvarchar](50),
    @title [nvarchar](50),
    @country [nvarchar](50),
    @state [nvarchar](20),
    @rowguid uniqueidentifier
WITH RECOMPILE
AS
DECLARE @password varbinary(100)
SET @password = HASHBYTES('sha1', @pwd)

-- Make sure the UID is not already taken...
IF (Exists(SELECT TOP 1 * FROM Users WHERE NTUserName = @uid))
BEGIN
    RAISERROR(N'0x001 - User ID already in use', 16, 1)
END
ELSE
BEGIN
    INSERT INTO Users
         (Name, NTUserName, Domain, Intro, Title, State, Country, PWD, rowguid)
    VALUES
         (@name, @uid, '', '', @title, @state, @country, @password, @rowguid)
 END
GO
Now that you've made the necessary corrections, you're ready to create your objects in a SQL Azure database.

You don't have a SQL Azure database to run the script against, so let's create one now:

You now have your script, a database, and a connection to that database. Click the Execute button. Your script should run and create the tables, procedures, and data in your SQL Azure Database.

The SQL Server Generate and Publish Script wizard is a great way to start understanding the required changes that need to be made when migrating to SQL Azure. With this foundation, let's discuss one of the other options, SQL Server Integration Services.

To create your project, follow these steps:

Figure 5.6. Creating a new SSIS project

You now see an SSIS package designer surface. This surface has several tabs along the top: Control Flow, Data Flow, Event Handlers, and Package Explorer, shown in Figure 5-7. This example uses the Control Flow and Data Flow tabs.

Figure 5.7. SSIS Designer

In Visual Studio, select View → Toolbox. The Toolbox contains a plethora of what are called tasks, which are control and data-flow elements that define units of work that are contained and preformed within a package. You use a few of these tasks to migrate the data from your local database to your SQL Azure database.

Let's clear any data that exists in the SQL Azure database so you can start with a clean slate. In SSMS, open a new query connecting to the TechBio database, and delete the data from the UserDocs, Users, and Docs tables by executing the following DELETE statements:

DELETE FROM UserDocs
DELETE FROM Docs
DELETE FROM Users

All three of those tables should now contain no data, as shown in Figure 5-8.

Figure 5.8. Checking for existing data

Let's start building an SSIS package to migrate your data. Follow these steps:

This is interesting, because you didn't get this error when configuring the Execute SQL task. The difference is that the two Connection Manager dialogs don't operate quite the same way. The Connection Manager dialog for the Execute SQL task let you type in the table name, whereas the Connection Manager dialog for the OLE DB Destination task required you to select from the list. But when you expanded the list, you received the error shown in Figure 5-12.

The fix is to use an ADO.NET destination instead of the OLE DB destination. To do this, continue as follows:

If you're new to SSIS, congratulations: you've just configured your first SSIS data flow. Your data flow should look like Figure 5-14—not very exciting, but useful nonetheless. If you aren't new to SSIS, you still deserve congratulations, because you've successfully configured a data flow to migrate data to the cloud.

Figure 5.13. ADO.NET Destination Editor with mappings

Figure 5.14. Data flow

Put down the root beer, though, because you aren't quite done. Continue with these steps:

When you're finished, your control flow task should look like Figure 5-15. The tasks aren't green (yet), but the flow should be similar.

Figure 5.15. Successful execution of an SSIS package

You're now ready to test your SSIS package. In Visual Studio, click the green arrow on the toolbar to begin package execution. Execution starts at the Clear Data task—which, as you recall, deletes all the data from the UserDocs, Users, and Docs tables. Execution next goes to the first data flow, which queries data from the local Docs table (source) and copies it to the TechBio database in the Docs table in SQL Azure (destination). Execution then goes the second and third data flow tasks.

When execution is complete, all the tasks are green, as shown in Figure 5-15, letting you know that they executed successfully. Any tasks that are yellow are currently executing. Red tasks are bad: that means an error occurred during the execution of the task, regardless of whether the task was in the control flow or data flow, and execution stops.

If your tasks are all green, you can go back to your root beer. Otherwise, the best place to start debugging is the Output window. All output, including errors, is written to this window. You can find errors easily by looking for any line that starts with Error: toward the end of the list.

Errors you receive may be SSIS specific or SQL Azure specific. For example, did you define your connections correctly? Microsoft makes testing connections very simple, and this doesn't mean the Test Connection button. The Source Editors dialog—regardless if whether it's an OLE DB or ADO.NET Editor—includes a Preview button that provides a small preview of your data, up to 200 rows. This ensures that at least your source connection works correctly.

When you have everything working and executing smoothly, in Visual Studio click the blue square button on the toolbar to stop execution. Go back to SSMS, and query the three tables in your SQL Azure instance to verify that data indeed copied successfully. As shown in Figure 5-16, you should see roughly 100 rows in the Users table, two rows in the Docs table, and two rows in the UserDocs table.

Figure 5.16. Viewing migrated data in SSMS

This example was simple; the source and destination tables were mirrors of each other, including column names and data types. This made data migration easy. However, in some cases the source and destination tables differ in column names and data types. There are tasks that help with this, such as the Derived Column, Data Conversion, and Lookup tasks. If you're using these tasks and are getting errors, start by looking at these tasks to make sure they aren't the source of data-truncation or data-conversion errors.

Again, this section isn't intended to be an SSIS primer. Great books about SSIS are available that focus on beginner topics all the way to advanced topics. Brian Knight is a SQL Server MVP who has written numerous books on SSIS; his books are highly recommended if you're looking for SSIS information and instruction.So far we have talked about SSIS and the SQL Server Generate and Publish Scripts wizard which both offer viable options for migrating your data, but with little differences. For example, SSIS doesn't migrate schema while the Scripts wizard does. Let's talk about the third tool, Bcp, which also provides a method for migrating data to SQL Azure.

The bcp utility has no GUI; it's a command prompt–driven utility. But don't let that intimidate you, especially given what you're using it for. It's very flexible and can seem a bit overwhelming, but it's quite simple. The basic syntax for the bcp utility is as follows:

bcp table direction filename -servername -username -password

where:

Let's get started by exporting the data from your source database.

Begin by copying data out of your local SQL instance. Open a command prompt, and type the command shown in Figure 5-17. Enter your own values for the server name, the target directory, and the username and password for your local server. (The password is blanked out in Figure 5-17.)

Figure 5.17. Using bcp to export data

Notice that in this example you're using the out keyword for the direction parameter. That's because you're copying data out of SQL Server.

The -n parameter performs the bulk-copy operation using the native database data types of the data. The -q parameter executes the SET QUOTED_IDENTIFIERS ON statement in the connection between the bcp utility and your SQL Server instance.

After you type in the command, press the Enter key to execute the bcp utility. In mere milliseconds, 105 rows are exported and copied to the user.dat file. Now, do the same for the Docs and UserDocs tables.

The next step is to copy the data into the cloud—specifically, to your SQL Azure TechBio database. The syntax for copying into a database is very similar to the syntax for copying data out. You use the in keyword and specify the server name and credentials for your SQL Azure database, as shown in Figure 5-18.

Figure 5.18. Uniqueidentifier data type error during bcp import

After you type in the command, press Enter to execute the bcp utility. Only one row is copied over, and then an error is generated, stating that an unexpected end-of-file (EOF) was encountered. This error isn't specific to SQL Azure; the bcp utility has issues with columns of the uniqueidentifier data type. You can find posts and blogs all over the Internet about this problem.

The solution is to execute the following T-SQL against the Users table in your SQL Azure database:

alter table users
drop column rowguid

The cool thing is that you don't need to re-export the data. You can re-execute the bcp import command. Do that, as shown in Figure 5-19, and all 105 rows are imported. Then, use the same syntax to import the Docs and UserDocs data.

Figure 5.19. Successful bcp import

Don't forget to put the rowguid column back on the Users table. You can do this by using the same syntax as before:

ALTER TABLE Users
ADD rowguid uniqeidentifier

Next, let's make this a little simpler and put the export and import together, so you aren't running the statements one at a time.

You can put the export and import processes together into a single file to make them easier to use. To do so, open Notepad, and type in the following, replacing the italicized information with the appropriate information for your environment:

bcp Techbio.dbo.Users out c:scottuser.dat -S Scott-PC -U sa -P Password -n -q
bcp Techbio.dbo.Docs out c:scottdocs.dat -S Scott-PC -U sa -P Password -n -q
bcp Techbio.dbo.UserDocs out c:scottuserdoc.dat -S Scott-PC -U sa -P Password -n -q

bcp Techbio.dbo.Users in c:scottuser.dat -S servername.database.windows.net
-U SQLScott@ servername -P #ackThis -n -q
bcp Techbio.dbo.docs in c:scottdocs.dat -S servername.database.windows.net
-U SQLScott@ servername -P #ackThis -n -q
bcp Techbio.dbo.userdocs in c:scottuserdoc.dat -S servername.database.windows.net
-U SQLScott@ servername -P #ackThis -n -q
Save the file as AzureBCP.cmd, and navigate to its location. Double-click the file to execute it. A command window appears, showing the results of the bcp commands that export and import the data.

As stated earlier, SQL Server BOL is full of information about how to use the bcp utility. This section is a brief introductory look at how to use this utility to move data from your local SQL Server instance to SQL Azure. The bcp utility is bulk-copy method of moving data. It lacks SSIS's ability to convert data from one data type to another, and SSIS's workflow components. But if all you're interested in is moving data from one table to a similar destination table, bcp is your best friend.