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 🙂