Things need to keep in mind while Migrating a SQL2008 db to SQL Azure

Couple of days ago, I got a chance to make a  SQL Server 2005 db to  Azure compatible. During it, I noticed couple of points  were very useful to prevent  errors while publishing db in Azure. There may be some tools for this but still I would like to share my experience. 🙂

Here are few points which a new generation db should follow
1. Every table must have a clusteredIndex :  Azure looks for a clustedIndex in every table. So make sure  your table has a clustered Index in it.

2.Not For Replication property: Need to remove Not For Replication property from table column- mostly it is with identity column

3. Avoid MS_Description: Ms description is a old property which is now obsolete. My db was designed in sql 2000 and then migrated to SQL2005. So at the start it has been used and this property was very helpful to to understand the columns details . But for Azure you need to  to remove MS_Description property from every table column

4.Avoid Fillfactor : Need to remove Fillfactor property from Indexes as Azure does not support it.

5. Avoid String literal : For column name like select ‘NAME’ = charge_Name ChargeType etc
instead use select [NAME] = charge_Name ChargeType etc

6. Avoid textpr datatype : does not support textpr datatype, we will nee to remove it from all sps

7. Apply WITH hint: Need to use WITH hint with nolock etc like SELECT PK_VISIT_ID FROM m_visit WITH (NOLOCK)

Hope it is worth to share 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s