Manually moving your SQL Server table structure to Azure

If you have a .NET application that uses SQL Server as a traditional on-premises installation and you want to move that database to Microsoft's Azure cloud service, there are many ways to proceed with the migration work.

Probably the easiest way is to use SQL Server Management Studio 2012 or later for the job. With Management Studio, you can simply right-click your database, then choose "Tasks", and finally "Deploy Database to Windows Azure SQL Database". Nice instructions for this are provided here.

However, sometimes, you might want to manually create your table structures in Azure, and this is perfectly fine for smaller applications. Say that you just want to add one more table to your application, and you have your local testing database already with this new table.

For instance, your table might look like this:

CREATE TABLE [dbo].[EventLog](
	[Id_EventLog] [int] IDENTITY(1,1) NOT NULL,
	[EventDate] [datetime] NULL,
	[EventType] [int] NULL,
	[MachineName] [nvarchar](100) NULL,
	[Data1] [nvarchar](1000) NULL,
	[Data2] [nvarchar](1000) NULL,
	[Data3] [nvarchar](1000) NULL,
	[Data4] [nvarchar](1000) NULL,
	[CallingMethod] [nvarchar](100) NULL,
	[CallStack] [nvarchar](4000) NULL,
 CONSTRAINT [PK_OperationLog] PRIMARY KEY CLUSTERED 
(
	[Id_EventLog] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

However, if you try to run this CREATE TABLE statement against your SQL Azure database, you will get the following error message:

Msg 40514, Level 16, State 1, Line 1
'Filegroup reference and partitioning scheme' is not
supported in this version of SQL Server.

Although the Management Studio's own utilities and other tools like SQL Database Migration Wizard can handle these kind of errors automatically, you can also do it manually by hand.

Shortly put, file groups or advanced index options and constraints are not (yet) supported on SQL Azure. Thus, it's easiest to simply strip out these things, which, in small and simple applications, you won't even need.

Thus, by taking the bare essentials from the above CREATE TABLE statement, you'd get this:

CREATE TABLE [dbo].[EventLog](
	[Id_EventLog] [int] IDENTITY(1,1) NOT NULL,
	[EventDate] [datetime] NULL,
	[EventType] [int] NULL,
	[MachineName] [nvarchar](100) NULL,
	[Data1] [nvarchar](1000) NULL,
	[Data2] [nvarchar](1000) NULL,
	[Data3] [nvarchar](1000) NULL,
	[Data4] [nvarchar](1000) NULL,
	[CallingMethod] [nvarchar](100) NULL,
	[CallStack] [nvarchar](4000) NULL,
 CONSTRAINT [PK_OperationLog] PRIMARY KEY CLUSTERED 
(
	[Id_EventLog] ASC
))

After this edit, when you try to run your SQL query against Azure, you get the results you were after:

Command(s) completed successfully.

Notice how the above SQL statement needs to end with double parenthesis. These are easy to miss.

Good luck!