Since SQL Azure doesn’t support the database restore option, we have to create each db objects and execute them in Azure. So we have created our empty database in Azure and below steps to be performed for database objects.
User Login creation – We have to generate scripts for the required logins from on-premise database server. Each login should be associated with a password as there is no windows login available.
-- Create SQL Login template for SQLAzure Database
CREATE LOGIN [myLogin]
WITH PASSWORD = 'test001chk@sys_12345'
The password must be minimum 8 characters and should be complex i.e. mix of numeric, special character etc. Below is the script of login create generated in Azure
/* For security reasons the login is created disabled and with a random password. */
/****** Object: Login [myLogin] Script Date: 13-12-2013 08:47:44 ******/
CREATE LOGIN [myLogin] WITH PASSWORD=N'à¶''°÷½Û¾ë2§Ýe ²ëå''û}Èæ¿Lh`', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
ALTER LOGIN [myLogin] DISABLE
DB Object creation – We have to generate scripts in SSMS and that should be executed in Azure db.
Tables – we need to analyze the script as there some features not supported by Azure.
Data Compression is not allowed – If we have specified data compression, we have to remove option.
DATA_COMPRESSION = PAGE is not supported in current version of SQL Azure
Creating object on Filegroup option – We cannot create a table with index on a specified filegroup. We can specify filegroup for table, but not for index
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[Customers](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[CustomerName] [varchar](100) NULL,
[CustomerBusiness] [varchar](200) NULL,
[CustomerMSToolsUsage] [varchar](300) NULL,
[AdditionalInfo] [varchar](300) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
( [CustomerId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) -- ON [PRIMARY] is not allowed
) ON [PRIMARY]
SET ANSI_PADDING OFF
Filegroup reference and partitioning scheme error occurs when we mention the filegroup for index.
Each table should have clustered index. If it is not available, we have to add it before inserting data into table.
StoredProcedure,function, views- The current version of Azure doesn’t support cross database reference.
In above mentioned approach, we have to analyze each script manually and modify wherever needed. There is another way of generating the script by using a migration tool wizard.
We can use AzureMigrationWizard (codeplex) to automatically generate the scripts automatically with the appropriate syntax for Azure.
AzureMigrationWizard can be downloaded at http://sqlazuremw.codeplex.com/
Once downloaded the binaries, open the “Dependencies” config file of the tool. Please use the version 11 for sql server 2012.
<Dependency> <Assembly>Microsoft.SqlServer.Smo, Version=18.104.22.168,Culture=neutral, PublicKeyToken=89845dcd8080cc91</Assembly><Type>Microsoft.SqlServer.Management.Smo.Server</Type></Dependency>
Double click on SQLAzureMW application file.
Below are the changes done in the script by the tool.
Deprecated feature 'Table hint without WITH'. Automatically added WITH for you.
Adding clustered index ci_azure_fixup_dbo_Exxxxg on [dbo].[Exxxxg]. You may want to change this index.
Save the script generated by the tool which can be referred later.
We can also use the Deploy database to Azure option in SSMS. Please refer http://blogs.msdn.com/b/brunoterkaly/archive/2013/09/26/migrating-an-on-premises-sql-server-2012-database-to-windows-azure-sql-database.aspx for details.