Partager via

SQL Azure Migration

  • Article

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'

GO 

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

 USE [master]

GO

 /* 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 ²ëå''û}Èæž¿‚LŽh`', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO 

ALTER LOGIN [myLogin] DISABLE

GO

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

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

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]

GO

SET ANSI_PADDING OFF

GO

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.

  1. If there is a table hint provided in the query the WITH keyword is mandatory. Deprecated feature 'Table hint without WITH' is not supported in this version of SQL Server.
  2. SYSTEM_USER, DBCC CHECKIDENT, SELECT INTO, SUSER_NAME, NEWSEQUENTIALID are not supported. We canot create object with these.

 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 https://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=11.0.0.0,
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.

DATA_COMPRESSION = PAGE is not supported in current version of SQL Azure

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 https://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.

Comments

  • Anonymous
    December 28, 2013
    The Generate Script wizard allows you to target SQL Azure Database if you go into Advanced options,and select "Script for database engine type"

  • Anonymous
    December 30, 2013
    Thanks Erik, wow I spent ages fixing up my scripts as I had a DB that was replicated previously and wanted to script it, if I had only known at the time I'd have been able to just script it out in the right format and I'd have been up and running without tons of effort... :( Ah well!

  • Anonymous
    December 30, 2013
    Thanks Erik, for another good option. It will definitely save time.The table scripts are generated based on the Azure, but we have to work with stored procedure, functions if there are any cross database reference or the features that are not supported.